Counting Colours in Google Sheets

A function to count the number of cells with a given background colour:

function COUNTCOLOUR(range, colour) {
  // Get sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the formula from the cell and extract the range as a string
  // Google doesn't pass ranges through, it passes arrays of data within the range instead :(
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  // So... let's extract the bits between the brackets in the formula
  var args = formula.match(/\((.*?)\)/);
  // Now let's grab the first argument which should be a string representing the range
  var rangeString = args[1].split(',')[0]
  try {
    var range = sheet.getRange(rangeString);
  }
  catch(e) {
    throw new Error(rangeString + ' is not a valid range.');
  }

  // Get an array of all the colours in the range
  var colours = range.getBackgrounds();

  var count = 0;
  
  //Check each colour
  for (var i in colours){
    if (colours[i] == colour) {
      count = count + 1;
    }
  }

  return count;
}

The function should be called like so:

=COUNTCOLOUR(A1:A100, '#ff0000')

This example would count the number of red cells in the range A1:A100. Unfortunately, Google Sheets doesn’t trigger an update for this when a colour changes, it only does so when cell contents change rendering it largely useless 🙂