IssueTracker

Problem you have encountered:

Creation of external tables pointing to a sheet title that looks like a cell reference is duck typed as a cell reference for the first worksheet.

e.g. A sheet title of "AA2" will return the following when attempting to query the created table:

Error while reading table: myproject.test.gs_external_table_test, error message: Failed to read the spreadsheet. Error message: Range (Sheet1!AA2) exceeds grid limits. Max rows: 1000, max columns: 26 File: [file_id_removed]

When the second sheet title is renamed to "A2" & created again with that, it fails silently as A2 technically exists as Sheet1!A2

There is no data to display.

What you expected to happen:

Data to be returned from the named sheet in "sheet_range". Based on the help text for creating an external table in the UI, specifying sheet_range as "AA2" should be taken explicitly as it isn't qualified as [sheet_title]![cell_id]

Steps to reproduce:

Create sheets workbook, add new worksheet, name worksheet look like a cell reference, add headers into A1, B1, fill out some data.

Create external table via BQ UI

CREATE OR REPLACE EXTERNAL TABLE `myproject.test.gs_external_table_test`
(
  animal STRING,
  quantity INT64,
)
OPTIONS(
  sheet_range="AA2",
  skip_leading_rows=1,
  format="GOOGLE_SHEETS",
  uris=["https://docs.google.com/spreadsheets/d/file_id_removed/"]
);

Other information (workarounds you have tried, documentation consulted, etc):

Wrapping the sheet title in single quotes or qualifying cell references forces the expected behaviour e.g. sheet_range="'AA2'" or sheet_range="'AA2'!A1:B6"

Help Text from clicking on the "Sheet Range" (?) when creating a table through the UI -

Optionally indicate which sheet and cell range in the Google Sheets spreadsheet to create a table >from. You can specify:

[sheet_title] for the whole sheet, or

[sheet_title]![top_left_cell_id]:[bottom_right_cell_id] for a rectangular cell range in a sheet, >such as 'Sheet1!A1:B20'.

When this is not specified, the first sheet in the spreadsheet is used.

Thanks