Summary
This article shows a straightforward way to extract data from Google Sheets and load it to BigQuery. No code! No schema declaration!
Target audience
Data engineers who want to run ad-hoc data ingestion from spreadsheets to Google BigQuery without writing code.
Outline
- Upload your data to Google Sheets and get the URI.
- Create a new table in BigQuery and paste the URI.
1. Upload your data to Google Sheets and get the URI.
(I know you don’t need me to teach you how to do this. So just skip this step.)
2. Create a new table in BigQuery and paste the URI.
Be careful of the file format. I selected Google Sheet as the format but did not skip header row. Let me show you what happened to me.
If your table is nicely organized in a simple format, you can just select csv as the format with schema auto-detect on.
You don’t need to do anything extra to get the column name row correct.
But if you need to select a sheet range, like the image below, you have to use Google Sheet instead and change advanced settings.
Make sure you have keyed in the right sheet name, sheet range and skipped header rows.
Please make sure you have all the right access, otherwise you may face some obstacles. You can share this spreadsheet with your business stakeholders and nicely ask them to upload data here instead of sending emails every day.
This article only can perform one-time data ingestion. If your spreadsheet is regularly being updated, you may need other tools. For a small table, you can even use a serverless cloud function without managing any containers. If this sounds interesting to you, please read my article: Serverless data ingestion from Google Sheets to BigQuery using Google Cloud Function.
