google sheet method: spreadsheets.get ... how to get field?

Question

I'm trying to get all of the backgroundColor for a specific range on a sheet. It is nested pretty deeply inside the json.

It seems like you can't just specify a field with get. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

So you have to get the grid data and navigate through the json.

Is that correct? Is that the only way to do it? Must I loop through each rowData and values?

response = service.spreadsheets().get(spreadsheetId=key, ranges=range_, includeGridData=True).execute()

response.get('sheets')[0].get('data')[0].get('rowData')[0]["values"]

user_16118686 · Answer

If it were me, then looping would also be my choice as it gives me granular control on which fields I'd like to get and could also extract different kinds of data if needed. In your case, if it's backgroundColor you want, then this should work on you.

Sample data:

Code:

# fetching A1:B2 range
rowData = response['sheets'][0]['data'][0]['rowData']

for row in rowData:
    for values in row['values']:
        print(values['userEnteredFormat']['backgroundColor'])

Output:

Note:

You could also try adding fields parameter to only get the backgroundColor in the response:

response = service.spreadsheets().get(spreadsheetId=key, ranges=range_, includeGridData=True, fields="sheets/data/rowData/values/userEnteredFormat/backgroundColor").execute()

Response:

When using fields, you could further preprocess your data (e.g. flatten the json) but I'll doubt you'll ever need that. Safe and easy approach is to use the loop afterwards.