CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Herbert
Herbert

Reputation: 580

Why Apps Script execution failed only when it is triggered by onEdit event on Google Spreadsheet?

I am working on Google Apps Script. I have apps script that I have wrote that is supposed to be executed by onEdit event on specific sheet.

onEdit.gs

function onEdit(e) {
    var syokuninIchiran = new SyokuninIchiran();
    syokuninIchiran.syncTable();
}

When the above is executed by edit on the sheet, this error occurs:

You do not have permission to call openById. Required privilege: https://www.googleapis.com/auth/spreadsheets

But, it works with no problem when the script is triggered from Google Apps Script development window. I call the same code by making a test function as follows:

function test(){
  var syokuninIchiran = new SyokuninIchiran();
  syokuninIchiran.syncTable();
}

It might be problem for this issue? The spreadsheet's owner is not mine. My account has right to edit as the image below.

enter image description here

Try1: I tried to add the scope manually into the manifest file(appsscript.json). And I approved the authorization. But, nothing changed. It still have the same error message.

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/sqlservice",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request"
    ]
}

Upvotes: 0

Views: 1180

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

Answer:

Simple Triggers can not open files aside from the file it is bound to. For this, you need to use an installable trigger.

More Information:

As per the documentation on Simple Trigger restrictions:

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions:

They can modify the file they are bound to, but cannot access other files because that would require authorization.

So an installable trigger must be used.

Setting up the Installable Trigger:

Follow the Edit > Current project's triggers menu item, which will open a new page in the G Suite Developer Hub. Click the + Add Trigger button in the bottom right and set up the trigger settings as follows:

  • Choose which function to run: onEdit
  • Choose which deployment should run: Head
  • Select event source: From Spreadsheet
  • Select type of time based trigger: On Edit

And press save. This will increase the range of functionality of your edit triggers.

References:

Upvotes: 2

Related Questions