Use A Google Sheet To Send Reminder Emails To Your Team For Free

A lot of small teams could use reminder emails when it is time for a team member to perform a task, but there are not a lot of products where you can easily set up reminder emails for team members for free.

But you can do it easily with a Google Sheet.

Building on the work of others I created this little script you can copy/paste from https://github.com/RobKraft/GoogleSheetBasedEmailReminders

Open the Script Editor from the Tools menu of your Google Sheet and paste this script in.  The code is simple and documented if you desire to change it.

Then set up 4 columns in your google sheet.  Make row one headers for the 4 columns:

• Column A: Email Address – this is a single email address or comma separated list of email addresses to send to
• Column B: Reminder Begin Date – this is the date at which the reminder will start going out daily Column
• C: Subject – This is the subject of the email
• Column D: Email Body – This is the body of the email. Also the code adds some extra stuff to the body of the email.

You also need to create a trigger in your google sheet.

To do this, select the Edit menu from the script menu and select Current Project Triggers. You may need to give your project a name and save it at this point. Add a trigger. At the time of this writing in May 2019, you would need to set these values for your trigger:

• “Choose which function to run” – probably sendEmails
• “Choose which deployment to run” – probably Head
• “Select event source” – Time-driven
• “Select type of time based trigger” – Day Timer – for once per day
• “Select Time of Day” – During what time frame do you want the trigger to run. (GMT Time)

That is it – save that trigger and it is all yours.  Set up an email to yourself to test it all.  All the emails will be sent from your own @gmail.com account.

Just for fun, I include the script code here that is also in the repo:

function sendEmails() {
  //Set up some variables
  var startRow = 2; // First row of data to process
  var numRows = 100; // Number of rows to process
  var currentDate = new Date();
  var currentYear = currentDate.getFullYear();
  var currentMonth = currentDate.getMonth() + 1;
  var currentDay = currentDate.getDate();
  var emailSubjectPrefix = 'Reminder: ';
  var urlToGoogleSheet = 'https://docs.google.com/spreadsheets/????edit#gid=0';

  var sheet = SpreadsheetApp.getActiveSheet();
  // Fetch the range of cells A2:D100
  var dataRange = sheet.getRange(startRow, 1, numRows, 4);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i]; //Get the whole row
    var emailAddress = row[0]; // First column of row
    if (emailAddress != "") //If there is an email address, do something
    {
      var eventDate = new Date(row[1]); //second column of row
      var yearOfEvent = eventDate.getFullYear();
      var monthOfEvent = eventDate.getMonth() + 1;
      var dayOfEvent = eventDate.getDate();
      if (currentYear >= yearOfEvent && currentMonth >= monthOfEvent
           && currentDay >= dayOfEvent)
      {
        var subject = emailSubjectPrefix + row[2];  //third column of row
        var message = row[3]; // fourth column of row
        message = "\r\n\r\n" + message + "\r\n\r\n";
        //Add a link to the spreadsheet in the email so people 
        //can easily go disable the reminder 
        message = message + "\r\nSent on " + currentDate + 
        "\r\nDisable the notification by changing the date on it here: "
        + urlToGoogleSheet;
        message = message + "\r\nReminder Start Date: " + eventDate
        MailApp.sendEmail(emailAddress, subject, message);
      }
    }
  }
}