onEdit function not working and throwing 'Cannot read property range of undefined'

Question

Using a trigger to run this code when the value in cell G2 of my google sheets changes (due to the output of another function) and following the advice of other questions I've essentially overloaded the onEdit function as seen below but I keep getting the error:

'Cannot read property range of undefined'

referring to the e.range.getA1Notation() portion, which shouldn't be an issue given its meant to reference the event passed in as the parameter. I'm a bit stuck here, I've provided the code below as well as a screenshot on how I've configured the trigger:

function onEdit(e) {
  var cellAddress = e.range.getA1Notation();
  if(cellAddress === 'C2'){
    Logger.log('it worked');
    sendEmail();
  }
}

function sendEmail() {
  Logger.log("sent email");
  var sheet = SpreadsheetApp.getActive().getSheetByName('KitchenDuties');
  var cell = sheet.getRange(2,9).setValue("emailed 1");
}

EDIT: Updated code fixing other issues, same issue with error on line referring to 'e.range.columnStart'

function createEditDrivenTrigger(){
  ScriptApp.newTrigger('onMyEdit')
  .forSpreadsheet('my sheet id')
  .onEdit()
  .create();
}

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName()==="KitchenDuties" && e.range.columnStart ==3 e.range.rowStart==2){
    sendEmail();
  }
}

function sendEmail() {
  Logger.log("sent email");
  var sheet = SpreadsheetApp.getActive().getSheetByName('KitchenDuties');
  var cell = sheet.getRange(2,9).setValue("emailed 1");
}

Well, that's new. Does this happen all the time? More importantly - does this happen if you don't add the "onEdit(e)"? I don't get what you are doing - why have a simple trigger and an installable one that does the same? What's the purpose? Could you please link to the "other questions" you reference? — Oleg Valter

Cooper Cooper · Accepted Answer · 2020-06-18T19:24:37

Remove the trigger that you created and do this instead:

function onMyEdit(e) {
  const sh=e.range.getSheet()
  if(sh.getName()=="your sheet name" && e.range.columnStart==3 && e.range.rowStart==2){
    sendEmail();
  }
}

function sendEmail() {
  Logger.log("sent email");
  var sheet = SpreadsheetApp.getActive().getSheetByName('KitchenDuties');
  var cell = sheet.getRange(2,9).setValue("emailed 1");
}

Create an installable trigger for onMyEdit()

You could also do this:

function onMyEdit(e) {
  const sh=e.range.getSheet()
  if(sh.getName()=="your sheet name" && e.range.columnStart==3 && e.range.rowStart==2){
    var sheet=e.source.getSheetByName('KitchenDuties');
    sheet.getRange(2,9).setValue("emailed 1");
  }
}

onEdit function not working and throwing 'Cannot read property range of undefined'

1 Answers