Google Sheets Apps Script：按自定义下拉值顺序实现自动排序

可运行的自定义排序代码 #

function onEdit(e) {
  // 获取编辑的工作表和单元格信息
  const sheet = e.range.getSheet();
  const editedColumn = e.range.getColumn();
  const editedRow = e.range.getRow();

  // 仅当编辑的是"Testing"工作表的第2列，且行号≥3时执行排序
  if (sheet.getName() !== "Testing" || editedColumn !== 2 || editedRow < 3) return;

  // 定义业务需求的自定义排序优先级
  const customOrder = [
    "Final Phase",
    "In Progress",
    "Delayed Pending",
    "Suspended",
    "Cancelled",
    "Proposed",
    "Completed"
  ];

  // 获取目标数据范围：第3行第1列到最后一行，共14列
  const lastRow = sheet.getLastRow();
  const dataRange = sheet.getRange(3, 1, lastRow - 2, 14);
  const dataValues = dataRange.getValues();

  // 执行自定义排序
  dataValues.sort((a, b) => {
    // 取每行第2列（数组索引1）的值在自定义序列中的位置
    const posA = customOrder.indexOf(a[1]);
    const posB = customOrder.indexOf(b[1]);
    // 未匹配到的默认排到最后
    return (posA !== -1 ? posA : customOrder.length) - (posB !== -1 ? posB : customOrder.length);
  });

  // 将排序后的数据写回工作表
  dataRange.setValues(dataValues);
}

错误代码问题分析 #

第一段错误代码的核心问题： #

• 未提前定义ws、sh变量，直接调用会触发引用错误
• 自定义排序顺序与需求颠倒（把"In Progress"设为第一优先级）
• 同时使用range和vs两个数据范围，逻辑混乱导致排序结果无法正确写入

第二段错误代码的核心问题： #

• 触发条件错误：监测的是第14列而非需求的第2列
• 数组索引错误：a[12]对应第13列，实际需要的第2列应使用a[1]（数组从0开始计数）
• 拼写错误："Poposed"应为"Proposed"，导致该状态无法匹配排序规则
• 写入逻辑位置错误：排序后的数据写操作在if判断外，会导致非触发场景下也执行写入

关键知识点说明 #

1. 事件对象e的使用：通过e获取编辑信息，比直接调用getActiveSpreadsheet更高效，且避免权限问题
2. 自定义排序逻辑：利用Array.indexOf()获取值在自定义序列中的位置，通过位置差实现指定顺序排序
3. 边界处理：对不在自定义序列中的值，默认分配到最后一位（用customOrder.length作为默认位置）
4. 范围计算：lastRow - 2是因为数据从第3行开始，总行数为lastRow - 3 + 1 = lastRow - 2

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