Search code examples
pythongoogle-sheets-apibackground-colorbatch-updates

Python + Sheet | May I know the syntax to change BGcolor using BatchUpdate?

The following python code is a BatchUpdate that:

  1. Add a number 'x' to Column H of row
  2. Change the whole row to green

There can be 1 or many rows involved. How do I change the background color? Also, if rowListTH[] only has 1 item, will this code still work?

ssName = ssTH.title + '!'
request_body = {
    "valueInputOption": "RAW",
    "data": [
        {
            'range': ssName + 'H'+rowListTH[-1]+':H'+rowListTH[0],
            'values': [
                {
                    [str(x)],
                    "backgroundColor": {"green": 1}
                }
                ]
        },
    ]
}
service.spreadsheets().values().batchUpdate(
    spreadsheetId = ssID,
    body = request_body
).execute()

================================== EDIT ==================================

I have made the changes, but I could not get the update to color the entire row or column, it will always color only a single cell. Please check where do I do wrongly

I want to color rows 5-12 column A to I. For my result, I only get cell A5 colored.

request_body = {
"updateCells":{
    "rows":[
        {
            "values":[
                {
                    "userEnteredFormat":{
                        "backgroundColor":{
                            "red": 0,
                            "green": 1,
                            "blue": 0,
                            "alpha": 1
                        }
                    }
                }
            ]
        }
            ],
            "fields":"userEnteredFormat.backgroundColor",
            "range":{
                "sheetId": TH_gid,
                "startRowIndex": 4,
                "endRowIndex": 11,
                "startColumnIndex": 0,
                "endColumnIndex": 9
            }
        }
    }
body = {
    "requests": request_body
}
response = service.spreadsheets().batchUpdate(spreadsheetId=ssID, body=body).execute()

BGcolor

Solution

  • Answer:

    To change the background colour of a cell you need to use the spreadsheets.batchUpdate endpoint, not the spreadsheets.values.batchUpdate endpoint.

    Example request:

    request_body = {
    "updateCells":{
        "rows":[
            {
                "values":[
                    {
                        "userEnteredFormat":{
                            "backgroundColor":{
                                "red": 0,
                                "green": 1,
                                "blue": 0,
                                "alpha": 1
                            }
                        }
                    }
                ]
            }
        ],
        "fields":"userEnteredFormat.backgroundColor",
        "range":{
            "sheetId": sheet-id,
            "startRowIndex": 0,
            "endRowIndex": 0,
            "startColumnIndex": 0,
            "endColumnIndex": 1
        }
    }
}

body = {
    "requests": request_body
}

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

    Things you will need to change:

    • The red, green, blue and alpha values. As per the documentation, these are floating point representations between 0 and 1 of the RGB colourspace with a denominator of 255.
      • For example, for white, which is #FFFFFF or 255 255 255 you would use for each of red, green and blue.
    • The Sheet ID - this is NOT the spreadsheet ID, this is the #gid which is seen in the URL when you view the Spreadsheet in a browser. Sheet1 by default is always 0, but any added sheets are random. This value is an integer.
    • The start and end column and rows:
      • startRowIndex, endRowIndex, startColumnIndex and endColumnIndex are 0-indexed. This means that if you want to colour only cell A1, then your request would be:
    "startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 1

    To colour an entire column, you would first need to find out the number of rows and make the following request (assumiung column A):

    "startRowIndex": 0,
"endRowIndex": number_of_rows - 1,
"startColumnIndex": 0,
"endColumnIndex": 1

    As this is a different endpoing than the one you have used, this will need to be made as a separate HTTP request.

    References: