[FIXED] Google Sheets API : Formatting select ranges differently within the same batchUpdate request array

Issue

I am struggling to format multiple sections of the sheet on the same request array using the batch update in the google sheets API.

I've been able to update the formatting successfully, but only the last entry in the request array updates (using the repeatCell format).

Additionally, I want to use this summation "=SUM(B4:B12)" and could not get it.

    requestFormatBody = {
    "requests": [
        {
            "repeatCell": {
                "range": {
                    "endRowIndex": 1,
                    "endColumnIndex": 3,
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": 0.0,
                            "green": 0.0,
                            "blue": 0.0,
                        },
                        "horizontalAlignment": "CENTER",
                        "textFormat": {
                            "foregroundColor": {
                                "red": 1.0,
                                "green": 1.0,
                                "blue": 1.0,
                            },
                            "fontSize": 12,
                            "bold": True,
                        },
                    }
                },
                "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)",
            },
            "repeatCell": {
                "range": {
                    "startRowIndex": 2,
                    "endRowIndex": 3,
                    "endColumnIndex": 3,
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": 255 / 255,
                            "green": 255 / 255,
                            "blue": 204 / 255,
                        },
                        "horizontalAlignment": "CENTER",
                        "textFormat": {
                            "fontSize": 12,
                            "bold": True,
                        },
                    }
                },
                "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)",
            },
        },
    ],
}

I was able to create the sheets and assign values as needed, stuck on formatting. Thanks

Edit: Adding reference pictures Essentially I want these in the same "requests" array sent in the body of spreadsheets().batchUpdate(). When using as shown above I only get the last "repeatCell" block, when I only do one of the "repeatCell"'s I can get either. I would like both at once though. Is this possible?

(Don't have enough points to embed, sorry for the links)

Solution

I believe your goal is as follows.

• You want to achieve the output situations of your 2 images by one API call.

In this case, how about the following modification?

When I saw your showing request body, it is as follows.

{
   "requests":[
      {
         "repeatCell":{,,,},
         "repeatCell":{,,,}
      }
   ]
}

In this case, 2 properties of repeatCell are existing. By this, one of them is used. In this case, it is required to be the following object.

{
   "requests":[
      {"repeatCell":{,,,}},
      {"repeatCell":{,,,}}
   ]
}

By this, 2 requests of repeatCell are run.

When this is reflected in your showing request body, it becomes as follows.

Modified request body:

requestFormatBody = {
   "requests":[
      {
         "repeatCell":{
            "range":{
               "endRowIndex":1,
               "endColumnIndex":3
            },
            "cell":{
               "userEnteredFormat":{
                  "backgroundColor":{
                     "red":0.0,
                     "green":0.0,
                     "blue":0.0
                  },
                  "horizontalAlignment":"CENTER",
                  "textFormat":{
                     "foregroundColor":{
                        "red":1.0,
                        "green":1.0,
                        "blue":1.0
                     },
                     "fontSize":12,
                     "bold":True
                  }
               }
            },
            "fields":"userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)"
         }
      },
      {
         "repeatCell":{
            "range":{
               "startRowIndex":2,
               "endRowIndex":3,
               "endColumnIndex":3
            },
            "cell":{
               "userEnteredFormat":{
                  "backgroundColor":{
                     "red":255 / 255,
                     "green":255 / 255,
                     "blue":204 / 255
                  },
                  "horizontalAlignment":"CENTER",
                  "textFormat":{
                     "fontSize":12,
                     "bold":True
                  }
               }
            },
            "fields":"userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)"
         }
      }
   ]
}

• In your showing request body, sheetId is not used. So, 1st sheet is used. If you want to use other sheet, please add sheetId in the range.

Reference:

• Method: spreadsheets.batchUpdate

Answered By - Tanaike