Edit Google Script “new Date” timestamp format

Question

I have a google script (below) which is doing the job of checking Column N (14) for when a cell is updated and then putting a timestamp in Column O in the adjacent cell. However, I'm having trouble setting the format of the timestamp. Currently the timestamp is the correct date format, however, the time format is not GMT but seems to be GMT-8. I'm able to remedy this by adding another column in the sheet and have a custom formula I could use to change the timezone, but really I want this all done within the onEdit function below so as to save on unneccesary additional columns and script.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //The sheet name that the timestamp will be implemented on
    var r = s.getActiveCell();
    if( r.getColumn() == 14 ) { //Enter column number i.e A = 1, B = 2 etc... 
      var nextCell = r.offset(0, 1); 
      if( nextCell.getValue() === '' ) //Checks if the column for the timestamp is empty 
        nextCell.setValue(new Date());
    } 
  } 
}

I have tried editing the last line in a variety of ways such as:

nextCell.setValue(new Date("GMT","dd/mm/yyyy hh:mm")); 
nextCell.setValue(new Date("dd/mm/yyyy hh:mm"));

But this is not working as it is leaving the timestamp cell in Column O as blank. Any ideas?

Perfect, thank you. The Speadsheet location was set to UK but with the time zone as GMT-8. That's solved it. — Darth Fruitcake
If you never to change your date formatting, the method formatDate() might be useful for you: developers.google.com/apps-script/reference/utilities/… — ziganotschka

Cooper Cooper · Accepted Answer · 2019-10-08T13:39:46

function onEdit(e) {
  var sh=e.range.getSheet();
  e.source.toast('Here');
  if(sh.getName()!="Sheet1" )return;
  if( e.range.columnStart==14 && e.range.offset(0,1).isBlank()) {
    e.range.offset(0,1).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "d/MM/yyyy HH:mm"));
  } 
}

Utilities.formatDate()

onEdit Event Object

Edit Google Script “new Date” timestamp format

1 Answers