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.

// [START bigquery_auth_drive_scope]

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

// [END bigquery_auth_drive_scope]

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

}

}