robin900
Requests to sheets API cause 400 error is worksheet name is valid cell reference (was: Error using get_as_dataframe)
I am using get_as_dataframe to read a sheet. If the sheet name is "CRM26" the extraction fails with:
{'code': 400, 'message': 'Range (query!CRM26) exceeds grid limits. Max rows: 10002, max columns: 35', 'status': 'INVALID_ARGUMENT'}
If the sheet name is "CRM", "CR26M" or "CRM 26" the extraction is successful.
Turns out that CRM26 is a valid cell address in Sheets -- CRM is the column identifier, and 26 is the row. Columns go from A to ZZZ and any letter combination therein is a valid column identifier, including CRM.
This apparently causes problems for the underlying call to gspread's Spreadsheet.values_get, whose first argument is a "range in A1 notation". Such a range can be A1:B3, or Sheet1!A1:B3, or even just Sheet1 to indicate the entire cell range in worksheet Sheet1. A single cell reference, like A1, is invalid -- it must be a range.
So if a worksheet happens to have the name that is also a valid cell reference, such as A1 -- or CRM26 -- gspread is passing that to the API unquoted, and the API interprets it as a single cell reference and rejects it as an invalid argument.
I don't believe that it's gspread's fault -- the range argument to values_get is taken in good faith by gspread and dutifully passed to the API.
The solution will be to have get_as_dataframe take care to enclose the worksheet name in a range reference in single quotes, and to escape (as '') any single quote character occurring in a worksheet name:
https://developers.google.com/sheets/api/guides/concepts#a1_notation
