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