Apps Script中Console.Log排序正常，Google Sheets无效果问题求助

核心问题原因 #

你的代码中全局变量初始化时机是关键问题：Google Apps Script的全局变量会在脚本首次启动时加载，之后如果工作表（ROSTER/COMP）的数据更新，全局变量不会自动刷新，导致getData函数获取的是旧的角色/职业信息，最终排序逻辑失效。而test()函数直接运行时会重新执行全局变量初始化，所以console输出正确，但作为自定义函数在Sheets中调用时，全局变量不会重新加载，用的是旧数据。

修复方案 #

将依赖工作表数据的全局变量（ROSTER、COMPS、RANGE_ROSTER、RANGE_COMP）移到需要使用它们的函数内部，确保每次调用函数时都获取最新的工作表数据。

修改后的代码示例

const BOSSES = ['Magmaw','Omnotron Defense System','Chimaeron','Atramedes','Maloriak','Nefarian','Halfus Wyrmbreaker','Valiona and Theralion','Ascendant Council','Cho\'gall','Sinestra','Conclave of Wind','Al\'Akir'];
const CLASSES = ['Death Knight', 'Druid', 'Hunter', 'Mage', 'Paladin', 'Priest', 'Rogue', 'Shaman', 'Warlock', 'Warrior'];
const ROLES = ['Tank','Healer','Melee','Ranged'];
const SPECID = {
    250: { class: "Death Knight", spec: "Blood", role: "Tank" },
    251: { class: "Death Knight", spec: "Frost", role: "Melee" },
    252: { class: "Death Knight", spec: "Unholy", role: "Melee" },
    102: { class: "Druid", spec: "Balance", role: "Ranged" },
    103: { class: "Druid", spec: "Feral", role: "Melee" },
    104: { class: "Druid", spec: "Guardian", role: "Tank" },
    105: { class: "Druid", spec: "Restoration", role: "Healer" },
    253: { class: "Hunter", spec: "Beast Mastery", role: "Ranged" },
    254: { class: "Hunter", spec: "Marksmanship", role: "Ranged" },
    255: { class: "Hunter", spec: "Survival", role: "Ranged" },
    62 : { class: "Mage", spec: "Arcane", role: "Ranged" },
    63 : { class: "Mage", spec: "Fire", role: "Ranged" },
    64 : { class: "Mage", spec: "Frost", role: "Ranged" },
    65 : { class: "Paladin", spec: "Holy", role: "Healer" },
    66 : { class: "Paladin", spec: "Protection", role: "Tank" },
    70 : { class: "Paladin", spec: "Retribution", role: "Melee" },
    256: { class: "Priest", spec: "Discipline", role: "Healer" },
    257: { class: "Priest", spec: "Holy", role: "Healer" },
    258: { class: "Priest", spec: "Shadow", role: "Ranged" },
    259: { class: "Rogue", spec: "Assassination", role: "Melee" },
    260: { class: "Rogue", spec: "Combat", role: "Melee" },
    261: { class: "Rogue", spec: "Subtlety", role: "Melee" },
    262: { class: "Shaman", spec: "Elemental", role: "Ranged" },
    263: { class: "Shaman", spec: "Enhancement", role: "Melee" },
    264: { class: "Shaman", spec: "Restoration", role: "Healer" },
    265: { class: "Warlock", spec: "Affliction", role: "Ranged" },
    266: { class: "Warlock", spec: "Demonology", role: "Ranged" },
    267: { class: "Warlock", spec: "Destruction", role: "Ranged" },
    71 : { class: "Warrior", spec: "Arms", role: "Melee" },
    72 : { class: "Warrior", spec: "Fury", role: "Melee" },
    73 : { class: "Warrior", spec: "Protection", role: "Tank" },
};

// 封装工作表数据获取逻辑
function getRosterData() {
  const SHEET = SpreadsheetApp.getActiveSpreadsheet();
  const SHEET_ROSTER = SHEET.getSheetByName('ROSTER');
  const RANGE_ROSTER = SHEET_ROSTER.getRange(2, 1, SHEET_ROSTER.getLastRow() - 1, SHEET_ROSTER.getLastColumn()).getValues();
  return RANGE_ROSTER.map(([nick, char, specid, parse]) => ({ nick, char, specid, parse }));
}

function getCompData() {
  const SHEET = SpreadsheetApp.getActiveSpreadsheet();
  const SHEET_COMP = SHEET.getSheetByName('COMP');
  const RANGE_COMP = SHEET_COMP.getRange(1, 1, SHEET_COMP.getLastRow(), SHEET_COMP.getLastColumn()).getValues();
  return RANGE_COMP[0].map((_, colIndex) => RANGE_COMP.map(row => row[colIndex]));
}

function test() {
    console.log(setSortedNicks(getRolesFromComp(0,"Melee")));
}

function getDataBySpecID(specid, key) {
    const data = SPECID[specid];
    return data ? data[key] : 'Unknown';
}

function getData(nick, key) {
    const ROSTER = getRosterData(); // 每次调用获取最新数据
    const data = ROSTER.find(row => row.nick === nick);
    return data ? data[key] || getDataBySpecID(data.specid, key) : 'Unknown';
}

function setSortedNicks(comp, customOrder = ROLES) {
  const roleIndices = new Map();
  const dataMap = new Map();

  for (const nick of comp) {
    const role = getData(nick, 'role');
    const roleIndex = customOrder.indexOf(role);
    const class_ = getData(nick, 'class');
    dataMap.set(nick, { roleIndex, class_ });
    roleIndices.set(nick, roleIndex);
  }

  comp.sort((a, b) => {
    const aData = dataMap.get(a);
    const bData = dataMap.get(b);

    const roleDiff = aData.roleIndex - bData.roleIndex;
    if (roleDiff !== 0) return roleDiff;

    return aData.class_.localeCompare(bData.class_);
  });

  return comp;
}

function getRolesFromComp(boss, role, ...roles) {
    const COMPS = getCompData(); // 每次调用获取最新数据
    return COMPS[boss].filter(nick => role === getData(nick, 'role') || roles.includes(getData(nick, 'role')));
}

function getSpecsFromComp(boss, spec, ...specs) {
    const COMPS = getCompData(); // 每次调用获取最新数据
    return COMPS[boss].filter(nick => spec === getData(nick, 'spec') || specs.includes(getData(nick, 'spec')));
}

额外优化建议 #

• 如果工作表数据量较大，频繁调用getRosterData()和getCompData()可能影响性能，可以用CacheService添加临时缓存逻辑，减少重复读取工作表的次数。
• 作为自定义函数在Sheets中调用时，确保传入的参数类型正确，比如boss参数是对应BOSSES数组的数字索引。

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