Google-sheets – onEdit only updates top row after pasting (editing) multiple rows

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I'm using a Google Spreadsheets and have a script that updates the "last email" column with a date stamp when an employee enters an email address into the prior column. The problem occurs when my employee pastes multiple rows of email addresses at a time. When he does this the script only puts a date stamp in the top row of what was pasted. The code I'm currently using is:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Master Sheet" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 10 ) { //checks the column
    if( r.getValue() === '' ) {//don't update the timestamp if the cell is edited to a blank vaue
    }
     else
     {
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) {// only update the timestamp if it has not already been updated
     //Format the current date into datetime format (adjust to display how you want)
      var dateTime = Utilities.formatDate(new Date(), "EST-8", "MM-dd-yyyy");
      // Set the cell
      nextCell.setValue(dateTime);}
    }
  }
}
}

Is there a way to correct so that when he pastes multiple rows at once the date stamp is updated on all rows?

Best Answer

onEdit can be written to take an event parameter that among other things contains the range that was edited. Changing the function declaration to take a parameter 'event' and changing r to be event.range instead of the activecell gets the exact behavior I believe you want. 

function onEdit(event) {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Master Sheet"" ) { //checks that we're on the correct sheet
    var r = event.range;
    if( r.getColumn() == 10 ) { //checks the column
    if( r.getValue() === '' ) {//don't update the timestamp if the cell is edited to a blank vaue
    }
     else
     {
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) {// only update the timestamp if it has not already been updated
     //Format the current date into datetime format (adjust to display how you want)
      var dateTime = Utilities.formatDate(new Date(), "EST-8", "MM-dd-yyyy");
      // Set the cell
      nextCell.setValue(dateTime);}
    }
  }
}
}
Related Topic