如何利用另一工作表的列表筛选Smartsheet中的数据

需求背景 #

我是自学的业余Python程序员，刚接触Smartsheet API，认可它的应用潜力。所在组织用Smartsheet开展业务流程，现有两个数据源：

• 生产计划表（主数据源）：存储原始生产数据
• 商品目录表：包含产品SKU及检测信息，其中Micro Testing列取值为Yes或No

需要实现：筛选生产计划表中**SKU存在于商品目录表且对应Micro Testing值为Yes**的行，最终生成包含符合条件数据的新工作表。

当前脚本原本是检查生产计划表自身的Micro Testing列，但主数据源无法修改，因此要改成通过商品目录表的SKU列表做匹配筛选（类似Excel的INDEX/MATCH/COLLECT逻辑）。

修改后的实现脚本 #

import smartsheet
import os
import logging

_dir = os.path.dirname(os.path.abspath(__file__))

def get_cell_by_column_name(row, column_map, column_name):
    """根据列名获取单元格"""
    column_id = column_map[column_name]
    return row.get_column(column_id)

def build_sku_micro_map(smart, catalog_sheet_id):
    """从商品目录表构建SKU到Micro Testing状态的映射"""
    sku_micro_map = {}
    catalog_sheet = smart.Sheets.get_sheet(catalog_sheet_id)
    
    # 构建商品目录表的列映射
    catalog_column_map = {col.title: col.id for col in catalog_sheet.columns}
    
    for row in catalog_sheet.rows:
        sku_cell = get_cell_by_column_name(row, catalog_column_map, "SKU")
        micro_cell = get_cell_by_column_name(row, catalog_column_map, "Micro Testing")
        
        if sku_cell.display_value and micro_cell.display_value:
            sku_micro_map[sku_cell.display_value] = micro_cell.display_value
    
    return sku_micro_map

def filter_production_rows(production_sheet, sku_micro_map, production_column_map):
    """筛选符合条件的生产计划行"""
    qualified_rows = []
    for row in production_sheet.rows:
        sku_cell = get_cell_by_column_name(row, production_column_map, "SKU")
        sku_value = sku_cell.display_value
        
        # 检查SKU是否在映射中且Micro Testing为Yes
        if sku_value in sku_micro_map and sku_micro_map[sku_value] == "Yes":
            qualified_rows.append(row)
    
    return qualified_rows

def create_new_sheet(smart, qualified_rows, production_column_map, sheet_name="合格生产计划表"):
    """创建包含筛选结果的新工作表"""
    # 创建新表
    new_sheet = smart.Home.create_sheet(
        smartsheet.models.Sheet({
            "name": sheet_name,
            "columns": [
                smartsheet.models.Column({
                    "title": col_title,
                    "type": "TEXT_NUMBER"  # 根据实际列类型调整
                }) for col_title in production_column_map.keys()
            ]
        })
    ).data
    
    # 准备要插入的行数据
    rows_to_insert = []
    for row in qualified_rows:
        new_cells = []
        for col_title, col_id in production_column_map.items():
            cell = get_cell_by_column_name(row, production_column_map, col_title)
            target_col_id = [col.id for col in new_sheet.columns if col.title == col_title][0]
            new_cells.append(smartsheet.models.Cell({
                "column_id": target_col_id,
                "value": cell.value,
                "display_value": cell.display_value
            }))
        rows_to_insert.append(smartsheet.models.Row({"cells": new_cells}))
    
    # 插入行到新表
    if rows_to_insert:
        smart.Sheets.add_rows(new_sheet.id, rows_to_insert)
        print(f"已创建新工作表，包含{len(rows_to_insert)}条符合条件的数据")
    return new_sheet

if __name__ == "__main__":
    print("Starting...")
    
    # 初始化Smartsheet客户端
    smart = smartsheet.Smartsheet()
    smart.errors_as_exceptions(True)
    
    # 配置日志
    logging.basicConfig(filename='rwsheet.log', level=logging.INFO)
    print("Logging in-process")
    
    # --------------------------
    # 替换为你的实际配置
    CATALOG_SHEET_ID = 123456789  # 商品目录表ID（从Smartsheet页面URL获取）
    PRODUCTION_XLSX_PATH = _dir + '/Production Schedule.xlsx'
    # --------------------------
    
    # 导入生产计划表
    production_import = smart.Sheets.import_xlsx_sheet(PRODUCTION_XLSX_PATH, header_row_index=0)
    production_sheet = smart.Sheets.get_sheet(production_import.data.id)
    print(f"Loaded {len(production_sheet.rows)} rows from sheet: {production_sheet.name}")
    
    # 构建生产计划表的列映射
    production_column_map = {col.title: col.id for col in production_sheet.columns}
    
    # 构建SKU-Micro测试状态映射
    sku_micro_map = build_sku_micro_map(smart, CATALOG_SHEET_ID)
    print(f"Loaded {len(sku_micro_map)} SKU entries from catalog sheet")
    
    # 筛选符合条件的行
    qualified_rows = filter_production_rows(production_sheet, sku_micro_map, production_column_map)
    print(f"Found {len(qualified_rows)} qualified rows")
    
    # 创建新工作表
    create_new_sheet(smart, qualified_rows, production_column_map)
    
    print("Done")

关键改动说明 #

• 新增build_sku_micro_map函数：从商品目录表读取数据，构建SKU到Micro Testing状态的字典映射，实现快速查找
• 重构get_cell_by_column_name：新增列映射参数，支持多表的列名查找
• filter_production_rows函数：遍历生产计划表，检查每行SKU是否在映射中且对应状态为Yes，收集符合条件的行
• create_new_sheet函数：创建新工作表并插入筛选结果，完全避免修改主数据源
• 移除原脚本无效逻辑：原脚本中rowsToUpdate的赋值与追加逻辑存在错误，改为直接生成新表的方式

使用注意事项 #

1. 替换CATALOG_SHEET_ID为实际的商品目录表ID（可从Smartsheet页面URL末尾获取）
2. 确保生产计划表和商品目录表都有名称一致的SKU列
3. 若生产计划表存在非TEXT_NUMBER类型的列，需在创建新表时调整对应列的type参数（如DATE、CHECKBOX等）
4. 确保SMARTSHEET_ACCESS_TOKEN环境变量对应的令牌拥有读取商品目录表、导入生产计划表和创建新表的权限

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