Docs/External data/Google Sheets
External data

Google Sheets and BigQuery

BigQuery queries a Google Sheet in place through an external table, or you copy it into a native table. Querying needs Drive access, uses the credentials of whoever runs the query, and reads the sheet again every time. Without Drive access the query fails with "Permission denied while getting Drive credentials".

BigQuery does not load a Google Sheet. It reads one in place through an external table with format = 'GOOGLE_SHEETS', and every query fetches the sheet’s current cells. To get a copy into BigQuery’s own storage, you create a table from a query on the external table, export the sheet as CSV and load that, or use the Import tab. Reading a sheet in place needs the Drive OAuth scope on top of the BigQuery one, and whoever runs the query must be able to open the sheet. Without the scope, creating the table works and querying it fails.

For an Excel file, see Loading Excel files into BigQuery.

Create an external table over a sheet

CREATE EXTERNAL TABLE mydataset.us_states (
  name STRING,
  post_abbr STRING
)
OPTIONS (
  format = 'GOOGLE_SHEETS',
  uris = ['https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E'],
  sheet_range = 'us-states!A20:B49',
  skip_leading_rows = 1
);

This is the public sample sheet from Google’s own docs. We ran it on BigQuery without Drive access: the statement validated in a dry run and the table was created with the schema and options above. The query is where it stops (next section).

OptionMeaning
urisOne sheet URL, https://docs.google.com/spreadsheets/d/FILE_ID, or https://drive.google.com/open?id=FILE_ID. Wildcards are not supported for Drive URIs
sheet_rangeSheet1 for a whole tab, or Sheet1!A1:B20 for a range. Without it, the first sheet in the file is used
skip_leading_rowsRows to skip at the top, for a header or a title
max_bad_recordsBad rows to ignore, as for CSV

You can leave out the column list. Google’s docs say BigQuery then detects whether the first row is a header, takes the column names from it and changes names to fit BigQuery’s rules, for example spaces become underscores. They do not say how it picks column types for a mixed column, and we could not test it (below). Give a column list for anything you query regularly.

The same table with the bq tool, from Google’s docs:

bq mk \
  --external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
  mydataset.sales

The source can also be a CSV, newline-delimited JSON or Avro file in Drive, with the same Drive requirements.

The Drive scope and the error

A normal BigQuery login does not include Drive. We queried the table above without it:

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

The fix is in how you sign in, not in IAM:

  • Console: follow the prompts when you create the table and click Allow for Drive.
  • bq and gcloud: gcloud auth login --enable-gdrive-access.
  • API or client libraries: request the Drive scope (https://www.googleapis.com/auth/drive) together with the BigQuery scope. For Application Default Credentials that is gcloud auth application-default login --client-id-file=CLIENT_ID_FILE --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform.

Google’s sample code notes you need to use a shareable link or grant viewing access to the account that authenticates with BigQuery. The sheet is read with the credentials of the person running the query. Giving a colleague access to the BigQuery table does not give them the sheet.

Creating the table needs only bigquery.tables.create, which is why it worked for us. A dry run of SELECT * FROM the table also worked and reported lower bound of 0 bytes: BigQuery does not know what is in the sheet until it reads it, the same as for any external table.

What a query costs and does

  • Read on every query. An edit in the sheet shows up in the next query. A query that runs while someone edits sees whatever state the sheet is in; BigQuery gives no consistency guarantee for external data.
  • Billed for the bytes read from the sheet, the same per-TiB price as a native table. The dry run cannot tell you the figure in advance.
  • Not cached. Results from Drive sources are not cached, so a repeated query is read and billed again (External tables).
  • Slower. Google’s docs rank Drive below Cloud Storage for speed, and recommend loading the data when speed matters.
  • Read-only. No INSERT, UPDATE or MERGE on the table. You write to the sheet, not to BigQuery.
  • As available as the sheet. Delete the sheet or lose access and every query on the table fails.

That is a good trade for a small lookup list someone maintains by hand, such as a mapping of campaign names to teams. It is a poor one for a dashboard that many people refresh.

Copying a sheet into BigQuery

To keep a snapshot, query the external table into a native table:

CREATE OR REPLACE TABLE mydataset.us_states_snapshot AS
SELECT * FROM mydataset.us_states;

This is not run here, because it needs Drive access. It reads the sheet once, and from then on the copy is a normal table: cached, clusterable, with a real dry-run estimate. Run it as a scheduled query to refresh it daily. The alternatives:

RouteHow
Query into a native tableThe statement above. Types come from the external table’s schema
Export CSV, then loadFile > Download > CSV in Sheets, then bq load. The CSV traps in the Excel article apply to the text
Querylab.io Import tabPick the spreadsheet, a tab and an optional range, then import

The Import tab reads the sheet with the Sheets API, takes the first non-empty row of the range as the header, gives blank headers a name like column_1, makes duplicate headers unique, and warns when the first 100 rows contain formula errors (#N/A, #REF!, #DIV/0! and others). It shows the detected schema for you to edit before the load. Ticking Connected table creates an external table like the one above instead of copying the data.

Cleaning what comes out of a sheet

A sheet is typed by people. Cells hold trailing spaces, empty strings, a note where a number should be, and blank rows at the end of the range. This example trims the text, turns blanks and non-numbers into NULL, and drops the empty row:

trim, blank cells and empty rowsOpen in Querylab.io
123456789101112
WITH sheet AS (
  SELECT 'Ada' AS name, '42' AS score UNION ALL
  SELECT ' Linus ', '' UNION ALL
  SELECT NULL, NULL UNION ALL
  SELECT 'Grace', 'n/a'
)
SELECT
  TRIM(name) AS name,
  SAFE_CAST(NULLIF(TRIM(score), '') AS INT64) AS score
FROM sheet
WHERE COALESCE(name, score) IS NOT NULL
ORDER BY name
Result in BigQuery
Rownamescore
1Ada42
2Gracenull
3Linusnull
3 rows

Wide sheets with one column per month turn into rows with UNPIVOT. INCLUDE NULLS keeps the empty March for APAC:

month columns to rowsOpen in Querylab.io
12345678
WITH sheet AS (
  SELECT 'EMEA' AS region, 120 AS jan, 135 AS feb, 160 AS mar UNION ALL
  SELECT 'APAC', 80, 95, NULL
)
SELECT region, month, sales
FROM sheet
UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar))
ORDER BY region, month
Result in BigQuery
Rowregionmonthsales
1APACfeb95
2APACjan80
3APACmarnull
4EMEAfeb135
5EMEAjan120
6EMEAmar160
6 rows

Sheets that people append to often hold a history of updates. Keep the newest row per key with QUALIFY:

newest row per ticketOpen in Querylab.io
123456789
WITH sheet AS (
  SELECT 'A-1' AS ticket, 'open' AS status, DATE '2026-03-01' AS updated UNION ALL
  SELECT 'A-1', 'closed', DATE '2026-03-04' UNION ALL
  SELECT 'B-7', 'open', DATE '2026-03-02'
)
SELECT ticket, status, updated
FROM sheet
QUALIFY ROW_NUMBER() OVER (PARTITION BY ticket ORDER BY updated DESC) = 1
ORDER BY ticket
Result in BigQuery
Rowticketstatusupdated
1A-1closed2026-03-04
2B-7open2026-03-02
2 rows

Dates that arrive as serial numbers convert the same way as in the Excel article.

Sending query results to Google Sheets

The other direction is a console feature. After a query, the console can save results to Google Sheets or Drive. Per Google’s docs, this is not available through the bq tool or the API, results with nested and repeated fields cannot be saved to Sheets, and a save to Drive is limited to 1 GB and to CSV or newline-delimited JSON. Connected Sheets goes the other way round: a spreadsheet runs BigQuery queries for you on request or on a schedule and keeps the results in the sheet.

What we could not test

We cannot create files in Drive here, so no query ever read from a sheet. The error above, the dry-run result, the created table and the validated DDL are from real jobs. What the docs say about header detection, type detection for a Sheets column, sheet_range defaults and the Drive scope is from Google’s pages and was not tried against a sheet. The SQL examples run on inline data.

Open the playground. It is the real app on public datasets and needs no sign-in.