I am encountering the infamous Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request
in a production context after installing my Google Sheets Workspace add-on in a separate test/personal Google sheets environment.
Specifically my application is encountering this issue after I finish the end to end installation process of the add-on or upon opening the Sheet with the Add-on, I see an exception in the logs with the Required permissions
error.
So here's my question: how can I debug this issue? For example, is it an issue where I have a server side script that is not a CUSTOM_FUNCTION
(which is its own authMode
enum) and is attempting to make an outbound request? (in this case, the outbound request is to my API server to retrieve and save an API token
)
Or is there some other bug I am hitting on Google Sheet/App script's side where I need to create a workaround for what I am trying to achieve here?
Here is the event
logged upon onOpen(e)
:
{"range": {"columnEnd":1,"columnStart":1,
"rowEnd":1,"rowStart":1},
"authMode":"LIMITED",
"source":{},
"user":{"email":"","nickname":""}}
(also, Im not sure why both the email and nickname properties are empty, especially since this is a LIMITED
authMode, and not a NONE
authMode)
Here is effectively what my code is structured like in the code.gs
file where this exception issue is encountered:
function onInstall(e) {
Logger.log(`Install event: ${JSON.stringify(e)}`);
onOpen(e);
}
function onOpen(e){
// add menu item, regardless of auth mode
addMenu();
if(e && e.authMode == ScriptApp.AuthMode.NONE){
Logger.log(`Auth mode: ${e.authMode}`);
// prompt user to authorize
ScriptApp.getAuthorizationInfo(AuthMode).getAuthorizationUrl()
} else {
// any other authMode *other* than `NONE`
tempKey = Session.getTemporaryActiveUserKey();
var user = Session.getActiveUser();
var email = user.getEmail();
var username = user.getUsername();
const currentTime = new Date().getTime();
//... abridging other code, but basically we package all this up and...
// make an outbound request to the API server to get a token
try{
// store results of running a function, which itself is a wrapper around a UrlFetchApp request that sets up the payload and headers
var myApp = createMyApplication();
// setup a json to store in document properties
var userOrg = {organization: myApp.data.organization, user: myApp.data.user};
// save response token in document properties.
documentProperties.setProperty('APP_AUTH_TOKEN', token);
documentProperties.setProperty('APP_ORG_DETAILS', JSON.stringify(userOrg) );
// assign the token variable to the new property of 'APP_AUTH_TOKEN'
token = orgAuthDetails().token;
}catch(err){
return "Error configuring application user";
}
}
}
}
And here is my appscript.json manifest file:
{
"addOns": {
"common": {
"name": "My sheets app",
"logoUrl": "https://lh3.googleusercontent.com/__PIC_ID__",
"layoutProperties": {
"primaryColor": "#B8136C"
}
},
"sheets": {
"homepageTrigger": {
"runFunction": "onOpen"
}
}
},
"timeZone": "America/New_York",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "Sheets",
"serviceId": "sheets",
"version": "v4"
}
]
},
"exceptionLogging": "STACKDRIVER",
"oauthScopes": [
"https://www.googleapis.com/auth/script.container.ui",
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/userinfo.profile",
"openid",
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/script.external_request"
],
"runtimeVersion": "V8",
"urlFetchWhitelist": [
"https://www.mysamplesite.net/"
],
"webapp": {
"executeAs": "USER_ACCESSING",
"access": "ANYONE"
}
}
Okay answering this question, and it's frankly quite simple, although the implications were not clear to me from the docs, this became clear at runtime:
Even if the corresponding UrlFetchApp
scope has been approved and properly configured for the application, the underlying google apps script engine will not execute any such requests and actively block them UNLESS the authMode
is FULL
which it only is in these circumstances:
Manage -> Add extension
overlay menuI solved my issue by updating the code accordingly and the apps script and the UrlFetchApp
requests are behaving as expected.