Search code examples
google-apps-scriptgoogle-sheets-api

My OAuth Scope verified and approved Google Sheets Add-on throwing AppUrlFetch exceptions (scope not approved?)


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"
  }
}

Solution

  • 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:

    1. upon onInstall(e) event which is relevant in this use-case if the user installs the add-on while in a google sheet from the Manage -> Add extension overlay menu
    2. upon the user opening and authorizing a (html) sidebar menu item

    I solved my issue by updating the code accordingly and the apps script and the UrlFetchApp requests are behaving as expected.