Is there a way to format multiple worksheets using the googlesheets api and gspread

Question

I'm currently writing a python program which scrapes data from a website and then writes that info into a google spreadsheet. Based on the data contained in each row the data is separated into different worksheets inside of the main spreadsheet. I've been sending multiple requests using gspread's batch_update() function but it only formats sheet1 and doesn't format the subsequent pages. What can I do to make all of my sheets be formatted the same.

batch_update() calls the spreadsheets.batchUpdate() method through the googlesheets api which should affect the whole spreadsheet instead of the first worksheet which I don't understand

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
vt = client.open(sheetName)

formatRequests = []
formatRequests.append({
        "repeatCell" : {
            "range" : {
                "startColumnIndex" : 0,
                "endColumnIndex" : 1
            },
            "cell" : {
                "userEnteredFormat" : {
                    "numberFormat" : {
                        "type": "DATE",
                        "pattern" : "mmm dd, yyyy, hh:mm am/pm"
                    }
                }
            },
            "fields" : "userEnteredFormat.numberFormat"
        }
    })

#... A bunch of other formatting appends

body = {
        'requests' : formatRequests
    }
vt.batch_update(body)

This only formats the first page in the spreadsheet

Answer 1

• You want to set the format for all sheets in the Spreadsheet.
• You want to achieve this using Sheets API with gspread.
• You have already been able to get and put values using Sheets API.

If my understanding is correct, how about this modification?

Modification point:

• In this modification, at first, all sheets are retrieved from the Spreadsheet. Then the request body is created using the retrieved sheet IDs.

Modified script:

Please modify your script as follows.

From:

formatRequests = []
formatRequests.append({
        "repeatCell" : {
            "range" : {
                "startColumnIndex" : 0,
                "endColumnIndex" : 1
            },
            "cell" : {
                "userEnteredFormat" : {
                    "numberFormat" : {
                        "type": "DATE",
                        "pattern" : "mmm dd, yyyy, hh:mm am/pm"
                    }
                }
            },
            "fields" : "userEnteredFormat.numberFormat"
        }
    })

To:

formatRequests = []
worksheet_list = vt.worksheets()  # Added
for sheet in worksheet_list:  # Added
    formatRequests.append({
        "repeatCell": {
            "range": {
                "startColumnIndex": 0,
                "endColumnIndex": 1,
                "sheetId": sheet.id  # Added
            },
            "cell": {
                "userEnteredFormat": {
                    "numberFormat": {
                        "type": "DATE",
                        "pattern": "mmm dd, yyyy, hh:mm am/pm"
                    }
                }
            },
            "fields": "userEnteredFormat.numberFormat"
        }
    })

Reference:

• Selecting a Worksheet

If I misunderstood your question and this was not the direction you want, I apologize.