with this script I can send a salck notification but I would activate it only if in column 26 there is the text START.
const sendMessageToSlack3 = () => {
const reminderText = getReminderText3();
if( reminderText === "") {
return;
}
var payload = {
"channel" : "#XXX",
"username" : "",
"icon_url" : reminderText,
"text" : `:memo: *ATTENZIONE!!!* :face_with_monocle:`,
"attachments": [{
// "text": `:memo: ${quotes[quoteNumber].quote}`,
"text": getReminderText3(),
"type": "image",
"image_url": "",
"footer": "<NOTIFICATION>",
"mrkdwn_in": ["text"]
}]
}
var options = {
"method" : "post",
"contentType" : "application/json",
"payload" : JSON.stringify(payload)
};
var results = UrlFetchApp.fetch( SD_SUBSCRIPTION_WEBHOOK3 , options);
}
const getReminderText3 = () => {
var sheet = SpreadsheetApp.getActive().getSheetByName('XXXX');
var startRow = 2; // Ignore the cloumn hedings row
var numRows = sheet.getLastRow() - 1; // Get the valid number of rows that has content with excluding heading row
var numColumns = sheet.getLastColumn(); // Get the valid number of column that has content
//Get data range dynamically and will return a 2-D array
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];
//console.log(row[3]);
var today = new Date(), // today's date
exp_date = row[31] // exp date
var cert_details =
{
domain_name:row[0],
cert_provider_name:row[1],
account: row[2],
cert_manager:row[9],
cert_manager_email:row[4]
};
//Remove the time part from the date
var t2 = new Date(exp_date);
t2.setHours(0,0,0,0);
var t1 = new Date(today);
t1.setHours(0,0,0,0);
//Calculate the ms difference between two date
var difference_ms = (t2.getTime() - t1.getTime());
// 24*3600*1000 is milliseconds in a day
var days_left = Math.round(difference_ms/(24*3600*1000));
//Put the days_left to cert_details array
cert_details.days_left = days_left;
if (days_left == 4) {
sendMessageText += `\n USER: ${cert_details.cert_manager} \n company: ${cert_details.domain_name} remains *4 days\n`;
}
}
return (sendMessageText == "") ? "" : sendMessageText
// return sendMessageText;
}