Using batch_get for multiple pages such as spreadsheets.values.batchGet supports

[fredericomattos]

I tried using batch_get to collect values in a single call:

sheet = client.open_by_key('XXXXXXXXXXXXXXXXXXXXXXXX')
print(sheet.batch_get(["'Page 1'!A1:A5","'Page 2'!A1:A5"]))

But I realized that it only accepts by setting the desired page after open_by_key, like open_by_key('...').worksheet('...'). That way are limited to using it on a single page.

I would like to request an "update" to this option that gives us the freedom to work with multiple pages in a single call, as is possible through the API (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet):

{
  "spreadsheetId": "XXXXXXXXXXXXXXXXXXXXXXXX",
  "valueRanges": [
    {
      "range": "'Page 1'!A1:A5",
      "majorDimension": "ROWS",
      "values": [
        [
          "id"
        ],
        [
          "1"
        ],
        [
          "2"
        ],
        [
          "3"
        ],
        [
          "4"
        ]
      ]
    },
    {
      "range": "'Page 2'!A1:A5",
      "majorDimension": "ROWS",
      "values": [
        [
          "id"
        ],
        [
          "1"
        ],
        [
          "2"
        ],
        [
          "3"
        ],
        [
          "4"
        ]
      ]
    }
  ]
}

[lavigne958]

Hi this is an interesting request, in your case you try to use the method batch_get from a Worksheet object, so this is expected to only work with ranges in that sheet because you only need to provide the range, the method will handle the sheet name etc for you.

If you with to pull more ranges, from different worksheets, you can (for now) build your request manually and use the method Spreadsheet.values_batch_get which belong to a Spreadsheet instance and is not linked to any worksheet.

I can check if we can add a new method in the Spreadsheet class that can accept multiple ranges, but the spreadsheet class is not intended to provide such access, so some proper design is required, meaning I don't think I can plan that for the next release.

[fredericomattos]

@lavigne958 looking further into the case, I think my view of expanding batch_get to multiple pages will be wrong indeed.

As values_batch_get returns all data and not just the values in the cells, if there was an option that returns exactly the same as batch_get but multiple sheets it would be perfect.

Just so don't have use ['valueRanges'][0]['values'] and ['valueRanges'][1]['values'] to get to cell values as is currently required in values_batch_get.