Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created June 2, 2019 08:47
Show Gist options
  • Save tanaikech/6ebf4bafbdc35116470bd197d00fe614 to your computer and use it in GitHub Desktop.
Save tanaikech/6ebf4bafbdc35116470bd197d00fe614 to your computer and use it in GitHub Desktop.
Fixing Value Putting by Custom Function of Spreadsheet using Google Apps Script

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

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment