bigquery googleSheetsOptions range in Apps Script

Question

Similarly to the bq command-line, can the bigquery googleSheetsOptions range be used when defining bigquery queries in Apps Script (externalDataConfiguration.googleSheetsOptions.range)?

https://cloud.google.com/bigquery/docs/reference/rest/v2/tables

For example, given a table definition like google_sheets_tabeledef.json below, can I pass this table definition as a bigquery method in Apps Script?

{
  "autodetect": false,
  "sourceFormat": "GOOGLE_SHEETS", 
  "sourceUris": [
    "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxx"
  ],
  "maxBadRecords": 1,
  "googleSheetsOptions":
  {
    "range": "test_sheet!A1:B20",
    "skipLeadingRows": 0
  },
  "schema" : {
    "fields": [
      {
        "name": "col1",
        "type": "string"
      },
      {
        "name": "col2",
        "type": "int64"
      },
    ]
  }
}

Related question: bigQuery Google Drive query multiple sheets with googleSheetsOptions range

@li-tan a related question regarding googleSheetsOptions range, this time in Apps Script. — 719016
Is it possible to provide a use case. It is unclear what you are trying to do. You can access the BigQuery API from AppScript and manage your BigQuery datasets and tables. You can use that table definition to load data in a table. — Nathan Nasser

user11916945 user11916945 · Accepted Answer · 2019-08-12T09:30:16

I believe that this has now (answer written in Aug-2019) been added to the table definition in bigQuery. So when defining the table via the webpage, there is now a range section as shown in the screenshot below:

specify range in BigQuery Google Sheets table connector

bigquery googleSheetsOptions range in Apps Script

1 Answers