如何修改Google App Script实现双列负数置顶排序并兼容数组公式

要实现Paymentlist1工作表中第5列（E列）和第9列（I列）的负数均置顶，同时保留表头数组公式不被破坏，可以通过添加临时辅助列标记负数行，再按辅助列优先排序的方式实现，修改后的脚本如下：

function autoSort() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Paymentlist1');
  const lastRow = sheet.getLastRow();
  if (lastRow <= 2) return; // 无数据时直接退出

  // 定位数据区域：从第3行开始（避开第2行表头）
  const dataRange = sheet.getRange(3, 1, lastRow - 2, sheet.getLastColumn());
  const helperColIndex = sheet.getLastColumn() + 1;

  // 添加辅助列，标记E列或I列为负数的行
  sheet.getRange(3, helperColIndex, lastRow - 2).setFormulaR1C1('=OR(RC[5-COLUMN()]<0, RC[9-COLUMN()]<0)');
  SpreadsheetApp.flush(); // 强制完成公式计算

  // 排序规则：先按辅助列降序（负数行置顶），再按E列、I列升序
  dataRange.sort([
    {column: helperColIndex, ascending: false},
    {column: 5, ascending: true},
    {column: 9, ascending: true}
  ]);

  // 删除临时辅助列
  sheet.deleteColumn(helperColIndex);
}

关键说明 #

• 辅助列标记：用OR(E3<0, I3<0)公式自动标记所有包含负数的行，确保这类行在排序时被优先置顶
• 排序优先级：先按辅助列降序（TRUE值在前），再按原需求对E列、I列升序，既保证负数行全部置顶，又维持非负数行的排序逻辑
• 表头保护：数据范围从第3行开始，完全避开第2行的表头数组公式，避免排序操作对表头造成影响
• 临时列清理：排序完成后立即删除辅助列，不影响表格原有结构

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