google-apps-script - onEdit(e) 在附加组件中不起作用

Question

我编写了一个在 native 电子表格中使用时效果很好的脚本。我现在正尝试将其作为附加组件发布，并且发现 onEdit(e)不工作，当 onOpen(e)和 onInstall(e)工作正常。

我已经查看了有关授权模式和安装/启用附加组件的文档，但我认为我可能遗漏了一些东西(希望很简单)，因为我是初学者。我应该以不同的方式调用函数吗？或者放置onEdit ?任何帮助表示赞赏。谢谢!!

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);
}

编辑 1

我尝试创建一个可安装的触发器而不是使用简单的 onEdit ，还是无济于事。

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('elisabeth@groupon.com', "error", errMsg);
  }
}

Answer 1

您的 onOpen()触发器正在运行 onEdit()扳机。 opOpen()安装并启用附加组件后，在 AuthMode.LIMITED 中运行。

在 this documentation ， 它指出:

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.

您正在运行 onOpen()简单的触发器，它在 LIMITED 模式下运行，因为它是一个附加组件。

所以，那部分，我很确定。

我相信您可以做的是创建一个可安装的编辑触发器，并在 FULL 模式下运行。所以，这就是我要尝试的，摆脱简单的触发器，并使用 ScriptApp 安装触发器。 .

在文档中，它指出:

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

所以，那个 尝试/捕捉 包括发送电子邮件，这会阻止 onEdit()工作的简单触发。

添加 尝试/捕捉 到您的代码，并在出现错误时给自己发送电子邮件。

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('yourEmail@gmail.com', "Subject", errMsg);
};