Send Personalised Birthday Emails from Google Sheets Using Apps Script

Remembering birthdays is easy when a script does it for you. Keep a list of contacts with birthdays in a Sheet, and let Apps Script send personalised emails automatically every morning.

Sheet structure

Set up your sheet with these columns:

Column D prevents duplicate emails if you run the script multiple times.

Check for birthdays and send emails

function sendBirthdayEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  const todayMonth = today.getMonth() + 1;
  const todayDay   = today.getDate();
  const thisYear   = today.getFullYear();

  for (let i = 1; i < data.length; i++) {
    const [name, email, birthday, lastSentYear] = data[i];
    if (!birthday || !email) continue;

    const bDate = new Date(birthday);
    const bMonth = bDate.getMonth() + 1;
    const bDay   = bDate.getDate();

    // Check if today is their birthday and we haven't sent this year
    if (bMonth === todayMonth && bDay === todayDay && lastSentYear !== thisYear) {
      sendBirthdayEmail(name, email);
      sheet.getRange(i + 1, 4).setValue(thisYear); // Mark as sent
      Logger.log(`Birthday email sent to ${name} (${email})`);
    }
  }
}

function sendBirthdayEmail(name, email) {
  const subject = `🎂 Happy Birthday, ${name}!`;
  const html = `
    <div style="font-family:sans-serif;max-width:500px;margin:0 auto;text-align:center;">
      <h1 style="font-size:48px;">🎂</h1>
      <h2 style="color:#1a73e8;">Happy Birthday, ${name}!</h2>
      <p style="font-size:16px;color:#444;">
        Wishing you a wonderful day filled with joy and celebration.
        Hope this year brings you everything you're looking for!
      </p>
      <p style="color:#888;font-size:13px;margin-top:32px;">
        With warm wishes from the team
      </p>
    </div>
  `;

  GmailApp.sendEmail(email, subject, `Happy Birthday, ${name}!`, { htmlBody: html });
}

Handle MM/DD format (no year)

If your birthdays are stored as strings like "03/15" or "March 15":

function parseBirthday(value) {
  if (!value) return null;

  // Handle MM/DD string format
  if (typeof value === 'string' && value.includes('/')) {
    const [month, day] = value.split('/').map(Number);
    return { month, day };
  }

  // Handle Date object or full date string
  const d = new Date(value);
  return { month: d.getMonth() + 1, day: d.getDate() };
}

function checkBirthdays() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  const thisYear = today.getFullYear();

  for (let i = 1; i < data.length; i++) {
    const [name, email, birthdayRaw, lastSentYear] = data[i];
    const birthday = parseBirthday(birthdayRaw);
    if (!birthday || !email) continue;

    const isBirthday = birthday.month === today.getMonth() + 1
                    && birthday.day   === today.getDate();

    if (isBirthday && lastSentYear !== thisYear) {
      sendBirthdayEmail(name, email);
      sheet.getRange(i + 1, 4).setValue(thisYear);
    }
  }
}

Send a preview list of upcoming birthdays

function getUpcomingBirthdays(daysAhead = 7) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues().slice(1);
  const today = new Date();

  const upcoming = data.filter(([name, , birthday]) => {
    if (!birthday) return false;
    const b = new Date(birthday);
    const thisYearBirthday = new Date(today.getFullYear(), b.getMonth(), b.getDate());
    const diff = (thisYearBirthday - today) / (1000 * 60 * 60 * 24);
    return diff >= 0 && diff <= daysAhead;
  });

  if (upcoming.length > 0) {
    const list = upcoming.map(([name, , b]) =>
      `${name}: ${new Date(b).toLocaleDateString('en-US', { month: 'long', day: 'numeric' })}`
    ).join('\n');

    GmailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      `Upcoming Birthdays (next ${daysAhead} days)`,
      list
    );
  }

  Logger.log(`Found ${upcoming.length} upcoming birthday(s).`);
}

Set up a daily morning trigger

function createBirthdayTrigger() {
  ScriptApp.newTrigger('sendBirthdayEmails')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

Tips

The "Last Sent Year" column prevents double-sending if the trigger fires multiple times or the script is run manually.
Be mindful of the GmailApp daily email quota (100 for Gmail, 1,500 for Workspace).
Consider adding an "Active" column (Yes/No) so you can easily disable birthday emails for specific contacts without deleting rows.
Test with your own email first before pointing at a real contact list.