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.
- At first, as a sample, the custom formula of
=GetCellColorCode("A1")
is put in a cell "B1".
- When the background color of cell "A1" is changed, a function is run by OnChange trigger.
- 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: