3

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

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.

  • +1; How would you make sure the increment is executed, without script? – Jacob Jan Tuinstra Dec 24 '14 at 20:35
  • @Jacob: Either manually by changing the value in F1 (just changing it is enough incrementing the value is only an example) or write a script, accessible via a custom menu that increments the value in F1. It's still a workaround, but a little easier that cutting and pasting the formula (or go into the script editor) every time you want the custom function to recalculate... – JPV Dec 25 '14 at 10:27
1

Short answer

Instead of using the scripts as a custom function use a Google Apps Script simple or installable triggers to update the cell values.

Explanation

Custom functions only are recalculated when one or their arguments changes. As was already mentioned in the this other answer, one alternative is to add an additional argument to force the recalculation. Another alternative is to use a simple or an installable trigger to update the cell values. See https://developers.google.com/apps-script/guides/triggers/ for details about the available triggers in Google Apps Script.

The event object that includes a property that could be used to detect the change of the background of a cell is the object related to the change installable trigger. The related property is changeType and the value is Format.

Example

The following script will update B1 cell with the calculation made by a function every time that the format of the spreadsheet is changed. If the cells background is not changed the result will be the same but if the a cell background changes the cell value will be different.

It was constructed assuming that there is only one sheet. If your spreadsheet has more than one, add the sheet name to the references.

var rangeSpecification = 'A1:A10';
var resultDestination = 'B1';
var color = '#ffff00'; //yellow

function myFunction(e) {
  if(e.changeType == 'FORMAT') {
    SpreadsheetApp
      .getActiveSheet()
      .getRange(resultDestination)
      .setValue(sumWhereBackgroundColorIs(color, rangeSpecification));
  }
}

References

Related

Answer by me to Google Spreadsheet generate random numbers with Math.random()

  • Will the simple trigger work if the function name is myFunction instead of onEdit? (changed in revision 2) – user202729 Jun 4 at 3:37
  • @user202729 It' will not work as changeType property belongs to the event object of the on change installable trigger. – Rubén Jun 4 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.