CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Auster
Auster

Reputation: 11

Counting non empty cells with matching font color in Google Sheets

I'm using a custom function to count the number of cells with a matching font color in a range.

function countIfFont(countRange,fontRef,rangeSum) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var range = activeSheet.getRange(countRange);
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  var fc = range.getFontColors(); 
  var fontCell = activeSheet.getRange(fontRef);
  var font = fontCell.getFontColor();
  var count = 0;
  
  for(var i = 0; i < rangeHeight; i++)
    for(var j = 0; j < rangeWidth; j++)
      if(fc[i][j] == font)
        count = count+1;
  
  return count;
};

It works, but it is counting blank cells and I want it to exclude blank cells. How do I check if the cell is blank?

Upvotes: 1

Views: 63

Answers (1)

Tanaike
Tanaike

Reputation: 201358

In your script, how about the following modification?

From:

for(var i = 0; i < rangeHeight; i++)
  for(var j = 0; j < rangeWidth; j++)
    if(fc[i][j] == font)
      count = count+1;

To:

var values = range.getDisplayValues();
for (var i = 0; i < rangeHeight; i++)
  for (var j = 0; j < rangeWidth; j++)
    if (fc[i][j] == font && values[i][j])
      count = count + 1;
  • By this modification, when fc[i][j] == font and the cell is not empty, count = count + 1 is run.

Upvotes: 1

Related Questions