Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

onEdit(e) not working in Add-on

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);
  }
}
like image 808
ebreckley Avatar asked Jan 15 '16 22:01

ebreckley


2 Answers

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);
};
like image 88
Alan Wells Avatar answered Sep 23 '22 22:09

Alan Wells


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"

like image 31
Hoang Trinh Avatar answered Sep 23 '22 22:09

Hoang Trinh