Send birthday and work-anniversary emails

Birthdays and work anniversaries are easy to miss and awkward to miss in public. As a team grows, remembering every date by hand stops being realistic, and a forgotten anniversary lands worse than no tradition at all. The dates are usually already recorded in an HR sheet — they just need someone, or something, to check them every morning.

Northwind keeps everyone’s birthday and start date on a Team sheet. This script reads that sheet once a day, finds anyone whose birthday or work anniversary falls today, and sends each of them a short, personalised note from [email protected]. Nobody has to remember anything; the roster does the remembering.

What you’ll need

• A Google Sheet with a Team tab whose first row is the header name, email, birthday, startDate.
• The birthday and startDate columns stored as real date values, not text. Type a date and Sheets right-aligns it — that is how you know it is a date.
• The script runs as whoever owns it, so mail is sent from that account. Run it under the address you want greetings to come from.

The script

// The spreadsheet that holds the team roster.
const TEAM_SHEET_ID = '1abcTeamSheetId';

/**
 * Reads the Team sheet and emails a greeting to anyone whose birthday
 * or work anniversary falls on today's date.
 */
function sendAnniversaryEmails() {
  // 1. Read the whole roster, splitting the header from the data rows.
  const sheet = SpreadsheetApp.openById(TEAM_SHEET_ID).getSheets()[0];
  const [header, ...rows] = sheet.getDataRange().getValues();

  if (!rows.length) {
    Logger.log('Team sheet is empty — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so the code is not tied to
  //    a fixed column order.
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 3. Build a "month-day" string for today, for comparing dates.
  const today = new Date();
  const todayMD = `${today.getMonth() + 1}-${today.getDate()}`;

  // 4. Walk every team member and check both dates against today.
  for (const row of rows) {
    const name = row[col.name];
    const email = row[col.email];
    const birthday = row[col.birthday];
    const startDate = row[col.startDate];

    if (!email) continue;

    // Birthday today? Send the birthday note.
    if (birthday instanceof Date && monthDay(birthday) === todayMD) {
      GmailApp.sendEmail(
        email,
        `Happy birthday, ${name} 🎂`,
        'From everyone at Northwind — have a great day.',
      );
      Logger.log(`Birthday email sent to ${name}.`);
    }

    // Work anniversary today? Send the anniversary note, but only
    // once at least a full year has passed.
    if (startDate instanceof Date && monthDay(startDate) === todayMD) {
      const years = today.getFullYear() - startDate.getFullYear();
      if (years > 0) {
        GmailApp.sendEmail(
          email,
          `${years} years at Northwind — thank you`,
          `That's ${years} year${years > 1 ? 's' : ''} of you ` +
            'making Northwind better.',
        );
        Logger.log(`Anniversary email sent to ${name} (${years}y).`);
      }
    }
  }
}

/**
 * Returns a "month-day" string for a date, e.g. "7-29", used to
 * compare two dates while ignoring the year.
 */
function monthDay(date) {
  return `${date.getMonth() + 1}-${date.getDate()}`;
}

How it works

1. The script opens the Team sheet and reads every row in one call, destructuring the result into the header row and the rows below it. If there are no data rows it logs that and stops.
2. It builds a col lookup from the header, so columns can be referenced by name (col.email) rather than by a fragile numeric index — the sheet’s column order can change without breaking the script.
3. It computes today’s date as a month-day string. Comparing on month and day only is what lets a fixed birthday match every year.
4. For each person it checks the birthday and startDate cells. The instanceof Date guard quietly skips blank cells and any value typed as text, so a bad row never throws.
5. A birthday match sends the birthday note. An anniversary match sends the anniversary note — but only when years is greater than zero, so nobody is thanked for their service on their very first day.
6. monthDay is a small helper shared by both checks, keeping the date logic in one place.

Example run

Say the Team sheet looks like this and the script runs on 29 July 2026:

name	email	birthday	startDate
Priya Shah	[email protected]	1990-07-29	2021-03-01
Tom Reed	[email protected]	1988-11-12	2023-07-29

Two emails go out:

• Priya receives “Happy birthday, Priya Shah 🎂” — her birthday is today.
• Tom receives “3 years at Northwind — thank you” — he started on this day three years ago.

Everyone else is skipped silently.

Trigger it

This needs to run once every morning, so use a time-driven trigger:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for sendAnniversaryEmails, choose Time-driven, then Day timer and the 8am to 9am slot.
3. Approve the authorisation prompt the first time it runs.

A daily 8am run means the greeting is waiting before the working day starts.

Watch out for

• Dates must be real date values. A birthday typed as text fails the instanceof Date check and that person is silently skipped — the most common reason a greeting “did not send”.
• The trigger must run exactly once a day. Two runs on the same date will send duplicate emails; this script keeps no record of what it has already sent.
• 29 February birthdays only match in leap years. If that matters, treat 29 Feb as 28 Feb (or 1 March) in non-leap years.
• The script sends mail as whoever owns it and counts against that account’s daily Gmail quota — generous for a normal team, but worth knowing.
• There is no opt-out. If someone would rather not be greeted, add a column to the sheet and skip rows where it is set.

Variations

• Cc the whole team on anniversaries by adding { cc: '[email protected]' } as a final argument to the anniversary sendEmail call.
• Add a message column to the Team sheet and use row[col.message] as the body when it is filled in, for a personal note per person.