Google Sheets to BigQuery: a step-by-step guide

When I first started using Google Sheets, I thought it was a joke.

My first experience with it back in 2010 was in high school when I was in my Intro to Accounting class. My exact thoughts were, “There’s no way Google can pack as much functionality into a web browser as Microsoft does into their desktop application.”

At the time, I think I was right. It seemed like Google Sheets was something that a Googler had released at 4:30PM on a Friday and seemingly never came back to. Formula coverage was sparse, performance with middling amounts of data was poor and seemingly unwarranted errors cropped up all the time. Not to mention the complete lack of Pivot Tables.

Fast forward to 2021 and the picture couldn’t be more different. Google’s GSuite customer base has grown past 6 million customers, and contributes over $1B in revenue to the advertising giant’s bottom line. 

Easily shareable Google Sheets have been replacing Excel workbooks in the workplace at a rapid clip. While they still are not the dominant spreadsheet application in use today, they are a force to be reckoned with as companies move away from emailing different versions of their files back and forth to sharing cloud-based documents.

However, despite Sheets’ ubiquity, there exists a key integration that is often overlooked by skilled data analysts and analytics practitioners alike: the ability to query Google Sheets data in BigQuery.

Why would you want to do this? There are several reasons:

• BigQuery has become your company’s single source of truth, and you need to get your data into it for it to be useful to others.
• You want to blend or enrich the data in BigQuery with data you have in your Google Sheet.
• You want to interact with the data you have in your Google Sheet using SQL.

In this guide, I’ll show you how you can take your data and turn it into a table inside BigQuery so all of the above are possible.

Step 1: Ensure you have the correct permissions in BigQuery

In BigQuery, you must have at least the following permissions in your Google Cloud project to begin: 

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

You can consult this guide if you need additional assistance.

Step 2: Create a dataset in your BigQuery project

You’ll do this by navigating to BigQuery in the Google Cloud interface and selecting your project. 

Then, you’ll create a new dataset using the defaults for your project. Think of datasets as folders for the tables you’ll be creating.

Step 3: Create the table in your dataset

Now that we have a dataset, we can create a table inside our dataset. Click the ‘plus’ button to create a new table.

For the Google Sheet that we’re trying to import, let’s take some sample marketing campaign data that we want to import as an example. Hypothetically, it could look something like this:

Psst! Did you know that you can use Supermetrics for Google Sheets to easily pull cross-channel marketing data like this into your spreadsheet?

For this kind of table, you’ll want to configure the table with the following options:

Create table from: Set this option to “Drive”, indicating Google Drive.

Select Drive URI: This is simply the link to your Google Sheet. Copy and paste it into the box.

File format: Set this to “Google Sheet”

Sheet range (Optional): This field says optional, but it’s really pretty handy. I like to manually specify my data range so there aren’t any surprises. 

Using the Google Sheets range syntax, you’ll want to specify the range of your data. Since I expect this data to grow over time, I’m only going to specify the columns, and let Google find the maximum row value.

In this example, I’ve input ‘Campaign_Data!A2:D’. The general format is as follows:

[Sheet name] ! [Data range]

Don’t be afraid to test your data range in your Google Sheet. You can always practice by using the following formula to select data in your Google Sheet: ={Campaign_Data!A2:D}

Table name: You can pick whatever table name you like. It must, of course, follow BigQuery’s naming guidelines for tables, which you can find here.

Schema: This is where you tell BigQuery what data types you have in your Google Sheet. You can of course check the box to auto detect these values, but I prefer to be explicit here. These are a few common data types that you can expect to see pop up:

• date: self-explanatory. Anything in a date format in Google Sheets will be recognized as a date here
• campaign_name: I classified this as a STRING – strings in BigQuery are used to hold text values
• impressions: This is an integer. Integers are countable, whole numbers with no decimal values.
• amount_spent: This is going to be a FLOAT, short for floating-point number. This just means that there can be a decimal value at the end.

You can read more about data types in BigQuery here.

Click “Create Table” and you’re ready to go.

Step 4: Query your data

Technically, what you’ve just created is called an “External Table” in BigQuery. It’s something that looks like and feels like a BigQuery table, but isn’t actually a table. It is a table that queries data that lives outside of BigQuery. 

Let’s run some queries against it.

If we want to see what our average CPM by campaign is, we could write the following query:

Conclusion

There are a few pretty interesting side-effects of setting up your data this way:

• BigQuery treats this Google Sheet as an external data source. That means any updates to the sheet are immediately represented in your BigQuery query results.
• Because we specified an unbounded table range (‘Campaign_Data!A2:D’), any new data added to the Google Sheet will be immediately accessible in BigQuery.
• If we add any columns to our Google Sheet, it’s easy to modify the BigQuery schema and add those fields to our BigQuery table.

When I first started using Google Sheets over 10 years ago, I had no idea how much they’d improve my life as an analytics engineer. I’m happy to say that they’ve matured enough for me to consider them a first-class member of the analytics ecosystem.

And psst! If you’d also like to move data directly from platforms like Facebook, Salesforce, LinkedIn, and many more into Google BigQuery, you can start your free 14-day trial of Supermertics for BigQuery today.