Count the cells with same color in google spreadsh

问题:

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; 
  }
 }  

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

回答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);
 }

回答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)

回答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);
 }