google-apps-scripttimezoneslackreminders

Send Slack notification n days before certain date


With this code, in Apps Script, I could send a slack notification to a channel, when in col E of my spreadsheet there is an expiring date in format (DD/MM/YYYY) GMT+01:00 (Rome Time).


Here if (days_left == 2) I could set the number of days before the expiring date for the notification and it works fine.
The problem I have that this works also if the date is expired, and so, for example if my expiring date is 20/12/2022, and I set if (days_left == 2) and today is 18/12/2022, the notification arrives fine, but if today is 22/12/2022 (so the expiring date 20/12/2022 is already expired) the notification arrives anyway.

What is wrong?
I am trying to change my Timezone, but without success, the notification arrives also for the date already expired.

const sendSlackAlert = () => {
  const reminderText = getReminderText();
  if( reminderText === "" ){
    return;
  }
    var payload = {
      "channel" : "#test",
      "text" : `test message`,
      "attachments": [{
        "text": getReminderText(),
        "mrkdwn_in": ["text"]
      }]
    }
 
  
  var options = {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : JSON.stringify(payload)
  };
 
  var results = UrlFetchApp.fetch( SD_SUBSCRIPTION_WEBHOOK , options);
  
}

const getReminderText = () => {
  var sheet = SpreadsheetApp.getActive().getSheetByName('domains');
  var startRow = 2;
  var numRows = sheet.getLastRow() - 1;
  var numColumns = sheet.getLastColumn();
  var dataRange = sheet.getRange(startRow, 1, numRows, numColumns);
  var data = dataRange.getValues();
  let sendMessageText = "";
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var today = new Date(), // today's date
    exp_date = row[4] // exp date

    var cert_details = 
        {
          company_name:row[0],
          cert_company_name:row[1],
          cert_company_email:row[4]
        };

    var t2 = new Date(exp_date);
    t2.setHours(0,0,0,0);
    var t1 = new Date(today);
    t1.setHours(0,0,0,0);

    var difference_ms = Math.abs(t2.getTime() - t1.getTime());
    var days_left = Math.round(difference_ms/(24*3600*1000));
    
    cert_details.days_left = days_left;
    
    if (days_left == 2) {
      sendMessageText += `\n${cert_details.company_name} will expire in *2 days*`;
    }
 }

  return (sendMessageText == "") ? "" : sendMessageText
}

Solution

  • I have solved removing Math.abs to the function,

    and so:

     var difference_ms = Math.abs(t2.getTime() - t1.getTime());
    

    become

     var difference_ms = (t2.getTime() - t1.getTime());