How to count text color in Google Spreadsheet range?

Question

I have the following code in Google spreadsheet script.

function countTextColor(inputColor, inputRange) {
  var inputRangeCells = SpreadsheetApp.getActiveSpreadsheet().getRange(inputRange);
  return inputRangeCells.getFontColor();
}

If I execute it like this:

=countTextColor("A1:E1")

I get the color of the first cell in the range.

But my purpose is to loop through the range and count the font color (specifeid as an input parameter) of all the cells. Adding the following code seems not to work:

var count = 0;
for(var r = 0; r < inputRangeCells.length; r++) {    
    var inputCell = inputRangeCells[r];
    if(inputCell.getFontColor() == inputColor) {
        count++;
    }
}

How to loop the range and get the color value of each cell?

TheMaster TheMaster · Accepted Answer · 2020-03-29T17:17:14

You shouldn't loop over class range. range is not iterable. getColors from the range and iterate over the colors:

/**
 * @param {string} inputColorStr color to check #ffffff
 * @param {string} inputRangeStr range to check A1:F2
 * @customfunction
 */
function countTextColor(inputRangeStr, inputColorStr = '#ff000000') {
  const inputRange = SpreadsheetApp.getActive()
    .getActiveSheet()
    .getRange(inputRangeStr);

  return inputRange
    .getFontColorObjects()
    .flat(2)
    .reduce(
      (count, color) =>
        count + Number(color.asRgbColor().asHexString() === inputColorStr),
      0
    );
}

Usage:

=COUNTTEXTCOLOR("A1:F1","#ff0000")

How to count text color in Google Spreadsheet range?

1 Answers