Google Sheets脚本自动计算日期差报#NUM!错误求助

问题背景 #

我在Google Sheets里有两个工作表，要实现两个自动化功能：

• 在工作表1（"Restricted soon"）的行中输入信息时，自动填入日期到E列
• 当C列的勾选框为true时，该行移至工作表2（"Verified"），同时在工作表2新增两列：F列记录移动日期，G列计算E列（原日期）和F列（移动日期）的天数差

目前脚本大部分功能正常，但G列的日期差计算出现#NUM!错误，添加简单错误处理后单元格变成空白。

原问题脚本 #

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;

  var ss = e.source;
  var tickedSheet = ss.getSheetByName("Restricted soon"); // 源工作表
  var verifiedSheet = ss.getSheetByName("Verified"); // 目标工作表

  var tickedRange = tickedSheet.getRange("C2:C"); // 勾选框从第2行C列开始
  var tickedValues = tickedRange.getValues();

  var targetRange = verifiedSheet.getRange(verifiedSheet.getLastRow() + 1, 1); // 目标表末尾追加行
  var currentDate = new Date();

  for (var i = 0; i < tickedValues.length; i++) {
    if (tickedValues[i][0] === true) { // 检查勾选框是否勾选
      var rowToMove = tickedSheet.getRange(i + 2, 1, 1, tickedSheet.getLastColumn());
      rowToMove.copyTo(targetRange);

      // 在Sheet2的F列设置日期
      var targetDateCell = targetRange.offset(0, 5);
      targetDateCell.setValue(currentDate);

      tickedSheet.deleteRow(i + 2);
    }
  }

  // 检查编辑的是A列且不是表头行
  if (range.getColumn() == 1 && range.getRow() > 1) {
    var row = range.getRow();
    var dateCell = sheet.getRange(row, 5);

    // 日期单元格为空时填充当前日期
    if (dateCell.getValue() === "") {
      dateCell.setValue(new Date());
    }

    // 计算E列和F列的日期差
    var startDate = new Date(sheet.getRange(row, 5).getValue());
    var endDate = new Date(sheet.getRange(row, 6).getValue());


    var difference = Math.floor((endDate - startDate) / (1000 * 60 * 60 * 24)); // 天数差


    // 将结果写入"Verified"表的G列
    verifiedSheet.getRange(verifiedSheet.getLastRow(), 7).setValue(difference);
  }
}

尝试的无效错误处理 #

if (!isNaN(startDate) && !isNaN(endDate)) {
      var difference = Math.floor((endDate - startDate) / (1000 * 60 * 60 * 24));

问题原因与修正方案 #

核心问题 #

1. 逻辑错位：原脚本把日期差计算放在「编辑A列自动填日期」的分支里，但实际上日期差应该在行移动到Verified表时计算，而非编辑A列时。
2. 日期对象异常：当endDate对应单元格为空（比如源表F列本来无数据），new Date(空值)会生成无效日期对象，导致计算出NaN，触发#NUM!错误。
3. 行索引混乱：循环遍历C列删除行时，删除操作会让后续行上移，直接用i+2会漏掉部分行。

修正后的完整脚本 #

function onEdit(e) {
  var ss = e.source;
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var currentDate = new Date();
  var tickedSheet = ss.getSheetByName("Restricted soon");
  var verifiedSheet = ss.getSheetByName("Verified");

  // 逻辑1：编辑源表A列时，自动填充E列日期
  if (sheet.getName() === "Restricted soon" && range.getColumn() === 1 && range.getRow() > 1) {
    var dateCell = tickedSheet.getRange(range.getRow(), 5);
    if (dateCell.getValue() === "") {
      dateCell.setValue(currentDate);
    }
  }

  // 逻辑2：勾选源表C列时，移动行到目标表并计算日期差
  if (sheet.getName() === "Restricted soon" && range.getColumn() === 3 && range.getRow() > 1) {
    var rowNum = range.getRow();
    if (range.getValue() === true) {
      // 获取要移动的行数据，追加到目标表末尾
      var targetRow = verifiedSheet.getLastRow() + 1;
      tickedSheet.getRange(rowNum, 1, 1, tickedSheet.getLastColumn())
        .copyTo(verifiedSheet.getRange(targetRow, 1));

      // 设置移动日期到目标表F列
      verifiedSheet.getRange(targetRow, 6).setValue(currentDate);

      // 获取源表E列的创建日期，计算天数差
      var startDate = tickedSheet.getRange(rowNum, 5).getValue();
      if (startDate instanceof Date && !isNaN(startDate)) {
        var daysDiff = Math.floor((currentDate - startDate) / (1000 * 60 * 60 * 24));
        verifiedSheet.getRange(targetRow, 7).setValue(daysDiff);
      } else {
        verifiedSheet.getRange(targetRow, 7).setValue("无创建日期");
      }

      // 删除源表中已移动的行
      tickedSheet.deleteRow(rowNum);
    }
  }
}

关键改进点 #

• 拆分逻辑：将自动填日期和移动行的逻辑分开，分别对应A列编辑和C列勾选的触发事件，避免逻辑混乱。
• 有效日期校验：确认startDate是合法Date对象后再计算，避免无效值导致的NaN。
• 修复行索引问题：直接使用触发编辑的行号，不用遍历整个C列，避免删除行后索引错位。
• 明确工作表判断：添加sheet.getName()校验，确保脚本只在源表操作时触发对应逻辑，避免其他表操作干扰。

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