javascript - Google 脚本 - 仅当当天等于今天时才从电子表格中提取行数据

Question

情况:
我有以下脚本，可以向我发送一封包含已关闭票证#的电子邮件。

问题:

该脚本向我发送任何截止日期的所有已关闭票证。我需要你只向我发送当天的封闭门票。

关闭日期列的格式为日期“29/11/2016 12:34:00”。

  function emailv2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Support = ss.getSheetByName("Historic");
  var lastrow = Support.getLastRow();

  var data1 = Support.getRange(3, 1, lastrow).getValues(); // Ticket# Column
  var data2 = Support.getRange(3, 2, lastrow).getValues(); // Comment Column
  var data3 = Support.getRange(3, 3, lastrow).getValues(); // Status Column
  var data4 = Support.getRange(3, 9, lastrow).getValues(); // Close Date Column

  var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm"); // Funtion Date + Format

  var report = " "
  var supportTable ="";
  for(var i in data3){
    if(data3[i]=='Closed') supportTable += "<tr><td style='color:blue;padding:5px'>" + "<a href=http://otrserver/otrs/index.pl?Action=AgentTicketZoom;TicketNumber="+data1[i]+">"+data1[i]+"</a>" 
                                      + "</td><td style='padding:5px'>" + data2[i] 
                                      + "</td><td style='padding:5px'>" + data3[i] 
                                      + "</td></tr>";
  }

  report ="Estimados: <br><br> Les envio el shift <br><br><table style='border-collapse:collapse;'border = 1 cellpadding = 5; align='center'><tr><th style='background-color:black;color:white'>Ticket#</th><th style='background-color:black;color:white'>Comment</th><th style='background-color:black;color:white'>Status</tr>" + supportTable + "</table>";

  MailApp.sendEmail("example@gmail.com",
                    "Shift Change -" + date, 
                    report, 
                    {name: 'Automatic Emailer Script',
                    htmlBody: report});
}

Answer 1

我添加了一个函数来测试日期并编辑了 if 语句来使用它:

function emailv2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Support = ss.getSheetByName("Historic");
  var lastrow = Support.getLastRow();

  var data1 = Support.getRange(3, 1, lastrow).getValues(); // Ticket# Column
  var data2 = Support.getRange(3, 2, lastrow).getValues(); // Comment Column
  var data3 = Support.getRange(3, 3, lastrow).getValues(); // Status Column
  var data4 = Support.getRange(3, 9, lastrow).getValues(); // Close Date Column

  var date = new Date()

  var report = " "
  var supportTable ="";
  for(var i in data3){
    if((data3[i]=='Closed') && (testDate(data4[i][0], date))) supportTable += "<tr><td style='color:blue;padding:5px'>" + "<a href=http://drsc.nubicua.net/otrs/index.pl?Action=AgentTicketZoom;TicketNumber="+data1[i]+">"+data1[i]+"</a>" 
                                      + "</td><td style='padding:5px'>" + data2[i] 
                                      + "</td><td style='padding:5px'>" + data3[i] 
                                      + "</td></tr>";

  }

  report ="Estimados: <br><br> Les envio el shift <br><br><table style='border-collapse:collapse;'border = 1 cellpadding = 5; align='center'><tr><th style='background-color:black;color:white'>Ticket#</th><th style='background-color:black;color:white'>Comment</th><th style='background-color:black;color:white'>Status</tr>" + supportTable + "</table>";

  MailApp.sendEmail("example@gmail.com",
                    "Shift Change -" + date, 
                    report, 
                    {name: 'Automatic Emailer Script',
                    htmlBody: report});
}

function testDate (dateToTest1,dateToTest2) {
    return (dateToTest1.getFullYear() === dateToTest2.getFullYear()) &&
           (dateToTest1.getMonth() === dateToTest2.getMonth()) &&
           (dateToTest1.getDate() == dateToTest2.getDate());
}

旧回复

更改 IF 也可以比较日期。如果两者都是日期，请将它们格式化为仅日期而不是时间

var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy"); // Function Date + Format

然后将 If 更改为:

if((data3[i]=='Closed') && (date === data4))

请注意，这尚未经过测试，因为我没有您的电子表格的副本，但应该可以满足您的要求。