Google Sheets脚本优化：避免重复/缺失值及完善操作逻辑

我有一个名为Voters的Google表格，包含两个标签页：Votazione和BackUpVotazione。其中Votazione的A2、B3-B12单元格设置了下拉菜单，数据源来自MainExcel表的A列。

原有操作逻辑 #

• 选中A2或B3-B12的下拉值时，将MainExcel对应行的B-E列内容复制到Votazione对应行的C-F列，并删除MainExcel该行；
• 在Votazione的B13输入0时，将A2、C2-F2及B3-F12内容按原位置复制到BackUpVotazioni表；输入1时，将上述内容复制到BackUpVotazioni的A-E列。

当前问题与优化需求 #

• 问题：重复选择A2等单元格的值时，原选中值丢失且无法恢复；多用户协作时易出现重复或缺失值。
• 需求：选中值同步至其他表，若值已存在于BackUpVotazioni或MainExcel则删除，不存在则追加到MainExcel。

现有onEdit脚本 #

function onEdit(e) {
  let range = e.range;
  let sheet = range.getSheet();
  let value = e.value;
  let row = range.getRow()

  let wantInfo = (
    sheet.getSheetName() == "Votazione" &&
    row != 1 &&
    (
      range.getColumn() == 1 ||
      range.getColumn() == 2
    )
  )

  let done = (value === "0")
  let undo = (value === "1")

  if (wantInfo & !done & !undo) {
    let s1 = SpreadsheetApp.openById("myID")
    let s1sheet = s1.getSheetByName("Votanti")
    let s2sheet = s1.getSheetByName("Selezionati")
    let s1values = s1range.getDataRange().getValues()
    let output = [];
    let rowToDelete;

    newS1values = s1values.filter((row, index) => {
      if (row[0] == value || row[1] == value){
        rowToDelete = index +1
        s2sheet.appendRow(row)   
        row.shift() 
        output.push(row)             
        return false
      } else return true 
    })

    let outputRange = sheet.getRange(row,3,1,4);
    outputRange.setValues(output)
    s1sheet.deleteRow(rowToDelete)
    
  } else if (undo) {
        let s1 = SpreadsheetApp.openById("myID")
        let s1sheet = s1.getSheetByName("Votanti")
        
        let s2range = sheet.getDataRange();
        let s2values = s2range.getValues();

         s1sheet.appendRow([s2values[1][0],s2values[1][2],s2values[1][3],s2values[1][4],s2values[1][5]])
       
         for (var i = 2; i < 12; i++) {
          s1sheet.appendRow([s2values[i][1],s2values[i][2],s2values[i][3],s2values[i][4],s2values[i][5]])}
  
        sheet.getRange("A2:F12").clearContent();
        sheet.getRange("A13:E13").clearContent();
        
  } else if (done) {
    let s3 = SpreadsheetApp.openById("myID")
    let s3sheet = s3.getSheetByName("RisultatoFinale")
    
    let s2range = sheet.getDataRange();
    let s2values = s2range.getValues();
    let headers = s2values.shift();
    
    s2values.pop();

    s2values.forEach(row => s3sheet.appendRow(row))

    sheet.getRange("A2:F12").clearContent();
    sheet.getRange("A13:E13").clearContent();
    
    let headerRange = sheet.getRange(1,1,1,headers.length)
    headerRange.setValues([headers])
  }
}
function init(){}

优化后的onEdit脚本 #

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const value = e.value;
  const row = range.getRow();
  const col = range.getColumn();
  
  // 替换为你的表格ID
  const MAIN_SPREADSHEET_ID = "myID";
  const mainSS = SpreadsheetApp.openById(MAIN_SPREADSHEET_ID);
  const mainExcelSheet = mainSS.getSheetByName("MainExcel");
  const backUpSheet = mainSS.getSheetByName("BackUpVotazioni");

  // 处理下拉选择操作（A2或B3-B12）
  const isTargetCell = sheet.getSheetName() === "Votazione" && row !== 1 && 
                       ((col === 1 && row === 2) || (col === 2 && row >=3 && row <=12));
  const isDone = value === "0";
  const isUndo = value === "1";

  if (isTargetCell && !isDone && !isUndo) {
    // 获取所有相关表的数据源，用于查重
    const mainData = mainExcelSheet.getDataRange().getValues();
    const backUpData = backUpSheet.getDataRange().getValues();
    
    // 检查值是否存在于MainExcel或BackUpVotazioni
    const existsInMain = mainData.some(r => r[0] === value);
    const existsInBackUp = backUpData.some(r => r.includes(value));
    
    if (existsInMain) {
      // 从MainExcel删除对应行
      const deleteRowIndex = mainData.findIndex(r => r[0] === value) + 1;
      mainExcelSheet.deleteRow(deleteRowIndex);
      // 清空当前单元格对应C-F列
      sheet.getRange(row, 3, 1, 4).clearContent();
      range.clearContent();
    } else if (existsInBackUp) {
      // 从BackUpVotazioni删除对应行
      const deleteRowIndex = backUpData.findIndex(r => r.includes(value)) + 1;
      backUpSheet.deleteRow(deleteRowIndex);
      // 追加到MainExcel
      const targetRow = backUpData[deleteRowIndex -1];
      mainExcelSheet.appendRow([value, targetRow[1], targetRow[2], targetRow[3], targetRow[4]]);
      // 同步到Votazione的C-F列
      sheet.getRange(row, 3, 1, 4).setValues([[targetRow[1], targetRow[2], targetRow[3], targetRow[4]]]);
    } else {
      // 不存在则从MainExcel获取数据并同步到Votazione
      const targetRow = mainData.find(r => r[0] === value);
      if (targetRow) {
        const deleteRowIndex = mainData.findIndex(r => r[0] === value) + 1;
        mainExcelSheet.deleteRow(deleteRowIndex);
        // 复制B-E到C-F
        sheet.getRange(row, 3, 1, 4).setValues([targetRow.slice(1,5)]);
      }
    }
  } else if (isDone) {
    // 处理B13输入0：按原位置复制到BackUpVotazioni
    const votazioneData = sheet.getRange("A2:F12").getValues();
    // 获取BackUp的最后一行
    const backUpLastRow = backUpSheet.getLastRow() + 1;
    // 写入A2行（A2, C2-F2）
    backUpSheet.getRange(backUpLastRow, 1, 1, 5).setValues([[votazioneData[0][0], votazioneData[0][2], votazioneData[0][3], votazioneData[0][4], votazioneData[0][5]]]);
    // 写入B3-F12行
    for (let i = 1; i < 10; i++) { // 对应B3-B12，即votazioneData的索引1到9
      if (votazioneData[i][1]) { // 有值才写入
        backUpSheet.getRange(backUpLastRow + i, 2, 1, 5).setValues([[votazioneData[i][1], votazioneData[i][2], votazioneData[i][3], votazioneData[i][4], votazioneData[i][5]]]);
      }
    }
    // 清空内容
    sheet.getRange("A2:F12").clearContent();
    sheet.getRange("B13").clearContent();
  } else if (isUndo) {
    // 处理B13输入1：复制到BackUpVotazioni的A-E列
    const votazioneData = sheet.getRange("A2:F12").getValues();
    const backUpLastRow = backUpSheet.getLastRow() + 1;
    // 写入A2行
    backUpSheet.getRange(backUpLastRow, 1, 1, 5).setValues([[votazioneData[0][0], votazioneData[0][2], votazioneData[0][3], votazioneData[0][4], votazioneData[0][5]]]);
    // 写入B3-F12行
    for (let i = 1; i < 10; i++) {
      if (votazioneData[i][1]) {
        backUpSheet.getRange(backUpLastRow + i, 1, 1, 5).setValues([[votazioneData[i][1], votazioneData[i][2], votazioneData[i][3], votazioneData[i][4], votazioneData[i][5]]]);
      }
    }
    // 清空内容
    sheet.getRange("A2:F12").clearContent();
    sheet.getRange("B13").clearContent();
  }
}

function init(){}

优化说明 #

• 查重逻辑：每次选择下拉值时，先检查值是否存在于MainExcel或BackUpVotazioni，存在则删除对应行，不存在则从MainExcel提取数据同步到Votazione；
• 多用户协作优化：通过一次性读取整表数据进行查重，减少多次读写操作，降低冲突概率；
• 数据恢复支持：当重复选择已备份的值时，会从备份表移回MainExcel并同步到当前单元格；
• 批量操作优化：写入BackUpVotazioni时使用批量范围写入替代appendRow，提升效率。

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