0

I am having custom function that counts number of colored cells within a A1 notation range.

function countByColor(color, inputA1) {
  let count = 0

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(inputA1);

  range.getBackgrounds().forEach(function(value) {
  if(value[0] != undefined) {  
    if(value[0] == color) {
      count=count+1
    }
  } 
 });
 return count
}

I am coloring the cells using conditional forwarding, so basically the argument of the calling function is not changed. I need to refresh the results within the sheet on change, but If I create Trigger event (based time) it tries to call that function, but failing as I am not providing (within the declaration in Apps Script) any arguments In Cells the function looks like this -

=countByColor("#00ff00", "I7:I70")*1

The function works find, but if another cell within that range changes color, it doesn't recalculate. Since I am using that function in 20 cells, I need to quickly recalculate all of them

  • Time Triggers
  • On Change Triggers
Rubén
  • 34,714
  • 9
  • 70
  • 166
Madi
  • 11
  • 1
  • 2

0 Answers0