Skip to content

Instantly share code, notes, and snippets.

@kyleburton
Created August 30, 2019 23:01
Show Gist options
  • Save kyleburton/64aff46b021702baf89cd555ecf7246a to your computer and use it in GitHub Desktop.
Save kyleburton/64aff46b021702baf89cd555ecf7246a to your computer and use it in GitHub Desktop.
counting colored cells in google sheets
// =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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment