CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

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

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.

Upvotes: 0

Views: 1160

Answers (1)

Diego
Diego

Reputation: 9571

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

    }    
  }
}

Upvotes: 2

Related Questions