Counting cells in Google Sheets based on font color?

Other Questions Google Sheets
1

Hey everyone,

I’m trying to figure out how to count cells in Google Sheets based on their font color. I found a script that works for cell background colors, but it doesn’t do the trick for font colors. Here’s a refreshed version of the code:

function countColoredText(range, colorReference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var targetRange = sheet.getRange(range);
  var textColors = targetRange.getFontColors();
  var data = targetRange.getValues();
  var referenceColor = sheet.getRange(colorReference).getFontColor();
  var textCount = 0;

  for (var row = 0; row < textColors.length; row++) {
    for (var col = 0; col < textColors[row].length; col++) {
      if (textColors[row][col] === referenceColor) {
        textCount++;
      }
    }
  }

  return textCount;
}

Could someone help me verify if this updated code checks for font colors properly? I would appreciate any suggestions to refine it further. Thanks a lot!

2

hey luna, i tried ur method and it seems solid. double-check your colorRef cell though - if its off, count returns 0. adding error handling might roght be useful in messy sheets. good luck!

3

Your code looks promising, Luna23. I’ve implemented similar functions in my spreadsheets and can confirm that getFontColors() should work for your purpose. One potential improvement would be to add a check for empty cells to avoid counting them if they happen to match the reference color. Also, consider caching the results if you’re working with large datasets, as repeated calls to getFontColors() can slow down performance. If you encounter any issues, try logging the textColors and referenceColor values to ensure they’re being captured correctly. Keep in mind that font colors in Google Sheets can sometimes be tricky due to inherited styles or conditional formatting.

4

I’ve used a similar approach for counting cells based on font color in my work projects. Your code looks solid, but there’s one potential improvement I can suggest.

Instead of using getFontColors(), which returns an array of arrays, you might want to try getFontColor() for each cell individually. This can be more reliable, especially if you’re dealing with mixed formatting within cells.

Here’s a tweaked version that worked well for me:

function countColoredText(range, colorReference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var targetRange = sheet.getRange(range);
  var referenceColor = sheet.getRange(colorReference).getFontColor();
  var textCount = 0;

  targetRange.getValues().forEach(function(row, i) {
    row.forEach(function(cell, j) {
      if (targetRange.getCell(i+1, j+1).getFontColor() === referenceColor) {
        textCount++;
      }
    });
  });

  return textCount;
}

This method iterates through each cell individually, which might be slightly slower but more accurate in my experience. Give it a try and see if it works better for your specific use case.