I have created a Google Spreadsheet which includes protected ranges, therefore, domain users cannot perform certain tasks, for instance sorting the sheet. As such, I have created a web app which runs under my account (admin) and does this. Trial and error got it to work, when I am running the script. The issue is that when other domain users are invoking the web app the following error is thrown:

Google Spreadsheet code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var m = ui.createMenu('Extra');
  m.addItem('↑↓ Surname','sortByName');
  m.addItem('↑↓ Section','sortBySection');
  m.addItem('↑↓ DOB','sortByDOB');
  m.addToUi();
}

function sortByName(){
  sortSheet("byName");
}
function sortBySection(){
  sortSheet("bySection");
} 
function sortByDOB(){
  sortSheet("byDOB");
}

function sortSheet(sortType){
  if (!isClassSheet()){
    return;
  }
  var sheet = aS.getActiveSheet();
  var sheetId = aS.getId();
  var baseUrl ="https://script.google.com/a/macros/ourdomain.com/s/xxxx/exec"
  var queryString = "?sheetID=" sheetId "&sortType=" sortType "&sheetName=" sheet.getName() "&firstDataRow=" FIRST_DATA_ROW "&classColStudentName=" S_CLASS_COL_STUDENT_NAME "&classColSection=" S_CLASS_COL_SECTION "&classColDOB=" S_STUDENTS_COL_DOB;
  var url = encodeURI(baseUrl   queryString)
  var params = {method: "get", headers: {Authorization: "Bearer "   ScriptApp.getOAuthToken()}};
  var request = UrlFetchApp.fetch(url,params)
    //logsheet.appendRow([request])
  Logger.log(request)
}

Unbound Web App code:

function doGet(e) {
  var param = e.queryString;
  var parameters = param.split("&")  // This just checks only 7 parameters are present else gives a invalid link

  if (param != null && parameters.length == 7){
    param = e.parameter;
    var sheetId = param.sheetID;
    var name = param.sheetName;
    var sortType = param.sortType;
    var S_CLASS_COL_STUDENT_NAME = Number(param.classColStudentName);
    var S_CLASS_COL_SECTION = Number(param.classColSection);
    var S_STUDENTS_COL_DOB = Number(param.classColDOB);
    var FIRST_DATA_ROW = Number(param.firstDataRow);
  } else {
      return ContentService.createTextOutput("Bad")
    }
  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.getLastColumn()); 
     
     switch (sortType){
       case "byName":
          dataRange.sort({column: S_CLASS_COL_STUDENT_NAME, ascending: true});
          break;
       case "bySection":
          dataRange.sort([{column: S_CLASS_COL_SECTION, ascending: true}, {column: S_CLASS_COL_STUDENT_NAME, ascending: true}]);
          break;  
       case "byDOB":
          dataRange.sort([{column: S_STUDENTS_COL_DOB, ascending: true}, {column: S_CLASS_COL_STUDENT_NAME, ascending: true}]);
          break;   
     }
     
     }
     catch (err){
      return ContentService.createTextOutput(err)
     }
 return ContentService.createTextOutput("Success")
}

Deployment settings

CodePudding user response：

For anyone who's interested in this, I have read this document which explains that the web app must be shared with users who are invoking it through the spreadsheet. Thanks to @TheMaster for pointing it out and to @Tanaike for writing the document