var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheets()[0];var range = sheet.getRange(1, 1, 3, 3);var values = range.getValues();// send email if conditions are metfor (var row in values) { if (values[row][col_with_ys] === 'Y' and values[row][date_col] === something) { sendEmail(); }}
//Sends an Email to info@etc, for each row where there is a Y in the AT column, and the date is either 6 or 12 months agofunction emailAlert() { // today's date information var today = new Date(); var todayMonth = today.getMonth() + 1; var todayDay = today.getDate(); var todayYear = today.getFullYear(); // 6 months ago var sixMonthsAgo = new Date(); sixMonthsAgo.setDate(sixMonthsAgo.getDate() - 182); var sixMonthsMonth = sixMonthsAgo.getMonth() + 1; var sixMonthsDay = sixMonthsAgo.getDate(); var sixMonthsYear = sixMonthsAgo.getFullYear(); // 1 year ago var oneYearAgo = new Date(); oneYearAgo.setDate(oneYearAgo.getDate() - 365); var oneYearMonth = oneYearAgo.getMonth() + 1; var oneYearDay = oneYearAgo.getDate(); var oneYearYear = oneYearAgo.getFullYear(); // getting data from spreadsheet var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // First row of data to process var numRows = 8000; // Number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, 20); var data = dataRange.getValues(); //loop through all of the rows for (var i = 0; i < data.length; ++i) { var row = data; // email information var subject = ''; var message = ' Reminder - ' + '\n' + ' Name: ' + row[1] + '\n' + ' Job: ' + row[2]; //trigger date information var rowDateMonth = new Date(row[0]).getMonth() + 1; var rowDateDay = new Date(row[0]).getDate(); var rowDateYear = new Date(row[0]).getFullYear(); //Checking for 6 Months from now if ( rowDateMonth === sixMonthsMonth && rowDateDay === sixMonthsDay && rowDateYear === sixMonthsYear && row[10] === 'Y' ) { var subject = 'Reminder 6 month: ' + row[1] + ' - ' + row[2]; MailApp.sendEmail('info@etc', subject, message); } //Checking for 1 year from now if ( rowDateMonth === oneYearMonth && rowDateDay === oneYearDay && rowDateYear === oneYearYear && row[10] === 'Y' ) { var subject = 'Reminder 1 Year: ' + row[1] + ' - ' + row[2]; MailApp.sendEmail('info@etc', subject, message); } }}