How to pass string to 'ranges' parameter in spreadsheets.values.batchGet() google sheets api

Question

Making a request to the spreadsheets API via .get(), for a single range, works as follows:

roster_import = service.spreadsheets().values().get(spreadsheetId=<SPREADSHEET ID>, ranges='<sheet name>!A1:A20', majorDimension='ROWS')

However, I want to get more than one range at a time due to the data I want being out of order. In the documentation found here and here, they both state:

Read multiple ranges The following spreadsheets.values.batchGet request reads values from the ranges Sheet1!B:B, and Sheet1!D:D. The ValueRenderOption UNFORMATTED_VALUE setting indicates that values will be calculated, but not formatted in the response. Empty trailing rows and columns are omitted from the response.

The request protocol is shown below. The Reading and Writing Values guide shows how to implement reads in different languages using the Google API client libraries.

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values:batchGet? ranges=Sheet1!B:B&ranges=Sheet1!D:D&valueRenderOption=UNFORMATTED_VALUES?majorDimension=COLUMNS

The response consists of an object with the spreadsheet ID and an array of ValueRange objects corresponding to each requested range, listed in the order they were requested. For example:

{ "spreadsheetId": spreadsheetId, "valueRanges": [ { "range": "Sheet1!B1:B1000", "majorDimension": "COLUMNS", "values": [ ["Cost",20.5,15,100,135.5] ] }, { "range": "Sheet1!D1:D1000", "majorDimension": "COLUMNS", "values": [ ["Ship Date",42430,42444,42449,42449] ] } ] }

and

Method: spreadsheets.values.batchGet Returns one or more ranges of values from a spreadsheet. The caller must specify the spreadsheet ID and one or more ranges.

HTTP request GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet

The URL uses gRPC Transcoding syntax.

Parameters

spreadsheetId - string - The ID of the spreadsheet to retrieve data from.

ranges - string - The A1 notation of the values to retrieve.

Neither of which tell me how to format the string when passing into the spreadsheets().values().batchGet()

I have tried the following:

ranges='!A1:A2!D1:D2'
ranges='!A1:A2&!D1:D2'
ranges='!A1:A2', ranges='!D1:D2'
ranges='!A1:A2D1:D2'
ranges='!A1:A2&D1:D2'

How do I format the string to query multiple ranges?

Thank you

BitShift

Could you please add the solution you found as an actual answer rather than as an edit? It would improve the readability for other community members that are facing the same issue as you did — Andrei Tigau
Yeh sure, I didn't make it an answer as I wasn't sure about ettiquette and I've been scolded before when doing that. Updating now :) — BitShift

BitShift BitShift · Accepted Answer · 2019-11-14T10:48:39

Answer found here

to quote:

range_names = [
    # Range names ...
]
result = service.spreadsheets().values().batchGet(
    spreadsheetId=spreadsheet_id, ranges=range_names).execute()
ranges = result.get('valueRanges', [])
print('{0} ranges retrieved.'.format(len(ranges)))

The necessary value is an array of strings, each string must contain the following

<sheet_name>!<start_range>:<end_range>

How to pass string to 'ranges' parameter in spreadsheets.values.batchGet() google sheets api

1 Answers