Range as a function parameter-CodePudding

I'm trying to create a function that counts the number of cells in a range that have the same background as the reference one

function Color_Count (rango, color_cell) {
 var book = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = book.getActiveSheet();
 var cell_colors = sheet.getRange('"'   rango   '"').getBackgroundColors();
 var color = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('"'   color_cell   '"').getBackgrounds();
 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;
     }
   }
 }
    return count;

But I get the error that the range is not found. How could I fix it?

CodePudding user response：

It's very likely that the problem are the following expressions:

'"'   rango   '"'

and

'"'   color_cell   '"'

Assuming that rango and color_cell are strings and they are valid references, replace the above expressions by

rango

and by

color_cell

respectively. In other words, don't add the " when passing a cell reference as string as getRange() parameter.

CodePudding user response：

Counting Background Colors that match a reference cell

function Color_Count(rango="A1:J1", color_cell="A2") {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  var cell_colors = sh.getRange(rango).getBackgroundColors();
  var color = sh.getRange(color_cell).getBackground();
  var count = 0;
  cell_colors.forEach(r => {
    r.forEach(c => {if(c == color){count  }})
  });
  Logger.log(count);
  return count;
}

Execution log
10:20:18 AM Notice  Execution started
10:20:19 AM Info    5.0
10:20:20 AM Notice  Execution completed

Sheet: