# 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". Source: https://docs.querylab.io/bigquery/google-sheets/ Updated: 2026-11-04 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](https://docs.querylab.io/data-management/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](https://docs.querylab.io/bigquery/loading-excel/). ## Create an external table over a sheet ```sql 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). | Option | Meaning | | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | | `uris` | One 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_range` | `Sheet1` for a whole tab, or `Sheet1!A1:B20` for a range. Without it, the first sheet in the file is used | | `skip_leading_rows` | Rows to skip at the top, for a header or a title | | `max_bad_records` | Bad 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: ```sh 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: ```text 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](https://docs.querylab.io/bigquery/external-tables/). ## 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](https://docs.querylab.io/bigquery/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: ```sql 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: | Route | How | | ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Query into a native table | The statement above. Types come from the external table's schema | | Export CSV, then load | **File > Download > CSV** in Sheets, then `bq load`. The CSV traps in [the Excel article](https://docs.querylab.io/bigquery/loading-excel/#what-excels-csv-export-changes) apply to the text | | Querylab.io Import tab | Pick 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 rows** ```sql 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 (3 rows): | name | score | | --- | --- | | Ada | 42 | | Grace | NULL | | Linus | NULL | Wide sheets with one column per month turn into rows with `UNPIVOT`. `INCLUDE NULLS` keeps the empty March for APAC: **month columns to rows** ```sql 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 (6 rows): | region | month | sales | | --- | --- | --- | | APAC | feb | 95 | | APAC | jan | 80 | | APAC | mar | NULL | | EMEA | feb | 135 | | EMEA | jan | 120 | | EMEA | mar | 160 | Sheets that people append to often hold a history of updates. Keep the newest row per key with `QUALIFY`: **newest row per ticket** ```sql 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 (2 rows): | ticket | status | updated | | --- | --- | --- | | A-1 | closed | 2026-03-04 | | B-7 | open | 2026-03-02 | Dates that arrive as serial numbers convert the same way as in [the Excel article](https://docs.querylab.io/bigquery/loading-excel/#things-that-only-go-wrong-in-the-xlsx). ## 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 at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Create Google Drive external tables](https://cloud.google.com/bigquery/docs/external-data-drive) - [Introduction to external tables](https://cloud.google.com/bigquery/docs/external-tables) - [Using schema auto-detection](https://cloud.google.com/bigquery/docs/schema-detect) - [Writing query results](https://cloud.google.com/bigquery/docs/writing-results)