Run onEdit script on a protected sheet as an editor, not an owner error TypeError: Cannot read prope-CodePudding

I have a complicated Task List spreadsheet which keeps track of done tasks. Due to the settings I need to have some formulas and ranges protected to avoid users of the sheet deleting them by accident. The issue is that some of the scripts need to run on protected parts of the sheet. Everything works as it should under the Owner account. When other users use the sheet they get the script error for the protected ranges. After searching online I have set up the following:

function doGet(e) {
  this[e.parameter.run](e.parameter.sheetName || null);
  return ContentService.createTextOutput();
}

function atEdit() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url   "?run=script_atEdit&sheetName="   activeSheet.getSheetName(), {headers: {authorization: "Bearer "   ScriptApp.getOAuthToken()}});

// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}


function readdTask() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url   "?run=script_readdTask&sheetName="   activeSheet.getSheetName(), {headers: {authorization: "Bearer "   ScriptApp.getOAuthToken()}});

// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}

Basically, after the script is published as web app it'll make it run under the owner account even when other users run it from the shared sheet.

I am however getting the following error in my onEdit script
(please note that the onEdit was renamed to atEdit due to onEdit not having access to Auth calls).

TypeError: Cannot read property 'activeSheet' of undefined at script_atEdit(sysW.1:57:34) at doGet(sysW.1:2:24)

Here is my atEdit script:

function script_atEdit(e) {
  
  var ss              = SpreadsheetApp.getActiveSpreadsheet();                                // → gets the spreadsheet we will use to get individual sheets from
  var activeSheet     = e.source.getActiveSheet();                                            // → gets the information and the active sheet  
  var globalsSheet    = ss.getSheetByName("Admin");                                           // → gets the sheet where we store our global values, GAS handles globals a bit differently therfore declaring a dynamic global is inconvenient
  
    if (activeSheet.getName() == 'Tasks') {                                                   // → specifies on what sheet within the worksheet the action below will happen
        var aCell = e.source.getActiveCell(), col = aCell.getColumn();                        // → gets the active cell and column the user clicks on and puts the info into aCell variable
                                                                                            
        if (col == 2) {                                                                    
            var dateCell = aCell.offset(0,1);                                                 // → offset, specifies what row and colum hold / will hold the date (- is up,   is down) and number of columns away (- is left,   is right)
            var aRow     = aCell.getRow();                                                 
                                                                                            
            if (aCell.getValue() === true) {                                               
                var newDate   = new Date();                                                   // → gets the current timestamp; the current date and the current time the user checked the cell
                var taskID    = activeSheet.getRange(aRow,7).getValue();
                var taskDate  = activeSheet.getRange(aRow,5).getValue();
                                globalsSheet.getRange(1, 2).setValue(taskID)                  // → sets the global value in the Admin sheet so that it becomes accessible to other functions, IE. other functions copy the value from Admin sheet
                                globalsSheet.getRange(2, 2).setValue(taskDate)
                var everyNth  = activeSheet.getRange(aRow,8).getValue();
                                globalsSheet.getRange(3, 2).setValue(everyNth)
                                dateCell.setValue(newDate);                                   // → puts the above information into the actual cell the user checked
                                activeSheet.getRange(aRow,1,1,6).setBackground("#87868c");    // → changes the background color of the row in which the user checked the cell                                                                                    
                                script_readdTask() }                                                 // → calls readdTask function which will repeat the task and set its date to whatever repeat frequency it is set in the setTasks sheet
           
           
            else {                                                                         
                   dateCell.setValue("");                                                     // → clears the date when the cell is unchecked     
                   activeSheet.getRange(aRow,1,1,6).setBackground("#ffffff"); }}}            // → restores the color of the row to what it was, white, if the cell is unchecked


  var sheetName  = activeSheet.getRange(1,15).getValue();
  var nameCheck1 = activeSheet.getRange(1,1).getValue();
  var nameCheck2 = activeSheet.getRange(1,6).getValue();
  var currentSheet = ""   nameCheck1   nameCheck2

         if (sheetName == currentSheet){
         var bCell = e.source.getActiveCell()
    
         if (bCell.getValue() === true){
             bCell.setBackground("green")
    
 }  else if (bCell.getValue() === false){ 
             bCell.setBackground("#f3f3f3");
    }
   }

}

CodePudding user response：

I think that your issue is due to that although script_atEdit(e) is called from doGet(e), you are trying to use script_atEdit(e) as the function called by OnEdit trigger.

In your situation, from your reply, I understood that script_atEdit(e) is run by doGet(e) called from atEdit(). atEdit() is run by OnEdit trigger.

In this case, please modify script_atEdit(e) as follows. In your script, e of script_atEdit(e) is the sheet name.

From:

var activeSheet     = e.source.getActiveSheet();

To:

var activeSheet = SpreadsheetApp.getActive().getSheetByName(e) || SpreadsheetApp.getActiveSheet();

And also,

From:

var aCell = e.source.getActiveCell(), col = aCell.getColumn();

To:

var aCell = activeSheet.getActiveCell(), col = aCell.getColumn();

And also,

From:

var bCell = e.source.getActiveCell()

To:

var bCell = activeSheet.getActiveCell();

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".