How to connect Google Sheets to BigQuery: three ways

Way 1: an external table over the sheet

The steps follow Google’s Drive external tables page, read in October 2026.

1. Copy the sheet’s URL from the browser.
2. In the BigQuery console, open a dataset and click Create table.
3. Set Create table from to Drive.
4. Paste the URL as the Drive URI. Wildcards are not supported: one table, one file.
5. Set the file format to Google Sheet.
6. Optionally give a sheet range, such as Budget!A1:D200. Without one, BigQuery reads the first tab.
7. Define the schema, name the table and click Create table.

The first time, the console asks you to allow access to Google Drive.

The same in SQL:

CREATE EXTERNAL TABLE mydataset.budget
OPTIONS (
  format = 'GOOGLE_SHEETS',
  uris = ['https://docs.google.com/spreadsheets/d/FILE_ID'],
  sheet_range = 'Budget!A1:D200',
  skip_leading_rows = 1
);

This statement creates a table, so it was not executed for this guide; it follows Google’s DDL reference.

You then query it like any table:

SELECT channel, SUM(budget) AS budget
FROM mydataset.budget
GROUP BY channel;

What you need

• To create the table: the BigQuery Data Editor role, or another role with bigquery.tables.create.
• To query it: access to the sheet itself. A colleague with BigQuery access but no access to the file gets an error, and so does a service account the sheet was never shared with. This is the most common reason a scheduled query or a dashboard on such a table fails.
• From the command line: sign in with Drive access, gcloud auth login --enable-gdrive-access.

Where it goes wrong

An external table has no copy of the data. Every query result depends on what the sheet looks like at that second:

• Someone inserts a column, and every column after it shifts.
• Someone types “n/a” into a number column, and the query fails or the row turns to NULL.
• Someone sorts or filters the sheet halfway through the month.
• Queries are slower than on a native table, and results are not cached.

That is acceptable for a table two people maintain and one query reads. It is not a base for a report other people rely on. For that, use the third way.

Way 2: download as CSV and upload

For a one-off load:

1. In Google Sheets, open File, then Download, then Comma-separated values (.csv). Only the current tab is exported.
2. In the BigQuery console, Create table, with Create table from set to Upload.
3. Choose the file, let BigQuery detect the schema or enter it, and create the table.

A local file can be up to 100 MB in the console. The schema options and the common errors are in how to upload data to BigQuery.

The table is a snapshot. When the sheet changes, you load again.

Way 3: a scheduled copy into a native table

This keeps the convenience of editing in Sheets and removes the fragility. The external table stays as the entry point, and a query copies it into a normal table on a schedule, with the checks you choose:

CREATE OR REPLACE TABLE mydataset.budget_clean AS
SELECT
  DATE(month) AS month,
  LOWER(TRIM(channel)) AS channel,
  SAFE_CAST(budget AS NUMERIC) AS budget
FROM mydataset.budget
WHERE channel IS NOT NULL;

Not executed for this guide, for the same reason as above.

Save it as a scheduled query in BigQuery, daily or hourly. Reports and joins read budget_clean:

• If someone breaks the sheet at noon, the reports keep yesterday’s good copy until the next run.
• SAFE_CAST turns a stray “n/a” into NULL instead of failing the load. Count the NULLs so a bad cell does not pass unnoticed; the CAST and SAFE_CAST guide shows how.
• The native table is fast, cached and does not need Drive access to read.

The account that owns the scheduled query needs access to the sheet.

Which way to choose

• A mapping or budget table that two people edit: an external table, with a scheduled copy as soon as a report depends on it.
• A file someone sent you once: CSV upload.
• Data that is really produced by a system: do not route it through a sheet at all. Load it from the source; loading data into BigQuery compares the routes.

A fuller worked example of the scheduled route is in the Google Sheets to BigQuery workflow.

Once the sheet is in BigQuery

The usual reason to bring a sheet in is to join it: budget against actual spend, targets against revenue, a channel mapping against campaign names. The join is where the sheet’s looseness bites, since “Paid Social” and “paid social “ do not match. Clean the key in the copy step, as above, and write the join once.

In OWOX Data Marts that query becomes a Data Mart: your SQL, with a description on every column, run in your own BigQuery project. OWOX Data Marts does not transform the data. It delivers the result on a schedule to the places people read it, including back to Google Sheets, so the plan a marketer typed into one sheet comes back next to the actuals in another.

Google BigQueryBigQuery aggregate functions: COUNT, SUM, STRING_AGG, ARRAY_AGG and moreOctober 16, 2026→Google BigQueryBigQuery Array Functions: Syntax and Tested ExamplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: Convert Data Types Without ErrorsOctober 6, 2026→

See all articles →