OnEdit Not Triggering With Copy & Paste in Google App Script

Question

I have a script that adds the current date when column one is edited, but at the moment it doesn't work when a value is pasted in.

Is it possible to make this script add the date for each row of the range that is pasted into column 1?

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Add Statements Here' && e.range.getColumn() == 1) {
    e.range.offset(0,10).setValue(e.value.length>0 ? new Date() : '');
  }

} 

Answer 1

When pasting onto the cell, the "value" property of the event object is set to undefined.

Thus, evaluating e.value.length>0 (as in line 3 of the provided code), results in a TypeError since undefined does not hold a "length" property.

In order to overcome this problem I suggest you use e.range.getValue() instead of e.value, since the range property will be declared whether you insert the value by manually typing it or pasting it.

Your modified code could look like below:

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Add Statements Here' && e.range.getColumn() == 1) {
    e.range.offset(0,10).setValue(e.range.getValue().length>0 ? new Date() : '');
  }
}

Answer 2

This is really weird behavior, but I think I have a rough idea of what's going on. The event parameter for onEdit() provides the property value, but note the caveat that is the final sentence

New cell value after the edit. Only available if the edited range is a single cell.

It seems that if you paste over the cell, then Google handles the action as a potential batch operation and assumes the range is not a single cell, so value isn't returned. Consider that if you selected range A1:D1 and pasted it over A2, that would modify cells B2, C2, & D2 as well.

However, if you paste into cell A2 (double-clicking into the cell before pasting), then the only cell modified is A2.

So the difference between pasting over and pasting into is basically the difference between using setValues() and setValue().

To work around this, try removing or changing your ternary condition e.value.length>0 ? new Date() : ''

For example, the modified code below will check if the first value in the edited range has a length greater than zero, but if you paste multiple rows at once, it will only print the timestamp for the first row. This is easily resolved, if you need to do so, by adjusting your .offset() or using .getHeight() (and potentially .getWidth()).

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Add Statements Here' && e.range.getColumn() == 1) {
    e.range.offset(0,10,1,1).setValue(e.range.getValue().length>0 ? new Date() : '');
  }
}