Comparing Dates in Google Sheets

0
votes

I am new to Java and to Google Script Editor. I have a custom CRM spreadsheet in google sheets, and would like to set up reminder emails based on regularly scheduled follow-up dates. I'm having trouble with the code. I think the trouble may be due to the fact that I'm trying to compare a date to a string, but I can't figure out how to get it to work.

The goal is to send off an email when the date for follow-up matches today's date. The date for follow-up is calculated based on a formula.

Even when the log reads:

[16-07-28 13:38:06:549 PDT] Date is Thu Jul 28 2016 00:00:00 GMT-0700 (PDT)

[16-07-28 13:38:06:549 PDT] Today is Thu Jul 28 2016 00:00:00 GMT-0700 (PDT)

My If statement if (date == todayFull) doesn't work. Here's the code:

function sendEmails() {
  var ss = SpreadsheetApp.openById("number");
  var sheet = ss.getSheetByName("Script");
  var startRow = 2;  // First row of data to process

  var lastRow = sheet.getLastRow();   
  var lastCol = sheet.getLastColumn();


  // Fetch the range of cells
  var dataRange = sheet.getRange(2, 1, lastRow, lastCol);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {

    var row = data[i];
    var date = row[32];
    var todayFull = new Date();
    todayFull.setHours(0);
    todayFull.setMinutes(0);
    todayFull.setSeconds(0);

    Logger.log("Date is "+date);
    Logger.log("Today is "+todayFull);

   if (date == todayFull) {
    Logger.log("This is a test. The Date is Today");
    //  var emailAddress = row[28];  // Email column
    //  var groupName = row[3];       // Group Name column
    //  var subject = "Follow up with this group right now!";
    // MailApp.sendEmail(emailAddress, subject, groupName);
    };

  };
}
2
javaemaildategoogle-sheets

2 Answers

1
votes

Thanks for the help. The first answer ended up working most of the way. Using .getDate() helped, but I also had to add arguments for month and year. Here's the code I ended up with:

function sendEmails() {
  var ss = SpreadsheetApp.openById("");
  var sheet = ss.getSheetByName("");
  var startRow = 4;  // First row of data to process

  var lastRow = sheet.getLastRow();    //Get the last row of data to be processed
  var lastCol = sheet.getLastColumn();

  var dataRange = sheet.getRange(2, 1, lastRow-3, lastCol);
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {

    var row = data[i];
    var date2 = new Date(row[24]); // creates a new Date (date in string) object
    var todayFull2 = new Date();        // creates a new Date (now) object

   if (date2.getDate() == todayFull2.getDate() && date2.getMonth() == todayFull2.getMonth() && date2.getYear() == todayFull2.getYear()) {

etc

0
votes

You're comparing two different data types:

var date      = row[32];    // reads in a String object
var todayFull = new Date(); // creates a new Date (now) object
...

if (date == todayFull) {    // compares Date with String
...
}

You might be better off creating the Date object when you read the value from your Sheet, and then comparing the actual dates in milliseconds (at time 00:00:00:00 of given date) of those Date objects, as you appear to be intending to do:

var date      = new Date(row[32]); // creates a new Date (date in string) object
var todayFull = new Date();        // creates a new Date (now) object
todayFull.setHours(0,0,0,0)        // sets time to midnight of current day

...

// compare Date with Date
if (date.getTime() == todayFull.getTime()) { 
...
}

See MDN's excellent documentation on Javascript's Date.