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.
You can adapt the code in this article to set up reminders for other types of dates. For example, you could set up email reminders for:
Anniversaries
Mother’s Day, Father’s Day etc.
Important school dates
Project due dates at school or work
etc.
Three steps to build the birthday reminder appEnter 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.
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 Tools menu and then selecting Script editor.
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);
}
💡 Tip #1
Some readers informed me that the code above was throwing the following error:
TypeError: birthday.getDate is not a functionThe 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)
★ How to email reminders to multiple people?
The above code will send email reminders only to you. This is the line in the code that gets your email address.
var recipient = Session.getActiveUser().getEmail();You can also directly specify the recipient's email address.
var recipient = "user1@example.com";Suppose you want to send these reminders to multiple people, you can also do that. Just use multiple comma-separated email addresses instead of a single email address.
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 play icon 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.
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.
You can see a list of all the apps you’ve previously authorized at https://myaccount.google.com/permissions. It is a good security practice to review this list regularly and remove apps that you’ve stopped using.
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 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.
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.
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.