In Google Apps Script, can't programmatically create trigger to send email from onEdit trigger

Question

I'm trying to make an onEdit trigger to send an email. I know that it doesn't work by default, as described here:

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions: They 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.

But I thought I could make onEdit event to programmatically create another, time-driven trigger, which in turn will send the email. I know that both (1) my onEdit trigger works on its' own, and (2) manually running the function to programmatically create time-driven trigger to send email, work on its' own. But when I put the 2 inside 1, it doesn't work.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssName = ss.getName();
var budgetLeft = ss.getRange("Test!B1").getValue();
var emailAddress = ss.getRange("Test!B4").getValue();

if (budgetLeft <= 4860) {
  function SendEmailOnTrigger() {
    MailApp.sendEmail({
      to: emailAddress,
      subject: "Warning: " + ssName,
      htmlBody: "Sample Message",

   });
 }
}

function onEdit(e){
      function createTimeDrivenTriggersTest() {
      // Trigger TEST.
      ScriptApp.newTrigger('SendEmailOnTrigger')
      .timeBased()
      .everyMinutes(1)
      .create();
}
}

Does it "knows" that the time-driven trigger that would be created will send email, which is not acceptable when initiated with simple trigger? That's why it doesn't work? Thanks in advance for any help.

Creating a trigger with code also requires authorization. You still have exactly the same issue. If you are trying to find a way to get around a security feature, hopefully there is no way to do it. Web security is already in a bad enough state, you want to find a way to make it worse? — Alan Wells
I did not intend to use this for any malicious actions. I'm trying to have a template spreadsheet with scripts and time-driven triggers to send email, which I can duplicate multiple times. When I make duplicates of file, the time-driven triggers don't copy over if they are manually created. If they are programmatically created, I need to manually run the function which creates them. I was looking for a way to set it up so I can just duplicate the file, authorize the bound script to send emails from my address, and it would just work from there. — Michael Ovdiienko
If this is for your own use, in your account, then just manually create an installable 'On Edit' trigger. If you set the trigger up from the code editor, under the Resources menu, Current project's triggers, the On Edit trigger will allow emails being sent. Sorry for any previous negative implications. Just want people to understand why the restrictions are the way they are. — Alan Wells
My higher-level problem is that triggers created manually in Resources>Current project's triggers don't carry over when you make duplicate of the file. So I thought I could somehow create the trigger in code, and it will work immediately after copying file. But I guess it's not possible, and my best option is to do this manually. Thank you for your response, no worries about negative implications. — Michael Ovdiienko
Do you ever actually call these functions in your script? I must be missing something, because I don't think any of your defined functions would ever run in the above code. Maybe try 1. Delete the function definition for createTimeDrivenTriggersTest and simply create the trigger within onEdit. 2. Move your if (budgetLeft <= 4860) statement inside your SendEmailOnTrigger function declaration. — Eric Dauenhauer

Unknown Unknown · Accepted Answer · 2016-04-24T18:52:49

As Sandy Good explained, installable triggers inherit authorization from the process that created them. If it was created by you running an onEdit function manually, than onEdit acts as you and can create a trigger that acts as you. But if a function is executed by a simple trigger, it is only authorized to modify the spreadsheet to which it is bound, and do nothing else.

To see why this is needed, imagine that you when you opened someone's shared spreadsheet with a bound script, an onOpen function in that script installed a trigger that periodically forwards your email somewhere else.

So, just use an installable on-edit trigger instead of a simple one.

In Google Apps Script, can't programmatically create trigger to send email from onEdit trigger

2 Answers