基于到期日的AutoEmail使用谷歌脚本的-_Reminder邮件

Question

Snapshot of the google spreadsheet

​

 function emailReminder() {
  
    //For testing purposes
    //You can view the log by typing CTRL+E after you run the script. 
  Logger.log(msg);
  
  // grab the Google Sheet object
  var googleSheets = SpreadsheetApp.getActiveSpreadsheet();
  // activates the first sheet
  SpreadsheetApp.setActiveSheet(googleSheets.getSheets()[0]);
  // grabs the sheet
  var currentSheet = googleSheets.getActiveSheet();
  
  // find the last row
  var lastRow = currentSheet.getLastRow();
 
  // The first row starts at row 2 to account for headers
  var startRow = 2;
 
  // get E column data
  var range = currentSheet.getRange(2,5,lastRow-startRow+1,1 );
  var numRows = range.getNumRows();
  var remainingDays = range.getValues();
  
  // get A column data
  range = currentSheet.getRange(2, 1, lastRow-startRow+1, 1);
  var columnA = range.getValues();
  
  // get D column data
  range = currentSheet.getRange(2, 4, lastRow-startRow+1, 1);
  var columnD = range.getValues();
  
  var count = 0;
  var msg = "";
  
  // Loop column E
  for (var i = 0; i <= numRows - 1; i++) {
    var daysLeft = remainingDays[i][0];
    if(daysLeft < 1) {
      // Checks to see if a cell value in column E less than 1
      var reminder = columnA[i][0];
      **var reminderdate = columnD[i][0];**
             
      msg = msg + "* Don't Forget: "+reminder+" is due on **"+reminderdate+"** which is "+daysLeft+" days.\n";
      count++;
    }
  }
  
   // Loop column E
  for (var i = 0; i <= numRows - 1; i++) {
    var daysLeft = remainingDays[i][0];
    if(daysLeft == 1) {
      // Checks to see if a cell value in column E equals 1
      var reminder = columnA[i][0];
       
      msg = msg + "Don't Forget: "+reminder+" is due tomorrow.\n";
      count++;
    }
  }
  // For multiple email addresses, use a comma to separate each email.
  if(count) {
    MailApp.sendEmail("yyy@gmai.com", 
        "Article Reminder", msg);
  }
   
}

​

输出将以电子邮件的形式出现

Don't Forget:1231 is due onSun Feb 09 2020 00:00:00 GMT+0530 (India Standard Time) which is -67 days.

我只想要

Don't Forget:1231 is due on Sun Feb 09 2020 which is -67 days

以在发送邮件时反映在输出中。

另外，当邮件发送时，详细信息可以是表格格式吗？(需要编写代码)

Answer 1

您可以设置从工作表中获得的日期的格式，以便它只使用dd-MM-yyyy。为此，请使用Date()方法和一个Utitilies.formatDate的应用程序脚本。

例如：

  var newDate = new Date(columnD[i][0]);
  var reminder = Utilities.formatDate(newDate, "IST", "dd-MM-yyyy");

然后，reminder将具有类似11-Jan-2020的格式。