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:
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
Turn your marketing data into opportunity
We streamline your marketing data so you can focus on the insights.