Choose format for new Date() in Google Apps Script

Question

I have tried almost all suggestions on the internet, yet nothing seems to work. Maybe someone can help a newbie out. I am using this at the end of my script:

sheet.appendRow([new Date(), null, text]);

and it works perfectly for my needs, but it shows the date in the format dd.MM.yyyy hh:mm:ss.

I, however, want it in the format dd.MM.yyyy. I am aware that I can choose the format within the sheet but that's just a way to display it differently and it messes up my =unique function later on.

I need a way to just enter the date without having to do any additional manipulations. I thought .setNumberFormat would be the way to go but this didn't work either: sheet.appendRow([cell.setValue(new Date()).setNumberFormat("dd.MM.yyyy"), text]);

What am I doing wrong? Any help will be greatly appreciated!

Answer 1

This should do the trick:

  const values = [[new Date(), null, text]];
  const firstBlankCell = sheet.getRange(sheet.getLastRow() + 1, 1);
  firstBlankCell
    .offset(0, 0, values.length, values[0].length)
    .setValues(values)
    .setNumberFormat('dd.MM.yyyy');

See this answer for an explanation of how date and time values work in spreadsheets.