Two nearly identical functions in the same project triggered by triggers set up in the Triggers editor.

onWrongEdit(e) (triggered by onEdit) works but when I add "var sheet = e.range.getSheet().getName();" to onWrongInsert(e) (triggered by onChange) that second function fails even though it worked before adding that var line.

What the heck have I missed?

Feeling stupid. Be kind :)

function onWrongEdit(e){
  var sheet = e.range.getSheet().getName();
  var rangeCol = e.range.getColumn();
    if((sheet == 'Campaign Pipeline' && (rangeCol === 2 || rangeCol === 8 || rangeCol === 2)) || (sheet == 'Onboarding Pipeline' && (rangeCol === 2 || rangeCol === 3 || rangeCol === 4 || rangeCol === 5 || rangeCol === 11 || rangeCol === 12 ))){
      SpreadsheetApp.getUi().alert("STOP. Don't edit grey shaded cells. To fix, press CTRL Z to undo. This error message will appear again, just ignore it.");
  }
}

function onWrongInsert(e){
  var sheet = e.range.getSheet().getName(); // The line that messes it up.
    if (e.changeType == 'INSERT_ROW'){  
    SpreadsheetApp.getUi().alert("STOP. You created a row the wrong way. Delete the row and use the 1POT menu.");
    }
}

CodePudding user response：

Your answer is from the executions page

Aug 25, 2022, 11:19:26 AM   Info    {"authMode":"FULL","changeType":"INSERT_ROW","source":{},"triggerUid":"","user":{"email":"","nickname":""}}


Aug 25, 2022, 11:19:26 AM   Error   TypeError: Cannot read property 'getSheet' of undefined
    at onWrongInsert(ag1:19:23)

It occurs because there is no range element in the event object for onChange

I rewrote them like below:

function onWrongEdit(e){
  Logger.log(JSON.stringify(e));
  var name = e.range.getSheet().getName();
    if((name == 'Sheet0' && (e.range.columnStart === 2 || e.range.columnStart === 8)) || (name == 'Sheet1' && (e.range.columnStart === 2 || e.range.columnStart === 3 || e.range.columnStart === 4 || e.range.columnStart === 5 || e.range.columnStart === 11 || e.range.columnStart === 12 ))){
      SpreadsheetApp.getUi().alert("STOP. Don't edit grey shaded cells. To fix, press CTRL Z to undo. This error message will appear again, just ignore it.");
  }
}
function onWrongInsert(e){
  Logger.log(JSON.stringify(e));
  var sheet = e.range.getSheet().getName(); // The line that messes it up.
    if (e.changeType == 'INSERT_ROW'){  
    SpreadsheetApp.getUi().alert("STOP. You created a row the wrong way. Delete the row and use the 1POT menu.");
    }
}

function createTriggers() {
  ScriptApp.newTrigger('onWrongEdit').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  ScriptApp.newTrigger('onWrongInsert').forSpreadsheet(SpreadsheetApp.getActive()).onChange().create();
}