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.

Screenshot of a Google Sheets spreadsheet.

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 Tools menu and then selecting Script editor.

Screenshot of a Google Sheets spreadsheet with the Tools menu open.

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

Screenshot of the Google Apps Script code editor.

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);
}

💡 Tip #1

Some readers informed me that the code above was throwing the following error:

TypeError: birthday.getDate is not a function

The reason for this error is that the code assumes that you've formatted the column containing birthdays as a Date (see video below). Sometimes Google Sheets automatically figures out that you've entered a date and chooses the format correctly but sometimes it doesn't. The error occurs when the dates in the spreadsheet are not formatted correctly. So one way to fix the error is to format all the dates in your spreadsheet as a date.

I've also added the following code snippet to the isBirthdayToday() function.

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

If the variable birthday is a string (i.e., text), the code above will attempt to convert it into a Date. This should help fix the most common cause for the type error.

💡 Tip #2

If you get the error "ReferenceError: isBirthdayToday is not defined" when you run the main() function, it means that the isBirthdayToday() function is missing. Please check and ensure that your script has this function. In particular, please check for:

  • Misspellings

  • Casing of letters in the name

  • [X] ISBIRTHDAYTODAY (uppercase)

  • [✓] isBirthdayToday (camel case)

  • [X] isbirthdaytoday (lower case)

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 play icon to run it.

Screenshot of the Google Apps Script editor

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.

Screenshot of a modal dialog that is asking the user to authorize the script.

When you click Continue, Google will tell you what data the script wants to access on your behalf. Carefully review this information. In this case, it should be safe to grant access since you’re the one writing the script so go ahead and click Allow.

Screenshot of a modal dialog that is asking the user to review and approve the permissions that the script needs to run.

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.

Screenshot of Gmail Inbox displaying the email that was sent by the script.

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 is 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 8AM 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.

Screenshot of Google Apps Script editor

A new window will open when you’ll see that there are no triggers set up for our code.

Screenshot of the triggers page.

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

Screenshot of a modal dialog displaying options to configure the trigger.

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.

Screenshot of the triggers page.

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.

Some ideas for future enhancements are:

  • Support multiple reminders for birthdays (say a week in advance as well as the day of).

  • Automatically create a “Happy Birthday” email as a draft message that you can review, edit and send in a few seconds.

  • Send a single email summarizing all the birthdays that are coming up instead of one email per birthday.


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!