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:

• 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 BigQuery with Connected Sheets, which is currently 50,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. 

• General usage of this feature is outlined in this blog post.
• For a detailed step-by-step guide, see Get started with BigQuery data in Google Sheets.
• For additional information and row limits, see Connecting BigQuery and Google Sheets to help with hefty data analysis.
• 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. If you attempt to do so, 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 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.

Connect AppSheet to Google Sheets

AppSheet can now use your 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:

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

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.