How to Properly Configure GAS Web App (as another user) to Execute GAS API Executable (as me) using OAuth2?

Question

Problem

After days of reading and attempting trial-and-error, I am trying to make a call from a GAS Web App (executed as any Google User) to a GAS API Executable (executed as Me), but consistently receive an error message after Reviewing/Granting permissions:

*"Error: Access not granted or expired."*

That is not a response from the server, but simply a notification from the OAuth2 library: "This method will throw an error if the user's access was not granted or has expired."

So it seems there may be some otherwise obvious step that is missing from instructions and Q&As. Somehow, after doing all of the setup, the web app cannot access the API Executable.

I also spent a few hours writing this question and formulating a minimal test/example. Here are the files in Google Drive, for viewing directly.

Desired Outcome

The desired outcome is to be able to have other users use the Web App as themselves and, from that app, execute the API Executable as Me.

Question

What is wrong with my configuration and/or code, and how can I receive data back from the API Executable?

What I've tried

I've combined various tutorials and Q&As and attempted to make a minimal example. The three most closely related are:

1. Google Groups - "Webapp execute as user BUT save data to spreadsheet"

...Service accounts, while applicable, are not the best fit for this use-case. They are better suited to situations where the service account acts as a proxy for multiple users...

...Basically, you'll need to generate OAuth2 credentials specific to your account and use the OAuth2 library to generate access tokens. That access token can then be used to make direct calls against the Spreadsheet REST API OR alternatively, the Apps Script API (formerly the Execution API) to invoke a function in the script under your own authority...

2. SO - "Can I have part of Google Apps Script code execute as me while the rest executes as the accessing user?"
3. SO - "Get user info when someone runs GAS web app as me"

The first link seems directly applicable to my scenario. However, the instructions are sparse, though I have done my best to follow them. The second is also applicable, but sparse. The third is related, but is actually the inverse of what I want to do, and so of limited help.

Summary of Steps in GCP

Here is what I did within console.cloud.google.com:

1. Created a new project named "apiExecTest".
2. Within "APIs & Services", enabled two APIs:
   • Apps Script API (unsure if necessary)
   • Google Sheets API (unsure if necessary)
3. Within "APIs & Services", configured the Oauth Consent Screen
   • Internal
   • Set App name, User support email, and Developer contact email. Did nothing else. Did not set "App domain" nor "Authorized domains".
   • Added all 61 scopes for Apps Script and Sheets (unsure if necessary)
4. Within "APIs & Services", created credentials
   • OAuth client ID
   • Web Application
   • Added Client name.
   • Added Authorized Redirect URI:
     https://script.google.com/macros/d/1zj4ovqMWoCUgBxJJ8u518TOEKlckeIazVBL4ASdYFiVmjoZz9BLXbJ7y/usercallback
   • Obtained Client ID & Client Secret to insert into webApp code.

Summary of Steps in GAS

Here is what I did in Google Drive / Apps Script. The files can be viewed here:

1. Created a new folder in Google Drive containing three things:

   • GAS file: "webApp"
     • Deployed as Web App
     • Execute as: User accessing the web app
     • Who has access: Anyone with Google account
   • GAS file: "apiExec"
     • Deployed as API Executable
     • Who has access: Anyone with Google account
   • Google Sheet: sSheet
     • Not shared with anyone, owned by Me.

2. Added a basic function to apiExec that obtains the first cell of the first sheet in sSheet, and confirmed it works by executing it within the GAS editor and observing the console output.

3. Added the OAuth2 library to webApp as oauth2.gs, copy/pasted from GitHub. Setup and configured setClientId(), setClientSecret(), API URL and other settings per the readme and examples cited above. For setScope(), I used:
   .setScope('https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/spreadsheets')

4. Added a basic functionality to webApp that makes a call to apiExec to obtain data from sSheet.

5. Added the following to the webApp appsscript.json (unsure if correct, have tried variations):
   "oauthScopes": ["https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/spreadsheets"]

6. I changed the GCP Project Number for both apiExec and webApp to that of the GCP project created in the steps above.

7. I then executed the doGet() function of webApp within the GAS editor. It does ask for authorization, which I granted. After authorization, as the execution continues, the error mentioned above is thrown. I also ran the function via webApp's URL, which of course also results in the error.

After attempting this multiple times, and spending days reading and with trial-and-error, I've made no progress. Any help is greatly appreciated.

---

To be thorough, here are the contents of the GAS files:

apiExec

appsscript.json

{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "executionApi": {
    "access": "ANYONE"
  }
}

Code.gs

function doPost() {
  var spreadsheet = SpreadsheetApp.openById("1aIMv1iH6rxDwXLx-i0uYi3D783dCtlMZo6pXJGztKTY");
  var sheet = spreadsheet.getSheetByName("test sheet");
  var data = sheet.getRange("A1").getValues()
  console.log(data)

  return data;
}

webApp

appsscript.json

{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/spreadsheets"
  ],
  "webapp": {
"executeAs": "USER_ACCESSING",
"access": "ANYONE"
  }
}

Code.gs

function doGet(e) {
  var myParam = "someParam";
  console.log(myParam);

  var apiExecResponse = makeRequest('doPost', [myParam]);
  console.log(apiExecResponse);
  
  var appsScriptService = getService();

  if (!appsScriptService.hasAccess()) {
// This block should only run once, when I authenticate as myself to create the refresh token.
var authorizationUrl = appsScriptService.getAuthorizationUrl();
var htmlOutput = HtmlService.createHtmlOutput('<a href="' + authorizationUrl + '" target="_blank">Authorize</a>.');
htmlOutput.setTitle('GAS Authentication');
return htmlOutput;
  }
  else {
console.log("It worked: " + myParam + " " + apiExecResponse);
htmlOutput.setTitle("The Results");
return HtmlService.createHtmlOutput("<p>It worked: " + myParam + " " + apiExecResponse + "</p>");
  }
}


function getService() {
  // Create a new service with the given name. The name will be used when
  // persisting the authorized token, so ensure it is unique within the
  // scope of the property store.
  return OAuth2.createService('apiExecService')

  // Set the endpoint URLs, which are the same for all Google services.
  .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
  .setTokenUrl('https://accounts.google.com/o/oauth2/token')

  // Set the client ID and secret, from the Google Developers Console.
  .setClientId('390208108732-s7geeikfvnqd52a0fhf6e015ucam0vqk.apps.googleusercontent.com')
  .setClientSecret('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')

  // Set the name of the callback function in the script referenced
  // above that should be invoked to complete the OAuth flow.
  .setCallbackFunction('authCallback')

  // Set the property store where authorized tokens should be persisted.
  .setPropertyStore(PropertiesService.getScriptProperties())

  // Set the scopes to request (space-separated for Google services).
  .setScope('https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/spreadsheets')

  // Below are Google-specific OAuth2 parameters.

  // Sets the login hint, which will prevent the account chooser screen
  // from being shown to users logged in with multiple accounts.
  //.setParam('login_hint', Session.getEffectiveUser().getEmail())

  // Requests offline access.
  .setParam('access_type', 'offline')

  // Consent prompt is required to ensure a refresh token is always
  // returned when requesting offline access.
  .setParam('prompt', 'consent');
}



function authCallback(request) {
  var apiExecService = getService();
  var isAuthorized = apiExecService.handleCallback(request);
  if (isAuthorized) {
return HtmlService.createHtmlOutput('Success! You can close this tab.');
  }
  else {
return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}




function makeRequest(functionName, paramsArray) {
  console.log("Running " + functionName + " via 'makeRequest'.");

  var apiExecUrl = 'https://script.googleapis.com/v1/scripts/AKfycbzHV5_Jl2gJVv0wDVp93wE0BYfxNrOXXKjIAmOoRu3D8W6CeqSQM9JKe8pOYUK4fqM_:run';

  var payload = JSON.stringify({
"function": functionName,
"parameters": paramsArray,
"devMode": false
  });

  var params = {
method:"POST",
headers: {
  Authorization: 'Bearer ' + getService().getAccessToken()
},
payload: payload,
contentType: "application/json",
muteHttpExceptions: true
  };

  var result = UrlFetchApp.fetch(apiExecUrl, params);

  return result;
}

OAuth2.gs

See: https://github.com/googleworkspace/apps-script-oauth2/blob/master/dist/OAuth2.gs

Answer 1

If I understand correctly, your current flow is as follows:

1. Use the OAuth2 library to do a one-time capture of the auth token for your own Google account.
2. Use that stored token to authenticate the request to the API Executable (when running the web app as another user).

Apps Script has a built-in method for accomplishing step 1: ScriptApp.getOAuthToken(), so I'm not sure you even need the OAuth2 library for this. (You would need that library for authorizing services other than Google.)

Possibly you can avoid using the OAuth2 library completely by doing the following:

Add this function to your web app project and run it once from the editor, i.e. under your own authorization:

function storeOauthToken() {
  PropertiesService.getScriptProperties().setProperty(
    'myToken',
    ScriptApp.getOAuthToken()
  )
}

Change the headers in the makeRequest function of your webApp project from this

headers: {
  Authorization: 'Bearer ' + getService().getAccessToken()
},

to this:

headers: {
  Authorization: 'Bearer ' + PropertiesService.getScriptProperties().getProperty('myToken')
},

I created a copy of your projects and was able to confirm that this technique works.

Token refresh

I assume that the token may expire like any other OAuth2 token, so you may need to set a timed trigger (again, under your own authorization) to run storeOAuthToken() periodically.