将背景颜色添加到单元格Google工作表(Python) [英] Add background color to cell Google sheet (python)
问题描述
(更新Google电子表格单元格(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.
这是我更新工作表的代码部分.我通过服务和行.当我只在单个单元格中使用'values'变量而不是对行背景进行修改时，就写了'OK'，它起作用了:
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
提前谢谢！
推荐答案
我相信您的目标如下.
- 您要使用带有python的googleapis修改单元格或行的背景颜色.
为此，这个答案如何?在该答案中，"spreadsheets.batchUpdate"的方法被称为"spreadsheets.在Sheets API中使用.在batchUpdate方法中，将GridRange用作范围. 参考
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
在此模式下，将修改单元格的背景色.
In this pattern, the background color of a cell is modified.
在此示例脚本中，单元格"A1"的背景色为sheet_id的纸页被修改为红色.
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()
模式2:
在这种模式下，一行的背景颜色被修改.
Pattern 2:
In this pattern, the background color of a row is modified.
在此示例脚本中，sheet_id表的第一行的背景色被修改为红色.
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()
- 在这种情况下，将
endColumnIndex从模式1中删除.这样，将使用一行. - In this case,
endColumnIndexis removed from the pattern 1. By this, a row is used. - Method: spreadsheets.batchUpdate
- RepeatCellRequest
当您要为单元格添加值并修改行的背景颜色时，以下示例脚本如何?在这种情况下，放置值的请求将添加到batchUpdate的请求中.
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.
而且，您还想从工作表名称中检索工作表ID.关于这一点，我添加了以下脚本.
And also, you want to retrieve the sheet ID from the sheet name. About this, I added the following script.
在该示例脚本中，"sample"的值等于0.将其放到单元格"A1"中.并将第一行的背景颜色修改为红色.
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()
已添加2:
您的脚本有几个修改点:
Added2:
Your script has several modification points:
-
在
updateCells，如果要将OK的值放在"L"列中，请进行如下修改.
At
updateCells, if you want to put the value ofOKto the column "L", please modify as follows.
-
来自
From
"startColumnIndex": 12,
"endColumnIndex": 12
收件人
To
"startColumnIndex": 11,
"endColumnIndex": 12
当您要在"M"列中添加时，请修改"startColumnIndex": 12,endColumnIndex": 13.
在repeatCell处，WORKSHEET_NAME用于工作表ID.请修改为"sheetId": sheet_id,.
At repeatCell, WORKSHEET_NAME is used for the sheet ID. Please modify to "sheetId": sheet_id,.
这篇关于将背景颜色添加到单元格Google工作表(Python)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
