如何让Apps Script自定义COLOR函数支持无引号参数与嵌套函数

问题分析 #

你的COLOR函数目前只能接受带引号的单元格地址字符串（比如COLOR("A1")），是因为代码里直接用getRange(input)，而当你传入不带引号的单元格引用（如COLOR(A1)）或嵌套函数（如COLOR(XLOOKUP(...))）时，传入的是单元格的值，不是地址字符串，导致函数无法正确定位到目标单元格。

修改后的函数代码 #

/**
 * 返回单元格或单元格区域的背景色
 * 
 * @customfunction
 * @param {Range} input 要获取背景色的单元格或区域
 */
function COLOR(input) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let range;

  // 处理两种输入类型：Range对象（不带引号的引用）或地址字符串
  if (input instanceof Range) {
    range = input;
  } else if (typeof input === 'string') {
    range = ss.getActiveSheet().getRange(input);
  } else {
    // 如果传入的是普通值（比如XLOOKUP直接返回的单元格值），无法定位单元格，返回提示
    return "请传入单元格引用或有效的地址字符串";
  }

  return range.getBackgrounds();
}

关键修改点 #

1. 添加了@param {Range} input的JSDoc注释，告诉Google Sheets这个函数可以接受单元格/区域引用作为参数，此时传入的会是Range对象而非值。
2. 增加了类型判断逻辑，同时兼容旧的字符串地址用法，也能处理直接传入的单元格引用。

嵌套函数的正确用法 #

如果你想用XLOOKUP动态指定目标单元格，需要让嵌套函数返回单元格引用而非值，推荐用INDEX+MATCH组合（因为INDEX返回的是单元格引用，直接能被COLOR识别）：

=COLOR(INDEX('Raw Data'!CQ:CQ, MATCH(AY35, 'Raw Data'!CO:CO, 0)))

如果一定要用XLOOKUP，需要让它返回目标单元格的地址字符串，再用INDIRECT转换为引用：

=COLOR(INDIRECT(XLOOKUP(AY35, 'Raw Data'!CO:CO, ADDRESS(ROW('Raw Data'!CQ:CQ), COLUMN('Raw Data'!CQ:CQ)))))

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