The following sections describe how to use data from Google BigQuery with Connected Sheets:
See also the Connected Sheets GA blog post.
Note: You can use data from Google BigQuery with the BigQuery data source or with Connected Sheets (as described in this topic). To compare the two options, see Using data from Google BigQuery with AppSheet.
If you have a lightweight use case and need to have production support right away, or have a lower license than the Enterprise Standard plan, use Connected Sheets, as described in this topic.
Limits when using BigQuery with Connected Sheets
AppSheet enforces the following limits when using BigQuery with Connected Sheets:
A maximum of 10,000 rows of data can be extracted from BigQuery into Google Sheets.
This procedure works for read-only use cases only from BigQuery into a Google Sheet and then to an AppSheet app. Any data that is written by the app to the specific tab in the Sheet that is connected to Big Query will be overwritten by the next BigQuery refresh, which would result in data loss for the app user.
Connecting 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 connect 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 an error similar to the following will be displayed:
What you will need to do is set up a refresh schedule in your Google Sheet.
As highlighted in the figure, click (1) the More menu (three dots) to display (2) Refresh options and a (3) 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.
As highlighted in the figure, click (1) Extract, (2) select New sheet or Existing sheet as your extraction target, and (3) click Create.
Connecting 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.