1-click sync from Google Sheets to BigQuery Tables
TL;DR: Keep your spreadsheets synchronized with BigQuery using my (Sheet and Apps Script) templates.
Does this seem familiar?
- You want to build a data visualization as a proof-of-concept
- The data are in a spreadsheet coming from some process that you don’t control — and it might be updated.
- You’d prefer to use a database for future-proofing your work because if this proof-of-concept goes well, you’ll likely be doing data integration later.
- You want cheap, quick results.
I was facing this problem (again) recently, and I cobbled together a solution to make working with data from Google Sheets available in BigQuery.
Please reuse my work.
Define source Sheets and destination BigQuery tables
- Make a copy of the [template] table_loader Sheet. Start with the “load_status” tab. It looks like this:
Column A: it’s a URL to another Sheet containing data you want to sync to BigQuery. You need to have read permission on this other Sheet.
Columns B-D: Google Cloud (project ID, dataset ID, and table name) you want to sync to. Dataset and table will be created, as needed. You need to have BigQuery Admin permission on the project.
Column E: Whether to append any existing table (TRUE) or to overwrite (FALSE). This is useful if you want to define several sheets that need to be appended together (set the first row to FALSE to (re)create the table, then subsequent rows to TRUE)
Column F: Timestamp of the last load, if any. Or an error message.
Column G: Table schema (explained below)
Define a BigQuery table schema
Look at the tab called “schema_editor”. You can use this to define a number of columns, with the datatype. I’ve given some example values in the template to get you started, and a corresponding example spreadsheet. These column definitions need to match the Sheet columns from the corresponding file (first tab only) linked on the “load_status” tab.
After you define the columns, grab the generated value from cell F1, and copy it into column G of the “load status” tab. Use “paste as plain text”.
Any time you update the source Sheet, you’ll need to redo this process.
Load the data
Go to the “load_status” tab. Look at the menu of your copy of [template] table_loader. There is a custom menu called “Sheets to BigQuery”, and a single item inside “Upload”.
Click it.
If all goes well, you’ll see column F update as data are loaded into BigQuery.
Oh, and it handles updates. Just click “upload” again and it will re-sync.
That’s all :)
Attribution
The code for this template came from these other two blog posts. I didn’t write it myself, I only integrated it.
Create BigQuery tables with Google Sheets and Apps Script
Apps Script tutorial: Upload to a database (Sheets => BigQuery)
