Query Sheets with a temporary table

  import 
  
 com.google.auth.oauth2. GoogleCredentials 
 
 ; 
 import 
  
 com.google.auth.oauth2. ServiceAccountCredentials 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. ExternalTableDefinition 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Field 
 
 ; 
 import 
  
 com.google.cloud.bigquery. GoogleSheetsOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. QueryJobConfiguration 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Schema 
 
 ; 
 import 
  
 com.google.cloud.bigquery. StandardSQLTypeName 
 
 ; 
 import 
  
 com.google.cloud.bigquery. TableResult 
 
 ; 
 import 
  
 com.google.common.collect.ImmutableSet 
 ; 
 import 
  
 java.io.IOException 
 ; 
 // Sample to queries an external data source using a temporary table 
 public 
  
 class 
 QueryExternalSheetsTemp 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 tableName 
  
 = 
  
 "MY_TABLE_NAME" 
 ; 
  
 String 
  
 sourceUri 
  
 = 
  
 "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing" 
 ; 
  
  Schema 
 
  
 schema 
  
 = 
  
  Schema 
 
 . 
 of 
 ( 
  
  Field 
 
 . 
 of 
 ( 
 "name" 
 , 
  
  StandardSQLTypeName 
 
 . 
 STRING 
 ), 
  
  Field 
 
 . 
 of 
 ( 
 "post_abbr" 
 , 
  
  StandardSQLTypeName 
 
 . 
 STRING 
 )); 
  
 String 
  
 query 
  
 = 
  
 String 
 . 
 format 
 ( 
 "SELECT * FROM %s WHERE name LIKE 'W%%'" 
 , 
  
 tableName 
 ); 
  
 queryExternalSheetsTemp 
 ( 
 tableName 
 , 
  
 sourceUri 
 , 
  
 schema 
 , 
  
 query 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 queryExternalSheetsTemp 
 ( 
  
 String 
  
 tableName 
 , 
  
 String 
  
 sourceUri 
 , 
  
  Schema 
 
  
 schema 
 , 
  
 String 
  
 query 
 ) 
  
 { 
  
 try 
  
 { 
  
 // Create credentials with Drive & BigQuery API scopes. 
  
 // Both APIs must be enabled for your project before running this code. 
  
  GoogleCredentials 
 
  
 credentials 
  
 = 
  
  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. 
  
  BigQuery 
 
  
 bigquery 
  
 = 
  
  BigQueryOptions 
 
 . 
 newBuilder 
 (). 
 setCredentials 
 ( 
 credentials 
 ). 
 build 
 (). 
 getService 
 (); 
  
 // Skip header row in the file. 
  
  GoogleSheetsOptions 
 
  
 sheetsOptions 
  
 = 
  
  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. 
  
  ExternalTableDefinition 
 
  
 externalTable 
  
 = 
  
  ExternalTableDefinition 
 
 . 
 newBuilder 
 ( 
 sourceUri 
 , 
  
 sheetsOptions 
 ). 
 setSchema 
 ( 
 schema 
 ). 
 build 
 (); 
  
  QueryJobConfiguration 
 
  
 queryConfig 
  
 = 
  
  QueryJobConfiguration 
 
 . 
 newBuilder 
 ( 
 query 
 ) 
  
 . 
  addTableDefinition 
 
 ( 
 tableName 
 , 
  
 externalTable 
 ) 
  
 . 
 build 
 (); 
  
 // Example query to find states starting with 'W' 
  
  TableResult 
 
  
 results 
  
 = 
  
 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 
  
 | 
  
 IOException 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Query not performed \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 } 
 

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 import 
  
 google.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-platform 
 credentials 
 , 
 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_options 
 
 assert 
 options 
 is 
 not 
 None 
  options 
 
 . 
 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 
 ) 
 ) 
 )