Use data from Google BigQuery with Connected Sheets

The following sections describe how to use data from Google BigQuery with Connected 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:

  • A maximum of 10,000 rows of data can be extracted from BigQuery into Google Sheets.
    (Note that the maximum enforced by AppSheet is lower than the maximum enforced by BiqQuery with Connected Sheets, which is currently 25,000).
  • 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

Google Sheets now has a new data connector for BigQuery. 

Image showing how to connect to BigQuery in a spreadsheet

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:

Connected sheet with database icon

However, this table cannot 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

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.

Set up a refresh schedule in your Google Sheet

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 tab 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.

Set up an extraction target in Google Sheet

Connect AppSheet to Google Sheets

AppSheet can now use your extract tab/sheet as a Google Sheets Data Source:

Data > Tables pane showing the Extract1 table being used in the AppSheet app

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:

Set the Table as Read-Only.

And if you have configured a schedule, an example of the display is as follows:

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.

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:

  1. A row limiter by date to less than 10,000 rows.
  2. A row limiter by some other dimension (such as, geospatial, product, region) to less than 10,000 rows.
  3. An aggregate query which rolls up information to less than 10,000 rows.
Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false
false