This is a sample script for fixing a value putting by a custom function of Spreadsheet using Google Apps Script. When a custom function is used, the value retrieved by the custom function of Spreadsheet is automatically updated by recalculating. So in the case that the value retrieved by the custom function is changed by the time, the value is also changed by automatically updating. In this sample script, I would like to introduce a method for fixing such values.
Flow:
When you use this script, please do the following flow.
-
Copy and paste the script to the bound-script of Spreadsheet and save it.
-
Put
=foo()to a cell “A1” in a sheet.- In this case,
=foo()is put as a custom function. So when the Spreadsheet is automatically calculated, the value is changed by recalculating.
- In this case,
-
Put
=foo(true)to a cell “A2” in a sheet.- In this case,
=foo(true)is converted to a value byonEdit(). So even when the Spreadsheet is automatically calculated, the value is NOT changed by recalculating.
- In this case,
-
Sample script:
function onEdit(e) {
if (e.range.getFormula().toUpperCase() == "=FOO(TRUE)") {
e.range.setValue(new Date());
}
}
function foo() {
return new Date();
}
Reference:
I think that this method will be able to be applied to various scenes.