Google Sheet Script for automatically entering date/time stamp when the adjacent column

Question

I've found a script on here for entering a timestamp in a column based on edits to the adjacent column:
Date and time stamp automatically added in column
  if(e.source.getActiveSheet().getName() == "Sheet1" ) {
    var col = e.source.getActiveCell().getColumn();
    if(col == 1 || col == 4) {
      var nCell = e.range.offset(0,1);   
      if(nCell.getValue() == "" && e.value != "") {
        var time = Utilities.formatDate(new Date(), "GMT+1", "HH:mm:ss");
        nCell.setValue(time);
      } else if(nCell.getValue() != "" && e.value == "") { 
        nCell.setValue("");
      }
    }
  }    
}

I can only get it to enter the time or the date not both. What am I missing? I need to have a timestamp auto entered after we enter completed in the previous cell before the document is transferred to our historical tab.

Rubén · Answer

The code is passing the timestamp as string / TEXT value but it's very likely that you aren't using the proper date format string but the simpler solution is to pass the Date object.
Replace
var time = Utilities.formatDate(new Date(), "GMT+1", "HH:mm:ss");

by
var time = new Date();

Google Sheet Script for automatically entering date/time stamp when the adjacent column

One Answer

Add your own answers!

Ask a Question