I’m having the problem lots are having where my Apps Script code on Gsheets doesn’t automatically update unless it registers a change in the parameters. The custom function I’m using counts the number of cells that are highlighted a specific color. Changing the color of a cell doesn’t re-run the script. I wanted to add a check box in a cell and every time I click it, it reruns the function.
function COUNTCOLOR(countRange,colorRef,recalc) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/((.*),/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/,(.*))/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
The function works, but only without the “recalc” variable, which is the variable I need to add a check box to re-run the code. I get an error: “Range not found (line 6)”. Any advice on getting this to work?
Thanks!