3

An event driven script in a spreadsheet opens another spreadsheet

var ss = SpreadsheetApp.openById(otherSpreadsheetId);

Looking at the execution transcript, I get the following error

SpreadsheetApp.openById([0AjqSnE_p3nFqdDN0LWpFbjFqVDRwNmFGOV91QzZrZc]) [0 seconds] Execution failed: You do not have permission to perform that action.

When I run the function directly in the debugger, I successfully open the other spreadsheet. When the function is run by an "onEdit" event, then I get the error.

Do I need to enable a particular API?

Rubén
  • 34,714
  • 9
  • 70
  • 166
kusi
  • 177
  • 1
  • 7
  • 2
    Read about "onEdit" triggers and AuthMode: https://developers.google.com/apps-script/guides/triggers/events Have a look at installable triggers. https://developers.google.com/apps-script/guides/triggers/installable Sorry, it is a lot of reading, it is complex. – eddyparkinson Jan 03 '15 at 01:03
  • 1
    Indeed, a simple trigger (which I used so far) cannot alter another file, according to the docs. I've setup an installable trigger which successfully opens the other spreadsheet. Thanks for the hint! – kusi Jan 03 '15 at 15:29

2 Answers2

5

Installable Triggers

There are two types of OnEdit trigger. Installable triggers are able to 'alter another file', see: https://developers.google.com/apps-script/guides/triggers/installable

@kusi supplied the answer as a comment, just adding it here.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
  • Links and all, sorry, but I really don't find this useful at all. This one should be the answer: https://stackoverflow.com/a/49063346/5871613 – duduwe Aug 12 '20 at 22:22
3

You can set up an installable trigger programmatically in the current sheet and this way the openById can be called.

function onOpen() {
  ScriptApp.newTrigger('myOnEdit')
   .onEdit()
   .create();
}

function myOnEdit(e){
   SpreadsheetApp.openById('id_of_other_sheet');
}
gazdagergo
  • 6,187
  • 1
  • 31
  • 45
  • 1
    This is the direct answer (manual trigger version). It's much easier to understand the document now. Lol. – duduwe Aug 13 '20 at 07:59