Google-sheets – How to refresh script results when a cell color is changed

google sheetsgoogle-apps-script

I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. How to update/refresh the cell count?

The script I'm using is from http://pastebin.com/4Yr095hV:

function getBackgroundColor(rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  return sheet.getRange(rangeSpecification).getBackgroundColor();     
}

function sumWhereBackgroundColorIs(color, rangeSpecification) {     
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);     
  var x = 0;     
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {         
      var cell = range.getCell(i, j);         
      if(cell.getBackgroundColor() == color) {
        x += parseFloat(cell.getValue());
      }
    }
  }     
  return x;
}

function countCellsWithBackgroundColor(color, rangeSpecification) {     
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackgroundColor() == color) {
        x++;
      }          
    }
  }     
  return x;
}

Best Answer

The only way I know is to use a 'dummy' parameter to the function, that is not really used in the custom function, but will trigger the function to recalculate. So, where you would normally use:

=sumWhereBackgroundColorIs(color, rangeSpecification)

you would now use

sumWhereBackgroundColorIs(color, rangeSpecification, F1)

In F1 (or any other cell you may want to use) you can have value (e.g. 1) that when changed (e.g: incremented with 1) will force the recalculation of the custom function. This 'incrementing act' can also be scripted.

Related Topic