How to code "re-run custom function" button in Google Sheet custom function?

Question

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!

Answer 1

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(',');

Answer 2

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.