Query data from a Google Sheets file by creating a permanent table.
Code sample
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Java API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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. TableId
;
import
com.google.cloud.bigquery. TableInfo
;
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 permanent table
public
class
QueryExternalSheetsPerm
{
public
static
void
main
(
String
[]
args
)
{
// TODO(developer): Replace these variables before running the sample.
String
datasetName
=
"MY_DATASET_NAME"
;
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.%s WHERE name LIKE 'W%%'"
,
datasetName
,
tableName
);
queryExternalSheetsPerm
(
datasetName
,
tableName
,
sourceUri
,
schema
,
query
);
}
public
static
void
queryExternalSheetsPerm
(
String
datasetName
,
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
();
TableId
tableId
=
TableId
.
of
(
datasetName
,
tableName
);
// Create a permanent table linked to the Sheets file.
ExternalTableDefinition
externalTable
=
ExternalTableDefinition
.
newBuilder
(
sourceUri
,
sheetsOptions
).
setSchema
(
schema
).
build
();
bigquery
.
create
(
TableInfo
.
of
(
tableId
,
externalTable
));
// Example query to find states starting with 'W'
TableResult
results
=
bigquery
.
query
(
QueryJobConfiguration
.
of
(
query
));
results
.
iterateAll
()
.
forEach
(
row
-
>
row
.
forEach
(
val
-
>
System
.
out
.
printf
(
"%s,"
,
val
.
toString
())));
System
.
out
.
println
(
"Query on external permanent table performed successfully."
);
}
catch
(
BigQueryException
|
InterruptedException
|
IOException
e
)
{
System
.
out
.
println
(
"Query not performed \n"
+
e
.
toString
());
}
}
}
Python
Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Python API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
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/bigquery"
,
]
)
# Construct a BigQuery client object.
client
=
bigquery
.
Client
(
credentials
=
credentials
,
project
=
project
)
# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"
# Configure the external data source.
dataset
=
client
.
get_dataset
(
dataset_id
)
table_id
=
"us_states"
schema
=
[
bigquery
.
SchemaField
(
"name"
,
"STRING"
),
bigquery
.
SchemaField
(
"post_abbr"
,
"STRING"
),
]
table
=
bigquery
.
Table
(
dataset
.
table
(
table_id
),
schema
=
schema
)
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
]
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
.
external_data_configuration
=
external_config
# Create a permanent table linked to the Sheets file.
table
=
client
.
create_table
(
table
)
# Make an API request.
# Example query to find states starting with "W".
sql
=
'SELECT * FROM `
{}
.
{}
` WHERE name LIKE "W%"'
.
format
(
dataset_id
,
table_id
)
results
=
client
.
query_and_wait
(
sql
)
# Make an API request.
# Wait for the query to complete.
w_states
=
list
(
results
)
print
(
"There are
{}
states with names starting with W in the selected range."
.
format
(
len
(
w_states
)
)
)
What's next
To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser .
