Modifiqué un código que encontré en línea para mirar una hoja de Google (muestra a continuación) y enviar un correo electrónico cuando el permiso de trabajo de un empleado esté a punto de caducar en 30 días.
Sin embargo, me gustaría modificarlo para enviar un correo electrónico (a una dirección de correo electrónico) con una tabla de todos los empleados cuyo permiso está a punto de caducar en lugar de un correo electrónico por empleado. Por favor ayuda !
Ejemplo de formato de hoja de Google
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 }); }Traté de resolver su problema, podría darle una idea general sobre la solución, descanse, puede averiguarlo o pedirme más explicaciones.
// 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 });