I need to count the cells that have a color and contain a string within a range
When i add a 3rd param it says Range not found line 14 which is var range = activeSheet.getRange(rangeA1Notation);
Here is what I have tried so far but only get #value
and says range not found.
/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @param {range} comparisonRef cell with string to be searched for in colorRef
* @return {number}
* @customfunction
*/
function countColoredCells(countRange,colorRef,comparisonRef) {
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 && values(i)(j).slice(0,2) == comparisonRefValue) //Count++ only if there is a color match and a model match
count=count+1;
return count;
};
Function used as such:
=countColoredCells(C5:I16,J7,A3)
Updated code
If i replace comparisonRefValue variable with “string” it works as
expected as long as I only pass 2 variables the 3rd breaks the
function