1

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!

1

1 Answer 1

3

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.

2
  • Thank you @doubleunary! It worked perfectly! There's actually another thing I'd like to do. In the second column (where it says "null") I'd like to have the timestamp in the format hh:mm:ss. I thought I might be able to figure it out myself based on the response but that wasn't the case. It would be great if you could help me. Thank you in advance! May 27 at 12:56
  • Please ask only one question per post. May 27 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.