CODEWITHSUNDEEP
pythongoogle-sheetsbackground-colorgoogle-sheets-api
dtamab
dtamab

Reputation: 85

Add background color to cell Google sheet (python)

related to this question (Update Google spreadsheet cell (python)), I'd like to know how to change the background color of a cell (or a full row) in the link example.

This is the part of my code where I update the sheet. I pass the service and the row. It worked when I only wrote 'OK' in a single cell with the 'values' variable, but not with the modifications for the background of the row:

SPREADSHEET_ID = '###'
WORKSHEET_NAME = 'Name of the sheet'

async def escribirEnSheet(service, row):
    # print('voy a escribir en celda ' + str(range_))
    # range_ = 'M'+str(row)  # TODO: Update placeholder value.
    row += 1
    range_ = WORKSHEET_NAME + "!M" + str(row)
    print('voy a escribir en celda: ' + str(range_))

#values = [['OK']]

sheetObj = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID, fields='sheets(properties(sheetId,title))').execute()
sheet_id = ""
for sheet in sheetObj['sheets']:
    if sheet['properties']['title'] == WORKSHEET_NAME:
        sheet_id = sheet['properties']['sheetId']
        break


batch_update_spreadsheet_request_body = {
    "requests": [
        {
            "updateCells": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": row,
                    "endRowIndex": row+1,
                    "startColumnIndex": 12,
                    "endColumnIndex": 12
                },
                "rows": [
                    {
                        "values": [
                            {
                                "userEnteredValue": {
                                    "stringValue": "OK"
                                }
                            }
                        ]
                    }
                ],
                "fields": "userEnteredValue"
            }
        },
        {
            "repeatCell": {
                "range": {
                    "sheetId": WORKSHEET_NAME,
                    "startRowIndex": row,
                    "endRowIndex": row+1,
                    "startColumnIndex": 0,
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": 0,
                            "green": 1,
                            "blue": 0
                        }
                    }
                },
                "fields": "userEnteredFormat.backgroundColor"
            }
        }
    ]
}

value_input_option = 'RAW'  # TODO: Update placeholder value.


try:
    print('voy a hacer la request para escribir')
    
    request = service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=batch_update_spreadsheet_request_body)
    response = request.execute()
    time.sleep(1)
    print('ya he escrito')
    
    print(response)
except:
    print('algo ha ocurrido al escribir en la sheet')
    traceback.print_exc()
    pass

Thanks in advance!

Upvotes: 0

Views: 2447

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to modify the background color of a cell or a row using googleapis with python.

For this, how about this answer? In this answer, the method of "spreadsheets.batchUpdate" in Sheets API is used. At the batchUpdate method, the GridRange is used as the range. Ref

Pattern 1:

In this pattern, the background color of a cell is modified.

Sample script:

In this sample script, the background color of the cell "A1" of the sheet of sheet_id is modified to red.

SPREADSHEET_ID = '###'  # Please set the Spreadsheet ID.
sheet_id = '###'  # Please set the sheet ID.

service = build('sheets', 'v4', credentials=creds)
batch_update_spreadsheet_request_body = {
    "requests": [
        {
            "repeatCell": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": 1
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": 1,
                            "green": 0,
                            "blue": 0
                        }
                    }
                },
                "fields": "userEnteredFormat.backgroundColor"
            }
        }
    ]
}

request = service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=batch_update_spreadsheet_request_body)
res = request.execute()

Pattern 2:

In this pattern, the background color of a row is modified.

Sample script:

In this sample script, the background color of the 1st row of the sheet of sheet_id is modified to red.

SPREADSHEET_ID = '###'  # Please set the Spreadsheet ID.
sheet_id = '###'  # Please set the sheet ID.

service = build('sheets', 'v4', credentials=creds)
batch_update_spreadsheet_request_body = {
    "requests": [
        {
            "repeatCell": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": 1,
                            "green": 0,
                            "blue": 0
                        }
                    }
                },
                "fields": "userEnteredFormat.backgroundColor"
            }
        }
    ]
}

request = service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=batch_update_spreadsheet_request_body)
res = request.execute()
  • In this case, endColumnIndex is removed from the pattern 1. By this, a row is used.

References:

Added1:

When you want to put a value to a cell and modify the background color of the row, how about the following sample script? In this case, the request for putting a value is added to the requests for the batchUpdate.

And also, you want to retrieve the sheet ID from the sheet name. About this, I added the following script.

Sample script:

In this sample script, a value of "sample" is put to the cell "A1" and the background color of the 1st row is modified to red.

SPREADSHEET_ID = '###'  # Please set the Spreadsheet ID.
sheet_name = 'Sheet1'  # Please set the sheet name.

service = build('sheets', 'v4', credentials=creds)
sheetObj = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID, fields='sheets(properties(sheetId,title))').execute()
sheet_id = ""
for sheet in sheetObj['sheets']:
    if sheet['properties']['title'] == sheet_name:
        sheet_id = sheet['properties']['sheetId']
        break

batch_update_spreadsheet_request_body = {
    "requests": [
        {
            "updateCells": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": 1
                },
                "rows": [
                    {
                        "values": [
                            {
                                "userEnteredValue": {
                                    "stringValue": "sample"
                                }
                            }
                        ]
                    }
                ],
                "fields": "userEnteredValue"
            }
        },
        {
            "repeatCell": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": 1,
                            "green": 0,
                            "blue": 0
                        }
                    }
                },
                "fields": "userEnteredFormat.backgroundColor"
            }
        }
    ]
}

request = service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=batch_update_spreadsheet_request_body)
res = request.execute()

Added2:

Your script has several modification points:

Modification points:

  • At updateCells, if you want to put the value of OK to the column "L", please modify as follows.

    • From

        "startColumnIndex": 12,
  "endColumnIndex": 12

    • To

        "startColumnIndex": 11,
  "endColumnIndex": 12

    • When you want to put to the column "M", please modify "startColumnIndex": 12,endColumnIndex": 13.

  • At repeatCell, WORKSHEET_NAME is used for the sheet ID. Please modify to "sheetId": sheet_id,.

Upvotes: 3

Related Questions