Search code examples
google-apps-scriptgoogle-sheetstimestamp

Google sheet TIMESTAMP script format Time with milliseconds & Date with Day and Month without Year

In my Google Sheets, I am using a script TIMESTAMP for function NOW() is updating when I open the Google Sheet to check the file
that is been given to me by Tanaike that retrieves time and date from the selection of a dropdown list in column D to column E. capture1

  1. I want the format of the time to be with milliseconds and the date without the year.
  2. Also, I want if it has a single-digit number (like 8 AM) to have a leading zero in front of the digit. capture2
Solution

  • From your showing sample output situation, how about using Utilities.formatDate as follows?

    Modified script 1:

    In this case, when you want to put the value as the string value, how about modifying TIMESTAMP as follows?

    function TIMESTAMP() {
  return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'HH:mm:ss:SSS" - "dd/MM');
}

    Modified script 2:

    If you want to put the value as the date object, how about modifying onEdit as follows? I think that this might be the same direction as TheMaster's comment. Ref

    function onEdit(e) {
  const excludeSheetNames = ["START", "MASTER"]; // Please set exclude sheet names.

  const { range } = e;
  const sheet = range.getSheet();
  if (excludeSheetNames.includes(sheet.getSheetName()) || range.columnStart != 4 || range.rowStart == 1) return;
  range.offset(0, 1).setNumberFormat('HH:mm:ss:SSS" - "dd/MM').setValue(new Date());

  if (e.value != "TRUE") return;
  e.source.getActiveSheet().getRange(e.range.rowStart, e.range.columnStart + 1).setValue(new Date()).setNumberFormat('HH:MM:SS - dd/MM ');
}
    • In this modification, TIMESTAMP is not used. The number format is changed and the date object is put into the cell.

    References: