如何使用pygsheets为Google Sheets设置单元格条件格式

以下是针对你需求的完整实现方案，结合**条件格式（不符合时背景变红）和数据验证（限制输入）**来实现目标：

完整代码 #

import pygsheets

# 授权并打开工作表
service = pygsheets.authorize(service_file='key.json') 
spread_sheet_id = "你的表格ID"
spreadsheet = pygsheets.Spreadsheet(client=service, id=spread_sheet_id)
worksheet = spreadsheet.worksheet(0)

# 设置表头格式
header_list = ["number int", "email", "date"]
for index, element in enumerate(header_list):
    header_cell = worksheet.cell(f"{chr(65+index)}1")
    header_cell.value = element
    header_cell.text_format['bold'] = True
    header_cell.color = (0.9529412, 0.9529412, 0.9529412, 0)  # 浅灰色背景
    header_cell.update()

# --------------------------
# 1. A2-A5：仅允许0-50之间的数字
# --------------------------
# 添加条件格式：值不在0-50之间时，背景设为浅红色
worksheet.add_conditional_formatting(
    start='A2',
    end='A5',
    condition_type='NUMBER_NOT_BETWEEN',
    format={'backgroundColor': {'red': 1, 'green': 0.8, 'blue': 0.8}},
    condition_values=['0', '50']
)

# 添加数据验证：限制输入0-50的数字，输入无效时弹出提示
num_validation = pygsheets.DataValidation(
    type='NUMBER',
    criteria='BETWEEN',
    value1=0,
    value2=50,
    input_message='请输入0到50之间的整数',
    strict=True,
    show_custom_ui=True
)
worksheet.set_data_validation('A2:A5', num_validation)

# --------------------------
# 2. B2-B5：仅允许@gmail.com或@yahoo.com结尾的邮箱
# --------------------------
# 添加条件格式：邮箱不符合规则时，背景设为浅红色
worksheet.add_conditional_formatting(
    start='B2',
    end='B5',
    condition_type='CUSTOM_FORMULA',
    format={'backgroundColor': {'red': 1, 'green': 0.8, 'blue': 0.8}},
    condition_values=['=NOT(OR(REGEXMATCH(B2,"@gmail.com$"),REGEXMATCH(B2,"@yahoo.com$")))']
)

# 添加数据验证：限制输入指定后缀的邮箱
email_regex = r'.*@(gmail|yahoo)\.com$'
email_validation = pygsheets.DataValidation(
    type='TEXT',
    criteria='REGEX_MATCH',
    value1=email_regex,
    input_message='请输入以@gmail.com或@yahoo.com结尾的邮箱',
    strict=True,
    show_custom_ui=True
)
worksheet.set_data_validation('B2:B5', email_validation)

# --------------------------
# 3. C2-C5：仅允许有效日期格式
# --------------------------
# 添加条件格式：非有效日期时，背景设为浅红色
worksheet.add_conditional_formatting(
    start='C2',
    end='C5',
    condition_type='CUSTOM_FORMULA',
    format={'backgroundColor': {'red': 1, 'green': 0.8, 'blue': 0.8}},
    condition_values=['=NOT(ISDATE(C2))']
)

# 添加数据验证：限制输入有效日期
date_validation = pygsheets.DataValidation(
    type='DATE',
    criteria='IS_VALID',
    input_message='请输入有效的日期（例如：2024-05-20）',
    strict=True,
    show_custom_ui=True
)
worksheet.set_data_validation('C2:C5', date_validation)

关键说明 #

1. 条件格式：通过add_conditional_formatting实现不符合规则时的背景色提醒，针对不同需求使用了不同的条件类型：
   • 数字范围用NUMBER_NOT_BETWEEN直接匹配
   • 邮箱和日期使用自定义公式（CUSTOM_FORMULA），借助Google Sheets内置函数REGEXMATCH和ISDATE实现规则判断
2. 数据验证：通过set_data_validation限制用户输入，确保只有符合规则的值才能被输入，同时配置了提示信息帮助用户理解要求
3. 修正了你代码中的几个错误：
   • 表头部分的wksheet拼写错误，改为worksheet
   • 取消了无效的set_number_format操作，改用更直接的条件格式和数据验证方案

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