google-apps-scriptgoogle-apps-script-addon

Install trigger for google app script in custom addon


I am still very new to addons and I am having trouble to install triggers and have the related functions to run.

Below is the function to add 1 "on open" trigger and 1 "on edit" trigger to the sheet.

function addTriggers() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var triggers = ScriptApp.getUserTriggers(sheet);

  if(triggers.length!=2)//
  {
    ScriptApp.newTrigger('sheetOpen')
    .forSpreadsheet(sheet)
    .onEdit()
    .create();

    ScriptApp.newTrigger('sheetEdited')
    .forSpreadsheet(sheet)
    .onOpen()
    .create();     
  }

Then I tried to install this function through onInstall();

function onInstall(e){
  addSpreadsheetEditTrigger(); 
  sheetOpen();
}

function sheetOpen()
{
//do something after the sheet is open;
}

function sheetEdited()
{
//do something when the sheet is edited by user;
}

When I tested this addon, the triggers were not installed and thus none happened. Also please note that I need to use installable triggers because I need to access external files.

Could someone let me know where I did wrong?


Solution

  • 1. How to build a trigger manually

    If you want to build a trigger for a spreadsheet, you need to specify as parameter in forSpreadsheet() the spreadsheet, not the sheet!

    So:

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    ...
    
        ScriptApp.newTrigger('sheetOpen')
        .forSpreadsheet(spreadsheet)
        .onOpen()
        .create();
    ...
    
    1. It seems that you got the assignment of the functions 'sheetOpen' and 'sheetEdited' the wrong way around

    2. You should doublecheck either you really need to build the trigger manually. Instead you can call the already existing onOpen(e) trigger (unless you need an installable one).

    Sample:

    function onInstall(e){
      sheetOpen();
    }
    function onOpen(e){
      sheetOpen();
    }
    function sheetOpen()
    {
    //do something after the sheet is open;
    }
    

    UPDATE

    Now, the limitations of Addons won't allow you to install triggers directly. Instead, you can create a custom menu giving the user the option to install the triggers when choosing the respective option.

    Sample:

    function onInstall(e) {
      onOpen(e); 
    }
    function onOpen(e) {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('MyAddOn Menu')
      .addItem('Please click here to get started', 'addTriggers')
      .addToUi();
    }
    
    function addTriggers() {
      ...
    }