How to Connect Google Sheets data in BigQuery

  • sree 

Google BigQuery is a Platform as a Service offering. It is commonly used by performance marketers to store their marketing information. Connecting Google Sheets data to 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:

  • tables.create
  • tables.getData
  • jobs.create

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.

Google Cloud Platform Select Project

Google Cloud Platform Select Project

Source: https://cloud.google.com/blog/products/data-analytics/how-to-transfer-bigquery-tables-between-locations-with-cloud-composer

Create a new dataset.

Create dataset in BigQuery

Create dataset in BigQuery

Source: https://cloud.google.com/bigquery/docs/quickstarts/quickstart-web-ui

3. Create a table in the dataset

Click the ‘+’ to create a new table.

Create table BigQuery

Create table BigQuery

Source: https://stackoverflow.com/questions/58861264/create-table-schema-and-load-data-in-bigquery-table-using-source-google-drive

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’.

Create table BigQuery 2

Create table BigQuery 2

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.

RunQuery BigQuery

RunQuery BigQuery

Source: https://cloud.google.com/bigquery/docs/bigquery-web-ui

 

Summary

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.