'Send different email reminders based on dates on multiple columns
I have a list of properties in Col A and different certificates expiry dates in Col G (Gas certificate),H ( Electric certificate),I (EPC),K (Fire alarm),L (Emergency lighting). I have found a script that works perfectly the only problem is that this code only reads one column of dates. If I do a separate code for another Column and so a different certificate, it disable the other one. I suppose it's not possible to have so many different script for the same sheet referring to different columns so it must be within same script but with different conditions. I need it to read all dates and send me an email 30 days before expiry and 2 weeks before expiry and when is expired but with the name of the certificate situated in the header row 2 of the same column.
Below is the script that I have so far but which only reads one column
function emailAlert() {
// today's date information
var today = new Date();
var todayMonth = today.getMonth() + 1;
var todayDay = today.getDate();
var todayYear = today.getFullYear();
// 27 days from now
var twoWeeksFromToday = new Date();
twoWeeksFromToday.setDate(twoWeeksFromToday.getDate() + 14);
var twoWeeksMonth = twoWeeksFromToday.getMonth() + 1;
var twoWeeksDay = twoWeeksFromToday.getDate();
var twoWeeksYear = twoWeeksFromToday.getFullYear();
// 1 month from now
var newToday = new Date()
var oneMonthFromToday = new Date(newToday.setMonth(newToday.getMonth()+1));
var oneMonthMonth = oneMonthFromToday.getMonth() + 1;
var oneMonthDay = oneMonthFromToday.getDate();
var oneMonthYear = oneMonthFromToday.getFullYear();
// getting data from spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Compliance");
var startRow = 3; // First row of data to process
var numRows = sheet.getLastRow() - 2; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 14);
var data = dataRange.getValues();
//looping through all of the rows
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var expireDateFormat = Utilities.formatDate(
new Date(row[6]),
'ET',
'dd/MM/yyyy'
);
//expiration date information
var expireDateMonth = new Date(row[6]).getMonth() + 1;
var expireDateDay = new Date(row[6]).getDate();
var expireDateYear = new Date(row[6]).getFullYear();
//checking for today
if (
expireDateMonth === todayMonth &&
expireDateDay === todayDay &&
expireDateYear === todayYear
) {
var subject =
row[0] +
'\n' +
'-' +
'\n' +
' REMINDER Gas Cert expired: ' +
expireDateFormat;
var message =
' Arrange with engineer or Landlord ASAP ' ;
MailApp.sendEmail('[email protected]', subject, message);
}
//checking for 2 weeks from now
Logger.log('2 weeks month, expire month' + twoWeeksMonth + expireDateMonth);
if (
expireDateMonth === twoWeeksMonth &&
expireDateDay === twoWeeksDay &&
expireDateYear === twoWeeksYear
) {
var subject =
row[0] +
'\n' +
'-' +
'\n' +
' REMINDER Gas Cert ending: ' +
expireDateFormat;
var message =
' Has it been arranged ? ' ;
MailApp.sendEmail('[email protected]', subject, message);
Logger.log('2 weeks from now');
}
//checking for 1 month from now
if (
expireDateMonth === oneMonthMonth &&
expireDateDay === oneMonthDay &&
expireDateYear === oneMonthYear
) {
var subject =
row[0] +
'\n' +
'-' +
'\n' +
' GAS Cert ending: ' +
expireDateFormat;
var message =
' Email Landlord or organise with gas engineer ' ;
MailApp.sendEmail('[email protected]', subject, message);
Logger.log('1 month from now');
}
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
