BigQuery - Integrate Connected Sheets

For those opting to use a cloud service like BigQuery as a data warehouse, it is often a goal to migrate data from spreadsheets to a database. Consequently, pairing a data warehouse and a spreadsheet may seem redundant.

However, connecting a Google Sheet to BigQuery allows for a seamless recurring "refresh" of spreadsheet data, since the source is a view or table in BigQuery.

Google Sheets supports BigQuery integration in two ways −

• Connecting directly to a table.
• Connecting to the result of a custom query.

Unlike BigQuery where available external data sources are presented in a drop-down menu, finding data sources in Google Sheets requires a bit of digging.

Connecting a BigQuery Resource to Google Sheets

To connect a BigQuery resource to Google Sheets, follow the steps given below −

• Open a new Google Sheet
• Click on the "data" tab
• Under data, navigate to data connections
• Choose an existing dataset
• Find your desired table
• Alternatively, write a custom query
• Click connect

The sheet should change from a standard spreadsheet into a UI that resembles a hybrid between a spreadsheet and SQL table.

How to Ensure Synchronization and Schedule a Refresh?

While following these steps ensures the connection is live, stopping here will not ensure future synchronization.

• To automatically update the sheet as its associated resource is updated, you must schedule a refresh.
• You can schedule a refresh by navigating to "Connection Settings."
• Like configuring a scheduled query, scheduling a refresh is simple. Choose your refresh interval, start time and end time.

Once configured, the sheet will now update on that schedule, assuming data is available in the BigQuery table.