CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Meysam
Meysam

Reputation: 18177

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

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)

Upvotes: 0

Views: 2784

Answers (2)

Kuldeep Singh
Kuldeep Singh

Reputation: 79

If you running loop, format this way :

var date = utilities.formatDate(new Date(row[3]),"GMT","dd/MMM/yyyy") 
//row[3] is colounm number in data array where is your dates are

otherwise without loop :

var date = utilities.formatDate(new Date(),"GMT","dd/MMM/yyyy")

Upvotes: 0

Marios
Marios

Reputation: 27400

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);
}

Upvotes: 3

Related Questions