Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active July 26, 2023 16:04
Show Gist options
  • Save tanaikech/cb5447616bfc6a01de8a49131c0d2db0 to your computer and use it in GitHub Desktop.
Save tanaikech/cb5447616bfc6a01de8a49131c0d2db0 to your computer and use it in GitHub Desktop.
Automatic Recalculation of Custom Function on Spreadsheet Part 2

Automatic Recalculation of Custom Function on Spreadsheet Part 2

Description

I have already reported about "Automatic Recalculation of Custom Function on Spreadsheet Part 1" at here. Here, I would like to introduce other workaround for forcibly recalculating the custom functions and built-in functions using Class TextFinder. Class TextFinder has added at April 5, 2019. By this, this workaround can be proposed.

Sample scripts

Pattern 1

If you want to refresh all functions of all sheets in a Spreadsheet, you can use the following script. In this script, when the script is run, all built-in functions and custom functions in the Spreadsheet are refreshed.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var temp = Utilities.getUuid();
  ss.createTextFinder("=")
    .matchFormulaText(true)
    .replaceAllWith(temp);
  ss.createTextFinder(temp)
    .matchFormulaText(true)
    .replaceAllWith("=");
}

If you want to run the script when the Spreadsheet is opened, you can use the following script. onOpen is fired as the simple trigger when the Spreadsheet is opened. Class TextFinder can be used with the simple trigger. I think that this is very useful.

function onOpen(e) {
  var temp = Utilities.getUuid();
  e.source
    .createTextFinder("=")
    .matchFormulaText(true)
    .replaceAllWith(temp);
  e.source
    .createTextFinder(temp)
    .matchFormulaText(true)
    .replaceAllWith("=");
}

Pattern 2

When you want to refresh only the specific functions, you can use the following script.

function myFunction() {
  var customFunctions = ["CUSTOM1", "CUSTOM2"]; // Please set the function names here.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var temp = Utilities.getUuid();
  customFunctions.forEach(function(e) {
    ss.createTextFinder("=" + e)
      .matchFormulaText(true)
      .replaceAllWith(temp);
    ss.createTextFinder(temp)
      .matchFormulaText(true)
      .replaceAllWith("=" + e);
  });
}

Note:

  • TextFinder can be also used for searching for the specific range and sheet. So when you modify to like this, please modify above script. At that time, the official document will be useful.

References:

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