Birthday reminders using Google Sheets

I am terrible at remembering dates and feel awful when I forget birthdays. A few weeks ago, I built an app using Google Sheets to send me birthday reminders by email. In this article, I’ll describe how I built this app so you can build one for yourself.

Three steps to build the birthday reminder app

• Enter the birthdays you want to remember into a Google Sheet.

• Write a Google Apps Script that sends email reminders.

• Automate the script to run every morning using triggers.

I’ll describe each step in more detail.

Step 1 — Enter the birthdays you want to remember into a Google Sheet.

Create a new Google Sheet with two columns and rename the sheet from “Sheet1” to “Birthdays”. Tip: The URL spreadsheets.new will create a new Google Sheet.

Enter all the birthdays you want to remember into the sheet. Enter names in the first column and birthdays in the second column. Don’t forget to enter column headers (I entered Person for the first column and Birthday for the second).

When you’re done, you should have a sheet that looks like the following.

Step 2 — Create a Google Apps Script to send email reminders

Google Apps Script enables you to extend the functionality of Sheets, Docs, Slides and other G Suite apps using JavaScript. We’re going to use Apps Script to send email reminders.

The first step is to create a new script by clicking on the Extensions menu and then selecting Apps Script.

When you select Script editor, a code editor will open in a new window.

Click on "Untitled project" and enter a name for the script. I entered “Birthday reminder script” as the name.

Next, we have to write code to send birthday reminders. We will use the following logic to write the code:

• Load all the data in the Birthdays sheet.

• Go through the data row by row.

• For each row, check if the person’s birthday is today.

• If the birthday is today, send an email reminder.

 function main() {
  // Load the sheet that contains the birthdays.
  var sheet = SpreadsheetApp.getActive().getSheetByName("Birthdays");

  // Get the last row in the sheet that has data.
  var numRows = sheet.getLastRow();

  // Load data in the first two columns from the second row till the last row. 
  // Remember: The first row has column headers so we don’t want to load it.
  var range = sheet.getRange(2, 1, numRows - 1, 2).getValues();

  // Use a for loop to process each row of data
  for(var index in range) {

    // For each row, get the person’s name and their birthday
    var row = range[index];
    var name = row[0];
    var birthday = row[1];

    // Check if the person’s birthday is today
    if(isBirthdayToday(birthday)) {
      //If yes, send an email reminder
      emailReminder(name);
    }
  }
}

// Check if a person’s birthday is today
function isBirthdayToday(birthday) {
  // If birthday is a string, convert it to date
  if(typeof birthday === "string")
    birthday = new Date(birthday);
  var today = new Date();
  if((today.getDate() === birthday.getDate()) &&
      (today.getMonth() === birthday.getMonth())) {
    return true;
  } else {
    return false;
  }
}

// Function to send the email reminder
function emailReminder(name) {
  var subject = "Birthday reminder: " + name;
  var recipient = Session.getActiveUser().getEmail();
  var body = "It is " + name + "'s birthday today.";
  MailApp.sendEmail(recipient, subject, body);
}

TypeError: birthday.getDate is not a function

//If birthday is a string, convert it to date
if(typeof birthday === "string")
  birthday = new Date(birthday);

var recipient = Session.getActiveUser().getEmail();

var recipient = "user1@example.com";

var recipient = "user1@example.com,user2@example.com";

The next step is to enter some test data so we can test the app. Make sure that there is at least one person whose birthday is today.

Then, select the main function from the dropdown menu in the script editor and click the Run button to run it.

If this is the first time you are running the script, you’ll be asked to authorize the script to access your data and send emails on your behalf. Please refer to the Apps Script authorization tutorial if you haven't authorized scripts before.

Once you authorize the script, it will complete executing. You should receive an email assuming there is at least one row in the sheet where the birthday is today.

Step 3 — Create a trigger that runs every morning

So far, we’ve coded a script that sends email reminders, but we still need to run it manually by clicking the play icon in the script editor.

However, we want to automate this script to run daily so we get an email reminder in the morning when it’s someone’s birthday. That’s the whole point of this micro app!

To do this, we will use a trigger. In simplistic terms, a trigger automatically runs a function for you based on a schedule that you specify. Depending on the app you’re building and what you’re using the trigger for, you can set the schedule to be every hour, every 3 hours, daily at a specific time, every Monday at 8 AM, etc. It’s pretty flexible.

You can also create triggers that are not time-based, but we’ll discuss these in a future post. Let’s create a simple time-based trigger to automate sending out birthday email reminders.

First, click the clock icon to get to the triggers page.

A new window will open when you’ll see that there are no triggers set up for our code. Click the + Add Trigger button to create a trigger

Configure the trigger to run daily at the time of your choosing. I chose the following settings:

• Which function to run: main

• Which deployment to run: Head (I’ll explain what this means in a later post but for now just choose Head)

• Event source: Time-driven (since we want the trigger to be time based)

• Type of time based trigger: Day timer (since we want the script to run at a specific time everyday)

• Time of day: I like to get reminders in the morning so I chose 5am to 6am

• Failure notification settings: I want to get notified daily if the trigger fails so I chose daily

When you click Save, the trigger will be created and the main function will be automatically run daily sometime between 5am and 6am and you will get reminders when it is someone’s birthday.

Conclusion

If you’re like me and tend to forget birthdays or dates in general, you just learned how to solve this problem using automation and Google Sheets.

Future Enhancements

When I first published this tutorial, I mentioned several ideas for future enhancements:

• Support multiple reminders for birthdays (advance notices and day-of)

• Automatically create "Happy Birthday" email drafts ready to send

• Send summary emails of upcoming birthdays

I'm excited to share that I've now implemented all these features in an enhanced version of the Birthday Reminder template. The advanced version includes:

• Daily or weekly summary emails showing both today's birthdays and upcoming ones

• Automatic creation of personalized draft birthday emails

• Beautiful HTML-formatted notifications with a modern design

• Customizable settings for look-ahead period and notification frequency

If you've successfully implemented the basic version described in this post and want to take your birthday reminder system to the next level, check out the Advanced Birthday Reminder System.