Google Sheets Function to Actively Check Cell

Question

I've a function I'm using that checks the cell of the color and returns Yes/No in another column. However, after applying the function, and then changing the color of the cell afterwards the function isn't applied anymore.

For example the function is applied and returns "Yes", then if I change the color of the cell to white it doesn't return to "No". Is it possible to have the function to actively check the cell, or I just need to reapply the function each time the color of the cell changes?

function GetCellColorCode(input) {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var cell = ss.getRange(input); 
  var result = cell.getBackground(); 

  var green = '#d9ead3';
  var white = '#ffffff';

  if (result == green) {
    return 'Yes';
  } else {    
    return 'No';
  }  

}

Answer 1

• You want to refresh the custom formulas of =GetCellColorCode(###) in the cells when the background color is changed.

In order to achieve your goal, I would like to propose to refresh the formulas using TextFinder and the OnChange event trigger.

Usage:

1. Sample script.

Please copy and paste the following script.

function onChange(e) {
  if (e.changeType === "FORMAT") {
    const formula = "=GetCellColorCode";  // Function name of your custom function.

    const sheet = e.source.getActiveSheet();
    const tempFormula = "=sampleFormula";
    sheet.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
    sheet.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
  }
}

• In this sample script, =GetCellColorCode is replaced to other name using TextFinder, and then, it is modified to =GetCellColorCode. By this, the custom formula are refreshed.
• When you directly run the function of onChange at the script editor, an error occurs, because this function uses the event object of OnChange event trigger. Please be careful this.

2. Install OnChange event trigger.

Please install the OnChange event trigger to the function of onChange.

1. From the script editor, choose Edit > Current project's triggers.
2. Click Add Trigger.
3. Under Choose which function to run, select the name of function you want to trigger.
   • In this case, please select onChange.
4. Under Select event source, select From spreadsheet.
5. Under Select event type, select On change.
6. Click Save.

3. Test run.

After above flow was done, please change the background color of the cell. By this, the OnChange event trigger is fired and the function onChange is run. Then, the custom formula of GetCellColorCode is refreshed.

In this script, even when the cell is copied and pasted, the OnChange vente trigger is not run. Please be careful this. In this case, please modify the background color.

Note:

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

References:

• Installable Triggers
• Event Objects
• Class TextFinder