The following python code is a BatchUpdate that:
- Add a number 'x' to Column H of row
- 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()
Answer:
To change the background colour of a cell you need to use the
spreadsheets.batchUpdate
endpoint, not thespreadsheets.values.batchUpdate
endpoint.Example request:
Things you will need to change:
red
,green
,blue
andalpha
values. As per the documentation, these are floating point representations between 0 and 1 of the RGB colourspace with a denominator of 255.#FFFFFF
or255 255 255
you would use for each ofred
,green
andblue
.#gid
which is seen in the URL when you view the Spreadsheet in a browser.Sheet1
by default is always0
, but any added sheets are random. This value is an integer.startRowIndex
,endRowIndex
,startColumnIndex
andendColumnIndex
are 0-indexed. This means that if you want to colour only cellA1
, then your request would be:To colour an entire column, you would first need to find out the number of rows and make the following request (assumiung column A):
As this is a different endpoing than the one you have used, this will need to be made as a separate HTTP request.
References:
UpdateCellsRequest
| Sheets API | Google DevelopersCellFormat
| Sheets API | Google DevelopersGridRange
| Sheets API | Google DevelopersColor
| Sheets API | Google Developers