AppSheet does not currently have a direct, in-platform connection to BigQuery. However, customers who desire to see BigQuery data inside of their AppSheet apps can follow the steps outlined in this G Suite blog post which we also outline below.
1. There is a 10,000 row limit on extracting data from BigQuery into Google Sheets.
2. This will work for read-only use cases from BigQuery into a Google Sheet and then onward to an AppSheet App. Any data that is written by the App to the specific tab in the sheet which is connected to Big Query will be overwritten by the next BigQuery refresh, which would cause data loss for the app user.
CONNECT GOOGLE SHEETS TO GOOGLE BIGQUERY
- Google Sheets now has a new data connector for BigQuery. General usage of this feature is outlined in this blog post.
- A detailed step by step guide is available here.
- Additional information and row limits is here.
- Currently available to G Suite Enterprise and G Suite Enterprise for Education customers.
- Not available to G Suite Basic, G Suite Business, G Suite for Education, and G Suite for Nonprofits customers
When you harness up a Google Sheet to a BigQuery table in your Google account, you end up with a very special type of tab/sheet with a “database” icon:
However, this table cannot be connected to from within AppSheet, and the following type of error will be displayed:
What you will need to do is set up a refresh schedule in your Google Sheet. In the following screenshot:
- Click the three dots next to (1), which brings up
- Refresh options as well as
- A scheduler for the refresh
You will also need to set up an extraction of the tab/sheet - in our example we have a special tab called “Schedules” and another tab called “Extract 1” - this second one is the data that you can connect to with AppSheet. The following screenshot shows:
- Click “Extract”
- Specify a new or existing sheet as your extraction target
- Click "Create".
CONNECT APPSHEET TO GOOGLE SHEETS
If you have configured all of the above, AppSheet can now use your "extract" tab/sheet as a Google Sheets Data Source:
Remember, you should mark your connection as "read only" in Appsheet as there is no ability to write back to BigQuery with this configuration:
And if you have configured a schedule, e.g.:
Each time the schedule runs it will refresh both of the two tabs. Each time, BigQuery will overwrite any changes made to the Extract table.
This is one of the reasons why this connection from Appsheet should be considered a Read-Only connection.
The BigQuery to Sheets connection currently has a row limit of 10,000 rows. You may want to consider writing specific BigQuery queries to achieve one of the following characteristics:
- A row limiter by date to less than 10k rows.
- A row limiter by some other dimension (geospatial, product, region, etc) to less than 10k rows.
- An aggregate query which rolls up information to less than 10k rows.