Call an app script from another script with UrlFetchApp.fetch without manual log in

Question

I have the same problem as described here: Google Web Apps - Get user email but run scripts as owner

The given solution is to create a client script that runs as the current user and a middleware script that runs as the owner. The client script then calls the middleware script with UrlFetchApp.fetch(...) and the middleware handles the request in the doGet(e) function. This works fine in a test environment.

But to have it productive, I need to have the middleware script only accessible by domain users. Than, the UrlFetchApp.fetch call returns a login dialog.

So, is there a way to call a Google App Script by URL without manual log in?

This is my frontend script that runs with the user's privileges:

function doGet(e) {
  var data = HtmlService.createHtmlOutput(backendCall());
  // TODO: Format data as HTML
  return HtmlService.createHtmlOutput(data);
}

function backendCall() {
  const url = `${backendUrl}?user=${encodeURI(Session.getEffectiveUser().getEmail())}`;

  const options = {
      method: 'GET',
      muteHttpExceptions: true,
      headers: { 
        Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,
        Accept: 'application/json'
      }
  };

  return UrlFetchApp.fetch(url, options).getContentText();
}

And the backend script that runs with the owner's privileges:

function doGet(e) {
    return ContentService.createTextOutput(getUserData(e.parameter.user)).setMimeType(ContentService.MimeType.JSON);
}

function getUserData(user) {
  const sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  const data = sheet.getDataRange().getValues() // Get whole sheet data. This is much faster than getting data row by row.
      .filter((row) => row[cols.user] === user) // Select data for selected user.
      .map(formatRow)                           // Format sheet row to JavaScript objects.
      .sort((a, b) => a.day - b.day);           // Sort by date.
  return JSON.stringify(data);                  // Return as JSON string.
}

The result is, that the backend script returns not the data, but the error message "Error 401 Unauthorized".

Answer 1

I copied your codes and made some modifications in order to make it work :

For both projects :

• Go to Setup, and check display manifest "appsscript.json" to ON
• Modify your appscript.json with oauthScopes like following :

{
  "timeZone": "Europe/Paris",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "DOMAIN"
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/userinfo.email"
  ]
}

Your front-end project :

const backendUrl = "https://script.google.com/.../exec";

function doGet(e) {
  return ContentService.createTextOutput(backendCall());
  //instead of : HtmlService.createHtmlOutput
}

function backendCall() {
  const url = `${backendUrl}?user=${encodeURI(Session.getEffectiveUser().getEmail())}`;

  const options = {
      method: 'GET',
      muteHttpExceptions: true,
      headers: { 
        Authorization: `Bearer ${ScriptApp.getOAuthToken()}`
      }
  };

  return UrlFetchApp.fetch(url, options).getContentText();
}

Back-end project:

function doGet(e) {
    return ContentService.createTextOutput(getUserData(e.parameter.user)).setMimeType(ContentService.MimeType.JSON);
}

function getUserData(user) {
  const sheet = SpreadsheetApp.openById([ID_SPREADSHEET]).getSheetByName([SHEETNAME]); 
  //instead of  getActiveSheet()

  const data = sheet.getDataRange().getValues() 
               .filter((row) => row[cols.user] === user)
               .map(formatRow)                          
               .sort((a, b) => a.day - b.day);           

  return JSON.stringify(data);
}