Google Apps Script表格同步：整行删除时onEdit触发器失效求助

问题背景 #

使用Google Apps Script实现子表格与主Database表格的双向同步，依赖onEdit触发器在表格变更时执行同步。单个单元格修改时功能正常，但一次性删除整行时同步完全失效。原代码如下：

function onEdit(e) {
   
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  var column = e.range.getColumn();
  Logger.log(column)
  if (sheet.getName() == "Database") {
    var dataRange = sheet.getDataRange();
   
    var data = dataRange.getValues();
     Logger.log(e.changeType)
    var identifier = data[row-1][0];
    var sheetName = data[row-1][2];
    if(identifier && sheetName){
   

    var otherSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
   
    if (otherSheet) {
      var rowIndex = findRowIndexByIdentifier(otherSheet, identifier);
      var checkname=otherSheet.getRange(rowIndex, 2).getValues()[0]
      var checksite=otherSheet.getRange(rowIndex, 3).getValues()[0]
      var checkident=otherSheet.getRange(rowIndex, 1).getValues()[0]
      var stat=otherSheet.getRange(rowIndex, 12).getValues()[0]
      var  numsite=returnsite(checksite[0])
      var numname=returnnameindex(numsite)
     
      if (rowIndex > 0) {
        otherSheet.getRange(rowIndex, column).setValue(e.value);
        if(column==2){
         
          toemail=redacteur_emails[numname]
          sendemail("timar72243@asoflex.com"," UPDATE ON SITE "+checksite+" Article No: "+checkident,"The status is" +stat)
        }
        if(column==12 && stat=="A réviser"){
          
        
          toemail=redacteur_emails[numname]
          sendemail("timar72243@asoflex.com"," UPDATE ON SITE "+checksite+" Article No:"+checkident,"The status is"+stat)

        }
        if(column==12 && (stat=="A réviser" ||  stat=="En cours de révision par CM")){
         
          for(var i=0;i<content_managers_emails.length;i++){
            toemail=content_managers_emails[i]
            sendemail("zukkoyilte@gufum.com"," UPDATE ON SITE "+checksite+" Article No: "+checkident," The status is "+stat)
          }

        }
        else if(column==12 && (stat=="A publier sous WP" ||  stat=="A reviser par integrateur")){
          for(var i=0;i<integrateurs_emails.length;i++){
            toemail=integrateurs_emails[i]
            sendemail("zukkoyilte@gufum.com"," UPDATE ON SITE "+checksite+" Article No: "+checkident," The status is "+stat)
         
          }
        }
        else if(column==12 && (stat=="Livré" ||  stat=="Publié")){
          for(var i=0;i<admins_emails.length;i++){
            toemail=admins_emails[i]
            Logger.log("arrived12")
            sendemail("zukkoyilte@gufum.com"," UPDATE ON SITE "+checksite+" Article No: "+checkident," The status is "+stat)
          }
        }

      }
      else{
       var rowToAppend = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues();
       
        otherSheet.appendRow(rowToAppend[0])

      }
    }
    }
  }
  else {
    var sheetName = sheet.getName();
    var databaseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database");
    var identifier = "";
    var rowIndex = e.range.getRow();
    var dataRange = sheet.getDataRange();
    var data = dataRange.getValues();

    for (var i = 0; i < data.length; i++) {
      identifier = data[i][0];
      var databaseRowIndex = findRowIndexByIdentifier(databaseSheet, identifier);
      if (databaseRowIndex > 0 && sheetName == databaseSheet.getRange(databaseRowIndex, 3).getValue()) {
        databaseSheet.getRange(databaseRowIndex, column).setValue(data[i][column-1]);
      }
    }
  }

  
}

失效原因 #

1. 删除事件未被处理：整行删除时e.changeType返回REMOVE_ROW，但原代码未针对该事件做逻辑处理。
2. 依赖已删除数据：删除Database表的行后，data[row-1][0]会获取到错误的行数据（删除后下方行上移，原索引对应内容已改变）；子表删除行时，原循环逻辑会跳过已删除行，无法同步删除Database中的对应条目。
3. 单个单元格依赖：原代码仅处理单个单元格修改（依赖e.value、column），整行删除时e.value为undefined，导致同步逻辑直接中断。

修复后的代码 #

以下代码新增整行删除同步逻辑，同时优化数据获取方式提升性能：

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = e.range.getSheet();
  const changeType = e.changeType;

  // 处理Database表的变更
  if (sheet.getName() === "Database") {
    // 处理整行删除事件
    if (changeType === "REMOVE_ROW") {
      const deletedRowIndex = e.range.getRow();
      // 单行删除场景下获取删除前的标识符（批量删除需扩展循环逻辑）
      const tempData = sheet.getDataRange().getValues();
      const identifier = tempData[deletedRowIndex - 2]?.[0];
      const sheetName = tempData[deletedRowIndex - 2]?.[2];
      
      if (identifier && sheetName) {
        const otherSheet = ss.getSheetByName(sheetName);
        if (otherSheet) {
          const rowIndex = findRowIndexByIdentifier(otherSheet, identifier);
          if (rowIndex > 0) {
            otherSheet.deleteRow(rowIndex);
          }
        }
      }
      return;
    }

    // 处理普通单元格修改/新增
    const row = e.range.getRow();
    const column = e.range.getColumn();
    const data = sheet.getDataRange().getValues();
    const identifier = data[row - 1][0];
    const sheetName = data[row - 1][2];

    if (!identifier || !sheetName) return;

    const otherSheet = ss.getSheetByName(sheetName);
    if (!otherSheet) return;

    const rowIndex = findRowIndexByIdentifier(otherSheet, identifier);
    if (rowIndex > 0) {
      // 更新对应单元格
      if (e.value !== undefined) {
        otherSheet.getRange(rowIndex, column).setValue(e.value);
      }
      
      // 邮件通知逻辑
      const [checkname, checksite, checkident, , , , , , , , stat] = otherSheet.getRange(rowIndex, 2, 1, 11).getValues()[0];
      const numsite = returnsite(checksite);
      const numname = returnnameindex(numsite);

      if (column === 2) {
        const toemail = redacteur_emails[numname];
        sendemail("timar72243@asoflex.com", `UPDATE ON SITE ${checksite} Article No: ${checkident}`, `The status is ${stat}`);
      }

      if (column === 12) {
        switch(stat) {
          case "A réviser":
            const toemailRedacteur = redacteur_emails[numname];
            sendemail("timar72243@asoflex.com", `UPDATE ON SITE ${checksite} Article No: ${checkident}`, `The status is ${stat}`);
            content_managers_emails.forEach(email => {
              sendemail("zukkoyilte@gufum.com", `UPDATE ON SITE ${checksite} Article No: ${checkident}`, `The status is ${stat}`);
            });
            break;
          case "En cours de révision par CM":
            content_managers_emails.forEach(email => {
              sendemail("zukkoyilte@gufum.com", `UPDATE ON SITE ${checksite} Article No: ${checkident}`, `The status is ${stat}`);
            });
            break;
          case "A publier sous WP":
          case "A reviser par integrateur":
            integrateurs_emails.forEach(email => {
              sendemail("zukkoyilte@gufum.com", `UPDATE ON SITE ${checksite} Article No: ${checkident}`, `The status is ${stat}`);
            });
            break;
          case "Livré":
          case "Publié":
            admins_emails.forEach(email => {
              sendemail("zukkoyilte@gufum.com", `UPDATE ON SITE ${checksite} Article No: ${checkident}`, `The status is ${stat}`);
            });
            break;
        }
      }
    } else {
      // 新增行到子表
      const rowToAppend = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
      otherSheet.appendRow(rowToAppend);
    }
  } 
  // 处理子表格的变更
  else {
    const sheetName = sheet.getName();
    const databaseSheet = ss.getSheetByName("Database");

    // 处理子表整行删除
    if (changeType === "REMOVE_ROW") {
      const deletedRowIndex = e.range.getRow();
      const tempData = sheet.getDataRange().getValues();
      const identifier = tempData[deletedRowIndex - 2]?.[0];
      
      if (identifier) {
        const databaseRowIndex = findRowIndexByIdentifier(databaseSheet, identifier);
        if (databaseRowIndex > 0 && sheetName === databaseSheet.getRange(databaseRowIndex, 3).getValue()) {
          databaseSheet.deleteRow(databaseRowIndex);
        }
      }
      return;
    }

    // 处理子表单元格修改
    const row = e.range.getRow();
    const column = e.range.getColumn();
    const identifier = sheet.getRange(row, 1).getValue();
    if (!identifier) return;

    const databaseRowIndex = findRowIndexByIdentifier(databaseSheet, identifier);
    if (databaseRowIndex > 0 && sheetName === databaseSheet.getRange(databaseRowIndex, 3).getValue()) {
      const cellValue = sheet.getRange(row, column).getValue();
      databaseSheet.getRange(databaseRowIndex, column).setValue(cellValue);
    }
  }
}

// 保留原有的辅助函数（假设已存在）
function findRowIndexByIdentifier(sheet, identifier) {
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === identifier) {
      return i + 1; // 返回行号（从1开始）
    }
  }
  return 0;
}

function returnsite(site) {
  // 原函数逻辑
}

function returnnameindex(numsite) {
  // 原函数逻辑
}

function sendemail(to, subject, body) {
  // 原函数逻辑
}

关键优化点 #

1. 新增REMOVE_ROW事件处理：分别针对Database表和子表的整行删除操作，同步删除对应表格中的行。
2. 优化标识符获取逻辑：处理删除后数据偏移问题，确保正确获取被删除行的标识符。
3. 简化邮件通知逻辑：用switch语句替代多层if-else，提升代码可读性。
4. 减少不必要的数据获取：子表修改时直接获取当前行的标识符，无需遍历整个表格，提升性能。

注意：如果需要支持批量删除多行，建议结合onChange触发器，因为onEdit在批量删除时的e.range包含所有删除行，需循环处理每个行的标识符。

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