You need to enable JavaScript to run this app. 如何在Google Sheets中自动化跟踪月度自动更新评分,存储历史并计算月环比?
优惠活动
大模型
产品
解决方案
定价
更多

如何在Google Sheets中自动化跟踪月度自动更新评分,存储历史并计算月环比?

月度评分对比自动化实现方案

核心逻辑对齐

  • 评分取值范围为1-100,100为最优值
  • 数据每月固定更新一次
  • 所有历史评分永久留存,支撑长期趋势查看
  • 流程闭环:新数据更新前留存旧评分副本→新评分生成后自动计算评分差值、月环比变化

前置表格结构搭建

  • 「实时数据页」:保留原有各维度数值列,末尾固定生成Current Score总评分,对接所有自动更新的数据源
  • 「历史评分归档页」:设置固定列头,依次为「归档年月」「当期评分数值」「上期评分数值」「评分绝对差值」「月环比变化率」,所有历史数据永久存入该页面,不设置自动清理规则
  • 「临时缓存页」:单格暂存新数据更新前的旧评分,避免新旧数据覆盖导致对比失效

自动化流程配置(基于Google Apps Script)

通过两个独立触发函数,匹配每月的更新时间节点完成全流程自动化:

  1. 旧评分暂存阶段:在每月数据正式更新前10分钟触发,自动读取实时页当前留存的Current Score值,写入临时缓存页作为对比基准
  2. 新评分归档计算阶段:在每月数据全部同步完成、新的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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 01:15:03