如何使用Google Apps Script在电子表格中实现排名替代数组公式

你可以直接按以下步骤操作实现需求：

• 打开需要处理的Google表格，点击顶部菜单栏「扩展程序」→「Apps 脚本」进入脚本编辑页面
• 清空编辑器内默认的空代码，直接粘贴下方的代码
• 根据你的实际表格情况修改代码开头配置项的参数：
  • 修改SHEET_NAME为你要操作的工作表名称
  • 对应修改数据源列、结果列的序号，以及绿色单元格对应的十六进制颜色值
• 点击顶部保存项目，点击运行按钮，首次运行按照提示完成授权即可生效
• 如果后续数据有更新，重新运行一次脚本就能更新排名结果

function rankToGreenCells() {
  // 配置项，可根据自己的表格修改
  const SHEET_NAME = "Sheet1"; // 你要操作的工作表名称
  const DATA_COLUMN = 2; // 要排名的数据源所在列，A=1，B=2以此类推
  const RESULT_COLUMN = 3; // 排名结果要填充的列
  const TARGET_COLOR = "#00ff00"; // 目标绿色单元格的十六进制颜色码
  const RANK_ASC = false; // false=降序（分数越高排名越靠前，true=升序

  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const lastRow = ss.getLastRow();
  // 获取所有需要排名的数值
  const dataValues = ss.getRange(2, DATA_COLUMN, lastRow - 1, 1).getValues().flat();
  // 计算排名映射表，默认同分同排名不跳号
  const rankMap = new Map();
  const sortedValues = [...dataValues].sort((a, b) => RANK_ASC ? a - b : b - a);
  sortedValues.forEach((val, idx) => {
    if (!rankMap.has(val)) {
      rankMap.set(val, idx + 1);
    }
  });
  // 获取结果列的背景色和单元格范围
  const resultRange = ss.getRange(2, RESULT_COLUMN, lastRow - 1, 1);
  const backgroundColors = resultRange.getBackgrounds();
  // 遍历填充排名
  const output = backgroundColors.map((color, rowIdx) => {
    if (color[0].toUpperCase() === TARGET_COLOR.toUpperCase()) {
      const currentVal = dataValues[rowIdx];
      return [rankMap.get(currentVal) || ""];
    }
    return [""];
  });
  // 把结果写回表格
  resultRange.setValues(output);
}

*如果需要实现同分同排名且下一名次跳号的需求，修改排名映射的赋值逻辑即可。

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