如何在Google Sheets中自动化跟踪月度自动更新评分,存储历史并计算月环比?
月度评分对比自动化实现方案
核心逻辑对齐
- 评分取值范围为1-100,100为最优值
- 数据每月固定更新一次
- 所有历史评分永久留存,支撑长期趋势查看
- 流程闭环:新数据更新前留存旧评分副本→新评分生成后自动计算评分差值、月环比变化
前置表格结构搭建
- 「实时数据页」:保留原有各维度数值列,末尾固定生成
Current Score总评分,对接所有自动更新的数据源 - 「历史评分归档页」:设置固定列头,依次为「归档年月」「当期评分数值」「上期评分数值」「评分绝对差值」「月环比变化率」,所有历史数据永久存入该页面,不设置自动清理规则
- 「临时缓存页」:单格暂存新数据更新前的旧评分,避免新旧数据覆盖导致对比失效
自动化流程配置(基于Google Apps Script)
通过两个独立触发函数,匹配每月的更新时间节点完成全流程自动化:
- 旧评分暂存阶段:在每月数据正式更新前10分钟触发,自动读取实时页当前留存的
Current Score值,写入临时缓存页作为对比基准 - 新评分归档计算阶段:在每月数据全部同步完成、新的
Current Score完全生成后触发,自动完成数值读取、差值计算、环比统计、历史归档全流程
对应的核心脚本代码:
// 数据更新前暂存旧评分 function cacheOldScore() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const liveSheet = ss.getSheetByName('实时数据页'); const cacheSheet = ss.getSheetByName('临时缓存页'); // 替换为实际Current Score所在的单元格位置 const lastValidScore = liveSheet.getRange('B25').getValue(); cacheSheet.getRange('A1').setValue(lastValidScore); } // 新评分生成后自动计算归档 function archiveAndCalc() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const liveSheet = ss.getSheetByName('实时数据页'); const cacheSheet = ss.getSheetByName('临时缓存页'); const historySheet = ss.getSheetByName('历史评分归档页'); // 读取缓存的旧值和实时页的新值 const oldScore = cacheSheet.getRange('A1').getValue(); // 替换为实际Current Score所在的单元格位置 const newScore = liveSheet.getRange('B25').getValue(); // 异常值拦截,避免空值写入 if(!oldScore || !newScore) return; // 计算指标 const scoreDiff = newScore - oldScore; const momRate = ((scoreDiff / oldScore) * 100).toFixed(2) + '%'; const recordMonth = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM'); // 追加写入历史归档页 historySheet.appendRow([recordMonth, newScore, oldScore, scoreDiff, momRate]); // 清空临时缓存,等待下一轮周期 cacheSheet.getRange('A1').clearContent(); }
定时触发规则设置
- 为
cacheOldScore函数设置每月定时触发:触发时间和现有自动数据更新流程留10-15分钟间隔,确保旧评分在新数据覆盖前完成暂存 - 为
archiveAndCalc函数设置每月定时触发:触发时间在数据更新流程结束后留10分钟缓冲,等待新的Current Score完全计算生成
配套功能优化
- 基于历史归档页的永久留存数据,可直接插入折线图,直观展示长期评分涨跌趋势
- 为归档页的「评分绝对差值」列设置条件格式:差值为正(评分上涨)标记为绿色,差值为负(评分下跌)标记为红色,实现涨跌状态快速识别
内容的提问来源于stack exchange,提问作者MegaMikeJr
相关产品推荐
相关产品推荐

