Automatic Recalculation of Custom Function on Spreadsheet Part 2

submit.md

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:

• Custom Functions in Google Sheets
• Class TextFinder
• Automatic Recalculation of Custom Function on Spreadsheet Part 1
• Function NOW() + Setting Timezone (Google Sheets)
  • I think that this can be seen as one of sample situations.