what I am trying to do is to check to see if the date in column RTC is 90 days past the date in column "Initial Date Seen by Dr." I have already done that using Sheet's formulas. Works perfectly. But then I want to compare this date in column L to see if today date (The computer's date) matches this date in column L. If so, I want to send an email.
So far everything works. But here's one little problem. As you can see in the image below, the first record shows that the date in column RTC is 7/21/20 which doesn't match today's date 5/26/20 but I still receive email for this record saying follow up with the patient.
Is there any fix for this?
function ifItsBeen90Days()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName("PAOS");
range = sheet.getDataRange();
values = range.getValues();
var lR = sheet.getLastRow();
var dateinfo = sheet.getRange('L3:L').offset(0, 0, lR, 1).getValues();
var today = new Date();
var y0 = today.getFullYear();
var m0 = today.getMonth() + 1;
var d0 = today.getDate();
for (var i=0; i<dateinfo.length; i++){
x = Date.parse(dateinfo[i]);
var date = new Date(x);
var y = date.getFullYear();
var m = date.getMonth() + 1;
var d = date.getDate();
if (y0 === y && m0 === m && d0 === d) {
SendItIf90daysPast();
} else {
Logger.log("error:" + i) // difference date
};
};
}
function SendItIf90daysPast(){
const ss = SpreadsheetApp.getActiveSheet();
const dataRange = ss.getDataRange();
const headers = 2;
const dataValues=ss.getRange(3,1,ss.getLastRow()-2,ss.getLastColumn()).getValues();
dataValues.forEach(function(row){
if(row[11] !== "") {
let message = " It's been 90 days since your last visit with the following Patient. " + '\n' +
" Can you please do a follow visit with this Patient? " + '\n' + '\n' +
" Thank you for your time and effort. " + '\n' +
" _______________________________________________________________ " + '\n' +
" Patient#: " + row[0] + '\n' +
" Patient Name: " + row[1] + '\n' +
" P-Code: " + row[2] + '\n' +
" PAO/INF: " + row[3] + '\n' +
" Score 1: " + row[4] + '\n' +
" Score 1 Date: " + new Date(row[5]).toLocaleDateString("en-US") + '\n' +
" Score 2: " + row[6] + '\n' +
" Score 2 Date: " + new Date(row[7]).toLocaleDateString("en-US") + '\n' +
" Tx Plan Entry Date: " + new Date(row[8]).toLocaleDateString("en-US") + '\n' +
" First Date: " + new Date(row[9]).toLocaleDateString("en-US") + '\n' +
" Initial Date Seen by DR: " + new Date(row[10]).toLocaleDateString("en-US") + '\n' +
" RTC: " + new Date(row[11]).toLocaleDateString("en-US") + '\n' +
" Notes: " + row[13];
let email = row[12];
let subject = 'Encrypt: Please Follow Up With Patient:' + " " + row[1] + " Patient #: " + row[0];
MailApp.sendEmail(email, subject, message);
Logger.log(`${subject}: ${message} sent to ${email}`);
}
});
}