Birthday Emails

I built a sheet that emails me the morning of my friends’ birthdays.

Video Walkthrough

WALKTHROUGH
How to build it

For a long time, I’ve been bad at remembering to send birthday wishes to my friends. Sure, I remember some. Sure, I’ve got some on my Google Calendar.

But for those of you, like me, who aren’t relying on Facebook to spam birthday celebrations to your cell phone everyday, this little Google Sheet may be exactly what you want.

Sometimes, simple is best. Here’s what we need:

  1. A table in a Google Sheet that I can add names and dates to

  2. Every day, the sheet checks for an anniversary in the morning

  3. If there is one, I get an email reminding me to text them

  4. I’ve also got a third column where I can specify what the date is (birthday, anniversary, etc)

That’s it.

Here’s what you need to do:

Open a new Google Sheet (sheets.new)

Rename the sheet tab, Birthdays.

Add the column headers and text

Convert this to a table (not 100% necessary, but it’s good practice to do. CTRL + ALT + T or CMD + OPTION + T).

Put your email in another cell somewhere in the spreadsheet and create a named range named NOTIFY_EMAIL. This will pull your email address into the Apps Script we’re about to create.

Open up Apps Script (Extension → Apps Script)

Paste the code (below ⬇️) and save.

Run the function setup() once so it’ll set up the trigger to check every morning.

I like to add a date to test that the email part is working. Add a row with someone’s name and today’s month and day. You can pick a random year. Now run the reminderToday() function and make sure you get an email.

Call me crazy, but getting an email from myself like this has already worked better than throwing an all day calendar event in my Google Calendar.

This is already working for me. My friend Mike’s birthday popped up a couple days ago and I immediately shot him a text.

I’ve always had the best of intentions with birthdays and anniversaries, but sometimes I need a little help from technology.

Careful!

The version of the script that i’ve written expects ONLY one table of information to be on the Birthday sheet. If you’re putting more information in the workbook, do it on a new sheet tab.

Too Complicated? Get the Sheet ⬇️

You can build this for free by following the steps above, but if you need the pre-built custom sheet that’s ready to roll… Grab it below.

Anniversary Reminder

Anniversary Reminder

Stop forgetting birthdays and other important dates.

$7.00 usd

Will you use this?

Login or Subscribe to participate

APPS SCRIPT
The Code

const SHEET = 'Birthdays';
function getNotifyEmail() {
  return SpreadsheetApp.getActive()
    .getRangeByName('NOTIFY_EMAIL')
    .getValue();
}


function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Setup')
    .addItem('Run setup', 'setup')
    .addToUi();
}

function reminderToday() {
  const sh = SpreadsheetApp.getActive().getSheetByName(SHEET);
  const rows = sh.getRange(2, 1, sh.getLastRow() - 1, 3).getValues();
  const tz = Session.getScriptTimeZone();

  const today = new Date();
  const mmdd = Utilities.formatDate(today, tz, 'MM-dd');
  const year = Number(Utilities.formatDate(today, tz, 'yyyy'));

  const matches = rows
    .filter(([name, d]) =>
      name && d instanceof Date &&
      Utilities.formatDate(d, tz, 'MM-dd') === mmdd
    )
    .map(([name, d, reason]) => ({
      name,
      age: year - d.getFullYear(),
      reason: reason || 'anniversary'
    }));

  if (!matches.length) return;

  matches.forEach(m =>
    GmailApp.sendEmail(
      getNotifyEmail(),
      `Reminder today about ${m.name}`,
      `Reminder: ${m.name}'s ${m.age} ${m.reason}`
    )

  );
}

// run once to set up daily trigger at 8am
function setup() {
  ScriptApp.newTrigger('reminderToday')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

NEXT STEPS
Whenever you’re ready, here’s how I can help:

  1. Work with me
    I’m available for consulting projects. Reach out and let’s chat.

  2. Business tech stack
    Some of my recommendations for software and tools.

  3. Personal budget tool
    As a subscriber, you get a discount on my personal finance system.

  4. YouTube
    If you aren’t subscribed yet, come on over to the YouTube channel where all the spreadsheet, automation and productivity tutorials live.

TOOLS
Make life easier

  • Coefficient - Live spreadsheet connections to 60+ business systems

  • Lido - Automate your spreadsheets; accurately extract date from PDFs

  • TransactionPro - quickly import, export or delete data in Quickbooks

  • beehiiv - my choice for a newsletter operating system

  • Carrd - free one-page website builder

  • Transistor - my favorite podcast host

Happy Spreadsheeting!

Enjoyed this issue?
Subscribe below or Leave a testimonial

<iframe src="https://embeds.beehiiv.com/eab8502a-f9ab-4d3f-abc8-30b1abb520a9?slim=true" data-test-id="beehiiv-embed" height="52" frameborder="0" style="margin: 0; border-radius: 0px !important; background-color: transparent;"></iframe>

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.

Reply

or to participate

Recommended for you