Requests to sheets API cause 400 error is worksheet name is valid cell reference (was: Error using get_as_dataframe) about gspread-dataframe

[robin900]

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