在google表格中更新颜色

Question

我试图将列从一个工作表复制到另一个工作表。我从源表中得到响应的列。我需要把它们插到纸上。因为像insertDimension和insertRange这样的方法不能这样做。我使用request = service.spreadsheets().values().update(spreadsheetId=to_spreadsheet_id, range=range_, valueInputOption = "USER_ENTERED", body={"values": response})，但它给了我这样的错误:- googleapicclient .errors.HttpError:

如果我使用其他方式，在代码块中注释，我得到以下错误详细信息:"无效请求[0]。updateCells:试图写入行:15000，超出了最后请求的行:14999">

def copy_column(service, from_spreadsheet_id, to_spreadsheet_id, from_sheet='Analysis', to_sheet_id, from_column='F', from_column_till='K', to_column='G'):
request = service.spreadsheets().get(spreadsheetId=from_spreadsheet_id, ranges=[
from_sheet + "!" + from_column + ":" + from_column_till], includeGridData=True)
response = request.execute()["sheets"][0]["data"][0]["rowData"]
range_ = "Working Analysis!G2"
print(response)
# value_range_body = {
#     "requests": {
#         "insertDimension": {
#             "range": {
#                 "sheetId": to_sheet_id,
#                 "dimension": "COLUMNS",
#                 "startIndex": 6,
#                 "endIndex": 11
#             },
#             "inheritFromBefore": True
#         }
#     }
# }
# request_1 = service.spreadsheets().batchUpdate(spreadsheetId=to_spreadsheet_id, body=value_range_body)
# response_1 = request_1.execute()
# body = {
#     "requests": {
#         "updateCells": {
#             "rows": response,
#             "fields": "userEnteredFormat, userEnteredValue",
#             # "start":{
#             #     "sheetId": to_sheet_id,
#             #     "rowIndex": 1,
#             #     "columnIndex": 6
#             # },
#             "range": {
#                 "sheetId": to_sheet_id,
#                 "startRowIndex": 1,
#                 "startColumnIndex": 6,
#                 "endColumnIndex": 13
#             },
#         }
#     }
# }
request = service.spreadsheets().values().update(spreadsheetId=to_spreadsheet_id,
range=range_, valueInputOption = "USER_ENTERED", body={"values": response})
response = request.execute()
return print('Done')

Answer 1

我认为service.spreadsheets().get()的响应值不能直接用于service.spreadsheets().values().update()。从您的注释脚本中，我猜您可能不仅要复制值，还要复制单元格格式。

在这种情况下，下面的修改如何?

修改脚本:

sheetId = "###"  # Please set the sheet ID of the sheet "Working Analysis"
request = service.spreadsheets().get(spreadsheetId=from_spreadsheet_id, ranges=[from_sheet + "!" + from_column + ":" + from_column_till], includeGridData=True)
response = request.execute()["sheets"][0]["data"][0]["rowData"]
requests = {
"requests": [
{
"updateCells": {
"start": {"sheetId": sheetId, "rowIndex": 1, "columnIndex": 6},
"rows": response,
"fields": "*",
}
}
]
}
request = service.spreadsheets().batchUpdate(spreadsheetId=to_spreadsheet_id, body=requests)
response = request.execute()
print("Done")

例如，如果您只想复制值，则可以使用service.spreadsheets().values().update()，如下所示。

request = service.spreadsheets().values().get(spreadsheetId=from_spreadsheet_id, range=from_sheet + "!" + from_column + ":" + from_column_till)
response = request.execute()["values"]
range_ = "Working Analysis!G2"
request = service.spreadsheets().values().update(spreadsheetId=to_spreadsheet_id, range=range_, valueInputOption="USER_ENTERED", body={"values": response})
response = request.execute()
return print("Done")

引用:

• 方法:spreadsheets.batchUpdate
• 方法:spreadsheets.values.get