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());}}}

fromgoogle.cloudimportbigqueryimportgoogle.auth# Create credentials with Drive & BigQuery API scopes.# Both APIs must be enabled for your project before running this code.## If you are using credentials from gcloud, you must authorize the# application first with the following command:## gcloud auth application-default login \#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platformcredentials,project=google.auth.default(scopes=["https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/cloud-platform",])# 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"),]options=external_config.google_sheets_optionsassertoptionsisnotNoneoptions.skip_leading_rows=1# Optionally skip header row.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)))