Python实现Google Sheets数据匹配更新与新增功能求助

核心逻辑 #

1. 读取目标工作表的现有数据，提取前两列作为唯一匹配键，建立键到行号的映射
2. 遍历清洗后的上传数据行，检查匹配键是否存在于映射中
3. 存在匹配则更新对应行，无匹配则追加新行

代码修改部分 #

假设你已经完成了Streamlit文件上传、数据清洗以及Google Sheets的授权逻辑，以下是关键修改代码：

1. 生成工作表匹配映射函数

import gspread
from google.oauth2.service_account import Credentials
import pandas as pd
import streamlit as st

def get_sheet_key_mapping(sheet):
    # 获取工作表所有数据（含表头）
    raw_data = sheet.get_all_records()
    sheet_df = pd.DataFrame(raw_data)
    
    # 构建前两列到行号的映射（Google Sheets行号从1开始，表头占第1行，数据行从第2行起）
    key_to_row = {}
    for data_idx, row in sheet_df.iterrows():
        # 取前两列作为匹配键，确保键是可哈希类型（字符串/数字）
        match_key = (str(row.iloc[0]), str(row.iloc[1]))
        key_to_row[match_key] = data_idx + 2  # 转换为实际工作表行号
    
    return key_to_row, sheet_df.columns.tolist()

2. 处理上传文件的匹配更新逻辑

# 假设已完成Google Sheets授权
SCOPE = ["https://www.googleapis.com/auth/spreadsheets"]
CREDS = Credentials.from_service_account_file("你的服务账号密钥.json", scopes=SCOPE)
CLIENT = gspread.authorize(CREDS)
SPREADSHEET_NAME = "你的Google Sheets文件名"
# 上传文件与目标工作表的对应关系
FILE_SHEET_MAP = {0: "工作表1", 1: "工作表2", 2: "工作表3"}

# Streamlit文件上传组件（你的现有代码）
uploaded_files = st.file_uploader("上传3个Excel文件", type=["xlsx"], accept_multiple_files=True)

if len(uploaded_files) == 3:
    for file_idx, uploaded_file in enumerate(uploaded_files):
        # 读取并清洗数据（替换成你的现有清洗逻辑）
        df_clean = pd.read_excel(uploaded_file)
        # 这里加入你的数据清洗代码...
        
        # 获取目标工作表
        target_sheet = CLIENT.open(SPREADSHEET_NAME).worksheet(FILE_SHEET_MAP[file_idx])
        # 获取匹配映射和工作表列名
        key_mapping, sheet_columns = get_sheet_key_mapping(target_sheet)
        
        # 校验列名一致性，避免数据错位
        if list(df_clean.columns) != sheet_columns:
            st.error(f"文件{file_idx+1}的列与目标工作表{FILE_SHEET_MAP[file_idx]}列不匹配")
            continue
        
        # 遍历处理每一行
        processed_rows = 0
        total_rows = len(df_clean)
        progress_bar = st.progress(0)
        
        for row_idx, row in df_clean.iterrows():
            # 生成当前行的匹配键
            current_key = (str(row.iloc[0]), str(row.iloc[1]))
            
            if current_key in key_mapping:
                # 匹配到现有行，执行更新
                target_row = key_mapping[current_key]
                # 计算最后一列的字母（比如列数为5则是E）
                last_col = chr(ord('A') + len(sheet_columns) - 1)
                # 更新整行数据
                target_sheet.update(f"A{target_row}:{last_col}{target_row}", [row.tolist()])
            else:
                # 无匹配，追加新行
                target_sheet.append_row(row.tolist())
            
            processed_rows += 1
            progress_bar.progress(processed_rows / total_rows)
        
        st.success(f"文件{file_idx+1}处理完成，已同步到工作表{FILE_SHEET_MAP[file_idx]}")
else:
    st.warning("请上传恰好3个Excel文件")

关键注意事项 #

• 匹配键类型统一：将前两列数据转为字符串，避免因数字/字符串类型不一致导致匹配失败
• API请求优化：如果数据量较大，建议批量收集更新行，一次性调用update方法，减少API请求次数（Google Sheets API有请求频率限制）
• 权限验证：确保你的服务账号拥有目标Google Sheets的编辑权限
• 列名校验：必须保证清洗后的数据列与目标工作表列完全一致，否则会出现数据错位

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