Count the cells with same color in google spreadsheet

user1974764 picture user1974764 · Jan 16, 2013 · Viewed 124.7k times · Source

I'm trying count the number of cells with the same background color and put the result in other cell with a script in google apps script, but I can't do it. I have the next script but not work and I don't know which is the problem:

function countbackgrounds() {
 var book = SpreadsheetApp.getActiveSpreadsheet();
 var range_input = book.getRange("B3:B4");
 var range_output = book.getRange("B6");
 var cell_colors = range_input.getBackgroundColors()[0];
 var color = "#58FA58";
 var count = 0;

 for( var i in cell_colors )
  if( cell_colors[i] == color ){
    range_output.setValue(++count);
  }
  else {
    return count; 
  }
 }  

Answer

Wolph picture Wolph · May 16, 2015

Easy solution if you don't want to code manually using Google Sheets Power Tools:

  1. Install Power Tools through the Add-ons panel (Add-ons -> Get add-ons)
  2. From the Power Tools sidebar click on the Σ button and within that menu click on the "Sum by Color" menu item
  3. Select the "Pattern cell" with the color markup you want to search for
  4. Select the "Source range" for the cells you want to count
  5. Use function should be set to "COUNTA"
  6. Press "Insert function" and you're done :)