How we Query Google Sheets in BigQuery Using gcloud

Most tutorials show how to load Google Sheets into BigQuery through the Cloud Console. In this guide, we’ll do the same thing but using the Cloud Shell.

What we’re doing

Creating an external table in BigQuery that points to your Google Sheet so we can query the data.

How to do it

Create a new sheet by entering sheets.new in the browser:

Name this sheet sample_data and add some data:

Go to your BigQuery studio via console.cloud.google.com/bigquery:

Open up the cloud shell:

Click continue and authorize cloud shell to use your credentials for API calls i.e. run commands as me:

You may get a popup window to sign into Google account, if so then sign in to complete authorization.

When you see the cloud shell window, you may need to reconnect the shell session:

Quick tip: Restart the cloud shell or refresh your browser so cloud shell can set the project for you. You want to see this:

Not this:

Alternatively, just run gcloud config set project <YOUR_PROJECT_ID_HERE>.

To find your project ID, go to your projects list in the console and copy the ID:

Alternatively, run gcloud projects list in the cloud shell to find your project ID:

Then set the project by running: gcloud config set project core-port-470119-i2

Create a dataset for your Google Sheet table: 

bq mk --location=EU --dataset sample

Note: Refresh the BigQuery studio pane to see changes:

Copy the Google Sheet URL:

And paste into this gcloud command to create the external table:

bq mk \

--external_table_definition=GOOGLE_SHEETS=https://docs.google.com/spreadsheets/d/1qk2JKj6fQmYd_BIxDix0x0bbGGZPBANsIntnkPmhS-I \

sample.sample_data

The syntax:

bq mk \

--external_table_definition=GOOGLE_SHEETS=<YOUR_GOOGLE_SHEET_URL> \

<YOUR_DATASET_NAME>.<YOUR_TABLE_NAME>

Query your Google Sheet table:

Done 👍

Caveats

Costs can snowball

If you're a big organisation (i.e. you're processing more than 1TiB per month) querying loads of these external tables, every time a report or dashboard reads from the Sheet, BigQuery charges for a live scan. Lots of viewers or frequent refreshes = higher costs. Instead, schedule a daily copy into a normal BigQuery table and point reports there.

Not built for heavy use

Live reads from Sheets are slower and can hit limits. Busy dashboards may lag or fail and repeated retries mean more cost. Use the copied table for speed and predictable spend.

Easy to break

Edits in the Sheet take a bit to show in reports, and renaming/moving the Sheet or changing sharing can break things.

Make sure to keep the Sheet stable and share it with the exact account your reports use.

For more info check out the official Google documentation here 🔗