onEdit only updates top row after pasting (editing) multiple rows

Question

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?

Answer 1

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);}
    }
  }
}
}

Answer 2

That's not possible via the onEdit trigger in Google Apps Script (I hope @TomHorwood proves me wrong). The first cell that's being used to paste from, is only used to trigger the onEdit, as you have described yourself.

I can think of the following two options, that might help:

1. time based trigger
2. manual trigger via a menu option

The first option will automatically update the dates, throughout whatever range you set it to be. The second option will allow you to paste multiple values at once, and trigger the selected range afterwards.

In both cases, you need to re-write your code. If you need help with that, just re-ask.

Answer 3

You should loop through each cell of e.range to achieve your goal. i.e.:

function onEdit(e) { myFunction(e) };

function myFunction(e) {

  let dateTime = Utilities.formatDate(new Date(), "EST-8", "MM-dd-yyyy");

  for (let c=1; c <= e.range.getNumColumns(); c++) {
    for (let r=1; r <= e.range.getNumRows(); r++) {
      let cell = e.range.getCell(r, c);
      if ( cell.getValue() ) {
        e.source.getActiveSheet().getRange(cell.getRow(), cell.getColumn()+1).setValue(dateTime);
        // Do other things...
      } else {
        e.source.getActiveSheet().getRange(cell.getRow(), cell.getColumn()+1).clearContent();
        // Do other things...
      }
    }
  }
};