Get user info when someone runs Google Apps Script web app as me

Question

I have a standalone Google Apps Script deployed as a web app. The app is executed as me, because I want it to access files stored on my Drive, and because I want it to generate Google Sheets files that have some ranges protected from the user that are still editable by the script. However, I want these files to be segregated into folders, and each folder is assigned to a user, so I need to know who the user is each time the app runs.

Session.getActiveUser().getEmail() doesn't work since the web app is deployed as me and not as the user. My other thought was to make the app "available to everyone, even anonymous" (right now it's just "available to everyone") to skip Google's login screen, and use some kind of third-party authentication service or script. Building my own seems like overkill because this seems like it should already exist, but so far my research has only turned up things like Auth0 which seem incompatible with my simple Google Apps Script-based app, or else I'm too inexperienced to figure out how to use them.

Does anyone have a suggestion for how to authenticate users for this kind of web app? Preferably something that comes with a beginner-friendly tutorial or documentation? Or, is there another way for me to find out who's running the app while still executing it as myself?

I am so new to this I'm not even sure I'm asking this question in the right way, so suggested edits are taken gratefully.

Answer 1

I can think of two ways you might approach this where the Web App is deployed to execute as the user accessing it:

1. Scenario A: Create a service-account to access files stored on your Drive and to generate google sheets.
2. Scenario B: Create a separate Apps Script project deployed as an API Executable and call its functions from the main Web App.

These methods are viable but there are a number of pros and cons to each.

Both require OAuth2 authentication, but that bit is fairly easy to handle thanks to Eric Koleda's OAuth2 library.

Also, in both scenarios, you'll need to bind/link your main Apps Script project to a GCP project and enable the appropriate services, in your case Google Sheets and Google Drive APIs (see documentation for more details).

For Scenario A, the service account must be created under the same GCP project. For Scenario B, the secondary Apps Script project for the API executable must also be bound to the same GCP project.

---

Issues specific to Scenario A

You'll need to share the files and folders you want to access/modify (and/or create content in) with the service account. The service account has it own email address and you can share google drive files/folders with it as you would with any other gmail account.

For newly created content, permissions could be an issue, but thankfully files created under a folder inherit that folder's permissions so you should be good on that front.

However, you'll have to use the REST APIs for Drive and Sheets services directly; calling them via UrlFetch along with the access token (generated using the OAuth2 library) for the Service Account.

---

Issues specific to Scenario B

You'll need to setup a separate Apps Script project and build out a public API (collection of non-private functions) that can be called by a 3rd party.

Once the script is bound to the same GCP project as the main Web App, you'll need to generate extra OAuth2 credentials from GCP console under the IAM (Identity Access Management) panel.

You'll use the Client ID and Client Secret, to generate a refresh token specific to your account (using the OAuth2 library). Then you'll use this refresh token in your main Web App to generate the requisite access token for the API executable (also using the OAuth2 library). As in the previous scenario, you'll need to use UrlFetch to invoke the methods on the API Executable using the generated access token.

One thing to note, you cannot use triggers within the API executable code as they are not allowed.

---

Obviously, I've glossed over a lot of the details but that should be enough to get you started.

Best of luck.

Answer 2

Now that I've implemented TheAddonDepot's suggested Scenario B successfully, I wanted to share a few details that might help other newbies.

Here's what the code in my web app project looks like:

function doGet(e) {

  // Use user email to identify user folder and pass to var data
  var userEmail = Session.getActiveUser().getEmail();

  // Check user email against database to fetch user folder name and level of access
  var userData = executeAsMe('getUserData', [userEmail]);
  console.log(userData);

  var appsScriptService = getAppsScriptService();
  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('FMID Authentication');
    return htmlOutput;
  } else {
    var htmlOutput = HtmlService.createHtmlOutputFromFile('Index');
    htmlOutput.setTitle('Web App Page Title');
    if (userData == 'user not found') {
      var data = { "userEmail": userEmail, "userFolder": null };
    } else {
      var data = { "userEmail": userData[0], "userFolder": userData[1] };
    }
    return appendDataToHtmlOutput(data, htmlOutput);
  }

}

function appendDataToHtmlOutput(data, htmlOutput, idData) { // Passes data from Google Apps Script to HTML via a hidden div with id=idData
    if (!idData)
        idData = "mydata_htmlservice";

    // data is encoded after stringifying to guarantee a safe string that will never conflict with the html
    var strAppend = "<div id='" + idData + "' style='display:none;'>" + Utilities.base64Encode(JSON.stringify(data)) + "</div>";
    return htmlOutput.append(strAppend);
}

function getAppsScriptService() { // Used to generate script OAuth access token for API call
  // See https://github.com/gsuitedevs/apps-script-oauth2 for documentation
  // The OAuth2Service class contains the configuration information for a given OAuth2 provider, including its endpoints, client IDs and secrets, etc.
  // This information is not persisted to any data store, so you'll need to create this object each time you want to use it.

  // 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('appsScript')

    // 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('[client ID]')
    .setClientSecret('[client secret]')

    // 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())

    // Enable caching to avoid exhausting PropertiesService quotas
    .setCache(CacheService.getScriptCache())

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

    // 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) { // This should only run once, when I authenticate as WF Analyst to create the refresh token.
  var appsScriptService = getAppsScriptService();
  var isAuthorized = appsScriptService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab.');
  }
}

function executeAsMe(functionName, paramsArray) {
  try {
    console.log('Using Apps Script API to call function ' + functionName.toString() + ' with parameter(s) ' + paramsArray.toString());

    var url = '[API URL]';

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

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

    var results = UrlFetchApp.fetch(url, params);
    var jsonResponse = JSON.parse(results).response;
    if (jsonResponse == undefined) {
      var jsonResults = undefined;
    } else {
      var jsonResults = jsonResponse.result;
    }
    return jsonResults;
  } catch(error) {
    console.log('error = ' + error);
    if (error.toString().indexOf('Timeout') > 0) {
      console.log('Throwing new error');
      throw new Error('timeout');
    } else {
      throw new Error('unknown');
    }
  } finally {
  }
}

I generated the OAuth2 credentials at https://console.cloud.google.com/ under APIs & Services > Credentials > Create Credentials > OAuth Client ID, selecting "Web application". I had to add 'https://script.google.com/macros/d/[some long ID]/usercallback' as an authorized redirect URI, but I apologize as I did this two weeks ago and can't remember how I figured out what to use there :/ Anyway, this is where you get the client ID and client secret used in function getAppsScriptService() to generate the access token.

The other main heads up I wanted to leave here for others is that while Google Apps Scripts can run for 6 minutes before timing out, URLFetchApp.fetch() has a 60s timeout, which is a problem when using it to call a script via the API that takes more than 60s to execute. The Apps Script you call will still finish successfully in the background, so you just have to figure out how to handle your timeout error and call a follow-up function to get whatever the original function should have returned. I'm not sure if that makes sense, but here's the question I asked (and answered) on that issue.