如何让Google Sheets的onEdit(e)持续检测指定单元格值？

原脚本的问题 #

• 未定义变量s，代码中s.getName()无意义，应使用已定义的工作表相关变量进行判断
• 获取指定单元格B6的方式错误，range1.getRange(B6)不是合法写法，需明确定位目标工作表的B6单元格
• 未判断单元格状态的变化逻辑，当前代码只要B6值不是“Formula Works”就弹窗，会重复触发，不符合“从正常状态变为错误时才弹窗”的需求
• 注意：onEdit触发器仅在用户手动编辑单元格时触发，IMPORTRANGE公式计算导致的单元格值变化不会触发onEdit，若需检测公式结果变化，需配合时间驱动触发器或onChange触发器

修正后的onEdit脚本 #

function onEdit(e) {
  const ss = e.source;
  const targetSheetName = "Settings";
  const targetCellAddr = "B6";
  
  // 仅在编辑目标工作表时执行检测逻辑
  if (ss.getActiveSheet().getName() !== targetSheetName) return;
  
  // 获取目标单元格当前值
  const currentValue = ss.getSheetByName(targetSheetName).getRange(targetCellAddr).getValue();
  
  // 用脚本属性存储上一次单元格状态，用于判断状态变化
  const scriptProps = PropertiesService.getScriptProperties();
  const lastStatus = scriptProps.getProperty("lastB6Status") || "Formula Works";
  
  // 仅当状态从正常变为错误时触发弹窗
  if (currentValue !== "Formula Works" && lastStatus === "Formula Works") {
    SpreadsheetApp.getUi().alert("Error");
  }
  
  // 更新存储的状态记录
  scriptProps.setProperty("lastB6Status", currentValue);
}

针对公式自动变化的补充方案 #

如果需要持续检测IMPORTRANGE的结果变化（无需用户手动编辑触发），可以添加时间驱动触发器，配合以下单独的检测函数：

function checkImportRangeStatus() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("Settings");
  const targetCell = targetSheet.getRange("B6");
  const currentValue = targetCell.getValue();
  
  const scriptProps = PropertiesService.getScriptProperties();
  const lastStatus = scriptProps.getProperty("lastB6Status") || "Formula Works";
  
  if (currentValue !== "Formula Works" && lastStatus === "Formula Works") {
    SpreadsheetApp.getUi().alert("Error");
  }
  
  scriptProps.setProperty("lastB6Status", currentValue);
}

添加触发器步骤：

• 打开脚本编辑器，点击左侧「触发器」图标
• 点击「添加触发器」，选择函数checkImportRangeStatus，设置时间间隔（如每5分钟）

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