2

Wazzup guys, thanks to this website, I was able to get a code that allows sending email reminders. Now, I wanted to send only reminders based on date, particularly when the Reminder date matches the date today. I found out this awesome post, but was still not able to make it work.

I tried several attempts to google similar cases, but to no avail. The code is actually working without the "based on date" condition, but I can't seem to work it out. I tried changing formats, used the "Utilities" in the script, but still nothing.

Below is how the sheet looks like and as well the code. So on the current data, I should be receiving an email reminder for the "Go to gym" task, but I don't get any (I've included my correct email in column A though).

Where could have this gone wrong? Hoping for your insights and guidance on this. Thanks!

enter image description here

 var EMAIL_SENT = "Yes";

function sendEmails() {
  var now = new Date().toLocaleDateString();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var startRow = 2;  
  var sheet = ss.getSheetByName ('Tasks')
  var numRows = sheet.getLastRow();
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; 
    var message = row[2];       
    var ReminderDate = row[3];
    var emailSent = row[4];
    var subject = "Task reminder: "+row[1];
    if (ReminderDate != now)
      continue;

    if (emailSent != EMAIL_SENT) {  
      message = "Good day! This is to remind you of a spefic task: "+ message +". \n\nThis reminder was triggered by the task monitoroing sheet: "+ ss.getUrl()      
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 5).setValue(now);
      SpreadsheetApp.flush();
      }
     }
    }
Tanaike
  • 181,128
  • 11
  • 97
  • 165
J.Mapz
  • 511
  • 2
  • 12
  • 2
    In your script, when `row[3]` is the date object, `if (ReminderDate != now)` is always `false`. So how about Modifying `var ReminderDate = row[3];` to `var ReminderDate = row[3].toLocaleDateString();`. Or how about modifying from `var now = new Date().toLocaleDateString()` and `var ReminderDate = row[3];` to `var now = new Date().getTime()` and `var ReminderDate = row[3].getTime;`? If this didn't resolve your issue, I apologize. – Tanaike Aug 01 '19 at 06:46
  • @Tanaike, the first suggestion you provided is working, but how come? Can you link me to a good read on this? Thanks! Likewise, while the code is able to perform well, I'm encountering the following error: `TypeError: Cannot find function toLocaleDateString in object . (line 16, file "Code")`. I encounter this with `var ReminderDate = row[3].toLocaleDateString();`. What does this seem to mean? Thanks! – J.Mapz Aug 01 '19 at 06:55
  • Thank you for replying. I apologize for the inconvenience. About your error message, I cannot understand about your current script. So can you provide the script for replicating your issue? By this, I would like to confirm it. About `toLocaleDateString()`, how about this document? https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString – Tanaike Aug 01 '19 at 06:58
  • @Tanaike, thanks! Well, the code is working, so I'm not really much concerned about the error. If you need upvoting, just post in your answers so I could tag it as the solution. Thanks! – J.Mapz Aug 01 '19 at 07:26
  • Thank you for replying. I'm glad your issue was resolved. I posted it as an answer. Could you please confirm it? From your replying, I could confirm that the first suggestion worked. So I proposed it as an answer. – Tanaike Aug 01 '19 at 07:33

1 Answers1

2

Issue:

At Spreadsheet, when the date is used, the value retrieved by getValue() and getValues() is the date object. In your script, when row[3] of var row = data[i] is the date object, if (ReminderDate != now) is always false.

Modified script:

In order to avoid above issue, please modify as follows, because now of if (ReminderDate != now) is var now = new Date().toLocaleDateString();.

From: 
var ReminderDate = row[3];
To: 
var ReminderDate = row[3].toLocaleDateString();

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165