CODEWITHSUNDEEP
google-sheetsgoogle-bigquery
denim
denim

Reputation: 463

Creating external tables from Google Sheet in BigQuery Clould Shell using a sheet other than the default

I am creating a BQ table from the second tab of a GoogleSheets spreadsheet. First, I create the table definition file as follows:

bq mkdef \
--noautodetect \
--source_format=source_format \
"drive_uri" \
path_to_schema_file > /tmp/mytable_def.json

and then I manually modify mytable_def.json to indicate that the table should be created from the second tab:

"googleSheetsOptions":{"range": "sheetB"}

However, I am looking for a way to do this directly from the first mkdef command. Is this possible?

Upvotes: 1

Views: 607

Answers (1)

Nick_Kh
Nick_Kh

Reputation: 5243

I think it is worth trying JQ tool as reliable way to process JSON objects as a data manipulation step in the above mentioned approach:

bq mkdef \
--noautodetect \
--source_format=source_format \
"drive_uri" \
path_to_schema_file | jq '.googleSheetsOptions += {"range": "sheetB"}' > /tmp/mytable_def.json

As was mentioned by @Daniel Zagales, referencing the documentation page, table definition file should be adjusted by the hand or substituted for any tool that can afford it as a part of command-line processing.

Upvotes: 1

Related Questions