Google Apps Script URLFetchApp Permission

Question

I'm new to GAS and I struggle with the permission system.

I'm a normal Google drive user and I started a spreadsheet and tried to add some code to it. My code is working, but only if I'm in the code editor. I want to use the onEdit() function so it's important for me that it works within the sheet as well. When I ran my code in the editor for the first time it opened a new window where I needed to enter my credentials to allow the script, then it worked. If I do some changes to a cell in my sheet and the onEdit() function is triggered I receive an error message that says something like this(translated):

Exception: You are not permitted to call UrlFetchApp.fetch. Required permission: https://www.googleapis.com/auth/script.external_request

In the editor I displayed the manifest file and added the permission to the oauthScopes but within the sheet I still receive the message. This is how my code looks like (simplified):

function onEdit(e)
{
    var data = {
        'key1': 'value1',
        'key2': 'value2'
    };
    var options = {
        'method' : 'post',
        'contentType': 'application/json',
        'payload' : JSON.stringify(data)
    };
    try{
        var response = UrlFetchApp.fetch('https://a-working-url.com', options); //error happening in this line
        //some more data wizardry
    }catch(error)
    {
        Browser.msgBox(error)
    }

}

Any ideas how I can open this permission screen in my sheet or any hints how to solve it in a different way? I want to create a sheet with some code running in the back online. I want to share the sheet with some friends, tried it with Excel and VBA before until I realized that it's not working with Excel Online, so I switched to GAS.

Thanks in advance for your help :)

TheAddonDepot TheAddonDepot · Accepted Answer · 2019-05-05T21:42:47

onEdit(), like all simple triggers, is bound by the following restrictions (see official documentation):

The script must be bound to a Google Sheets, Slides, Docs, or Forms file, or else be an add-on that extends one of those applications.

They do not run if a file is opened in read-only (view or comment) mode.

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

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.

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

They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.

They cannot run for longer than 30 seconds.

In certain circumstances, editor add-ons run their onOpen(e) and onEdit(e) simple triggers in a no-authorization mode that presents some additional complications. For more information, see the guide to the add-on authorization lifecycle.

Simple triggers are subject to Apps Script trigger quota limits.

The ones highlighted in bold apply to your question.

Basically, it boils down to this - UrlFetchApp.fetch() is a service that requires authorization, so you won't be able to execute it from your onEdit(e) trigger, even if you have its associated scope set in your manifest file.

Google Apps Script URLFetchApp Permission

2 Answers