Modify multiple cells in the same row at once with google sheets API

1
votes

I'm using the Google Sheets API BatchUpdate() Endpoint to modify the background color of specific cells in a given spreadsheet.

The problem right now is that the request that I'm sending only updates a single cell and I am not able to find a solution to update at once a full row (there are around 22 cells in each row in my sheet)

The request body that I'm using is this one:

request = [{
"updateCells": {
    "range":  {
        "sheetId":          sheet_id,
        "startRowIndex":    20,
        "endRowIndex":      21,
        "startColumnIndex": 1,
        "endColumnIndex":   8
    },
    "rows":   [
        {
            "values": [{
                "userEnteredFormat": {
                    "backgroundColor": {
                        "red":   1,
                        "green": 1,
                        "blue":  0,
                        "alpha": 1.0,  # this parameter is not working properly in the API
                    }}}
            ]
        }
    ],
    "fields": 'userEnteredFormat.backgroundColor',
}}]

The values shown in the rows and columns parameters are a bit arbitrary due to doing some testing on it.

Python code with the request made:

response = service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute()

The problem here is that Google Sheets API limits the amount of request an user is able to make so the process that could take approximately 3 mins is taking 3 hours.

Any ideas on how to update the request body? Maybe I'm missing something.

1
google-sheetsgoogle-apigoogle-sheets-apispreadsheet

1 Answers

1
votes

I believe your goal and situation as follows.

  • You want to modify the background color of the cells in a row.
    • From your script, you want to modify the cells in the columns "B" to the end of column of the sheet.
  • You want to achieve this using googleapis with python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

In this case, I would like to propose to use RepeatCellRequest in the batchUpdate method. When your script is modified, it becomes as follows.

Modified script:

request = [{
    "repeatCell": {
        "range":  {
            "sheetId": sheet_id,
            "startRowIndex": 20,
            "endRowIndex": 21,
            "startColumnIndex": 1,
            # "endColumnIndex":   8
        },
        "cell": {
            "userEnteredFormat": {
                "backgroundColor": {
                    "red":   1,
                    "green": 1,
                    "blue":  0,
                    "alpha": 1.0,
                }}},
        "fields": 'userEnteredFormat.backgroundColor',
    }}]
  • In this case, when endColumnIndex is not used, the background color is modified from the cell of startColumnIndex to the end of column on the sheet.
  • When "endColumnIndex": 8 is used, the background color of the column "B" to "H" is modified.

Reference: