counting colored cells in google sheets

google-sheet-script.js

// =countbackgrounds("C2:K2","#00ff00")
/*

it's a bit of a hack to send in the cell range as a string, though it works when calling getRange:

    SpreadsheetApp.getActiveSpreadsheet().getRange(range)

This also means you can't "fill down" to have the ranges updated to match your sheet, so hack number two genreates
the formulas so you can copy/paste them into the sheet:

  perl -e 'print("=countbackgrounds(\"C$_:K$_\",\"#00ff00\", ",time%10000,")\n") for (2..61)' | pbcopy 
  
The `time%10000` param is ignored by the script function ... google sheets won't run your function every 
time cells or the function itself is updated.  That 3rd parameter kinda forces it to re-execute the script 
function .. if it changes from the previous value (hence the use of time, which is likely to be different).

 */
 
 function countbackgrounds(range, color) {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var range_input = book.getRange(range);
  var cell_colors = range_input.getBackgroundColors();
  var count = 0;

  for( var ii in cell_colors ){
    for ( var jj in cell_colors[ii] ) {
      var cellColor = ("" + cell_colors[ii][jj]).toLowerCase()
      if( cellColor == color.toLowerCase() ) { 
        ++count;
      }
    }
  }
  
  return count;
}