Important and valuable data is often kept in Google Sheets. Learn how to make use of this data from within BigQuery in this post.
Google sheets is a very useful source of data for BigQuery, particularly for any lookup or reference data that changes semi frequently which business users keep updated. A good example of this is if abbreviations or shortened versions are used in Google Analytics UTM parameters. Creating reports and analysis based of the UTM codes would be greatly more understandable if a full description were used instead of any abbreviations that are actually passed in the URL.
The analyst in this context may not have the full context of what all the abbreviations mean when analysing the data or creating the reports. So having a lookup table that can be used to get the full description would be very useful. This lookup information should be something that the marketing team maintains as they create new campaigns to drive traffic.
Walkthrough
STEP 1: Get some data in Google Sheets to be queried from BigQuery.
NB: 1 “table” of data in Google Sheets can be referenced from BigQuery at a time.
STEP 2: In the BigQuery user interface, click the 3 dots on the dataset that the Google Sheets data will be added into then click create table.
In the below example the Google Sheets data will be added to ‘marquinsmith_dot_com’ dataset.
STEP 3: Enter the necessary details to specify what data BigQuery will reference from Google Sheets.
Create table from: Drive
Select Drive URI: {Spreadsheet URL}
File format: Google Sheet
Sheet range: The sheet name where the data is. Include a range if the data does not start in cell ‘A1’.
Table: Name of the table as it will appear in BigQuery.
Schema: Enter the column names and types of each column. Schema auto detection does not work well for Google Sheets, it is better to specify them manually.
The Advanced options section is where the number of rows to skip can be specified. If there are header rows in Google Sheets this is where it can be set that these are not read into the table in BigQuery.
Once all the details have been input, click the “Create table” button to actually create the external table in BigQuery that uses the Google sheet as its data source.
STEP 4: Once the table has been created then it can be queried from within BigQuery just like any other table
And there you have it. BigQuery is now set up to query directly from Google Sheets.
When the data in Google Sheets changes, any subsequent query against that table in BigQuery will represent the updated data.