IssueTracker

A short description of the request:

I would like to propose to add new type in changeType of the event object of OnChange event trigger, when the function of event trigger is executed by the OnChange event trigger.

What is the purpose of this new feature?

Flow for replicating the issue.

1. At first, it prepares the following sample script. In this case, this script is put to the container-bound script of Spreadsheet.

    ```javascript
    function sample() {
      return new Date();
    }
   
    function onChange(e) {
      const formula = "=sample";
      const ss = e.source;
      const tempFormula = "=sampleFormula";
      ss.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
      ss.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
    }
    ```
   

2. Install the function of "onChange" as the installable OnChange event trigger.

3. When =sample() is put to a cell on Spreadsheet, when =sample() is replaced with the script of onChange, the OnChange event trigger is fired. By this, the infinite loop of the event trigger occurs.

   • The event object of OnChange event trigger has the property of changeType. In the current stage, there are EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER. In this case, changeType is OTHER. But, OTHER includes several changes. For example, it's that the sheet name is changed.

Proposal

In this proposal, when onChange is executed by the OnChange event trigger, when the event trigger is executed by the script, if changeType returns like SCRIPT, the infinite loop can be easily avoided.

In the current workaround, I used the following sample script.

function onChange(e) {
  const prop = PropertiesService.getScriptProperties();
  if (!prop.getProperty("run")) {
    const formula = "=sample";
    const ss = e.source;
    const tempFormula = "=sampleFormula";
    ss.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
    ss.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
    prop.setProperty("run", "done");
  } else {
    prop.deleteProperty("run");
  }
}

Reference

• Event Objects