How do I iteratively parse data from each "sub-sheet" in a Google spreadsheet using their API?

Question

Language: Python

I'm trying to go through each "sub-sheet" - or Sheet - in a Google Spreadsheet, and parse it's data. This will ultimately be used to create a local .csv file.

I've tried using both gspread and gsheets to solve this issue, but both had authentication errors, so I had to use the API directly.

All examples on the Google Spreadsheets API get all the values from a specific range, as in "get everything from row A1:A4 in sheet Sheet1". This isn't beneficial for me, as my script needs to go through however many sheets that are in the spreadsheet, I'm parsing all the data and I don't know the Sheet name beforehand.

According to the docs, a Sheet object should have a 'data' field, but all of my curr_sheet.get('data') parses return None: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#Sheet

The Excel sheet I'm trying to parse looks like this: https://i.gyazo.com/62606b2434435d34dd3ba9fbd9825a52.png

    sheet = service.spreadsheets()
    result=sheet.get(spreadsheetId=SPREADSHEET_ID).execute()
    print(result2)
    all_sheets = result.get('sheets')

    for curr_sheet in all_sheets:
        print(curr_sheet)

I need to parse a single value from a specific row, and then save the file as a .csv locally. I've narrowed the problem down to iterating through the specific sheets, so any help would be greatly appreciated!

Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. — Tanaike

Tanaike Tanaike · Accepted Answer · 2019-04-11T03:37:07

You know the Spreadsheet ID.
You want to retrieve all values from all sheets in a Spreadsheet.
You have already been used Sheets API.
- Sheets API is enabled at API console.
- You can retrieve the access token by OAuth2 process.

If my understanding is correct, how about this modification. Please think of this as just one of several answers.

Modification points:

If you want to retrieve data using spreadsheets.get method, in your case, please add sheets/data to the fields like result=sheet.get(spreadsheetId=SPREADSHEET_ID, fields='sheets/data').execute().

But I would like to propose the following flow, because the values retrieved by spreadsheets.values.batchGet is more readability than that retrieved by spreadsheets.get method.

In order to retrieve all sheet names, use spreadsheets.get method.
In order to retrieve all values from a sheet, use spreadsheets.values.batchGet method.

By combining both, all values from all sheets in a Spreadsheet can be retrieved.

Modified script:

sheet = service.spreadsheets()
sheets = sheet.get(spreadsheetId=SPREADSHEET_ID, fields='sheets/properties/title').execute()
ranges = [sheet['properties']['title'] for sheet in sheets['sheets']]
values = sheet.values().batchGet(spreadsheetId=SPREADSHEET_ID, ranges=ranges).execute()
print(values)

Result:

When above script is run, the following values are returned.

{
    "spreadsheetId":"###",
    "valueRanges":[
        {
            "range":"Sheet1!A1:Z1000",
            "majorDimension":"ROWS",
            "values":[["a1","b1","c1"],["a2","b2","c2"],["a3","b3","c3"]]
        }
        {
            "range":"Sheet2!A1:Z1000",
            "majorDimension":"ROWS",
            "values":[["a1","b1","c1"],["a2","b2","c2"],["a3","b3","c3"]]
        }
        ,
        ,
        ,
    ]
}

["a1","b1","c1"] is the 1st row.
b1 is the column "B".

References:

If this was not the result you want, I apologize.