Google Sheets 时间触发器实现C列值为N/A时自动剪切行到指定工作表

需求说明 #

• 现有电子表格包含两个结构一致的工作表：
  • 「Induction Checklist」：存储在职员工信息，有效数据范围为A3:M，第2行为表头，C列为员工ID，通过ImportRange/Vlookup公式从外部源表匹配，员工从源表移除后对应C列会显示N/A
  • 「Induction Checklist - Staff Left」：存储已离职员工信息，表头、列结构与在职表完全一致
• 需实现定时触发功能：自动检索在职表C列值为N/A的行，将整行剪切到离职表末尾，同时删除在职表的对应行，无匹配N/A时不执行任何操作

原脚本问题排查 #

你提供的脚本无法运行的核心问题如下：

1. 未遍历所有行：仅读取了整个表格范围的首个单元格值，没有逐行检查C列内容，无法识别到其他行的N/A
2. 变量未定义：判断条件中使用了未声明的sheetName变量，直接触发运行时报错
3. 删行逻辑有缺陷：如果从前往后删除行，会导致后续行号偏移，出现漏检情况
4. 范围设置错误：没有限定从第3行开始检索，会误判表头及上方空白行

修复后可用脚本 #

function removeOldStaff() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const activeSheet = ss.getSheetByName("Induction Checklist");
  const leaveSheet = ss.getSheetByName("Induction Checklist - Staff Left");
  const checkColumn = 3; // 对应C列
  const startRow = 3; // 有效数据起始行
  const lastRow = activeSheet.getLastRow();
  const lastCol = activeSheet.getLastColumn();

  // 无有效数据直接退出
  if (lastRow < startRow) return;

  // 从后往前遍历，避免删行导致行号偏移漏检
  for (let i = lastRow; i >= startRow; i--) {
    const cellValue = activeSheet.getRange(i, checkColumn).getDisplayValue();
    if (cellValue === "N/A") {
      // 读取当前行完整数据
      const rowData = activeSheet.getRange(i, 1, 1, lastCol).getValues();
      // 写入离职表最后一行
      const targetRow = leaveSheet.getLastRow() + 1;
      leaveSheet.getRange(targetRow, 1, 1, lastCol).setValues(rowData);
      // 删除在职表对应行
      activeSheet.deleteRow(i);
    }
  }
}

触发器配置说明 #

脚本测试运行正常后，可在Apps Script编辑器的「触发器」页面新建时间驱动触发器，按需设置执行频率即可。操作前建议先备份表格数据，避免误操作丢失内容。

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