onEdit(e) not working in Add-on

Question

I've written a script that works great when used in a native spreadsheet. I am now trying to publish it as a Add-on, and am finding that onEdit(e) is not working, when onOpen(e) and onInstall(e) work fine.

I've looked over the documentation on Authorization modes and installing/enabling an Add-on, but I think I am probably missing something (hopefully straightforward) since I am a beginner. Should I be calling the functions differently? Or the placement of the onEdit? Any help is appreciated. Thanks!!

function setup() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Send Auto Emails');

  try {ss.setActiveSheet(ss.getSheetByName('Send Auto Emails'));}
  catch (e) {ss.insertSheet('Send Auto Emails', 0);}

  sheet.getRange(1, 1).setValue('Recipient Email Address');   

  //etc...
}

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = e.source.getActiveSheet();
  var range = e.source.getActiveRange();

  if (range.getA1Notation() == "C1" | range.getA1Notation() == "D1" |     range.getA1Notation() == "E1" && sheet.getName() == "Send Auto Emails") {
    Browser.msgBox(
      'Alert',
      'Feel free to change the title here to something more relevant to you.  But be aware, if you would like to use an optional item, please make sure you are referencing it in your email message exactly as it appears here, wrapped in < and >. Example: <Optional Item 1>.',
      Browser.Buttons.OK
    );
  }
  if (range.getA1Notation() == "J4" && sheet.getName() == "Send Auto     Emails") {
    Browser.msgBox(
      'Alert',
      'Only add the email message body.  "Hello, Recipient Name" and "Best, Your Name" will be automatically added.  If you would like to use Optional Items in this message, see the example text to make sure you are using them the right way.',
      Browser.Buttons.OK
    );
  }

  if (range.getA1Notation() == "A2") {
    ss.toast("Your data in column A must not be separated by any blank rows. Any data after a blank row will be ignored.", "Be aware", 90);
  }
}

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('BulkEmail beta')
      .addItem('1. Sheet Setup', 'setup')
      .addItem('2. Send Emails', 'sendEmails')
      .addToUi();
  onEdit(e);
}

function onInstall(e) {
  onOpen(e);
}

EDIT 1

I've tried creating an installable trigger instead of using the simple onEdit, still to no avail.

function createonEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('installableonEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

function installableonEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = e.source.getActiveSheet();
  var range = e.source.getActiveRange();

  try {
    if (range.getA1Notation() == "C1" | range.getA1Notation() == "D1" | range.getA1Notation() == "E1" && sheet.getName() == "Send Auto Emails") {
      Browser.msgBox(
        'Alert',
        'Feel free to change the title here to something more relevant to you.  But be aware, if you would like to use an optional item, please make sure you are referencing it in your email message exactly as it appears here, wrapped in < and >. Example: <Optional Item 1>.',
        Browser.Buttons.OK
      );
    }
    if (range.getA1Notation() == "J4" && sheet.getName() == "Send Auto Emails") {
      Browser.msgBox(
        'Alert',
        'Only add the email message body.  "Hello, Recipient Name" and "Best, Your Name" will be automatically added.  If you would like to use Optional Items in this message, see the example text to make sure you are using them the right way.',
        Browser.Buttons.OK
      );
    }

    if (range.getA1Notation() == "A2") {
      ss.toast("Your data in column A must not be separated by any blank rows. Any data after a blank row will be ignored.", "Be aware", 90);
    }
  } catch(err) {
    var errMsg = 'There was an error: ' + err +
        + " \n \n" +
        'from the: onEdit function ' +
        + " \n \n" +
        'The call stack is: ' + err.stack;

    GmailApp.sendEmail('[email protected]', "error", errMsg);
  }
}

Haggi

It's possible that it is called but fails on some error. Use the Logger service to record errors at various points in the function. Plus check if its failing because of the restrictions mentioned here: developers.google.com/apps-script/guides/triggers – Denier

@SujayPhadke Logger cannot record errors. Stackdriver's console will be a much more dev-friendly tool (and uncaught errors are by default logged to Stackdriver). Most (all?) triggered functions will silently fail with an error (and log it to Stackdriver) - only user-explicitly-executed functions will display an error message back to the user. – Mundford

I can't get the simple onEdit() trigger to run at all in a Sheets add-on when testing, even with a single line of code: Logger.log("onEdit"); I've tried all AuthModes, assigned the project to a standard Cloud Platform Project ID, configured the OAuth Screen, all to no avail. If I Run Function "onEdit()" it Logs fine. I also tried adding an installed trigger, but only have a "Calendar" option under "Event Source" which requires the owners email address, even though Sheets and Calendar are both configured under Advanced Services and in appscript.json under 'enabledAdvancedServices'. Any ideas? – Giselle

According to developers.google.com/gsuite/add-ons/how-tos/…, "Installable triggers aren't supported when testing. Functionality that depends on installable triggers are not testable". It seems simple triggers should work when testing though, as long as I don't use any code requiring authorisation... however Logger won't log, and developers.google.com/apps-script/guides/triggers/#onedite isn't working. – Giselle

Answer 1

Your onOpen() trigger is running the onEdit() trigger. opOpen() runs in in AuthMode.LIMITED when the Add-on is installed and enabled.

In this documentation, it states:

A mode (LIMITED) that allows access to a limited subset of services. This (LIMITED) mode occurs when an add-on or a script bound to a document executes an onOpen(e) or onEdit(e) simple trigger, except in the case described for NONE.

You are running an onOpen() simple trigger, and it is running in LIMITED mode because it's in an add-on.

So, that part, I'm quite sure of.

I believe what you can do, is create an installable edit trigger, and that runs in FULL mode. So, that's what I would try, get rid of the simple trigger, and install a trigger with ScriptApp.

In the documentation, it states:

They (a simple trigger) cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

Google Documentation

So, that try/catch included sending an email, which would have stopped the onEdit() simple trigger from working.

Add a try/catch to your code, and send yourself an email if there is an error.

function onEdit(e) {try{
  //Code Here

} catch(err) {
  var errMsg = 'There was an error: ' + err +
      + " \n \n" +
      'from the: onEdit function ' +
      + " \n \n" +
      'The call stack is: ' + err.stack;

  GmailApp.sendEmail('[email protected]', "Subject", errMsg);
};

Contemporaneous

Just tried this. I didn't receive emails at all when testing as an add-on and the function worked as expected again when testing as a native script. So the function's not running at all as an Add-on? – Haggi

I also tried to purposely make it catch by adding code to try that would fail, but still nothing. – Haggi

I tried both removing the onEdit from onOpen as well as creating an installable trigger as below. Neither worked to send the error email or run correctly in the sheet or add-on. I'm beginning to wonder if there is some kind of bug? It definitely feels like a permissions issue. – Haggi

@Alan Wells Thanks for sharing. I get the same error in test mode as an add-on. Your code in catch sends the following message to email: "There was an error: Exception: The Add-on attempted an action that is not permitted in Test as Add-on mode. To use this action, you must deploy the Add-on.0from the: onEdit function 0The call stack is: at setTriggers: 49 (createClockTriggerForSpreadsheet) at setTriggers: 14 (createTriggers) ". As you can see, \n does not break the line, something else is needed here. – Searchlight

I use bound project files to do testing and development. I stopped using "Test as add-on" quite a while ago. I have a custom Web App that I developed that copies my development file over to a different stand-alone Apps Script file, and that stand alone file is what I use for production. Actually, I'm working on trying to get that app published to the Workspace Marketplace. – Contemporaneous

Answer 2

One possible reason is that you "Test as add-on"

https://developers.google.com/gsuite/add-ons/how-tos/testing-editor-addons#testing_details

Installable triggers aren't supported when testing. Functionality that depends on installable triggers are not testable.

But the funny thing is, it works with a bound script.

It made me confusing for quite a while. So the solution is when you need to test the installable triggers, just stick with a bound document.

If it works, the trigger for the add-on will also work.

For other things, you can do "Test as add-on"

Stoke