使用gspread设置Google Sheets指定单元格区域垂直居中问题

问题场景 #

使用gspread操作Google Sheets时，第一行单元格因包含换行符（\n），垂直对齐自动设为底部，需要将A1:Z1区域设置为垂直居中。原代码中设置单元格加粗正常，但尝试以下写法时触发API错误：

sheet.format("A1:Z1", {"verticalAlignment": {"MIDDLE": True}})

报错信息 #

gspread.exceptions.APIError: {'code': 400, 'message': "Invalid value at 'requests[0].repeat_cell.cell.user_entered_format' (vertical_alignment), Starting an object on a scalar field", 'status': 'INVALID_ARGUMENT', 'details': [{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'requests[0].repeat_cell.cell.user_entered_format', 'description': "Invalid value at 'requests[0].repeat_cell.cell.user_entered_format' (vertical_alignment), Starting an object on a scalar field"}]}]}

错误原因 #

Google Sheets API中，verticalAlignment是标量字段，直接赋值为对应的字符串枚举值即可，不需要用键值对的对象格式。之前的写法错误地将其作为嵌套对象传递，导致参数格式不符合API要求。

修正方案 #

将垂直居中的格式设置代码改为：

sheet.format("A1:Z1", {"verticalAlignment": "MIDDLE"})

完整修正代码 #

import gspread
from google.oauth2.service_account import Credentials

scopes = ["https://www.googleapis.com/auth/spreadsheets"]
creds = Credentials.from_service_account_file("credentials.json", scopes=scopes)
client = gspread.authorize(creds)

sheet_id = "url"
workbook = client.open_by_key(sheet_id)

worksheet_list = map(lambda x: x.title, workbook.worksheets())
new_worksheet_name = "template"

# 检查新工作表是否已存在
if new_worksheet_name in worksheet_list:
    sheet = workbook.worksheet(new_worksheet_name)
else:
    sheet = workbook.add_worksheet(new_worksheet_name, rows=91, cols=30)

values = [
    ["Data\nYYYY/MM/DD", "Totale\ninizio giorno", "Totale\ndopo refresh", "Set giornaliero", "Serie\ngiornaliera"],
]

sheet.clear()

sheet.update(values, f"A1:Z{len(values)}")

# 设置单元格加粗
sheet.format("A1:Z1", {"textFormat": {"bold": True}})
# 设置垂直居中对齐
sheet.format("A1:Z1", {"verticalAlignment": "MIDDLE"})

内容的提问来源于stack exchange，提问作者gp12