Installable onEdit trigger in google spreadsheet script when copying a template spreadsheet script

Question

I have a script(A) that copies a template spreadsheet. This template spreadsheet has a script(B) in it. The template spreadsheet and script are successfully programmatically copied and shared with others. The template script needs to use an onEdit() trigger to modify a third spreadsheet (Edits in the copy of the template spreadsheet are going to be synced to the third spreadsheet). A simple trigger does not have permission to access the third spreadsheet. I have tried using installable triggers, but have had no success in getting an installable onEdit trigger to work in the copied spreadsheet.

What I want to achieve is to have an onEdit trigger in the copied spreadsheet that does not require separate authorization to write to a third spreadsheet.

All spreadsheets are owned by a single user.

I can create an installable trigger in script B using the following code in script A.

createSpreadsheetEditTrigger(id_of_shared_spreadsheet)
...
...
function createSpreadsheetEditTrigger(idss) {
var ss = SpreadsheetApp.openById(idss);
ScriptApp.newTrigger('myOnEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

The 'myOnEdit' function is in script B and has in it the code that syncs to the third spreadsheet. This is not triggered when the copied spreadsheet is edited.

If I create the installable trigger in script B by calling it from a simple onOpen() function it fails because of permissions.

Following the advice here: Execution failed: You do not have permission to call getProjectTriggers I can create the installable trigger and a menu item to call it, plus a msgbox that prompts the user to click on install and authorize the trigger.

Is it possible to programmatically install an onEdit trigger when copying spreadsheets that has permissions to edit a third spreadsheet without the user having to manually authorize the trigger?

Many thanks in advance

Trevor Storr

Tyler Newland Tyler Newland · Accepted Answer · 2015-06-25T14:50:29

I just went through this same sort of deal, I am pretty confident there is not an "easy" way to make this happen without Authorization.

Although you can assign your "createSpreadsheetEditTrigger" script to a button that just installs the trigger and authorizes all at the same time, 3 clicks and 5 seconds, then it will work just as you intended.

Installable onEdit trigger in google spreadsheet script when copying a template spreadsheet script

1 Answers