Google BigQuery is a Platform as a Service offering. It is commonly used by performance marketers to store their marketing information. Querying Google Sheets data in BigQuery helps access it.
Connecting Google Sheets to BigQuery has become important for more than one reason. Here are a few:
- Data in BigQuery has become a source of data validation for most businesses
- To add additional/historical data from Google Sheets to BigQuery
- It enables interaction with the data Google Sheet using SQL
So, here we understand how to query Google sheets in BigQuery in 4 simple steps
1. Get the necessary permissions in BigQuery
The following permissions are necessary for your Google Cloud project before you start:
2. Create a dataset in your BigQuery project
Datasets serve as folders for creating tables. So you need to create them first. Here’s how you do it.
Go to BigQuery in the Google Cloud interface and select your project.
Create a new dataset.
3. Create a table in the dataset
Click the ‘+’ to create a new table.
Create the table from ‘Drive’ using the URL and specifying the file format as Google Sheets. If you wish to specify the range, use Google Sheets syntax, say ‘Sheet3!A3:H19’.
Name the table in line with BigQuery guidelines.
You may manually specify the Schema or let BigQuery auto-detect it.
Now click on ‘Create Table’ to create an external table for BigQuery to run queries on.
4. Run Query
Once you create an external table, you can run queries on it.
Google Sheet here becomes an external data source to BigQuery. Any changes on Google Sheet reflect in BigQuery query results.
You may specify a bounded table specify the end row number in sheet range (‘Sheet3!A2:G99’) or skip it for an unbounded table. An unbounded table range looks like ‘Sheet3!A2:G’. Specifying an unbounded range makes any new data added to the sheet accessible in BigQuery.
To query Google sheets BigQuery, you need to get the necessary permissions, create a dataset and a table in BigQuery.
Windsor.ai automates the streaming of all your marketing data in a few clicks. Simply choose the platforms, tools you would like to connect and authenticate them. Now your data is connected. After choosing the synchronization interval your data will start appearing in BigQuery. Try it for free to understand how it works.