#!/usr/bin/env python3 """一次性修复： 1. 每日线索底部加月度合计行 2. 落单渠道分布重构（合计在前，先总GMV再拆渠道） 3. 销转总览 GMV列格式改为金额 4. 过程数据阶段上课人数改为累计口径 + 5月Tom补全 """ import json, requests, os, time, sys CRED_DIR = "/root/.openclaw/credentials/xiaoxi" SPREADSHEET_TOKEN = "NoZqsFi47hIOHEt9j8WcfRtbnug" def get_token(): with open(os.path.join(CRED_DIR, "config.json")) as f: cfg = json.load(f) resp = requests.post( "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal", json={"app_id": cfg["apps"][0]["appId"], "app_secret": cfg["apps"][0]["appSecret"]}, timeout=15) return resp.json()["tenant_access_token"] token = get_token() def write_values(sheet_id, range_str, values): resp = requests.put( f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values", headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"}, json={"valueRange": {"range": range_str, "values": values}}, params={"valueInputOption": "USER_ENTERED"}, timeout=60) result = resp.json() ok = result.get("code") == 0 if not ok: print(f" ❌ {range_str}: code={result.get('code')} msg={result.get('msg')}") else: print(f" ✅ {range_str}") return ok # ============================================================ # 1. 每日线索 底部加月度合计行 (row 96-99) # ============================================================ print("=== 1. 每日线索 月度合计 ===") monthly_totals = [ ["3月合计", '=SUMIF(A2:A95,"3月*",B2:B95)', '=SUMIF(A2:A95,"3月*",C2:C95)', '=SUMIF(A2:A95,"3月*",D2:D95)', '=SUMIF(A2:A95,"3月*",E2:E95)', '=SUM(F96:I96)'], ["4月合计", '=SUMIF(A2:A95,"4月*",B2:B95)', '=SUMIF(A2:A95,"4月*",C2:C95)', '=SUMIF(A2:A95,"4月*",D2:D95)', '=SUMIF(A2:A95,"4月*",E2:E95)', '=SUM(F97:I97)'], ["5月合计", '=SUMIF(A2:A95,"5月*",B2:B95)', '=SUMIF(A2:A95,"5月*",C2:C95)', '=SUMIF(A2:A95,"5月*",D2:D95)', '=SUMIF(A2:A95,"5月*",E2:E95)', '=SUM(F98:I98)'], ["6月合计", '=SUMIF(A2:A95,"6月*",B2:B95)', '=SUMIF(A2:A95,"6月*",C2:C95)', '=SUMIF(A2:A95,"6月*",D2:D95)', '=SUMIF(A2:A95,"6月*",E2:E95)', '=SUM(F99:I99)'], ] write_values("1ANW6c", "1ANW6c!A96:F99", monthly_totals) # ============================================================ # 2. 落单渠道分布重构 # 新布局: 月份 | 销售 | 总GMV | 总订单 | 销转GMV | 销转订单 | 端内GMV | 端内订单 | 直购GMV | 直购订单 # 合计行放在每个月的第一行 # ============================================================ print("\n=== 2. 落单渠道分布重构 ===") # 先写新表头 new_header = [["月份", "销售", "总GMV", "总订单", "销转渠道-GMV", "销转渠道-订单", "端内-GMV", "端内-订单", "直购-GMV", "直购-订单"]] write_values("3dIXcs", "3dIXcs!A1:J1", new_header) # 新布局: 每个月 合计 → 小龙 → 吴迪 → Bob → Tom months_list = ["3月", "4月", "5月", "6月"] all_sales = { "3月": ["合计", "小龙", "Bob", "Tom"], "4月": ["合计", "小龙", "吴迪", "Bob", "Tom"], "5月": ["合计", "小龙", "吴迪", "Bob", "Tom"], "6月": ["合计", "小龙", "吴迪", "Bob", "Tom"], } row_idx = 2 for month in months_list: for sales in all_sales[month]: if sales == "合计": total_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*")' total_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*")' xz_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"销转")' xz_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"销转")' dn_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"端内")' dn_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"端内")' zg_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"直购")' zg_orders = f'=COUNTIFS(\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"直购")' else: total_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*")' total_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*")' xz_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!J:J,"销转")' xz_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"销转")' dn_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!J:J,"端内")' dn_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"端内")' zg_gmv = f'=SUMIFS(\'订单汇总\'!G:G,\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!J:J,"直购")' zg_orders = f'=COUNTIFS(\'订单汇总\'!A:A,"{sales}",\'订单汇总\'!B:B,"{month}*",\'订单汇总\'!J:J,"直购")' row = [[month, sales, total_gmv, total_orders, xz_gmv, xz_orders, dn_gmv, dn_orders, zg_gmv, zg_orders]] write_values("3dIXcs", f"3dIXcs!A{row_idx}:J{row_idx}", row) row_idx += 1 time.sleep(0.3) # 清除旧数据（row 20+ 如果有的话） print(" 清除旧数据行...") write_values("3dIXcs", "3dIXcs!A21:J30", [[None]*10]*10) # ============================================================ # 3. 销转总览 GMV列(F) 格式改为金额 # ============================================================ print("\n=== 3. 销转总览 GMV格式 ===") # 飞书 API 设置单元格格式 resp = requests.put( f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/style", headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"}, json={ "appendStyle": { "range": "3fR34k!F2:F20", "style": { "formatter": "0.00" } } }, timeout=30) print(f" GMV格式: code={resp.json().get('code')} msg={resp.json().get('msg')}") # 也设置 G(退款) H(GSV) I(消耗) J(达人GMV) K(佣金) L(总成本) 为金额格式 for col in ["G", "H", "I", "J", "K", "L"]: resp = requests.put( f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/style", headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"}, json={ "appendStyle": { "range": f"3fR34k!{col}2:{col}20", "style": {"formatter": "#,##0"} } }, timeout=30) time.sleep(0.2) # E列(转化率) 百分比格式 resp = requests.put( f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/style", headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"}, json={ "appendStyle": { "range": "3fR34k!E2:E20", "style": {"formatter": "0.00%"} } }, timeout=30) print(f" 转化率格式: code={resp.json().get('code')}") print("\n✅ 任务1-3完成！") print("任务4-5(过程数据)需要查询数据库，继续执行...")