Connecting data from Google Sheets to BigQuery

In this tutorial, we'll show you how to read a Google Sheet from BigQuery.

Want to master BigQuery?

Check out our free BigQuery practice problems with solutions

At the time of writing, Google supports two directions of data flow:

• from BigQuery to Google Sheets
  
• from Google Sheets to BigQuery
  

There is no two-way connection.

This tutorial is about the latter, a connection that moves data from Google Sheets to BigQuery. Here's a gif that shows everything in about 90 seconds.

This guide was created following these docs.

Steps

1. Create a Google Sheet
2. In the BigQuery console, click Add Data
3. Select Drive as the data source
4. Set the Drive URI as the URL of your Google Sheet
5. Input the sheet range (hover over the icon for details)
6. Choose your dataset and table name
7. Enter the table schema. It should match the sheet!
8. Under Advanced Options set Header rows to skip equal to 1
9. CREATE TABLE

Access Denied: Permission denied while getting Drive credentials

If you get an error like this, it means that the account creating the connection in BigQuery does not have permission to the google sheet. To resolve this,

1. Head over to the IAM dashboard.
2. Copy the email address of the account responsible for creating tables in BigQuery
3. Head over to your Google Sheet

4. Click the green Share button in the top right and paste the email address you just copied

   

Now you should be able to create the table in BigQuery.

Notes

• Your freshly created "table" won't appear in the explorer pane until you refresh the window.
• You cannot preview the table like you can with normal BigQuery tables. To see the data, you need to query it: select * from mydataset.mysheet limit 1000.
• Your "table" is similar to a view. Every time you query the table, it fetches the data from your google sheet. It doesn't know when your sheet has been edited (and therefore doesn't use caching). It just grabs fresh data every time you use it.