如何通过Google Sheets onEdit触发器实现复选框勾选7天后自动取消勾选

需求说明 #

• 表格C15、D15单元格设置了复选框
• 勾选操作完成7天后，对应复选框需要在当初勾选的同一时间点自动取消勾选
• 目前无法使用单元格编辑事件触发的触发器，现有其他复选框重置功能使用的是按周触发的时间触发器

原有周重置参考代码 #

function WeeklyReset(){
  var ws = SpreadsheetApp.openById("Sheet");
  var tArray = ["C15:D15","C18:D21","C24:D25"];
  var d = new Date();
  var hr = d.getHours();
  var day = d.getDay();
  var min = d.getMinutes(); 

  if (hr == 5 && day == 1){
       for (tr of tArray){
      var tRange = ws.getRange(tr);
    tRange.setValue(false);
  }
 }
 }

优化后实现方案 #

基于AndrewJames提供的代码调整，通过两个函数配合实现需求：

1. onEdit编辑触发函数：检测到目标复选框被勾选时，在单元格备注中写入勾选时间戳
2. resetCheckbox时间触发函数：每分钟运行一次，检查勾选时间距当前是否满7天，满足条件则自动取消勾选

完整实现代码 #

// 编辑事件触发器：勾选复选框时写入时间戳到单元格备注
function onEdit(e) {
 // 仅当目标单元格复选框被勾选时更新备注时间戳
 var range = e.range;
 var rangeA1 = range.getA1Notation(); 
 var sheetName = e.range.getSheet().getName();
 // 如需支持D15，可修改判断条件为 ["C15","D15"].includes(rangeA1)
 if (sheetName === "你的工作表名称" && rangeA1 === "C15" && range.getValue()) {
    var date = new Date();
    // 存储毫秒级时间戳便于后续时间差计算
    range.setNote('Checked:' + date.getTime() + ':' + date);
 }
}

// 时间触发器执行函数：每分钟运行一次，检查是否满7天，满则取消勾选
function resetCheckbox() {
 var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的工作表名称");
 var range = mySheet.getRange("C15");
 var note = range.getNote().split(':');
 if (range.getValue() && note.length > 1) {
      var lastChecked = Number(note[1]);
      var now = new Date().getTime(); // 当前时间毫秒数
      // 时间差超过7天则取消勾选并清空备注
      if (now - lastChecked > 7 * 24 * 60 * 60 * 1000) {
        range.setValue(false);
        range.clearNote();
      }
 }
}

配置步骤 #

1. 将代码中你的工作表名称替换为实际使用的工作表名称
2. 如需支持D15单元格，可修改onEdit函数中的单元格判断规则，同时在resetCheckbox中增加D15的判断逻辑
3. 为resetCheckbox函数配置时间驱动触发器，触发频率设置为每分钟运行一次

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