Specifying valid “ranges” for batch values request

Question

I'm using the Google Sheets API quickstart for Python. I'm trying to pull multiple cells, just one cell at a time, from the google sheets api and plug each value into a text document. I've been doing this with spreadsheets().values().get(), but I'm hitting the API too much and the batchGet() method seems like it would be better. I read through the Google Sheets API v4 but was unable to find the correct formatting for the ranges parameter on spreadsheets().values().batchGet(). According to the docs, it appears that it takes an array of strings, or possibly a JSON object, but there's no example of the format in the docs (In fact, it says // TODO!). Does anyone know the correct format? I've tried:

spreadsheetId = 098217340987123049817235blahblahblah
ranges = ["A100:A100, "B100:B100"]
spreadsheets().values().batchGet(spreadsheetId=spreadsheetId, ranges=ranges)

and

spreadsheetId = 098217340987123049817235blahblahblah
ranges = ["A100:A100"]
spreadsheets().values().batchGet(spreadsheetId=spreadsheetId, ranges=ranges)

and just a string to be sure

spreadsheetId = 098217340987123049817235blahblahblah
ranges = "A100:A100"
spreadsheets().values().batchGet(spreadsheetId=spreadsheetId, ranges=ranges)

Each of these returns [].

When I use spreadsheets().values().get() and ranges="A100:A100, function returns [['cellvalue']], so I know there's data there.

Any thoughts?

Answer 1

You may check in this link the sample code in using batchGet.

spreadsheetId = '1SizdeufKaODnq3WMV3T3uS2W47v4mZJeqYg2J1MiP9k'
rangeName = 'Sheet1!A2:A'
result = service.spreadsheets().values().get(spreadsheetId=spreadsheetId, range=rangeName).execute()

Since ranges is the A1 notation of the values to retrieve, this is a string like Sheet1!A1:B2 that refers to a group of cells in the spreadsheet, and is typically used in formulas.

For example, valid ranges are:

• Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
• Sheet1!A:A refers to all the cells in the first column of Sheet1.
• Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
• Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.
• A1:B2 refers to the first two cells in the top two rows of the first visible sheet.
• Sheet1 refers to all the cells in Sheet1.

Named ranges are also supported. When a named range conflicts with a sheet's name, the named range is preferred.

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.