Google sheets - Get all cells in a sheet with A1 notation when the sheet's name conflicts with a cell

2
votes

Using the new v4 Google sheets api, is it possible to get a range of all cells in a spreadsheet when it conflicts with a cell in A1 notation ?

For example, I have a sheet called A001.

  1. A001!A1:D3 - Get range A1-D3 from sheet named A001
  2. Some-other-sheet - Get all cells from a sheet called "Some-other-sheet"
  3. A001 - instead of getting all cells from a sheet called A001, it gets cell A1 from the first visible sheet.

So i'm basically asking about case #3. Is it possible to get all range of A001 ? (not knowing the range in advance of course)

2
excelgoogle-sheetsgoogle-sheets-api

2 Answers

4
votes

Unfortunately, parsing as a cell or a named range will take precedence over a sheet name. You can workaround this by choosing a suitably large range and requesting that, e.g: A001!A:ZZZ -- that will request all rows in column A through column ZZZ in sheet A001. The response will only include the last row/col where data actually exists.

5
votes

Yes, use single quotes around the sheet name, like this:

'A001'

If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes ('), e.g 'Sheet One'!A1:B2. For simplicity, it is safe to always surround the sheet name with single quotes.

https://developers.google.com/sheets/api/guides/concepts