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.