Count the cells with same color in google spreadsheet

Question

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 1

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 :)

Answer 2

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

 for(var r = 0; r < cell_colors.length; r++) {
   for(var c = 0; c < cell_colors[0].length; c++) {
     if(cell_colors[r][c] == color) {
       count = count + 1;
     }
   }
 }
    range_output.setValue(count);
 }

Answer 3

You can use this working script:

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();

  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();

  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();

  var count = 0;

  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

Then call this function in your google sheets:

=countColoredCells(D5:D123,Z11)

Answer 4

here is a working version :

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();
 var color = "#58FA58";
 var count = 0;

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

Answer 5

The previous functions didn't work for me, so I've made another function that use the same logic of one of the answers above: parse the formula in the cell to find the referenced range of cells to examine and than look for the coloured cells. You can find a detailed description here: Google Script count coloured with reference, but the code is below:

function countColoured(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  }
  var c = 0;
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      c = c + ( range.getCell(i,j).getBackground() == "#ffffff" ? 0 : 1 );
    }
  }
  return c > 0 ? c : "" ;
}