Google Sheets Apps Script实现主表行自动分迁至对应子表并排序

需求与当前问题 #

• 纯Apps Script新手，无公司IT支持，需搭建共享Sheet供客户、技术人员、总部访问
• 核心要求：仅主表「WORKSHEET」可录入，子表仅允许查看；主表行标记「DONE」后自动移至对应单元子表，并按日期列排序子表留存历史
• 当前痛点：54+单元需重复复制修改代码，单单元配置后子表无法自动排序

现有代码问题排查 #

1. 语法错误直接导致函数失效
   现有代码存在多处无效语法：

   • var columnNumberToWatch = 6;2 多了无意义数字2
   • var valueToWatch = "Yes";"27" 错误的变量赋值逻辑，导致条件判断永远不成立
   • 注释与代码不符：Sort27注释标注按列J排序，但代码实际调用column:1（对应列A）

2. 排序未自动触发
   trailer27函数执行后未调用Sort27，且未配置触发器绑定行移动事件，即使Sort27代码正确也不会自动运行

3. 固定排序范围限制
   Sort27使用A3:F1000固定范围，数据超出1000行或存在空行时排序失效，应使用动态范围获取实际数据区域

通用简洁实现方案 #

第一步：权限控制（防止手动修改子表） #

• 打开Sheet，点击「数据」→「保护工作表和范围」
• 逐个选择子表，设置权限为「仅特定用户可编辑」，仅添加自己的账号；其他用户默认设为「查看者」
• 主表「WORKSHEET」可开放给需要录入的用户编辑，或保护特定列限制编辑范围

第二步：通用代码（无需重复编写单元函数） #

替换现有代码为以下通用版本，根据实际表格调整参数注释：

// 编辑触发自动移行与排序
function onEdit(e) {
  const ss = e.source;
  const activeSheet = ss.getActiveSheet();
  const activeCell = e.range;
  const cellValue = activeCell.getValue().toString().trim();

  // --------------------------
  // 主表「WORKSHEET」移行逻辑
  // --------------------------
  if (activeSheet.getName() === "WORKSHEET") {
    // 配置：标记「DONE」的列（F列=6，按需修改）、单元编号所在列（假设G列=7，按需修改）
    const doneColumn = 6;
    const unitColumn = 7;

    if (activeCell.getColumn() === doneColumn && cellValue === "DONE") {
      const unitName = activeSheet.getRange(activeCell.getRow(), unitColumn).getValue().toString();
      const targetSheet = ss.getSheetByName(unitName);
      if (!targetSheet) return; // 子表不存在则跳过

      // 移动行到子表
      const rowData = activeSheet.getRange(activeCell.getRow(), 1, 1, activeSheet.getLastColumn());
      rowData.moveTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1));
      activeSheet.deleteRow(activeCell.getRow());

      // 子表按日期排序（假设日期在B列=2，按需修改）
      sortSheet(targetSheet, 2);
    }
  }

  // --------------------------
  // 子表移回主表逻辑（撤销）
  // --------------------------
  else {
    // 配置：触发移回的状态值、状态列（同F列=6）
    const undoStatuses = ["CALLED", "EMAIL"];
    const statusColumn = 6;

    if (activeCell.getColumn() === statusColumn && undoStatuses.includes(cellValue)) {
      const targetSheet = ss.getSheetByName("WORKSHEET");
      if (!targetSheet) return;

      // 移动行回主表
      const rowData = activeSheet.getRange(activeCell.getRow(), 1, 1, activeSheet.getLastColumn());
      rowData.moveTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1));
      activeSheet.deleteRow(activeCell.getRow());

      // 主表按日期排序（假设日期在B列=2，按需修改）
      sortSheet(targetSheet, 2);
    }
  }
}

// 通用排序函数：跳过前2行表头，按指定列升序排序
function sortSheet(sheet, sortColumn) {
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  if (lastRow < 3) return; // 无数据无需排序

  const dataRange = sheet.getRange(3, 1, lastRow - 2, lastCol);
  dataRange.sort({column: sortColumn, descending: false});
}

第三步：代码使用说明 #

1. 修改代码中doneColumn、unitColumn、sortColumn等参数，匹配你的表格列位置
2. 无需手动配置触发器：onEdit是Google Sheets内置简单触发器，编辑单元格时自动触发
3. 测试：在主表某行对应状态列输入「DONE」，检查是否自动移到对应单元子表并按日期排序

内容的提问来源于stack exchange，提问作者S.Nicole