将含VLOOKUP的Google Sheets SORT公式转为等效Apps Script

现有Google Sheets公式：

=sort(D2:J,I2:I,TRUE,D2:D,TRUE,VLOOKUP(E2:E,'Frequency'!A:B,2,FALSE),TRUE,H2:H,TRUE,G2:G,TRUE)

该公式能生成正确排序结果，但不会修改源数据行。需要转为等效的Apps Script，实现点击按钮时按规则重排源数据行。

数据结构 #

• 源数据表（假设工作表名为Sheet1）：

（注：实际数据包含D到J列，上述为核心列示例）

• Frequency工作表（频次-数值映射）：

排序规则 #

1. 按I列（第9列）升序
2. 按D列（第4列）升序
3. 按「频次」列通过VLOOKUP转换后的数值升序
4. 按H列（第8列）升序
5. 按G列（第7列）升序

之前尝试的基础排序脚本仅能按单元格原值排序，无法实现VLOOKUP转换后排序的需求。

function sortSourceData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 替换为你的源数据工作表名称
  const sourceSheet = ss.getSheetByName("Sheet1");
  // 替换为Frequency工作表名称
  const freqSheet = ss.getSheetByName("Frequency");
  
  // 获取源数据（从第2行开始，跳过表头）
  const sourceRange = sourceSheet.getRange(2, 4, sourceSheet.getLastRow() - 1, 7); // D2:J
  const sourceData = sourceRange.getValues();
  
  // 构建频次-数值映射对象
  const freqRange = freqSheet.getRange(1, 1, freqSheet.getLastRow(), 2);
  const freqData = freqRange.getValues();
  const freqMap = {};
  freqData.forEach(row => {
    freqMap[row[0]] = row[1];
  });
  
  // 给每行添加排序用的辅助值（替代VLOOKUP结果）
  const dataWithSortKey = sourceData.map(row => {
    const freqText = row[1]; // E列是源数据数组的第2个元素（D=0, E=1）
    const sortKey = freqMap[freqText] || Infinity; // 无匹配项时排最后
    return [...row, sortKey];
  });
  
  // 按规则执行排序
  dataWithSortKey.sort((a, b) => {
    // 1. I列（数组索引5）升序
    if (a[5] !== b[5]) return a[5] > b[5] ? 1 : -1;
    // 2. D列（数组索引0）升序
    if (a[0] !== b[0]) return a[0] > b[0] ? 1 : -1;
    // 3. 频次数值（数组最后一位）升序
    if (a[a.length-1] !== b[b.length-1]) return a[a.length-1] - b[b.length-1];
    // 4. H列（数组索引4）升序
    if (a[4] !== b[4]) return a[4] > b[4] ? 1 : -1;
    // 5. G列（数组索引3）升序
    if (a[3] !== b[3]) return a[3] > b[3] ? 1 : -1;
    return 0;
  });
  
  // 移除辅助值，得到最终排序数据
  const sortedData = dataWithSortKey.map(row => row.slice(0, -1));
  
  // 将排序后的数据写回源数据区域
  sourceRange.setValues(sortedData);
}

代码说明 #

1. 数据与映射表处理：
   • 读取源数据区域D2:J（跳过表头），将Frequency表的键值对转为对象，实现快速匹配，替代VLOOKUP功能。
2. 添加排序辅助键：
   • 给每行数据追加对应的频次数值，作为第三排序依据，避免依赖单元格公式。
3. 自定义排序逻辑：
   • 严格按照需求优先级依次比较列值，其中频次使用转换后的数值进行排序。
4. 写回源数据：
   • 移除辅助键后，将排序后的数组覆盖写回源区域，直接修改源数据行顺序。

使用方法 #

1. 打开Google Sheets，点击「扩展程序」>「Apps脚本」，粘贴上述代码。
2. 修改代码中的工作表名称（Sheet1和Frequency）为实际表名。
3. 保存并运行一次脚本，完成权限授权。
4. 返回工作表，插入绘图/按钮，绑定sortSourceData函数，点击即可触发排序。

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