Suppose you’ve got new data coming in to Google Sheets each day. You want to automatically push this new data to BigQuery each day, appending the new data to a table. How do you do it? I’ll show you.
In this post, I’ll show you one way to schedule an automatic transfer of data from Google Sheets to BigQuery. In our example, we’ll have data in Google Sheets representing campaign performance stats from “yesterday”. Each day, the stats in our Google sheet will update with new data from the previous day. And each day, BigQuery will automatically pull that day’s data from the Google sheet and append it to a table that will store the data of all dates.
Table of Contents
- Prepare
- Create a table to retrieve your data from Google Sheets
- Create a table to store your historical data
- Create a scheduled query to append your Google Sheets data to your BigQuery table
- Authorize the data transfer
Prepare
Before we get started, let’s make sure we’ve got everything in place.
- Set up your Google Sheets data. Set up your data using the same column names, data types, etc. as will be used by your BigQuery table. Here’s an example Google Sheet with data.
- Create a Google Cloud project.
- Enable billing.
- Enable the BigQuery API.
- Enable the BigQuery Data Transfer API.
- Create a dataset.
Create a Table to Retrieve Your Data from Google Sheets
All right. Let’s get started!
The first table we’re going to create will be a table that is connected directly to the Google sheet with our data.
Navigate to your BigQuery dataset and open the dataset’s menu by clicking on the three vertical dots next to its name.
Select “Create table”.
Under “Source”, from the “Create table from” menu, choose “Drive”.
Into the field labeled “Select Drive URI”, paste the URL of your Google spreadsheet.
Choose the file format “Google Sheet”.
Enter the sheet range. To make sure that all rows of data are always transferred, use the column names without row numbers in your range, e.g., campaigns!A:F.
Under the “Destination” section, in the “Table” field, enter the name of your table, e.g., “campaigns_daily”.
Under the “Schema” section, check the “Auto detect” box.
Under ‘Advanced options”, in the “Header rows to skip” field, enter “1” (because the first row in our sheet is the header row).
Click “Create table”.
Create a Table to Store Your Historical Data
Next, we need to create a table that will store all of our historical data. We’ll call this our “destination table”.
Before we create our destination table, let’s take note of the schema of our first table.
Click on the first table’s name. Then, make sure you’re viewing the “schema” tab.
Next to the name of your dataset, once again click the three dots to open the menu. Click “Create table”.
Under “Source”, in the “Create table from” field, select “Empty table”.
Under “Destination”, in the “Table” field, give your table a name, e.g., “campaigns”.
Specify the schema for your destination table, making sure to use exactly the same schema as your first table.
Click “Create table”.
Create a Scheduled Query to Append Your Google Sheets Data to Your BigQuery Table
Click the blue + button to compose a new query.
Write a query that will retrieve all of the data and only the data that you want to transfer. Note that the table connected to Google Sheets may contain empty rows, i.e., rows with no data. So, you’ll probably want to write your query with some kind of WHERE clause. For our example, we’ll select only those rows where the date is yesterday’s date.
SELECT * FROM `stats.campaigns_daily`
WHERE `date` = DATE_SUB(CURRENT_DATE('America/Chicago'), INTERVAL 1 DAY)You may want to run your query to make sure it returns what you expect.
From the menu above the query editor, click “Schedule”. Then click “Create new scheduled query”.
Name the scheduled query. For example, I’ll name our example “Append daily campaign stats to campaigns table”.
Set the frequency. For our example, I’ll set the frequency to “Days”. I’ll set the time to 22:00 UTC. (Note that you’ll use military time, of course, and that the time is UTC time.)
Under the section “Destination for query results”, check the box to “Set a destination table for query results”.
Select your dataset by typing its name and then selecting it.
In the “Table Id” field, enter the name of your destination table, i.e., the name of the table that will store all of your data.
Under “Destination table write preference”, select “Append to table”.
Click “Save”.
Authorize the Data Transfer
Upon clicking “Save”, you’ll be asked to authorize the data transfer service. Select the account you want to use for authorization. Then, click “Allow” to authorize.
You’re in Business!
Congratulations! You’ve just set up a daily transfer of data from Google Sheets to BigQuery. Now, each day, your BigQuery table that’s connected directly to your Google sheet will contain the data that’s currently available in that Google sheet. And your scheduled query will then retrieve that data and append to your destination table where it will be stored permanently.
Well done!