Google-apps-script date comparison. Can't compare first day of the month. getMonth()

Question

EDIT: There was no bug. Somehow the project setting in google script was set to the wrong timezone. Ahh... :D

I cant figure out why when I'm collecting dates from my sheet in "var sheetDate" and compare it with "var date" every date works and the email is sent, from 02.01.2020 to 31.01.2020 but not 01.01.2020.

I use .getmonth() to compare so this script can run once a month for years. Is it something special with 01.01 that it doesn't work?

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  // Fetch the range of cells
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  Logger.log(data)

  for (var i in data) {
    var row = data[i];
    var date = new Date();
    var sheetDate = new Date(row[18]);
        if (date.getMonth() == sheetDate.getMonth()){
          var emailAddress = row[19];  // Email
          var message = row[1];       // Info
          var subject = row[11];
          MailApp.sendEmail(emailAddress, subject, message);
          Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
    }    
  }
}

Edit: The log as requested

 [20-01-04 15:03:48:762 CET] Sheetdate:Wed Jan 01 2020 18:00:00 GMT-0500 (EST)
[20-01-04 15:03:48:764 CET] Date:Sat Jan 04 2020 09:03:48 GMT-0500 (EST)
[20-01-04 15:03:48:764 CET] date.getMonth():0
[20-01-04 15:03:48:765 CET] sheetdate.getMonth():0
[20-01-04 15:03:48:837 CET] Sheetdate:Tue Jan 03 2023 18:00:00 GMT-0500 (EST)
[20-01-04 15:03:48:838 CET] Date:Sat Jan 04 2020 09:03:48 GMT-0500 (EST)
[20-01-04 15:03:48:839 CET] date.getMonth():0
[20-01-04 15:03:48:839 CET] sheetdate.getMonth():0
[20-01-04 15:03:48:934 CET] Sheetdate:Thu Jan 02 2020 18:00:00 GMT-0500 (EST)
[20-01-04 15:03:48:935 CET] Date:Sat Jan 04 2020 09:03:48 GMT-0500 (EST)
[20-01-04 15:03:48:936 CET] date.getMonth():0
[20-01-04 15:03:48:936 CET] sheetdate.getMonth():0
[20-01-04 15:03:48:998 CET] Sheetdate:Fri Jan 31 2020 18:00:00 GMT-0500 (EST)
[20-01-04 15:03:48:999 CET] Date:Sat Jan 04 2020 09:03:48 GMT-0500 (EST)
[20-01-04 15:03:48:999 CET] date.getMonth():0
[20-01-04 15:03:49:000 CET] sheetdate.getMonth():0

Image of part of sheet

Is there a date in the first row? Are you using a header row? — JPV
I'm using a header row. Are you thinking of changing the startrow to 2? I've tried that. To clarify: The problem is that if I change any cell where I should put the date to 01.01.2020 or 01.01.2021 etc. it doesn't work. Any other value works between 02.01.2020 to 31. does work fine. — FESV
Could you please try to Logger.log(sheetDate) and Logger.log(date), and also do it with the date.getMonth() and sheetDate.getMonth() and share the result to know how are these dates being treated? Thanks — Kessy
Added log. Thanks for the suggestion. The timezone is wrong I see (I have CET) and, when I run the script I've figured out that it sends emails between the dates 02.01 to 01.02 — FESV
Hi, could you solve your issue figuring out the dates or you still need help? If you have solved the issue, could you please set it up as the answer? Thank you. — Kessy

Cooper Cooper · Accepted Answer · 2020-01-04T01:48:23

function sendEmail() {
  var sh=SpreadsheetApp.getActive();
  var rg=sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn());
  var data=rg.getValues();
  for(var i=0;i<data.length;i++) {
    var toda=new Date().valueOf();
    var dt=new Date(data[i][18]);
    var s=new Date(dt.getFullYear(),dt.getMonth(),1).valueOf();//beginning of month
    var e=new Date(dt.getFullYear(),dt.getMonth()+1,0).valueOf();//end of month
    if (toda>=s && toda<=e){
      var emailAddress=data[i][19];  
      var message=data[i][1];       
      var subject=data[i][11];
      MailApp.sendEmail(emailAddress, subject, message);
      Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
    }    
  }
}

Google-apps-script date comparison. Can't compare first day of the month. getMonth()

2 Answers