Fixing Value Putting by Custom Function of Spreadsheet using Google Apps Script

GasTips
Google Apps Script / Custom function

Gists

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.

    1. 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.

    2. Put =foo(true) to a cell “A2” in a sheet.

      • In this case, =foo(true) is converted to a value by onEdit(). So even when the Spreadsheet is automatically calculated, the value is NOT changed by recalculating.

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.

 Share!