How do you format the date in the body of an email from Google Apps Script?

Question

I have an automated email script and in the body of the message is a date which is currently expressing Thu Nov 28 2019 00:00:00 GMT+1300 (NZDT) but I would like it expressed 28 November 2019.

Below is my code. The code regarding the date is:

 var date = sheet.getRange(i, 7).getValue();
 var formattedDate = Utilities.formatDate(date, "dd MMMMM yyyy")

//Email Alerts for H&S Reviews
function sendEmails() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
  var lastRow = sheet.getLastRow();
  var message = spreadsheet.getSheetByName("Email Alerts").getRange(1,1).getValue();

  for (var i = 4;i<=lastRow;i++){

    var emailAddress = sheet.getRange(i, 9).getValue();
    var firstName = sheet.getRange(i, 10).getValue();
    var todaysDate = sheet.getRange(1, 11).getValue();
    var date = sheet.getRange(i, 7).getValue();
    var formattedDate = Utilities.formatDate(date, "dd MMMMM yyyy")
    var task = sheet.getRange(i, 3).getValue();
    var description = sheet.getRange(i, 4).getValue();
    var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",date);
    var subject = "Health & Safety Review Task"; 
    var sendDate = sheet.getRange(i, 11).getValue();
    var sheetDate = new Date(sendDate);
    Sdate=Utilities.formatDate(todaysDate,"GMT+0200","dd-MM-yyyy")
    SsheetDate=Utilities.formatDate(sheetDate,"GMT+0200", "dd-MM-yyyy")
    
    
    if (Sdate == SsheetDate){
      var subject = "Health & Safety Review Task";
      MailApp.sendEmail(emailAddress, subject, messageBody);
      
    }    
  }
}

I would appreciate some help regarding this.

You're already using [`Utilities.formatDate()`](https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format) in your script. You can apply that to generate formatting like "28 November 2019". To get the ordinal suffixes, consider [this answer](https://stackoverflow.com/a/13627586/1329498). — Diego, Nov 28 '19 at 03:29
You're missing the timeZone parameter. `Utilities.formatDate(date, "GMT+0200", "dd MMMMM yyyy")`. — Diego, Nov 28 '19 at 05:08
Can you please add more detail? Was there an error or is it simply not appearing how you want it? — Diego, Nov 28 '19 at 06:12
It's still presenting the same format in the email Thu Nov 28 2019 00:00:00 GMT+1300 (NZDT) — McChief, Nov 28 '19 at 06:33
Did you update your `replace()` calls to use the formatted date? — Diego, Nov 28 '19 at 06:39
@Diego - did you want to present the new code so I could the question as resolved? — McChief, Nov 28 '19 at 06:46

score 2 · Accepted Answer · answered Nov 28 '19 at 06:56

You're already using Utilities.formatDate() in your script. You can apply that to generate formatting like "28 November 2019". To get the ordinal suffixes, consider this answer.

If you're okay with the "28 November 2019" format, then simply change these two lines

var formattedDate = Utilities.formatDate(date, "GMT+0200", "dd MMMMM yyyy");
// ... other code ...
var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",formattedDate);

Your final code would be:

//Email Alerts for H&S Reviews
function sendEmails() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
  var lastRow = sheet.getLastRow();
  var message = spreadsheet.getSheetByName("Email Alerts").getRange(1,1).getValue();

  for (var i = 4;i<=lastRow;i++){

    var emailAddress = sheet.getRange(i, 9).getValue();
    var firstName = sheet.getRange(i, 10).getValue();
    var todaysDate = sheet.getRange(1, 11).getValue();
    var date = sheet.getRange(i, 7).getValue();
    var formattedDate = Utilities.formatDate(date, "GMT+0200", "dd MMMMM yyyy");
    var task = sheet.getRange(i, 3).getValue();
    var description = sheet.getRange(i, 4).getValue();
    var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",formattedDate);
    var subject = "Health & Safety Review Task"; 
    var sendDate = sheet.getRange(i, 11).getValue();
    var sheetDate = new Date(sendDate);
    Sdate=Utilities.formatDate(todaysDate,"GMT+0200","dd-MM-yyyy")
    SsheetDate=Utilities.formatDate(sheetDate,"GMT+0200", "dd-MM-yyyy")


    if (Sdate == SsheetDate){
      var subject = "Health & Safety Review Task";
      MailApp.sendEmail(emailAddress, subject, messageBody);

    }    
  }
}

How do you format the date in the body of an email from Google Apps Script?

1 Answers1