LAD Service Desk LAD Service Desk - 8 days ago 5
Javascript Question

Google Script - Extract row data from spreadsheet only if the day equal today

Situation:

I have the following script with send me an email with the closed ticket#.




Problem:




This script sends me all closed tickets of any closing date. I need you to send me only the closed tickets of the day.




Close Date Column have this format date "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

I added a function to test the date and edited the if statement to use it as well:

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

OLD RESPONSE

Change your IF to also compare the dates. Provided both are Dates, format them to just the date and no time

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

Then change the If to:

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

Note that this is untested since I don't have a copy of your spreadsheet, but should get you what you are after.