Home > Software engineering >  How to make onEdit() only restricted to edition in specific range?
How to make onEdit() only restricted to edition in specific range?

Time:03-25

I tried to make onEdit function only respond to the specific range. my onEdit function as bellow:

function onEdit(e){
  var range = e.range;
  var sheetName = e.source.getActiveSheet().getSheetName();
  if (//trigger only when the cells from column A:H is edited 
    sheetName == "Email list" &&
    range.rowStart >= 2 &&
    range.columnStart >= 1 &&
    range.columnStart <= 8
  ) {
    sendMail()
  }
}


function sendMail() {.... //the main function here

I have also deployed installable trigger using function onEdit. But I got TypeError: Cannot read property 'range' of undefined. Not sure which part is wrong or how should I define the variable within onEdit? Thank you.

CodePudding user response:

Try following sample script:-

function onEdit(e)
{
  const range = e.range
  const sheet = range.getSheet();
  const row = range.getRow();
  const column = range.getColumn();
  if (//trigger only when the cells from column A:H is edited 
    sheet.getName() == "Email list" &&
    row >= 2 && 
    column <= 8 // not taking 1 into consideration
  ) {
    sendMail()
  }
}

If you run the function directly, it will throw an error TypeError: Cannot read property 'range' of undefined, remove Edit installable trigger, as onEdit() is simple trigger

CodePudding user response:

I believe your goal is as follows.

  • You want to run your onEdit by not only OnEdit simple trigger but also the standalone without the simple trigger. In this case, how about the following modification?

Modified script:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var sheetName = sheet.getSheetName();
  if (//trigger only when the cells from column A:H is edited 
    sheetName == "Email list" &&
    range.rowStart >= 2 &&
    range.columnStart >= 1 &&
    range.columnStart <= 8
  ) {
    sendMail()
  }
}
  • By this modification, you can directly run onEdit. And also, you can run onEdit by OnEdit trigger.
  • Related