如何编写脚本通过checkbox移动行并实现双工作表autosort排序

核心问题根因：两个功能通常都绑定编辑触发事件，排序操作会被识别为新的编辑事件，导致触发器重复触发、逻辑执行冲突，最终功能异常。

以下是Google Sheets环境的完整可运行实现，Excel环境可参考对应逻辑修改：

前置配置说明 #

你可以根据自己的实际表格结构修改代码开头的CONFIG配置项，无需改动核心逻辑：

• 源表、目标表名称
• checkbox所在列序号
• 排序依据的列序号
• 表格是否包含表头

完整代码 #

// Google Apps Script 实现代码
function onEdit(e) {
  // 自定义配置区
  const CONFIG = {
    sourceSheetName: "待处理", // 源工作表名称
    targetSheetName: "已完成", // 目标工作表名称
    checkboxCol: 1, // checkbox所在列序号，A列为1，B列为2以此类推
    sortCol: 2, // 排序依据的列序号
    hasHeader: true, // 表格是否有表头
    sortAscending: true // 排序方向，true为升序，false为降序
  }

  // 加锁避免并发触发导致逻辑混乱
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(3000)) return;

  try {
    const editRange = e.range;
    const currentSheet = editRange.getSheet();
    // 仅响应源表checkbox列的勾选操作，过滤排序等非目标操作触发的编辑事件
    if (
      currentSheet.getName() !== CONFIG.sourceSheetName 
      || editRange.columnStart !== CONFIG.checkboxCol 
      || e.value !== "TRUE"
    ) {
      return;
    }

    // 读取目标行数据
    const targetRowNum = editRange.rowStart;
    const rowData = currentSheet.getRange(targetRowNum, 1, 1, currentSheet.getLastColumn()).getValues()[0];
    // 重置checkbox状态避免后续误触发
    editRange.setValue(false);
    // 删除源表对应行
    currentSheet.deleteRow(targetRowNum);
    // 追加数据到目标表
    const targetSheet = e.source.getSheetByName(CONFIG.targetSheetName);
    targetSheet.appendRow(rowData);

    // 通用排序方法
    const autoSort = (sheet) => {
      const startRow = CONFIG.hasHeader ? 2 : 1;
      if (sheet.getLastRow() < startRow) return;
      const sortRange = sheet.getRange(startRow, 1, sheet.getLastRow() - startRow + 1, sheet.getLastColumn());
      sortRange.sort({column: CONFIG.sortCol, ascending: CONFIG.sortAscending});
    }

    // 对两个工作表分别执行自动排序
    autoSort(currentSheet);
    autoSort(targetSheet);
  } catch (error) {
    console.error("执行异常：", error);
  } finally {
    lock.releaseLock();
  }
}

注意事项 #

• Excel环境下只需将触发器替换为Worksheet_Change事件，移行、排序操作替换为VBA对应的对象方法即可，核心逻辑完全通用
• 第一次运行脚本需要按提示完成表格操作权限授权
• 调试前建议先备份表格数据，避免误操作导致内容丢失
• 需要多列排序规则时，只需修改sort方法的入参，传入多组排序配置即可

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