google-apps-script - 如何格式化来自 Google Apps Script 的电子邮件正文中的日期？

Question

我有一个自动电子邮件脚本，邮件正文中是一个当前正在表达的日期，Thu Nov 28 2019 00:00:00 GMT+1300 (NZDT)但我希望它表达出来28 November 2019。

下面是我的代码。关于日期的代码是：

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

我将不胜感激有关此的一些帮助。

标签： google-apps-scriptgoogle-sheets

Answer 1

您已经在脚本中使用了Utilities.formatDate()。您可以应用它来生成像“2019 年 11 月 28 日”这样的格式。要获得序数后缀，请考虑这个答案。

如果您对“2019 年 11 月 28 日”格式没问题，那么只需更改这两行

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

您的最终代码将是：

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

    }    
  }
}