I have a google app script which is published as a Google Sheets add on. One of the features of the add on is that it take a value of a cell, queries a Google Big Query table, and populates the next cell with a drop down of options.  Once the user selects an option another query is sent to Big Query and so on.

Because this activity happens when a a user edits a cell I think I need to use onEdit() type functionality. However the documentation makes clear that I can’t use onEdit() to make such requests that require authorisation (I use a service account) so I need to use a trigger, and because it’s for an add on I need to use an Installable Trigger.

I do this programmatically as per the docs 

/**
 * Creates a trigger for when a spreadsheet opens.
 */
function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('stu')
      .forSpreadsheet(ss)
      .onEdit()  //the docs actually is onOpen()
      .create();
}

In turn, function stu gets the values from the cell and calls another function which queries Big Query:

function stu(e) {
  var activeCell = e.range;
  var cellValue = e.value;
....
....
if (wsName === "Targets" && c < 7 && r > 1) {
    var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Targets");
    var rowValues = ws.getRange(r, 1, 1, 6).getValues();
    runQuery(cellValue, r, nextCol, rowValues);  // runQuery executes the BigQuery query
  }
}

There are a couple of problems that I have come across:

1. It seems that it is not possible to get the installable trigger to install when a user installs the add on.  To get around this I have followed advice at https://stackoverflow.com/questions/61314827/install-trigger-for-google-app-script-in-custom-addon  and added a menu item to allow the user to install the trigger.

2. I have started getting errors such as Exception: This add-on has created too many edit triggers in this document for this Google user account.  

Can anyone advise if I am correct in creating an Installable trigger onEdit

ScriptApp.newTrigger('stu')
          .forSpreadsheet(ss)
          .onEdit()
          .create();

Perhaps instead I should be using

ScriptApp.newTrigger('stu')
          .forSpreadsheet(ss)
          .onOpen()
          .create();

and then in function stu wrap the functionality in an onEdit():

function stu(e) {
  onEdit(e) {
    var activeCell = e.range;
    var cellValue = e.value;
   ....
   ....
   if (wsName === "Targets" && c < 7 && r > 1) {
     var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Targets");
     var rowValues = ws.getRange(r, 1, 1, 6).getValues();
     runQuery(cellValue, r, nextCol, rowValues);  // runQuery executes the BigQuery 
   query
  }
 }
}

If I am correct in using an Installable Trigger should I be deleting previous instances in order to avoid the Exception: This add-on has created too many edit triggers in this document for this Google user account.  error? I can see that there is a way to check for the existence of triggers and delete as appropriate (also on https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually).  

I suspect that actually I am fundamentally understanding how I should be using installable triggers. I've read through the docs, but any advice much appreciated. 

CodePudding user response：

This will make just one:

function creatonedittrigger(funcname) {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == funcname).length == 0) {
    ScriptApp.newTrigger(funcname).forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}