Google App Script - Set trigger when data is added to a specific column via a function

Question

I have a table in which column I gets updated programmatically via an Array Formula. And when that happens, i.e a new row of data is added in column I, I want to run the fillTemplateAndDuplicate function.

I've tried many times but even if Logger.log('filltemplatetrigger done') runs correctly, the fillTemplateAndDuplicate function just doesn't get triggered when new data is entered in I.

When I run the fillTemplateAndDuplicate manually it works.

How can I fix this?

function fillTemplateTrigger() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Main");
  var range = sheet.getRange("I:I");
  ScriptApp.newTrigger("makePNG")
    .forSpreadsheet(ss)
    .onChange() // use onChange() instead of onEdit()
    .create();
  Logger.log('filltemplatetrigger done');
}

function makePNG(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var column = range.getColumn();
  if (column == 9) {
    var row = range.getRow();
    if (row > 1) {
      // Call your function here
      fillTemplateAndDuplicate();
    }
  }
}

I tried onChange, onEdit.

Answer 1

https://developers.google.com/apps-script/guides/triggers#restrictions

• Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

If you're doing anything to modify the values or structure of a sheet programmatically, triggers will not fire unfortunately.