How to format Date in Google Script

Question

I am trying to write a small Google Script which takes data from some cells of Google Sheet and Paste it in the GMAIL. However, while pasting the 'Date Values' it always displays it in the following manner:-

Mon Apr 15 2019 00:00:00 GMT+0530 (IST)
Fri Apr 26 2019 00:00:00 GMT+0530 (IST)

But I need the dates in an appropriate way i.e.

"Mon Apr 15 2019" or
"Fri 04/26/2019"

I gone through these possible options i.e. Utilities.formatDate & .Split but somehow I am not able to write these codes appropriately. Can you please help me with this matter. Below I have mentioned the entire issue in detail.

My Code

function temp2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName('Sheet1');
  var templateSheet = ss.getSheetByName('Sheet2');
  var emailTemplate2 = templateSheet.getRange("G1").getValue();
  var rec = templateSheet.getRange("C1").getValue();
  var sub = templateSheet.getRange("D1").getValue();
  var date = templateSheet.getRange("E1").getValue();
//  Logger.log(rec);
  MailApp.sendEmail(rec, sub, emailTemplate2);

}

here var date = templateSheet.getRange("E1").getValue(); is the part of code which picks value of date.

Do let me know if you need more details in this regard Regards, Alok

ross · Accepted Answer · 2019-06-24 11:11:57Z

3

Requirement:

Format date value from cell in Google Apps Script.

Solution:

Pass value to date constructor new Date() then format using Utilities.formatDate().

// pass date to date constructor
var date = new Date(templateSheet.getRange("E1").getValue());

// "Mon Apr 15 2019" example
var formattedDate = Utilities.formatDate(date, "GMT+0", 'E MMM dd yyyy');

// "Fri 04/26/2019" example
var formattedDate = Utilities.formatDate(date, "GMT+0", 'E MM/dd/yyyy');

Note: I've set this to timezone "GMT+0" by default, you can change this to whichever time zone you need.

References:

new Date() for date constructor.
Utilities.formatDate() for formatting dates in Google Apps Script.
SimpleDateFormat for date format strings.

edited Jun 24, 2019 at 11:11

answered Jun 24, 2019 at 11:06

ross

2,7842 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

TheMaster Over a year ago

getValue() returns date object. You don't need a constructor.

Alok Nagar Over a year ago

Hi, if I use the following script, it gives an error of "Cannot find method formatDate(string,string,string)." Utilities.formatDate(templateSheet.getRange("E3").getValue(),"GMT+0", 'E MMM dd yyyy')

ross Over a year ago

@TheMaster Pretty sure you still need the constructor, see error above...

ross Over a year ago

@AlokNagar try passing the date to the new Date() constructor before you format it, like in my answer.

TheMaster Over a year ago

@ross I would agree with you. But, if they are datestrings, the constructor may or may not change it to a date. parsing of date strings with the Date constructor (and Date.parse, they are equivalent) is strongly discouraged due to browser differences and inconsistencies.. I think the best way is to fix it in the spreadsheet.

|

Collectives™ on Stack Overflow

How to format Date in Google Script

1 Answer 1

Requirement:

Solution:

References:

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Requirement:

Solution:

References:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related