How to set current date without timestamp in google sheets using Apps Script?

Question

How can we set the current date in Google Sheets without timestamp?

I am using the following script:

dateCell.setValue(new Date(new Date().setHours(0,0,0,0)));

And this is what is set for the cell:

2/23/2021 6:00:00

However, when I press "Ctrl + :" to automatically insert current date in a cell, the following is inserted:

2/23/2021

What script can I use to only set the date without the timestamp?

Please note that I recorded a macro when pressing "Ctrl + :" and the following code was generated:

var date = new Date();
date.setHours(0, 0, 0, 0);
spreadsheet.getActiveRangeList().setValue(date)

soMario soMario · Accepted Answer · 2021-02-23T11:21:38

Solution:

You can specify your desired format with the formatDate(date, timeZone, format) function:

Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");

This should work given spreadsheet is defined already in your current code:

const ss = SpreadsheetApp.getActive();
const timezone = ss.getSpreadsheetTimeZone();
const date = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");
spreadsheet.getActiveRangeList().setValue(date)

Minimal Reproducible Example:

Add the date to cell A1:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const timezone = ss.getSpreadsheetTimeZone();
  const sh = ss.getSheetByName('Sheet1'); // put the name of your sheet
  const date = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");
  sh.getRange('A1').setValue(date);
}

How to set current date without timestamp in google sheets using Apps Script?

1 Answers