Trigger Email Reminders Based on Dates in Google Sheets

Creating your Google script.

Spreadsheet example, sample data from Mockaroo
Sample email notification from triggered via Google Sheets
// today's date information
var today = new Date();
var todayMonth = today.getMonth() + 1;
var todayDay = today.getDate();
var todayYear = today.getFullYear();
// 2 weeks from now
var twoWeeksFromToday = today;
twoWeeksFromToday.setDate(twoWeeksFromToday.getDate() + 14);
var twoWeeksMonth = twoWeeksFromToday.getMonth() + 1;
var twoWeeksDay = twoWeeksFromToday.getDate();
var twoWeeksYear = twoWeeksFromToday.getYear();
// 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.getYear();
// getting data from spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 100; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 999);
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',
'MM/dd/yyyy'
);
// email information
var subject = '';
var message =
' A subscription is expiring. ' +
'\n' +
' Name: ' +
row[0] +
'\n' +
' Phone Number: ' +
row[1] +
'\n' +
' Email Address: ' +
row[2] +
'\n' +
' Description: ' +
row[3] +
'\n' +
' Notes: ' +
row[4] +
'\n' +
' Sign Up Date: ' +
row[5] +
'\n' +
' Expiration Date: ' +
expireDateFormat;
//expiration date information
var expireDateMonth = new Date(row[6]).getMonth() + 1;
var expireDateDay = new Date(row[6]).getDate();
var expireDateYear = new Date(row[6]).getYear();
//checking for today
if (
expireDateMonth === todayMonth &&
expireDateDay === todayDay &&
expireDateYear === todayYear
) {
var subject =
'A subscription expired today: ' + row[0] + ' - ' + expireDateFormat;
MailApp.sendEmail('subscriptionscript@gmail.com', subject, message);
}
//checking for 2 weeks from now
if (
expireDateMonth === twoWeeksMonth &&
expireDateDay === twoWeeksDay &&
expireDateYear === twoWeeksYear
) {
var subject =
'A subscription is expiring in 2 weeks: ' +
row[0] +
' - ' +
expireDateFormat;
MailApp.sendEmail('subscriptionscript@gmail.com', subject, message);
}
//checking for 1 month from now
if (
expireDateMonth === oneMonthMonth &&
expireDateDay === oneMonthDay &&
expireDateYear === oneMonthYear
) {
var subject =
'A subscription is expiring in 1 month: ' +
row[0] +
' - ' +
expireDateFormat;
MailApp.sendEmail('subscriptionscript@gmail.com', subject, message);
}

Running the Script.

Screenshot of the menu in Google Sheets script editor.

Scheduling the Script to Autorun.

Screenshots showing how to choose the correct time zone when setting up a script.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store