Use data from Google BigQuery with Connected Sheets

The following sections describe how to use data from Google BigQuery with Connected Sheets:

Limits when using BigQuery with Connected Sheets
Connect Google Sheets to Google BigQuery
Connect AppSheet to Google Sheets

See also the Connected Sheets GA blog post.

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 Use 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:

For the current data extraction limits from BigQuery into Google Sheets using Connected Sheets, refer to files you can store in Google Drive.
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.

Connect Google Sheets to Google BigQuery

Connect Google Sheet to Google BigQuery as described in Use Connected Sheets in your organization. For additional information, see Get started with BigQuery data in Google Sheets.

When you connect a Google Sheet to a BigQuery table in your Google account, you end up with a tab/sheet with a database icon. For example:

Connected sheet with database icon

However, this table can't be connected to from within AppSheet. If you attempt to do so, an error similar to the following will be displayed:

Error in AppSheet app when connecting directly to a connected sheet

To connect Google Sheets to Google BigQuery, you'll need to do the following:

Set up a refresh schedule in your Google Sheet, as described in Schedule a refresh
Pull data into an extract tab/sheet, as described in Pull data into an extract

Connect AppSheet to Google Sheets

AppSheet can now use the extract tab/sheet as a Google Sheets data source. Remember, you should set the Are updates allowed? option to Read-Only in AppSheet as there is no ability to write back to BigQuery with this configuration. For more information, see Tables: The Essentials.

Set the Table as Read-Only.

If you configured a refresh schedule, the following shows an example of how the refresh options display:

Configured refresh options

Each time the schedule runs it will refresh both of the two tabs and 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.

Was this helpful?

How can we improve it?