Search code examples
google-apps-scriptweb-applicationsgoogle-oauthhttp-status-code-401urlfetch

Another Google Apps Script UrlFetchApp returning Unauthorized Error 401

So, I am using a Google Spreadsheet which holds protected ranges, therefore, to accomplish sorting rows, an unbound web app is invoked from the sheet:

Google Sheet XYZ -> Unbound Web App (sort code) -> Google Sheet XYZ

Unbound Web App runs under my account and is shared with users using Sheet XYZ. Works perfectly.

Now, I am attempting to invoke the same Web App from a Google Form using OnSubmit:

Google Form ABC -> Unbound Web App (sort code) -> Google Sheet XYZ

however en exception si returned from the web app:

{ [Exception: Request failed for https://script.google.com returned code 401. Truncated server response: <HTML>
<HEAD>
<TITLE>Unauthorized</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Unauthorized</H1>
<H2>Error 401</H2>
</BODY>
</HTML>
 (use muteHttpExceptions option to examine full response)] name: 'Exception' }

Strange to say this occurs although I am both the form, web app, and sheet owner.... Also tried to add other scopes to the Form script.

This is the code running in the form's OnSubmit:

function callWebApp(){
  var queryString = "?action=sort&sheetID="+contactListSSID+"&sortType=byName&sheetName="+S_PARENTS_SHEET_NAME+"&firstDataRow="+S_PARENTS_FIRST_DATA_ROW+"&colUserName="+S_PARENTS_NAME_SURNAME_COLUMN+"&classColSection="+null+"&colDOB="+null; // null because unused for this sort type
  var baseUrl ="https://script.google.com/a/macros/ourdomain.com/s/xxxxxxx/exec"
  var url = encodeURI(baseUrl + queryString).replace("#","%23");
  var params = {method: "get", headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}};
  
  try {
      var request = UrlFetchApp.fetch(url,params)
      Logger.log(request);
  } catch (error) {
    console.error(error);
    return error;
  }
  return request;
}

Unbound Web App code (does not include all actions listed in the code):

function doGet(e) {
  var param = e.queryString;
  var parameters = param.split("&") 
  var validActions = ["sort", "duplicate", "delete"];
  if (param == null){
    return "Missing parameters";
  }
  param = e.parameter;
  const matches = parameters.filter(s => s.includes('action'));
  if (matches.length < 1 || validActions.indexOf(param.action) < 0){
    return "Missing or bad action";
  }
  var ret;
  switch(param.action){
    case "sort":
      ret = sortSheet(e);
      break;
    case "duplicate":
      ret = duplicateSheet(e);
      break;
    case "delete":
      ret = deleteSheet(e);
      break;  
  }

  return ContentService.createTextOutput(ret);
}

function sortSheet (e){
  param = e.parameter;
  var sheetId = param.sheetID;
  var name = param.sheetName;
  var sortType = param.sortType;
  var COL_STUDENT_NAME = Number(param.colUserName);
  var S_CLASS_COL_SECTION = Number(param.classColSection);
  var COL_DOB = Number(param.colDOB);
  var FIRST_DATA_ROW = Number(param.firstDataRow);
  try{  
    var ss = SpreadsheetApp.openById(sheetId)  
    var sheet = ss.getSheetByName(name)
    var dataRange = sheet.getRange(FIRST_DATA_ROW,1,(sheet.getLastRow() - FIRST_DATA_ROW + 1),sheet.getMaxColumns()); 
    var a1 = dataRange.getA1Notation();
    
    switch (sortType){
      case "byName":
        dataRange.sort({column: COL_STUDENT_NAME, ascending: true});
        break;
      case "bySection":
        dataRange.sort([{column: S_CLASS_COL_SECTION, ascending: true}, {column: COL_STUDENT_NAME, ascending: true}]);
        break;  
      case "byDOB":
        dataRange.sort([{column: COL_DOB, ascending: true}, {column: COL_STUDENT_NAME, ascending: true}]);
        break;   
    }
  }
    catch (err){
    return err;
  }
  return a1;
}

When the above code is run from the spreadsheet, it works. Once the same code is run in the form, it will throw an exception.

Solution

  • Issue:

    A drive scope is needed in order to access non-public Apps Script web apps.

    Solution:

    Adding any of these two scopes will work:

    You can either add them explicitly in the manifest, using the field oauthScopes:

    "oauthScopes": [
  "https://www.googleapis.com/auth/script.external_request", 
  "https://www.googleapis.com/auth/script.scriptapp",
  "https://www.googleapis.com/auth/forms",
  "https://www.googleapis.com/auth/drive.readonly"
]

    Or, more easily, you can add a commented line with a method that requires this scope, for example:

    //DriveApp.getFileById()

    The script will notice this and so ScriptApp.getOAuthToken() will be authorized with this scope.

    Related: