15 Oct 2025 | 2 min read
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.
Creating an external table in BigQuery that points to your Google Sheet so we can query the data.
Create a new sheet by entering sheets.new in the browser:
Name this sheet sample_data and add some data:
New year resolution going well 🫠
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
The project ID core-port-470119-i2 is specific to my account, your project ID will be different.
You know your project is set when you can see the project in yellow in the shell prompt.
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 👍
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.
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.
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 🔗