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

3 Answers 3

3

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.

2
  • +1; How would you make sure the increment is executed, without script?
    – Jacob Jan
    Dec 24, 2014 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, 2014 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()

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

I merged the input from this thread as well as another (https://support.google.com/docs/thread/18954165/run-function-on-cell-font-color-change?hl=en)

The final solution looks something like this:

function createSpreadsheetChangeTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onChange')
    .forSpreadsheet(ss)
    .onChange().create();
};

function onChange(e) {
    Logger.log('onChange triggered because of: ' + e.changeType);
    if(e.changeType === 'FORMAT') {
          var range = SpreadsheetApp.getActiveSheet().getRange('A1');
          var range2 = SpreadsheetApp.getActiveSheet().getRange('B1');
          var range3 = SpreadsheetApp.getActiveSheet().getRange('C1');
          var range4 = SpreadsheetApp.getActiveSheet().getRange('D1');
          var range5 = SpreadsheetApp.getActiveSheet().getRange('E1');
          var value = range.getValue();
          range.setValue(value + 0.01);
          range2.setValue(value + 0.01);
          range3.setValue(value + 0.01);
          range4.setValue(value + 0.01);
          range5.setValue(value + 0.01);
    }   
}

You then "run" the createSpreadsheetChangeTrigger() function in the App Scripts editor which will create a custom trigger in the "triggers" section.

You would then just make sure that the cell which you are referencing the background color of is a cell that is getting update by the on change event. In this case there is a formula that is calculating the count of other cells which are the same color as A1, B1, C1, etc. (all of which are different colors). As a result, if one cell in the sheet changes color, A1, B1, C1, etc. get a value update, which re-calculates the summation formula.

In the above example the function would be

=sumWhereBackgroundColorIs(A1, rangeSpecification)

Apologies for my poor formatting/explanation but I can share a spreadsheet that does this and it may explain it better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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