Query Drive data

This document describes how to query data stored in anGoogle Drive external table.

BigQuery supports queries against both personal Drivefiles and shared files. For more information on Drive, seeGoogle Drive training and help.

You can query Drive data from apermanent external table or from atemporary external table that you create when you runthe query.

Limitations

For limitations related to external tables, seeexternal tablelimitations.

Required roles

To query Drive external tables, ensure you have thefollowing roles:

  • BigQuery Data Viewer (roles/bigquery.dataViewer)
  • BigQuery User (roles/bigquery.user)

Depending on your permissions, you cangrant these roles to yourself or ask your administratorto grant them to you. For more information about granting roles, seeViewing the grantable roles on resources.

To see the exact BigQuery permissions that are required to queryexternal tables, expand theRequired permissions section:

Required permissions

You might also be able to get these permissions withcustom rolesor otherpredefined roles.

Drive permissions

At a minimum, to query external data in Drive you must begrantedViewaccess to the Drive file linked to the external table.

Scopes for Compute Engine instances

When you create a Compute Engine instance, you can specify a list of scopesfor the instance. The scopes control the instance's access to Google Cloudproducts, including Drive. Applications running on the VM use the serviceaccount to call Google Cloud APIs.

If you set up a Compute Engine instance to run as aservice account,and that service account accesses an external table linked to a Drivedata source, you must add theOAuth scope for Drive(https://www.googleapis.com/auth/drive.readonly) to the instance.

For information on applying scopes to a Compute Engine instance,seeChanging the service account and access scopes for an instance.For more information on Compute Engine service accounts, seeService accounts.

Query Drive data using permanent external tables

After creating a Drive external table, you canquery it usingGoogleSQL syntax, the same as ifit were a standard BigQuery table. For example,SELECT field1, field2FROM mydataset.my_drive_table;.

Query Drive data using temporary tables

Querying an external data source using a temporary table is useful for one-time, ad-hoc queries over external data, or for extract, transform, and load (ETL) processes.

To query an external data source without creating a permanent table, you provide a table definition for the temporary table, and then use that table definition in a command or call to query the temporary table. You can provide the table definition in any of the following ways:

The table definition file or supplied schema is used to create the temporary external table,and the query runs against the temporary external table.

When you use a temporary external table, you do not create a table in one of your BigQuery datasets. Because the table is not permanently stored in a dataset, it cannot be shared with others.

Create and query temporary tables

You can create and query a temporary table linked to an external data sourceby using the bq command-line tool, the API, or the client libraries.

bq

You query a temporary table linked to an external data source using thebq query command with the--external_table_definition flag. When you usethe bq command-line tool to query a temporary table linked to an external data source, youcan identify the table's schema using:

  • Atable definition file (stored onyour local machine)
  • An inline schema definition
  • A JSON schema file (stored on your local machine)

To query a temporary table linked to your external data source using a tabledefinition file, enter the following command.

bq--location=LOCATIONquery\--external_table_definition=TABLE::DEFINITION_FILE\'QUERY'

Where:

  • LOCATION is yourlocation.The--location flag is optional.
  • TABLE is the name of the temporary table you're creating.
  • DEFINITION_FILE is the path to thetable definition fileon your local machine.
  • QUERY is the query you're submitting to the temporary table.

For example, the following command creates and queries a temporary tablenamedsales using a table definition file namedsales_def.

bq query \--external_table_definition=sales::sales_def \'SELECT   Region,Total_sales FROM   sales'

To query a temporary table linked to your external data source using aninline schema definition, enter the following command.

bq--location=LOCATIONquery\--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=DRIVE_URI\'QUERY'

Where:

  • LOCATION is yourlocation.The--location flag is optional.
  • TABLE is the name of the temporary table you're creating.
  • SCHEMA is the inline schema definition in the formatFIELD:DATA_TYPE,FIELD:DATA_TYPE.
  • SOURCE_FORMAT isCSV,NEWLINE_DELIMITED_JSON,AVRO, orGOOGLE_SHEETS.
  • DRIVE_URI is yourDrive URI.
  • QUERY is the query you're submitting to the temporary table.

For example, the following command creates and queries a temporary tablenamedsales linked to a CSV file stored in Drive with the followingschema definition:Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq--location=US query \--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \'SELECT   Region,Total_sales FROM   sales'

To query a temporary table linked to your external data source using a JSONschema file, enter the following command.

bq--location=LOCATIONquery\--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI\'QUERY'

Where:

  • LOCATION is yourlocation.The--location flag is optional.
  • SCHEMA_FILE is the path to the JSON schema file on your localmachine.
  • SOURCE_FILE isCSV,NEWLINE_DELIMITED_JSON,AVRO, orGOOGLE_SHEETS.
  • DRIVE_URI is yourDrive URI.
  • QUERY is the query you're submitting to the temporary table.

For example, the following command creates and queries a temporary tablenamedsales linked to a CSV file stored in Drive using the/tmp/sales_schema.json schema file.

bqquery\--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \'SELECT   Total_sales FROM   sales'

API

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

fromgoogle.cloudimportbigqueryimportgoogle.auth# Create credentials with Drive & BigQuery API scopes.# Both APIs must be enabled for your project before running this code.credentials,project=google.auth.default(scopes=["https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/bigquery",])# Construct a BigQuery client object.client=bigquery.Client(credentials=credentials,project=project)# Configure the external data source and query job.external_config=bigquery.ExternalConfig("GOOGLE_SHEETS")# Use a shareable link or grant viewing access to the email address you# used to authenticate with BigQuery (this example Sheet is public).sheet_url=("https://docs.google.com/spreadsheets""/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing")external_config.source_uris=[sheet_url]external_config.schema=[bigquery.SchemaField("name","STRING"),bigquery.SchemaField("post_abbr","STRING"),]external_config.options.skip_leading_rows=1# Optionally skip header row.external_config.options.range=("us-states!A20:B49"# Optionally set range of the sheet to query from.)table_id="us_states"job_config=bigquery.QueryJobConfig(table_definitions={table_id:external_config})# Example query to find states starting with "W".sql='SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)query_job=client.query(sql,job_config=job_config)# Make an API request.# Wait for the query to complete.w_states=list(query_job)print("There are{} states with names starting with W in the selected range.".format(len(w_states)))

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.auth.oauth2.GoogleCredentials;importcom.google.auth.oauth2.ServiceAccountCredentials;importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.ExternalTableDefinition;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.GoogleSheetsOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.TableResult;importcom.google.common.collect.ImmutableSet;importjava.io.IOException;// Sample to queries an external data source using a temporary tablepublicclassQueryExternalSheetsTemp{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringtableName="MY_TABLE_NAME";StringsourceUri="https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING));Stringquery=String.format("SELECT * FROM %s WHERE name LIKE 'W%%'",tableName);queryExternalSheetsTemp(tableName,sourceUri,schema,query);}publicstaticvoidqueryExternalSheetsTemp(StringtableName,StringsourceUri,Schemaschema,Stringquery){try{// Create credentials with Drive & BigQuery API scopes.// Both APIs must be enabled for your project before running this code.GoogleCredentialscredentials=ServiceAccountCredentials.getApplicationDefault().createScoped(ImmutableSet.of("https://www.googleapis.com/auth/bigquery","https://www.googleapis.com/auth/drive"));// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.newBuilder().setCredentials(credentials).build().getService();// Skip header row in the file.GoogleSheetsOptionssheetsOptions=GoogleSheetsOptions.newBuilder().setSkipLeadingRows(1)// Optionally skip header row..setRange("us-states!A20:B49")// Optionally set range of the sheet to query from..build();// Configure the external data source and query job.ExternalTableDefinitionexternalTable=ExternalTableDefinition.newBuilder(sourceUri,sheetsOptions).setSchema(schema).build();QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query).addTableDefinition(tableName,externalTable).build();// Example query to find states starting with 'W'TableResultresults=bigquery.query(queryConfig);results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query on external temporary table performed successfully.");}catch(BigQueryException|InterruptedException|IOExceptione){System.out.println("Query not performed \n"+e.toString());}}}

Troubleshooting

Error string:Resources exceeded during query execution: Google Sheets serviceoverloaded.

This can be a transient error that can be fixed by rerunning the query. If theerror persists after a query rerun, consider simplifying your spreadsheet; forexample, by minimizing the use of formulas. For more information, seeexternal table limitations.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-18 UTC.