自动通过电子邮件发送符合特定条件的谷歌工作表的所有行

Question

我修改了在网上找到的一个代码，以便在员工的工作许可证30天后到期时查看谷歌表单(下面的示例(并发送电子邮件。

然而，我想修改它，发送一封电子邮件(到一个电子邮件地址(，其中包含许可证即将到期的所有员工的表格，而不是每个员工一封电子邮件。请帮忙！

谷歌表单格式示例

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

Answer 1

我试着解决你的问题，它可能会让你对解决方案有一个整体的想法，其余的你可能会想出来或要求我提供更多解释。

// the input users json object or array
var users = [
{
"id": 1,
"email": "[e-mail]",
"expire": "2019-01-01",
},
{
"id": 2,
"email": "[e-mail]",
"expire": "2019-12-01",
},
{
"id": 3,
"email": "[e-mail]",
"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
});