Sync birthdays and anniversaries to Calendar

At Northwind, the running joke is that somebody’s anniversary is always yesterday. The dates live in a People-ops spreadsheet that nobody looks at, and the cake order goes in twenty-four hours late. The fix is to put the dates where everyone already is — on a shared calendar that surfaces them at the right moment.

This script reads a Team sheet of birthdays and start dates, and creates all-day events on a dedicated “team rituals” calendar for every year currently in view. Run it on January 1st and the rest of the year is set up in seconds — birthdays with a cake, anniversaries with a milestone count.

What you’ll need

• A Team Google Sheet with the columns name, birthday, and startDate. birthday and startDate should be real Date cells, not text.
• A dedicated calendar for these events (so you can show/hide the lot at once). Grab the ID from Calendar settings and paste it into TEAM_CAL.
• Nothing else — no API keys, no add-ons.

The script

// The shared calendar for team birthdays and anniversaries.
const TEAM_CAL = '1abcTeamRitualsCalId';

// The team sheet — name, birthday, startDate.
const TEAM_SHEET = '1abcTeamId';

/**
 * Reads the team sheet and creates this year's birthday and
 * anniversary events on the rituals calendar. Run once a year.
 */
function syncTeamDates() {
  const team = readSheet(TEAM_SHEET);
  const cal = CalendarApp.getCalendarById(TEAM_CAL);
  if (!cal) {
    Logger.log('Calendar not found — check TEAM_CAL.');
    return;
  }

  const year = new Date().getFullYear();
  let created = 0;

  for (const p of team) {
    if (!p.name) continue;

    // 1. Birthday — same month and day, this year.
    if (p.birthday instanceof Date) {
      const date = new Date(year, p.birthday.getMonth(), p.birthday.getDate());
      cal.createAllDayEvent(`🎂 ${p.name}`, date);
      created++;
    }

    // 2. Anniversary — count years since start, skip the joiner year so
    //    we never log a "0 years" event for someone who joined this year.
    if (p.startDate instanceof Date) {
      const years = year - p.startDate.getFullYear();
      if (years > 0) {
        const date = new Date(year, p.startDate.getMonth(), p.startDate.getDate());
        cal.createAllDayEvent(`🎉 ${p.name} — ${years} years`, date);
        created++;
      }
    }
  }

  Logger.log('Created ' + created + ' events for ' + year + '.');
}

/**
 * Reads a sheet into an array of plain objects keyed by header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. syncTeamDates reads the Team sheet via the readSheet helper, which turns each row into a { name, birthday, startDate } object.
2. It opens the rituals calendar by ID and bails out with a log line if the ID is wrong.
3. It captures the current year once. Every event the script creates lands in that year.
4. For each person, it builds a birthday event by combining the current year with the month and day from the birthday cell. The cell’s year is ignored — what matters is when the cake gets ordered.
5. For anniversaries it computes currentYear - startDate.year. Anyone who joined this year gets 0, which the script skips so nobody sees a “0 years” event on their first month.
6. It logs how many events were created so you can sanity-check the run without trawling the calendar.

Example run

Say the Team sheet contains:

name	birthday	startDate
Priya	1990-03-12	2022-06-01
Sam	1985-11-22	2025-09-15
Jordan	1992-07-04	2024-04-10

Run on 1 January 2026. The rituals calendar now contains:

Date	Event
2026-03-12	🎂 Priya
2026-06-01	🎉 Priya — 4 years
2026-11-22	🎂 Sam
2026-07-04	🎂 Jordan
2026-04-10	🎉 Jordan — 2 years

Sam joined in 2025, so 2026 - 2025 = 1 and the script creates the “1 years” anniversary in September. There is no Sam anniversary in the 2025 run because that would have been “0 years” on the joining day — the guard skips it.

Trigger it

Run it once a year on New Year’s Day so the calendar is set up for the year ahead:

1. In the Apps Script editor, open Triggers (clock icon).
2. Add a trigger for syncTeamDates, event source Time-driven, type Specific date and time, set to 1 January 02:00 of next year.
3. Approve the authorisation prompt the first time.

Apps Script does not have a “yearly” trigger built in. The pragmatic fix is to re-create the trigger each year as part of the run, or set a calendar reminder to do it by hand on 1 January.

Watch out for

• This script only creates. It does not check whether last year’s events exist or whether this year’s already do, so running it twice in the same year gives you duplicate cake emojis. The fix is to either run it exactly once on January 1st, or to extend it to search the calendar for the current year’s events first.
• The birthday cell’s year is ignored. People can keep their real birth year private; the script only uses month and day. Same with startDate — the year matters there, but only to count anniversaries.
• All-day events default to “free” on Calendar, which is what most teams want for a rituals calendar. If you would rather they appear as “busy”, set event.setTransparency() after creation.
• If the team grows mid-year, this script will not pick the new joiners up until next January. For an “on hire” version, add a row-edit trigger that creates that person’s events immediately.