Make custom function refresh

Question

So I have this one code that takes a range and a reference cell, and returns the number of cells with the same background color as the ref. This works great, but I want it to be usable when the color of some of the cells changes. I put it on an every minute time based trigger, and I tried to use SpreadsheetApp.flush() to uncache the current number. This did not have the desired affect.

I also tried to make a second function that used the flush() and then returned the first function. This also did not work. The only way I know to make it refresh is to take the "=" from the beginning of the cell with the function in it, and then replace it. The code that works is below.

function countBGColor(range, ref) {

    var sheet = SpreadsheetApp.getActiveSheet();
    var color = sheet.getRange(ref).getBackground();
    var range = sheet.getRange(range);
    var rangeVal = range.getValues();
    var count = 0;
    var allColors = range.getBackgrounds();
    for (var i = 0; i < allColors.length; i++) {
        for (var j = 0; j < allColors[0].length; j++) {
            if (allColors[i][j] == color) count += 1;
        };
    };
    return count;
}

Max Makhrov Max Makhrov · Accepted Answer · 2016-02-18T12:36:06

I couldn't find direct way to do this. But you could make script that clears formula and then reenters it into cell:

function Refresh() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('D1');
  range.clear();
  SpreadsheetApp.flush();
 range.setFormula('=countBGColor("A1:A10","E1")');
}

Next link your image to a script's function: Insert > image > in top right menu of image, Assign Script

Sample file:

Make custom function refresh

1 Answers