如何实现Google Sheet按自定义顺序对下拉框数据自动重排序？

原代码错误点 #

• 核心方法调用错误：Google Sheet的Range.sort()仅支持按列的升/降序规则排序，不支持直接传入自定义排序函数，要实现自定义规则必须先读取范围内的值排序后再写回
• 状态常量未定义：代码里的READY、OFFSHIFT等变量没有赋值为实际使用的状态字符串，无法匹配下拉框选项
• 函数参数缺失：customSort需要的arrayOrder、numColumn参数调用时完全没有传入，逻辑无法正常运行
• 语法错误：代码末尾多了一个多余的闭合大括号，直接触发脚本报错
• 冗余逻辑无用：arrayOrder相关的转置逻辑完全没有使用场景，属于多余代码

修正后可用代码 #

const SHEET_NAME = "Waiters";
const SORT_DATA_RANGE = "A3:G15";
// 状态列在排序范围A3:G中的序号：A是第1列、B第2、C第3，根据你实际的状态列位置修改
const STATUS_COLUMN_IN_RANGE = 3;
// 自定义排序优先级，和下拉框的状态值完全对应，顺序就是表格从上到下的展示顺序
const SORT_ORDER = ["Ready", "Off-shift", "On-vacation", "Rest"]; // 可自行添加其他状态

function onEdit(e){
  // 仅修改指定表格的状态列时才触发排序，避免不必要的运行
  const editedSheet = e.source.getActiveSheet();
  if(editedSheet.getName() !== SHEET_NAME) return;
  const editedRange = e.range;
  const sortRange = editedSheet.getRange(SORT_DATA_RANGE);
  const realStatusColumn = sortRange.getColumn() + STATUS_COLUMN_IN_RANGE - 1;
  if(editedRange.columnStart !== realStatusColumn 
    || editedRange.rowStart < sortRange.getRow() 
    || editedRange.rowEnd > sortRange.getLastRow()) return;
  multiSortColumns();
}

function multiSortColumns(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  const range = sheet.getRange(SORT_DATA_RANGE);
  // 读取范围内所有值
  const data = range.getValues();
  // 按自定义规则排序
  data.sort((a,b) => {
    const aStatusIndex = SORT_ORDER.indexOf(a[STATUS_COLUMN_IN_RANGE - 1]);
    const bStatusIndex = SORT_ORDER.indexOf(b[STATUS_COLUMN_IN_RANGE - 1]);
    return aStatusIndex - bStatusIndex;
  })
  // 排序后的值写回表格
  range.setValues(data);
  ss.toast('排序完成');
}

使用注意事项 #

• 确认STATUS_COLUMN_IN_RANGE的数值和你实际状态列在排序范围内的序号一致
• 确认SORT_ORDER里的状态字符串和你下拉框的选项完全一致，大小写、空格、符号都不能有差异
• 排序范围如果包含表头，需要调整范围或者逻辑排除表头行

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