Google表格：基于时间触发onEdit脚本及列清理功能故障求助

一、ArrayFormula自动填充无法触发onEdit的问题 #

onEdit是Google Sheets的简单触发器，仅在用户手动编辑单元格时触发，公式自动计算（包括ArrayFormula为新行填充值）属于系统操作，不会触发该触发器。

解决办法：使用可安装触发器替代 #

推荐使用onChange可安装触发器，当表格发生结构变化（比如新增行）时触发，结合逻辑检查目标列的变化：

1. 先修改原脚本，把核心逻辑抽成独立函数，方便调用：

function handleRowMove(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event ? event.source.getActiveSheet() : SpreadsheetApp.getActiveSheet();
  var r = event ? event.source.getActiveRange() : null;

  // 处理Initial SS Addition表的复选框触发
  if(s.getName() == "Initial SS Addition") {
    // 如果是onChange触发，遍历列I的新行
    if(!r) {
      var lastRow = s.getLastRow();
      var checkCell = s.getRange(lastRow, 9);
      if(checkCell.getValue() == true) {
        moveRow(s, lastRow, ss.getSheetByName("Google to Mailshake"));
      }
    } else if(r.getColumn() == 9 && r.getValue() == true) {
      moveRow(s, r.getRow(), ss.getSheetByName("Google to Mailshake"));
    }
  } 
  // 处理Google to Mailshake表的复选框触发
  else if(s.getName() == "Google to Mailshake") {
    if(r && r.getColumn() == 9 && r.getValue() == false) {
      moveRow(s, r.getRow(), ss.getSheetByName("Initial SS Addition"));
    }
  }
}

// 抽出行移动的通用逻辑
function moveRow(sourceSheet, rowNum, targetSheet) {
  var numColumns = sourceSheet.getLastColumn();
  var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
  sourceSheet.getRange(rowNum, 1, 1, numColumns).moveTo(target);
  sourceSheet.deleteRow(rowNum);
}

2. 创建可安装的onChange触发器：
   • 打开脚本编辑器，点击左侧「触发器」图标
   • 点击「添加触发器」，设置：
     • 选择函数：handleRowMove
     • 选择事件源：「从电子表格」
     • 选择事件类型：「更改」
   • 保存授权即可

二、clean0脚本的null/undefined错误修复 #

原脚本存在变量未定义、范围获取错误、逻辑判断错误的问题，修复后的版本如下：

function clean0() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Initial SS Addition');
  // 获取列I的所有数据（从第2行开始跳过表头）
  var checkRange = sheet.getRange(2, 9, sheet.getLastRow() - 1, 1);
  var checkValues = checkRange.getValues();
  
  // 遍历每一行，标记需要清除的行范围
  var rangesToClear = [];
  for(var i = 0; i < checkValues.length; i++) {
    // 复选框的值是布尔值true/false，不是字符串
    if(checkValues[i][0] === true) {
      // 假设要清除当前行的A到G列，可根据需求调整列范围
      rangesToClear.push(`${String.fromCharCode(65)}${i+2}:${String.fromCharCode(71)}${i+2}`);
    }
  }
  
  // 如果有需要清除的范围，执行清除
  if(rangesToClear.length > 0) {
    sheet.getRangeList(rangesToClear).clearContent();
    // 可选：清除后把复选框设为false
    var checkBoxRanges = rangesToClear.map(range => range.replace(/[A-G]/, 'I'));
    sheet.getRangeList(checkBoxRanges).setValue(false);
  }
}

错误说明： #

1. 原脚本中where.getRange('I:I').getValue()仅返回I1单元格的值，无法获取整列数据，改用getValues()获取所有行的复选框状态
2. sheet和range变量未定义，已补全定义
3. 原逻辑判断混淆了布尔值和字符串，复选框的实际值是true/false（布尔类型），不是'TRUE'/'FALSE'（字符串）
4. 新增了批量处理逻辑，避免逐行操作提升效率

三、设置每日自动清理 #

如果需要每日自动运行clean0，添加时间驱动触发器：

• 打开脚本编辑器的「触发器」页面
• 点击「添加触发器」，设置：
  • 选择函数：clean0
  • 选择事件源：「时间驱动」
  • 选择基于时间的触发器类型：「日计时器」
  • 选择时间窗口，比如「上午9点到10点」
• 保存授权即可

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