How to query Google Sheets data in BigQuery: a step-by-step guide

Evan Kaeding
More from BigQuery

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.

How to create a dataset in BigQuery
Create an external dataset in Google Sheets in BigQuery

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.

Create a table inside an external dataset in Google BigQuery

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:

Campaign data example in Google Sheets

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:

Configure new table in Google BigQuery

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

Turn your marketing data into opportunity

We streamline your marketing data so you can focus on the insights.

SecurityTerms of ServicePrivacy PolicyCookie Policy
Cookie Settings
© Supermetrics 2023