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

Question

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

Karl_S · Accepted Answer · 2016-11-29T22:23:40.227

1

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.

edited Nov 29 '16 at 22:23

answered Nov 29 '16 at 19:06

Karl_S

3,364
2
19
33

Hi, Is not working for the moment not show any row. [Example Spreadsheet](https://docs.google.com/spreadsheets/d/1Om6334YNxmNfTIGrARShNY0VD74zdNlKprem_UJneec/edit?usp=sharing) - The script is Email > EmailV2 – LAD Service Desk Nov 29 '16 at 21:57
Great... NOW is working... I thinks was a Format Problem... Thanks So much – LAD Service Desk Nov 29 '16 at 22:30
I duplicate this code for Open cases, but when I ran it got the error getfullyear not found – LAD Service Desk Jan 09 '17 at 23:59
@LADServiceDesk MAke sure you are passing Dates to the testDate function. That error will occur is the passed value is not a date type, but a string. – Karl_S Jan 10 '17 at 13:12
I mean, when run it with close date working... but when I try to run with open cases didn't work. "TypeError: No se puede encontrar la función getFullYear en el objeto" mean can't found the function getFullYear. Also I apply the formating manually to all cell but didn't work. The same formatting as Close Date column – LAD Service Desk Jan 10 '17 at 14:05
I think i found the problem... when the filter said Closed work but change it to Open didn't work... but If I modify the Open to Opened works... Is possible exist an error with less than 4 letter to filter? – LAD Service Desk Jan 10 '17 at 14:29
@LADServiceDesk When you copied the code, did you edit the line `if((data3[i]=='Closed') && (testDate(data4[i][0], date))) supportTable += "" + ""+data1[i]+"" ` changing 'Closed' to 'Open' or 'Opened'? From your comment I am guessing it is 'Opened' – Karl_S Jan 10 '17 at 14:38
Status column said "Open" without " if((data3[i]=='Open') && (testDate(data4[i][0], date))) - Not Work give me error getFullYear. but If i change the data in column status from "Open" to "Opened" and the script change if((data3[i]=='Opened') && (testDate(data4[i][0], date))) work... The problem only appear with Open ticket... with Closed ticket I don't have any issue... I think meaby the problem is the count letter because Open have 4 letter and Opened 6 letter and work.. – LAD Service Desk Jan 10 '17 at 14:56
But I have other column with only 1 letter, and use the same function and work... For search open tickek the filter is if((data3[i]=='Opened') && (data5[i]=='Y') && (testDate(data4[i][0], date))) and work but didn't work if the status is Open... the value in the column status is Open... but did't work i change it to Opened and work... I don't know if a formatting text problem. – LAD Service Desk Jan 10 '17 at 15:07
3 = signs would be better and may have worked. == is an Abstract comparison and === is a Strict comparison. – Karl_S Jan 10 '17 at 15:08
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132821/discussion-between-lad-service-desk-and-karl-s). – LAD Service Desk Jan 10 '17 at 16:02
HI Thanks so much.... is working very well... But I have one more question is posible extract only the row between 8:30 AM Yestarday to 8:30 AM Today.... This is because my report should be send only with the issue returned bettween 8:30AM to 8:30AM – LAD Service Desk Feb 17 '17 at 03:32
That would be a straight date comparison. See [this thread](http://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) – Karl_S Feb 17 '17 at 14:04

score 0 · Answer 2 · answered Nov 29 '16 at 20:07

Since the spreadsheet is storing the value in date format, you can use simple date methods to compare the stored date with today's date instead of converting them to a string.

  var today = new Date();
  today.setHours(0,0,0,0);

  var status = Support.getRange(3, 3, lastrow).getValue(); // Status Column
  var closed = Support.getRange(3, 9, lastrow).getValue(); // Close Date Column
  closed.setHours(0,0,0,0);

  if ((status === "CLOSED") && (today - closed === 0))
    // do processing

For the momment is not working return error with formating date — LAD Service Desk, Jan 09 '17 at 23:58

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

2 Answers2