0
282
ViewsHow to code "re-run custom function" button in Google Sheet custom function?
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!
2 answers
Answer question0
A simplier way
function COUNTCOLOR(countRange,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 backGround = activeRg.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 color reference is the backgrund of the cell that contains the formula.
0
It works for me this way:
function COUNTCOLOR(countRange,colorRef,recalc) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var [countRangeAddress,colorRefAddress] = activeformula.match(/\((.*)\)/).pop().split(',');
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
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;
};
You can add trim() to every variable if you want.
It could be something like this:
var variables = activeformula.match(/\((.*)\)/).pop().split(',');
var countRangeAddress = variables[0].trim();
var colorRefAddress = variables[1].trim();
Or you can add replace() in the line to get rid of spaces:
var [countRangeAddress,colorRefAddress] = activeformula
.match(/\((.*)\)/).pop().replace(/\s+/g,'').split(',');