如何用gspread-formatting/gspread为Google Sheets整列设背景色

• 已查阅同类问题并尝试过社区现有解决方案，完成了前期自行排查
• 正在开展政治选区分析工作，需要将pandas DataFrame结果写入Google Sheets并自动给不同政党所属列设置对应背景色
• 现有代码除背景色设置功能外其余逻辑运行正常，原始代码如下：

import pandas as pd
from matplotlib import colors
from gspread_formatting import *
def save_to_google_sheets(df,spreadsheet_name,worksheet_name,folder_id=None,coloring={}):
    """save dataframe worksheet"""
    # get spreadsheet and worksheet instances (open if they exist, create if they don't)
    ss,ws=get_worksheet(spreadsheet_name,worksheet_name,folder_id)
    # save dataframe to worksheet
    set_with_dataframe(ws,df,include_index=True,include_column_header=True,resize=True)
    # figure out how many rows each column will be
    num_rows=len(ws.get_values())
    # get which level of the pandas MultiIndex tells us the 'party'
    col_pty_lvl=df.columns.names.index('party') if 'party' in df.columns.names else None
    if col_pty_lvl:
        # get all parties in race
        parties=ws.row_values(col_pty_lvl+1)
        # color columns for each party included in dictionary
        pty_colors={'democratic':'blue','republican':'red'}
        for pty,clr in pty_colors.items():
            # get red, green, and blue values for given color
            # ex: r=1.0, g=0.0, b=0.0
            r,g,b,a=colors.to_rgba(clr)
            # get index of all columns belonging to this party
            pty_cols=[i for i,p in enumerate(parties) if p==pty]
            # set background color for each column
            for c in pty_cols:
                fmt=CellFormat(backgroundColor=Color(r,g,b),
                    horizontalAlignment='CENTER')
                # get A1 notation of column
                # sample output: 'B1:B93'
                a1=f'{gspread.utils.rowcol_to_a1(1,c)}:{gspread.utils.rowcol_to_a1(num_rows,c)}'
                format_cell_range(ws,a1,fmt)

代码共有5处逻辑错误导致上色失败：

1. 条件判断错误：if col_pty_lvl:会在col_pty_lvl=0（即party层级是MultiIndex第一级）时返回False，直接跳过整个上色逻辑
2. 颜色格式不兼容：matplotlib.colors.to_rgba返回的r/g/b值是0_{1区间浮点数，而gspread-formatting的`Color`对象要求传入0}255区间的整数，直接传入会被识别为接近黑色的极暗颜色，视觉上和未设置背景无差异
3. 列索引偏移：enumerate(parties)返回的索引是0起始，而gspread.utils.rowcol_to_a1要求列参数为1起始，直接传入会导致列定位错位1位
4. 合并单元格识别遗漏：pandas MultiIndex写入Sheets时同层级同值单元格会自动合并，row_values读取合并区域时仅第一个单元格有值，其余位置为空，会导致对应列漏识别
5. 依赖缺失：代码调用了gspread.utils但未导入gspread模块，运行时会直接抛出名称错误

import pandas as pd
import gspread
from matplotlib import colors
from gspread_formatting import *

def save_to_google_sheets(df,spreadsheet_name,worksheet_name,folder_id=None,coloring={}):
    """save dataframe worksheet"""
    # 获取或创建目标工作表
    ss,ws=get_worksheet(spreadsheet_name,worksheet_name,folder_id)
    # 写入DataFrame内容
    set_with_dataframe(ws,df,include_index=True,include_column_header=True,resize=True)
    # 获取工作表总行数
    num_rows=len(ws.get_values())
    # 定位政党字段所在的列层级
    col_pty_lvl=df.columns.names.index('party') if 'party' in df.columns.names else None
    if col_pty_lvl is not None:
        # 读取政党层级的表头值
        parties=ws.row_values(col_pty_lvl+1)
        # 向前填充合并单元格的空值，补全所有列对应的政党名称
        current_party = None
        for idx, val in enumerate(parties):
            if val.strip():
                current_party = val.strip()
            parties[idx] = current_party
        # 配置各政党对应的填充色
        pty_colors={'democratic':'blue','republican':'red'}
        for pty,clr in pty_colors.items():
            # 转换matplotlib色值为Sheets要求的0-255整数格式
            r,g,b,a=colors.to_rgba(clr)
            sheet_color = Color(int(r*255), int(g*255), int(b*255))
            # 定位当前政党对应的所有列索引
            pty_cols=[i for i,p in enumerate(parties) if p.lower()==pty]
            # 逐列设置格式
            for col_idx in pty_cols:
                fmt=CellFormat(
                    backgroundColor=sheet_color,
                    horizontalAlignment='CENTER'
                )
                # 转换为A1范围标记（列索引+1适配1起始规则）
                start_cell = gspread.utils.rowcol_to_a1(1, col_idx+1)
                end_cell = gspread.utils.rowcol_to_a1(num_rows, col_idx+1)
                format_cell_range(ws, f"{start_cell}:{end_cell}", fmt)

• 调整层级判断条件为is not None，兼容第0层为政党字段的场景
• 补充gspread模块导入，解决依赖缺失问题
• 新增合并单元格空值向前填充逻辑，避免同政党列漏识别
• 色值统一转换为0~255整数区间，匹配Sheets API的颜色格式要求
• 列索引转换A1标记时统一+1，修复列错位问题
• 新增政党名称大小写兼容，避免大小写差异导致识别失败

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