Google Script批量勾选复选框后行丢失未转移问题求助

我是Google Script完全新手，编写了onEdit脚本实现勾选复选框后将行转移至其他工作表并删除原行。单个勾选时功能正常，但批量勾选时部分行被删除却未转移到目标工作表，随机出现行丢失情况。

原脚本代码 #

function onEdit(event) {// assumes source data in sheet named main
  // target sheet of move to named Completed
  // getColumn with check-boxes is currently set to column 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Acquisition" && r.getColumn() == 1 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("FirstTraining");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if(s.getName() == "FirstTraining" && r.getColumn() == 1 && r.getValue() == false) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Acquisition");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
  if(s.getName() == "FirstTraining" && r.getColumn() == 2 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Incubation");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if(s.getName() == "Incubation" && r.getColumn() == 2 && r.getValue() == false) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("FirstTraining");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
  if(s.getName() == "Incubation" && r.getColumn() == 3 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Graduated");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if(s.getName() == "Graduated" && r.getColumn() == 3 && r.getValue() == false) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Incubation");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

问题原因 #

批量勾选时，原脚本只处理选中区域的左上角单元格（r.getValue()仅返回单个值），且删除行时会导致后续行的索引偏移，部分符合条件的行无法被正确识别和转移。

修改后的脚本 #

function onEdit(event) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const activeSheet = event.source.getActiveSheet();
  const activeRange = event.source.getActiveRange();
  const rangeValues = activeRange.getValues();
  const startRow = activeRange.getRow();
  const startCol = activeRange.getColumn();
  
  // 收集所有需要处理的行信息，避免删除行导致索引混乱
  const rowsToProcess = [];
  
  // 遍历选中区域的每一个单元格
  for (let i = 0; i < rangeValues.length; i++) {
    for (let j = 0; j < rangeValues[i].length; j++) {
      const currentRow = startRow + i;
      const currentCol = startCol + j;
      const cellValue = rangeValues[i][j];
      
      let targetSheetName = null;
      
      // 匹配各个工作表的触发规则
      if (activeSheet.getName() === "Acquisition" && currentCol === 1 && cellValue === true) {
        targetSheetName = "FirstTraining";
      } else if (activeSheet.getName() === "FirstTraining" && currentCol === 1 && cellValue === false) {
        targetSheetName = "Acquisition";
      } else if (activeSheet.getName() === "FirstTraining" && currentCol === 2 && cellValue === true) {
        targetSheetName = "Incubation";
      } else if (activeSheet.getName() === "Incubation" && currentCol === 2 && cellValue === false) {
        targetSheetName = "FirstTraining";
      } else if (activeSheet.getName() === "Incubation" && currentCol === 3 && cellValue === true) {
        targetSheetName = "Graduated";
      } else if (activeSheet.getName() === "Graduated" && currentCol === 3 && cellValue === false) {
        targetSheetName = "Incubation";
      }
      
      if (targetSheetName) {
        rowsToProcess.push({row: currentRow, target: targetSheetName});
      }
    }
  }
  
  // 从下往上处理行，避免删除行后索引偏移
  rowsToProcess.sort((a, b) => b.row - a.row);
  
  rowsToProcess.forEach(item => {
    const targetSheet = ss.getSheetByName(item.target);
    const numColumns = activeSheet.getLastColumn();
    const sourceRange = activeSheet.getRange(item.row, 1, 1, numColumns);
    const targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    
    sourceRange.moveTo(targetRange);
    activeSheet.deleteRow(item.row);
  });
}

关键改进点 #

• 遍历选中区域的所有单元格，不再遗漏批量勾选的行
• 先收集所有待处理行的信息，从下往上执行删除操作，避免删除行后后续行的索引偏移
• 简化条件判断逻辑，代码结构更清晰，便于后续维护

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