Google Sheets: Custom function with dynamic change

Question

I am using a custom function that keeps track of what color a cell is. But there is a problem in that this function does not update itself if the cell color changes.

Cell color:

function GetCellColorCode(input) 
{ 
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var cell = ss.getRange(input); 
var result = cell.getBackground(); 
return result 
}

Next, I found and add a script that recalculates all the formulas in the table so that they update themselves. I added trigger so that this formula runs every hour.

function refresh() {SpreadsheetApp.flush()}

But my table has 17 pages and thousands of formulas. As a result, everything is updated much slower than I would like.

My question is: can I add something to a custom formula so that it works like a regular google sheets formula and changes immediately if changes occur in the cell?

Answer 1

I believe your goal as follows.

• You want to refresh the custom function when the background color of cell is changed.

Modification points:

• When the background color of cell is changed, this can be detected by OnChange trigger. I thought that this might be able to be used for achieving your goal.

• In order to achieve your goal, I would like to propose the following flow.

  1. At first, as a sample, the custom formula of =GetCellColorCode("A1") is put in a cell "B1".
  2. When the background color of cell "A1" is changed, a function is run by OnChange trigger.
  3. Function running by OnChange trigger refreshs the custom function of GetCellColorCode.
     • In this case, TextFinder is used.

By this flow, the custom function of GetCellColorCode can be refreshed.

Usage:

In order to use this method, please do the following flow.

1. Prepare script.

Please copy and paste the following script to the script editor of Spreadsheet and save it.

// I added this script.
function onChange(e) {
  if (e.changeType == "FORMAT") {
    var formula = "=GetCellColorCode";
    var tempFormula = "=sample";
    var sheet = e.source.getActiveSheet();
    sheet.createTextFinder(`^\\${formula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
    sheet.createTextFinder(`^\\${tempFormula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
  }
}

// This is your script.
function GetCellColorCode(input) { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var cell = ss.getRange(input); 
  var result = cell.getBackground(); 
  return result 
}

• In this sample, the cusrom formula of GetCellColorCode in the active sheet that the background cell is changed is refreshed.

2. Install OnChange trigger to onChange function.

Please install OnChange trigger to the function of onChange. Ref

3. Set custom formula.

Please put the custom formula of =GetCellColorCode("A1") in a cell "B1". In this case, when the cell "A1" has the default background color, #ffffff is shown in the cell "B1" from the custom function.

4. Testing script.

Please change the background color of "A1". By this, the function of onChange is run by OnChange trigger. And, the custom function is refreshed, and then, the value of cell "B1" is changed.

As the demonstration, when above flow is used, the following result is obtained.

Note:

• In this sample, it supposes that the function name of your custom function is GetCellColorCode. So when you modified it, please also modify above sample script. Please be careful this.

References:

• Installable Triggers
• Class TextFinder