How to select a sheet other than the default (first sheet) when creating external tables from Google Sheet in BigQuery Clould Shell?

Question

I created a table definition file from a Google Sheet link in BigQuery Cloud Shell, then used that table definition file to create an external table. The Google Sheet workbook has multiple sheets, but as default it made the external table based on the first sheet. I would like to make tables from the other sheets as well, but adding the parameter defining the sheet reference to the table definition file did not work - and I didn't find the reason why.

I created the definition file:

bq mkdef \
--autodetect \
--source_format=GOOGLE_SHEETS \
https://docs.google.com/spreadsheets/d/1H9fgHbn_ztDgZZyEy4TXV7DOsBto6xI2Aey1iDeg4YA/edit#gid=2054000496 > table_def

The definition file looks like this:

{
  "autodetect": true,
  "sourceFormat": "GOOGLE_SHEETS",
  "sourceUris": [
"https://docs.google.com/spreadsheets/d/1H9fgHbn_ztDgZZyEy4TXV7DOsBto6xI2Aey1iDeg4YA/edit#gid=2054000496"
  ]
}

I changed the definition file by adding the sheet reference:

{
  "autodetect": true,
  "sourceFormat": "GOOGLE_SHEETS",
  "sourceUris": [
"https://docs.google.com/spreadsheets/d/1H9fgHbn_ztDgZZyEy4TXV7DOsBto6xI2Aey1iDeg4YA/edit#gid=2054000496"
  ],
"range" : "Sheet2!A1:Z100"
}

And created the external table:

bq mk \
--external_table_definition=table_def \
myproject-bq:my_dataset.test_table

However, the table is created based on the default Sheet1, not the specified Sheet2.

I tried it with/without cell range definition, with/without quotation marks for the Sheet definition.

How could I select a sheet other than the default (first sheet) when creating external tables from Google Sheet in BigQuery Clould Shell?

Answer 1

The defined range should be in "googleSheetsOptions" in the JSON format. Please see below for your reference.

{ "autodetect": false, "sourceFormat": "GOOGLE_SHEETS", "sourceUris": [ "docs.google.com/spreadsheets/d/xxxxxxxxxxxxxx" ], "maxBadRecords": 1, "googleSheetsOptions": { "range": "test_sheet!A1:B20", "skipLeadingRows": 0 } }