Google Sheets Integration With BigQuery: Building Tables Like a Carpenter

Google Drive and BigQuery have been around forever in internet years, but recently Google announced an integration between the two. If you’re not sure why this is exciting, read on to see the top three reasons why Sheets together with BigQuery is a match made in heaven. If you are already as excited as we are… also read on, we will be looking at a practical use case for e-commerce retailers and going through some set up instructions.

What is BigQuery?

BigQuery is Google’s cloud hosted database solution using a variant of SQL (structured query language). It allows you to leverage Google’s infrastructure and database architecture to retrieve data incredibly quickly. One of the great benefits of BigQuery is that Google Analytics 360 customers can link with their GA accounts to retrieve analytics data.

Here is a look at the interface:

(Don’t worry if your SQL is a bit rusty, Google have plenty of documentation and there is a link at the end of the this article to get you started.)

Why BigQuery and Google Sheets go together like salt and vinegar:

1. Once you link your sheet to BigQuery, changes to the sheet are instantly available.

2. Creating the link is a matter of a few clicks.

3. Google App Scripts give you almost unlimited flexibility.

Let's take a look at each of these in turn:

1. Changes instantly available.

This combines the sheer data crunching power of a data warehouse with the flexibility of a dynamically changing table. An important caveat: this only applies to row changes; columns cannot change as you have to set the table schema when you create it.

2. Creating the link.

The entire integration process can be done through a web interface in a matter of minutes. We will look at this process later on.

3. Unlimited flexibility (almost).

If you are proficient with AppScripts, you can programmatically interact with your sheet data by attaching Scripts to your sheet. Or you can use the BigQuery API through sheets to schedule and run queries.

To enable the BigQuery API in Sheets, create a new AppScript, select the resources menu > Advanced Services and enable the API through the interface (seen in the image above). Finally, follow the link to the ‘Google Developers Console’ and enable the BigQuery API.

Use Case

Say you are a retailer and you want to match your back-end data containing product SKUs with GA data for the product sites to see which products perform better online or offline. If you have your SKU list in Sheets and dynamically update the content regularly, you can link that sheet to BigQuery. From then on, whenever the sheet gets updated, those changes will immediately be available in BigQuery. This allows you to run queries on your GA data and seamlessly join them up with the product information.

Guide: How to Use a Google Sheet as a Table in BigQuery

In the BigQuery web interface, select your project and create a new table. As your source data, select Google Sheets. Now get a shareable link for the table you want to link and use it as your data source.

Next enter your table schema. This is simply a road-map to how your table is structured; each field describes one column - make sure to enter your columns in the order they appear in the sheet and be aware that once you create your schema, you can’t change it, so don’t add or delete columns later. Finally press ‘Create Table’ and you are done.

With your table linked to BigQuery, you can immediately start querying your data together with any other tables you have available.

Combining the flexibility of Google Sheets with BigQuery’s data handling capability opens up a lot of user flexibility. Anyone who already uses Analytics 360 and has data in Sheets should seriously consider bringing together their data sets. To help you get started with the SQL syntax of BigQuery, Google have documentation available; one of the most useful resources is the BigQuery Cookbook. Have a read now and start playing with your data - let us know if you need any help!

To read this blog written by Adrian Hirschmuller on the Periscopix website, please click here.