Weird issue with Google App Script when trying to automate timstamp

Question

I have been messing around to build a google worksheet with script. So basically, in this file whenever there is input on column 1 and 4, there will be a timestamp shown in the next cell. And when the input is removed, the timestamp is clear. Everything runs fine so far, however as some weird issue happen. If instead of input in column 1 & 4, I paste values to the range, no timestamp appear or if I paste with the timestamp, the pasted timestamp will disappear. I don't know what happened or if you are clear on what I'm trying to ask. Anyway, here the script I'm using:

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  var r = e.range;
  var nextCell = r.offset(0, 1);
  if( s.getName() == "Nhap" && (r.getColumn() == 1 || r.getColumn() == 3) ){ 
    if( nextCell.getValue() === '' && e.value !=null)
    {
       var time = Utilities.formatDate(new Date(), "GMT+7", "MM/dd/yyyy HH:mm:ss");
       nextCell.setValue(time);
    }  
     else{ nextCell.clearContent()}
   };

}

Thanks

Answer 1

e.value undefined when copy/pasting range:

When a range is copied, instead of manually inputting a value on a cell, the value parameter from the corresponding event object (e.value) is undefined. This is a known behaviour and it has been reported several times in Issue Tracker before:

• Installable onEdit function doesn't pass correct event.value when pasting cells into spreadsheet.
• Apps Script On Edit New Value is undefined when value is pasted in cell
• for onEdit trigger value is null if there was copy-paste of cell

While this is still under investigation, this doesn't seem to be intended behaviour. While the description for the value parameter says it will only be populated when the edited range is a single cell,

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

This behaviour also happens when the copied/pasted range is a single cell. See this related answer from Diego.

Issue:

Since e.value is undefined when pasting the range, the following condition is never true:

if( nextCell.getValue() === '' && e.value !=null)

So the code goes to:

else{ nextCell.clearContent()}

That is, the timestamp cell is cleared of content.

Solution:

You can avoid this by changing e.value to r.getValue() (see Range.getValue()), which will be populated even when the range is copy/pasted:

if( nextCell.getValue() === '' && r.getValue() !=null)

Another issue:

Also, in your current code, when the timestamp cell is not empty, this condition is always false, so the timestamp cell is cleared:

if( nextCell.getValue() === '' && e.value !=null)

This is disregarding whether the range is pasted or the cell is inputted manually. If you don't want this to happen, remove nextCell.getValue() === '' from your condition.

Edit:

If you want to remove timestamp when cell is emptied, check that cell value is not an empty string ("" !== null):

if (r.getValue() !="")