Get Ahead of Special Occasions: Advance Event Reminders with Apps Script

Do you want to stay ahead of important events and never miss a chance to celebrate? One of the first tutorials I wrote on this site was on sending birthday reminders using Apps Script. The code in that tutorial only sends reminders on the day of the event. A number of readers have written to me asking how they can send event reminders in advance using Apps Script so they can plan ahead of time. How would you achieve that?

Imagine that you have a Google Sheets spreadsheet containing a sheet called "Anniversaries". This sheet has two columns: Name and Anniversary. Here is some sample data from this spreadsheet:

We'll modify the code in that original tutorial to send reminders in advance. Please be sure to read that tutorial first before proceeding especially if you are new to coding scripts.

First, instead of checking whether a person's special day is today, we will check if it is around the corner. The function checkEventStatus(dateToCheck) returns a string that tells us if the person's special day is today, tomorrow, a week from now or two weeks from now.

// Check if a person's special day is today or upcoming
function checkEventStatus(dateToCheck) {
  // If dateToCheck is a string, convert it to date
  if (typeof dateToCheck === "string") {
    dateToCheck = new Date(dateToCheck);
  }

  const today = new Date();
  let adjustedDateToCheck = new Date(today.getFullYear(), dateToCheck.getMonth(), dateToCheck.getDate());

  // Date passed this year? Set it to next year
  if (adjustedDateToCheck < today) {
    adjustedDateToCheck.setFullYear(adjustedDateToCheck.getFullYear() + 1);
  }

  const daysUntilSpecialDay = Math.ceil(
    (adjustedDateToCheck.getTime() - today.getTime()) / (24 * 60 * 60 * 1000)
  );


  if (daysUntilSpecialDay === 0) {
    return "today";
  } else if (daysUntilSpecialDay === 1) {
    return "tomorrow";
  } else if (daysUntilSpecialDay === 7) {
    return "in7days";
  } else if (daysUntilSpecialDay === 14) {
    return "in14days";
  } else {
    return "none"; // Not a relevant upcoming day
  }
}

Then it is easy to use the status returned by the above function to take relevant actions like sending reminder notifications. The script below shows you an example of how to do this.

function main() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Anniversaries");
  const rows = sheet.getDataRange().getValues();
  const header = rows.shift();
  let emailCopy = "";
  let anniversaryStatus = "none";
  let name = "";
  rows.forEach(function (row) {
    name = row[0].endsWith("s") ? row[0] + "'" : row[0] + "'s";
    anniversaryStatus = checkEventStatus(row[1]);
    if(anniversaryStatus === "today") {
      emailCopy += name + " anniversary is today.\n";
    } else if (anniversaryStatus === "tomorrow") {
      emailCopy += name + " anniversary is tomorrow.\n";
    }  else if (anniversaryStatus === "in7days") {
      emailCopy += name + " anniversary is in 7 days.\n";
    }  else if (anniversaryStatus === "in14days") {
      emailCopy += name + " anniversary is in 14 days.\n";
    }
  });
  

  sendEmailReminder(emailCopy);

}

// Check if a person's special day is today or upcoming
function checkEventStatus(dateToCheck) {
  // If dateToCheck is a string, convert it to date
  if (typeof dateToCheck === "string") {
    dateToCheck = new Date(dateToCheck);
  }

  const today = new Date();
  let adjustedDateToCheck = new Date(today.getFullYear(), dateToCheck.getMonth(), dateToCheck.getDate());

  // Date passed this year? Set it to next year
  if (adjustedDateToCheck < today) {
    adjustedDateToCheck.setFullYear(adjustedDateToCheck.getFullYear() + 1);
  }

  const daysUntilSpecialDay = Math.ceil(
    (adjustedDateToCheck.getTime() - today.getTime()) / (24 * 60 * 60 * 1000)
  );


  if (daysUntilSpecialDay === 0) {
    return "today";
  } else if (daysUntilSpecialDay === 1) {
    return "tomorrow";
  } else if (daysUntilSpecialDay === 7) {
    return "in7days";
  } else if (daysUntilSpecialDay === 14) {
    return "in14days";
  } else {
    return "none"; // Not a relevant upcoming day
  }
}

// Function to send the email reminder
function sendEmailReminder(body) {
  let subject = "Upcoming anniversaries";
  let recipient = Session.getActiveUser().getEmail();
  MailApp.sendEmail(recipient, subject, body);
}

When you run the above script, you'll receive an email like the one below. As you can see, the email notifies you about anniversaries in advance.

Hope this tutorial was helpful. Feel free to adapt this code to your own Google Sheets and set the advance reminder intervals that work best for you! Thanks for reading!