0

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?

enter image description here

    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}`);
        }
      });
    }
mrRay
  • 77
  • 1
  • 9

2 Answers2

2

If you want to check if a date is the same in one column as it is in another, or even if a particular date is 90 days after / before another date, I recommend creating additional columns (which can be hidden) for this purpose. Why? Well, once you grab the date, you (can) actually get the day before, see here for more infos which is an annoying issue.

So your for loop would then create the condition like this:

// lets assume column 15 contains your date check logic

function ifItsBeen90Days(){
  const rows = SpreadsheetApp.getActive().getSheetByName("PAOS").getDataRange().getValues();
  rows.forEach( (row, index) => {
    const doesConditionApply = row[16]; // 15th col, is 16th index
    // skip if the value is not what you are looking for
    if( doesConditionApply != true ){
      return
    }        

    SendItIf90daysPast()
  });
}
Neven Subotic
  • 1,399
  • 1
  • 6
  • 18
  • Oh hi. Thanks for your reply bud. So, the problem right now with this code is that it sends out emails if the RTC column's date is 3 months into the future. It does check if the RTC column has a date that is 3 months into the future but it doesn't care, it would just send email if RTC column has a date, lets say for august. Which is something that needs to happen as soon as we reach the date in august.not now. – mrRay May 26 '20 at 19:44
  • I don't know how to thank you my friend. I am at work right now. But I will try this code tonight and will let you know if it works. Thank you so much buddy. You've been the greatest help. – mrRay May 26 '20 at 19:52
  • I still cannot quite understand. Please provide a story-line example or something similar. – Neven Subotic May 26 '20 at 19:52
  • 1
    No worries, people help me, I help you, you help others. That is what makes us family. – Neven Subotic May 26 '20 at 19:53
  • Basically I used a your Google Sheet's formula you gave me in another post. I checked to see if the date in Column: "Initial Date Seen By Dr" is empty or not. If not empty, I added 90 days to this date and put that date into RTC column. Now, the function is supposed to check if Today's date is = to RTC's 90 days added date. For example, today is 5/26/20 + 90 = 8/26/20. Well, today is not 8/26/20 if not, then the record should be escaped and no emails should be sent. But as of now, the code wouldn't check if today's date = what's in RTC. it just sends out the email. – mrRay May 26 '20 at 19:55
  • Ok, what I did was I created another column which just calculates the difference between today and RTC is the days. This is simple `=RTC_Column - TODAY() = 90`. This formula returns TRUE or FALSE. Based off that formula, you can then either send out the E-Mail or not, see my answer above. In my answer I assume you will place this formula in column 15, but obviously you can use any column you want. – Neven Subotic May 26 '20 at 20:06
  • This what i did which works perfectly. I took your advise with a little modification. I got Col K = initial visit date. Col L = this formula: =IF(ISDATE(K3), K3+90, ""). Then Col M with Today() function. Then finally Col N with this formula: =if($M3=$L3, "true"). Now, in any case, if follow up date is not today's date meaning 90 days after the initial date, I don't get a email by setting a condition in my code: if(row[13] == "true") { "SEND MAIL IF NOT, STOP" }. I should've listened to your advise sooner. Because GS already had the wheel. I should've stop reinventing it. Thank you bud. – mrRay May 27 '20 at 05:01
0
function ifItsBeen90Days() { 
  const ss=SpreadsheetApp.getActive();
  const shsr=3;
  const sh=ss.getSheetByName("PAOS");
  const rg=sh.getDataRange();
  const v=sh.getRange(shsr,11,sh.getlastr()-shsr+1,1).getValues();
  const dt=new Date();
  const today=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate());
  const t90v=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()-90).getTime();
  const t91v=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()-91).getTime();
  const tv=today.getTime();
  v.forEach(function(r,i){
    var d=new Date(r[0]);
    var dv=new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf();
    var diff=dv-tv;
    if(diff>=t90v && diff<t91v){
      //it has been 90 days
    }
  });
}
Cooper
  • 59,616
  • 6
  • 23
  • 54