2

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

Arvind Sundar
  • 33
  • 6
  • 1
    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 Aug 20 '19 at 03:22
  • Thank you for the response! This got me the result I needed. When I was going through the documentation I somehow thought that the sheetId referred to the whole spreadsheet instead of each individual worksheet so I didn't it was necessary to put in. – Arvind Sundar Aug 20 '19 at 12:04
  • Thank you for replying. I'm glad your issue was resolved. Thank you, too. – Tanaike Aug 20 '19 at 22:22

1 Answers1

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:

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165