Another Google Apps Script UrlFetchApp returning Unauthorized Error 401-CodePudding

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("#","#");
  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.

CodePudding user response：

Google Form does not work in Google Sheet if you're working with Google Sheet by using App Script. Therefore, you could not call Authentication form and could not pass the authentication steps

CodePudding user response：

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.

Taking advantage of Web Apps with Google Apps Script

Issue:

Solution:

Related: