使用复选框触发自定义颜色计数函数时出现范围未找到错误

问题场景 #

我尝试用自定义函数统计指定背景色的单元格数量，单独使用函数时运行正常：

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

为实现公式重算需求，我将函数嵌套进IF函数：=if($a$41,countColoredCells(D4:D36,$BX$6), " ")。当复选框未勾选时结果正常，但勾选时却报错“Exception: Range not found (line 7)”。

问题原因 #

核心问题是自定义函数通过解析单元格公式获取参数范围的逻辑，在嵌套IF后完全失效：

• 单独使用=countColoredCells(D4:D36,$BX$6)时，activeRange.getFormula()拿到的是纯函数公式，正则/\((.*)\,/能精准匹配到D4:D36，/\,(.*)\)/能匹配到$BX$6，后续获取范围的操作正常。
• 嵌套进IF后，公式变成=if($a$41,countColoredCells(D4:D36,$BX$6), " ")，此时正则/\((.*)\,/会错误匹配到$a$41,countColoredCells(D4:D36，这显然不是合法的单元格范围，执行activeSheet.getRange(rangeA1Notation)时直接抛出“Range not found”错误。

修复方案 #

完全不需要通过解析公式获取参数，Google Sheets会自动将公式里的单元格范围解析为Range对象传入自定义函数，修改后的代码如下：

function countColoredCells(countRange, colorRef) {
  // 直接使用传入的Range对象，无需解析公式字符串
  var bg = countRange.getBackgrounds();
  var color = colorRef.getBackground();
  
  var count = 0;
  for(var i=0;i<bg.length;i++){
    for(var j=0;j<bg[0].length;j++){
      if( bg[i][j] == color ){
        count++;
      }
    }
  }
  return count;
};

修改后，嵌套IF的公式=if($a$41,countColoredCells(D4:D36,$BX$6), " ")即可正常运行。

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