我有一個 Excel 電子表格，其中包含我團隊每個成員的所有認證的到期日期。隨著到期日的臨近，單元格會根據證書的剩余有效期自動改變顏色。
我要做的是在認證將在 90 天或更短時間內到期時發送電子郵件通知。
function sendEmail() {
// EMAIL Check & Date
// Fetch Cert Dates & Employees
var CertExpiration = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,3,11,9); //All Cert Dates on Sheet
var CertDate = CertExpiration.getValue(); // Get Certificate Date as a value
var Employee = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,2,9,1); //Retrieve list of employee Names
var EmployeeName = Employee.getValue(); // Set variable for email notification
var Today = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(2,2,1,1); // Sets Today to Today() on sheet
// Check Cert Expiration
if (CertDate - Today < 90);
{
// Fetch the email address
var emailAddress = '[email protected]';
// Send Alert Email.
var message = 'Certificate for ' EmployeeName ' expiring soon'; //
var subject = 'CERT EXPIRING SOON | CV CERT TRACKING';
MailApp.sendEmail(emailAddress, subject, message);
}
}
如果可能，我希望這封電子郵件包含符合條件（CertDate - Today <90）的所有 EmployeeName 的串列。此外，一種防止電子郵件被多次發送的方法。目前，電子郵件發送成功，但僅包含第一個參考的 EmployeeName。
uj5u.com熱心網友回復：
我猜你需要一個回圈函式來遍歷你的所有資料。
function sendEmail() {
const data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,2,11,9).getValues();
for (let i = 0; i< data.length ;i ){
//change the [i][0] the second number in [] to the actual index of your number column. If your column number is 5, then the index will be 4
//index start from 0.
const EmployeeName = data[i][0];
const CertDates = data[i].slice(1);
const Today = new Date()
//this arr will hold each cert date less than 90 days.
const arr = [];
for(let j=0; j<CertDates.length ;j ){
const CertDate = CertDates[j];
if(typeof(CertDate)== 'object' && Math.floor((CertDate - Today ) / (1000*60*60*24)) < 90){
console.log(CertDate)
arr.push(CertDate);
}
}
//if any emlpoyees has expire date less than 90 days. 1 or many. It will send email to employee name.
if(arr.length>0){
// Fetch the email address
var emailAddress = '[email protected]';
// Send Alert Email.
var message = 'Certificate for ' EmployeeName ' expiring soon'; //
var subject = 'CERT EXPIRING SOON | CV CERT TRACKING';
MailApp.sendEmail(emailAddress, subject, message);
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/439632.html