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.
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.
What is wrong with my configuration and/or code, and how can I receive data back from the API Executable?
I've combined various tutorials and Q&As and attempted to make a minimal example. The three most closely related are:
...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...
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.
Here is what I did within console.cloud.google.com:
https://script.google.com/macros/d/1zj4ovqMWoCUgBxJJ8u518TOEKlckeIazVBL4ASdYFiVmjoZz9BLXbJ7y/usercallbackHere is what I did in Google Drive / Apps Script. The files can be viewed here:
Created a new folder in Google Drive containing three things:
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.
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')
Added a basic functionality to webApp that makes a call to apiExec to obtain data from sSheet.
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"]
I changed the GCP Project Number for both apiExec and webApp to that of the GCP project created in the steps above.
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:
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;
}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.gsIf I understand correctly, your current flow is as follows:
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.
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.