CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
J.Mapz
J.Mapz

Reputation: 511

Not working: Code to send email reminder based on date

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

Upvotes: 2

Views: 350

Answers (1)

Tanaike
Tanaike

Reputation: 201358

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:

Upvotes: 2

Related Questions