I have modified a code I found online to look at a google sheet (sample below) and send an email when an employee's work permit is about to expire in 30 days.
However, I would like to modify it to send one email (to one email address) with a table of all the employees whose permit is about to expire instead of one email per employee. Please help !
function emailReminderAlert() {
// getting data from spreadsheet
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var startRow = 2; // Ignore the column headings and frozen rows
var numRows = sheet.getLastRow() - 1; // Get the last number of row that has content with excluding header rows
var numColumns = sheet.getLastColumn(); // Get the last number of column that has content.
//Get data range dynamically
var dataRange = sheet.getRange(startRow, 1, numRows, numColumns);
var data = dataRange.getValues();
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[3] // exp date
var cert_details =
{
employee_name:row[0],
passport_number:row[1],
renewal_date: row[2],
email_primary: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 = Math.abs(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;
cert_details.exp_date = Utilities.formatDate(new Date(exp_date), "GMT+1", "dd/MM/yyyy")
if (days_left == 30) {
console.log(cert_details.employee_name+"'s permit is expiring in 30 days");
sendEmail(cert_details);
}
sendEmail(cert_details);
/*
else if (days_left == 3) {
console.log(cert_details.employee_name+" expired in 3 days");
sendEmail(cert_details);
}
else if (days_left == 7) {
console.log(cert_details.employee_name+" expired in 7 days");
sendEmail(cert_details);
}
else if (days_left == 30) {
console.log(cert_details.employee_name+" expired in 30 days");
sendEmail(cert_details);
}
*/
}
}
function sendEmail(cert_details){
//Get the html email template
var templ = HtmlService.createTemplateFromFile('EmailTemplatetest');
templ.cert_details = cert_details;
var message = templ.evaluate().getContent();
MailApp.sendEmail({
to: cert_details.email_primary,
subject: "Permit Expiry Reminder: "+cert_details.employee_name+"'s permit expires in " +cert_details.days_left + " day(s)",
htmlBody: message
});
}
I tried to solve your problem, It might give you an overall idea about the solution, rest you may figure out or ask me for more explaination.
// the input users json object or array
var users = [
{
"id": 1,
"email": "123@gmail.com",
"expire": "2019-01-01",
},
{
"id": 2,
"email": "234@gmail.com",
"expire": "2019-12-01",
},
{
"id": 3,
"email": "12423@gmail.com",
"expire": "2021-12-01",
}
]
// create the user stack for storing the users that are about to expire
var userStack = [];
users.forEach(function(user) {
var expire = new Date(user.expire);
// get the day difference between the current date and the user's expire date
// (1000 * 3600 * 24) is to convert the milliseconds to days
// Math.abs is to get the absolute value of the difference not the negative value
var dayDiff = Math.abs((expire.getTime() - new Date().getTime()) / (1000 * 3600 * 24));
// comparison of minimum days
if(dayDiff < 30) {
userStack.push(user);
}
});
// do something with that userStack
console.log(userStack);
// i am gonna create a sample email message
var emailMessage = "";
// loop through the userStack
userStack.forEach(function(user, index) {
// this will create something like a line by line message table
emailMessage += index.toString() + ": User " + user.id + " will expire in " + user.expire + "\n";
});
// now you can send the email message to a perticular email address
MailApp.sendEmail({
to: "<Your Email Address>",
subject: "The Expiration tables for the users",
htmlBody: emailMessage
});