google script openById : You do not have permission to perform that action

3
votes

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?

2
google-apps-scriptgoogle-sheetsgoogle-drive-apitriggers
Read about "onEdit" triggers and AuthMode: developers.google.com/apps-script/guides/triggers/events Have a look at installable triggers. developers.google.com/apps-script/guides/triggers/installable Sorry, it is a lot of reading, it is complex. – eddyparkinson
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

2 Answers

4
votes

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.

3
votes

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