Google Script问题：下拉触发行移动异常及公式丢失求助

需求说明 #

• 在「OpenPOs」表的F列下拉框选择「received」时，将该行移动至「Received」表，并删除原表对应行
• 在「Received」表的F列下拉框选择「invoiced」时，将该行移动至「Invoiced」表，并删除原表对应行
• 移动时保留行内所有公式
• 支持误操作回滚：将行移回原表

当前脚本存在的问题 #

• 触发移动时偶尔误删目标行下方的有效行
• 部分情况下行直接消失，未成功移至目标表
• 移动后的行丢失原有公式

问题根源分析 #

1. getActiveSelection()不可靠：若编辑时选中多行，会导致获取的行号错误，进而误删或移动错误行
2. 重复定义变量：脚本重复定义Received变量，易造成对象引用混乱
3. 大小写不匹配：下拉框选择小写「received」，但脚本判断大写「Received」，触发条件失效
4. moveTo()局限性：剪切移动逻辑若遇目标表插入行位置处理不当，易导致内容丢失；getMaxColumns()会选中多余空列，影响移动效果
5. 未过滤表头行：误编辑表头下拉框会导致表头被移动删除

修复后的完整脚本 #

function onEdit(e) {
  const ss = e.source;
  const range = e.range;
  const sheet = range.getSheet();
  const row = range.getRow();
  const col = range.getColumn();
  const value = range.getValue().toLowerCase(); // 统一转为小写，避免大小写冲突

  // 定义所有工作表对象
  const openPOsSheet = ss.getSheetByName("OpenPOs");
  const receivedSheet = ss.getSheetByName("Received");
  const invoicedSheet = ss.getSheetByName("Invoiced");

  // 跳过表头行（默认表头在第1行）
  if (row === 1) return;

  // 从OpenPOs移动到Received
  if (sheet.getName() === openPOsSheet.getName() && col === 6 && value === "received") {
    const targetRow = receivedSheet.getLastRow() + 1;
    // 复制整行（含公式、格式）到目标表
    openPOsSheet.getRange(row, 1, 1, 8).copyTo(
      receivedSheet.getRange(targetRow, 1),
      SpreadsheetApp.CopyPasteType.PASTE_ALL,
      false
    );
    openPOsSheet.deleteRows(row, 1);
    range.setValue(""); // 重置单元格，避免重复触发
  }

  // 从Received移动到Invoiced
  else if (sheet.getName() === receivedSheet.getName() && col === 6 && value === "invoiced") {
    const targetRow = invoicedSheet.getLastRow() + 1;
    receivedSheet.getRange(row, 1, 1, 8).copyTo(
      invoicedSheet.getRange(targetRow, 1),
      SpreadsheetApp.CopyPasteType.PASTE_ALL,
      false
    );
    receivedSheet.deleteRows(row, 1);
    range.setValue("");
  }

  // 回滚：从Received移回OpenPOs
  else if (sheet.getName() === receivedSheet.getName() && col === 6 && value === "open") {
    const targetRow = openPOsSheet.getLastRow() + 1;
    receivedSheet.getRange(row, 1, 1, 8).copyTo(
      openPOsSheet.getRange(targetRow, 1),
      SpreadsheetApp.CopyPasteType.PASTE_ALL,
      false
    );
    receivedSheet.deleteRows(row, 1);
    range.setValue("");
  }

  // 回滚：从Invoiced移回Received
  else if (sheet.getName() === invoicedSheet.getName() && col === 6 && value === "received") {
    const targetRow = receivedSheet.getLastRow() + 1;
    invoicedSheet.getRange(row, 1, 1, 8).copyTo(
      receivedSheet.getRange(targetRow, 1),
      SpreadsheetApp.CopyPasteType.PASTE_ALL,
      false
    );
    invoicedSheet.deleteRows(row, 1);
    range.setValue("");
  }

  // 回滚：从Invoiced直接移回OpenPOs
  else if (sheet.getName() === invoicedSheet.getName() && col === 6 && value === "open") {
    const targetRow = openPOsSheet.getLastRow() + 1;
    invoicedSheet.getRange(row, 1, 1, 8).copyTo(
      openPOsSheet.getRange(targetRow, 1),
      SpreadsheetApp.CopyPasteType.PASTE_ALL,
      false
    );
    invoicedSheet.deleteRows(row, 1);
    range.setValue("");
  }
}

关键修复说明 #

1. 用e.range替代getActiveSelection()：直接获取触发编辑的单个单元格，避免多选导致的行号错误
2. 统一大小写判断：将单元格值转为小写后匹配，兼容下拉框的大小写输入
3. 固定列数复制：明确复制8列（对应需求的每行8列信息），避免选中多余空列
4. copyTo()替代moveTo()：通过PASTE_ALL完整复制公式、格式和值，确保内容不丢失；复制后再删除原行，避免剪切移动的异常
5. 表头行过滤：直接跳过第1行，防止误操作移动表头
6. 新增回滚功能：支持在对应表的F列选择「open」或「received」将行移回原表
7. 重置触发单元格值：避免因单元格值未改变导致重复触发脚本

使用说明 #

1. 将上述脚本替换原有onEdit函数
2. 确保三个工作表（OpenPOs、Received、Invoiced）的表头都在第1行
3. 回滚操作：
   • Received表选「open」→ 移回OpenPOs表
   • Invoiced表选「received」→ 移回Received表；选「open」→ 直接移回OpenPOs表

内容的提问来源于stack exchange，提问作者CP-AP