Automatic Birthday Greetings Sender (Recurring)

[ecohive]

As the Provost of the Recyclers Association of Nigeria (RAN), I often found myself failing to remember members' birthdays and send personalized wishes. With over 200 members and still growing, manually sending birthday emails have become challenging. I am not a fan of 3rd party apps to schedule things, and I tried using the built-in gmail scheduler, but it only works for a year not for recurring things like birthdays. It is also manual because I must copy each message, edit the celebrant’s name, and input their mail every time. It is stressful.
That is why I decided to utilize coding to solve this problem.

The Challenge

Managing birthdays for an organization isn't just about remembering dates. It is about:

• Consistency: Ensuring every member receives birthday wishes on their special day
• Personalization: Making each message feel genuine and heartfelt
• Reliability: Having a system that works even when you're busy with other responsibilities
• Safety: No fear of hacking or unauthorized sensitive information.
• Efficiency: Automating the process to save time and reduce human error

This is why I built an automatic email system using Google Apps Script that integrates with Google Sheets. Google Apps Scripts is basically JavaScript built into Google Workspace. Since I have knowledge of Vanilla Javascript, I decided to give it a go. I settled for this system after many iterations and mistakes.

/**
 * Robust birthday sender with debug logging.
 * - Expects sheet columns:
 *   A = Name, B = Email, C = Birthday, D = Status (will be written), E = Debug log (will be written)
 *
 * BEFORE FIRST RUN: Save the project, select this function in the dropdown, then click Run and accept permissions.
 */
function sendBirthdayEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startRow = 2; // header row is 1
  var lastRow = sheet.getLastRow();
  if (lastRow < startRow) {
    SpreadsheetApp.getUi().alert('No data rows found (no rows below header).');
    return;
  }

  // Config
  var SEND_REAL_EMAILS = true; // <<-- set to true when you're ready to send to actual recipients
  var TEST_RECIPIENT = Session.getActiveUser().getEmail() || ""; // will receive test emails when SEND_REAL_EMAILS=false
  var tz = Session.getScriptTimeZone();

  var numRows = lastRow - startRow + 1;
  // Read A-D for each row (Name, Email, Birthday, Status)
  var rows = sheet.getRange(startRow, 1, numRows, 4).getValues();

  var now = new Date();
  var todayDay = now.getDate();
  var todayMonth = now.getMonth() + 1;
  var todayDateShort = Utilities.formatDate(now, tz, "dd/MM/yyyy");

  for (var i = 0; i < rows.length; i++) {
    var rowIndex = startRow + i;
    var name = rows[i][0] ? String(rows[i][0]).trim() : "";
    var email = rows[i][1] ? String(rows[i][1]).trim() : "";
    var birthdayCell = rows[i][2];
    var statusCell = rows[i][3] ? String(rows[i][3]) : "";

    var debugCol = 5; // column E
    var statusCol = 4; // column D

    // Parse birthday robustly
    var parsedBirthday = parseBirthday(birthdayCell);
    if (!name && !email) {
      sheet.getRange(rowIndex, debugCol).setValue("Skipped: empty name & email");
      continue;
    }
    if (!parsedBirthday || isNaN(parsedBirthday.getTime())) {
      sheet.getRange(rowIndex, debugCol).setValue("Invalid birthday value: " + String(birthdayCell));
      continue;
    }

    var bDay = parsedBirthday.getDate();
    var bMonth = parsedBirthday.getMonth() + 1;

    // Check if already sent this year
    var alreadySentThisYear = false;
    var m = statusCell.match(/✅ Sent on (\d{2}\/\d{2}\/\d{4})/);
    if (m) {
      var sentYear = Number(m[1].split("/")[2]);
      if (sentYear === now.getFullYear()) alreadySentThisYear = true;
    }

    // Not their birthday today?
    if (!(bDay === todayDay && bMonth === todayMonth)) {
      sheet.getRange(rowIndex, debugCol).setValue("Not birthday today (" + pad(bDay) + "/" + pad(bMonth) + ")");
      continue;
    }

    // Already sent this year?
    if (alreadySentThisYear) {
      sheet.getRange(rowIndex, debugCol).setValue("Already sent this year: " + statusCell);
      continue;
    }

    // Validate email
    if (!validateEmail(email)) {
      sheet.getRange(rowIndex, debugCol).setValue("Invalid email: " + email);
      continue;
    }

    // Build message
    var subject = "Happy Birthday " + name + " from Recyclers Association of Nigeria";
    var plainBody =
      "Dear " + name + ",\n\n" +
      "We extend our heartfelt wishes to you on this special day.\n\n" +
      "Your dedication and contributions to our association and the recycling industry inspire us all. " +
      "As you celebrate another year of life, we pray that it brings you good health, joy, and continued success in all your endeavors.\n\n" +
      "May this new chapter be filled with achievements, happiness, and endless opportunities. " +
      "Keep shining and keep making a difference.\n\n" +
      "Once again, Happy Birthday! 🎉\n\n" +
      "Best regards,\n" +
      "Olatunji\n" +
      "Provost\n" +
      "Recyclers Association of Nigeria (RAN)";

    var htmlBody =
      "<div style='font-family:Arial, sans-serif; line-height:1.6; color:#333;'>" +
        "<p><strong>Dear " + escapeHtml(name) + ",</strong></p>" +
        "<p>We extend our heartfelt wishes to you on this special day.</p>" +
        "<p>Your dedication and contributions to our association and the recycling industry inspire us all. " +
        "As you celebrate another year of life, we pray that it brings you good health, joy, and continued success in all your endeavors.</p>" +
        "<p>May this new chapter be filled with achievements, happiness, and endless opportunities. " +
        "Keep shining and keep making a difference.</p>" +
        "<p><strong>Once again, Happy Birthday! </strong></p>" +
        "<br>" +
        "<p>Best regards,<br>" +
        "<span style='color:green; font-weight:bold;'>Olatunji</span><br>" +
        "Provost<br>" +
        "<span style='color:#2e7d32;'>Recyclers Association of Nigeria (RAN)</span></p>" +
      "</div>";

    // Send email (or test send)
    try {
      if (SEND_REAL_EMAILS) {
        GmailApp.sendEmail(email, subject, plainBody, { htmlBody: htmlBody });
      } else {
        // test: send to TEST_RECIPIENT if available, otherwise to your account
        var toAddress = TEST_RECIPIENT || Session.getActiveUser().getEmail() || email;
        GmailApp.sendEmail(toAddress, "[TEST] " + subject, plainBody, { htmlBody: htmlBody });
      }

      // Log status and debug
      sheet.getRange(rowIndex, statusCol).setValue("✅ Sent on " + Utilities.formatDate(now, tz, "dd/MM/yyyy"));
      sheet.getRange(rowIndex, debugCol).setValue("Sent (" + (SEND_REAL_EMAILS ? "real" : "test to " + (TEST_RECIPIENT || "you")) + ") at " + Utilities.formatDate(now, tz, "HH:mm"));

    } catch (err) {
      sheet.getRange(rowIndex, debugCol).setValue("Error sending: " + err.message);
    }
  }

  // Helpers ----------------------------------------------------------------

  function parseBirthday(cell) {
    if (!cell && cell !== 0) return null;
    // If it's already a Date object
    if (Object.prototype.toString.call(cell) === '[object Date]') {
      return cell;
    }
    // If it's a number (rare), try to convert spreadsheet serial to JS date
    if (typeof cell === 'number') {
      try {
        // Sheets date serial -> JS epoch (approx). Works for typical cases.
        return new Date(Math.round((cell - 25569) * 86400 * 1000));
      } catch (e) {
        return null;
      }
    }
    // If it's a string like "25/09/1990" or "25-09-1990"
    if (typeof cell === 'string') {
      var s = cell.trim();
      var m = s.match(/^(\d{1,2})[\/\-\.\s](\d{1,2})[\/\-\.\s](\d{2,4})$/);
      if (m) {
        var d = parseInt(m[1], 10);
        var mo = parseInt(m[2], 10);
        var y = parseInt(m[3], 10);
        if (y < 100) { y += (y > 30 ? 1900 : 2000); } // heuristic
        return new Date(y, mo - 1, d);
      }
      // last resort: try Date.parse
      var dt = new Date(s);
      if (!isNaN(dt.getTime())) return dt;
    }
    return null;
  }

  function validateEmail(email) {
    if (!email) return false;
    var re = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return re.test(email);
  }

  function pad(n) { return (n < 10 ? '0' + n : String(n)); }

  function escapeHtml(text) {
    if (!text) return '';
    return String(text)
      .replace(/&/g, "&amp;")
      .replace(/</g, "&lt;")
      .replace(/>/g, "&gt;")
      .replace(/"/g, "&quot;")
      .replace(/'/g, "&#039;");
  }
}

The system contains some important features that make it unique.

• Duplicate Prevention: The system tracks users that have received wishes in a year and doesn’t spam them with multiple wishes.
• Date Recognition: The system can decode birthdays written in multiple formats:
  • Date objects from spreadsheet cells
  • Text formats like "28/09/1990" or "28-09-1990"
• Safe Mode: There is a test mode where you can send wishes to Google Sheet logged-in mail to confirm everything works fine. That prevents spam messages to members during testing.
• Log Information: Every action is logged with detailed information making it easy to debug any problem.
• Professional Templates: Clean and responsive HTML emails that look great on any device.

Setup

The setup is quite simple. You need to create a Google Sheet on the account you wish to send messages from and create these columns

Column	Data	Purpose
A	Name	The recipient's full name.
B	Email	The recipient's email address.
C	Birthday	The date of birth in 28/09/2025 format
D	Status [AUTO]	Will be updated with ✅ Sent on [Date] or Not birthday today.
E	Debug Log [AUTO]	Records the script's action for each row.

• Fill the Google Sheet with the necessary member details

• Go to Google Apps Script by clicking Extensions > Apps Script then copy and paste the code

• Save to drive and run. It will ask for authorization. Authorize it and continue

• Go to the sidebar and set up a daily trigger.

• Click Add a trigger. Choose the function to run which is sendBirthdayEmails. Select event source and change it to Time-driven. Select type of time based trigger and change to Day timer. Select the time of day you want your birthday greetings to be sent.

The message will be automatically sent within the timeframe whether you are online or not, your phone or laptop is on or not. It works automatically. I picked 10am to 11am and it has been working so far.

Save and enjoy automated birthday messages

Code Explanation

I will explain the code so that you can adjust and tailor it to your organization’s needs.

Line-by-Line Explanation

function sendBirthdayEmails() {
This is the main function. Every time it runs the code, it starts here and runs everything inside this function.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
Gets the active Google Sheet where your members information is stored and assign it a variable named sheet.

  var startRow = 2; 
  var lastRow = sheet.getLastRow();

We start from row 2 to skip headers that aren’t needed for this code.
lastRow finds the last row with data, so we know how many rows to process.

if (lastRow < startRow) {
    SpreadsheetApp.getUi().alert('No data rows found (no rows below header).');
    return;
  }

If there are no rows of data, display a popup alert and stop the script.

var SEND_REAL_EMAILS = true; 
  var TEST_RECIPIENT = Session.getActiveUser().getEmail() || "";
  var tz = Session.getScriptTimeZone();

The first variable allows you to switch from test mode to live mode. Test mode will send all emails to your own gmail and you need to change the Boolean value from true to false.
tz → gets the script’s timezone (important for checking dates correctly).

var numRows = lastRow - startRow + 1;
  // Read A-D for each row (Name, Email, Birthday, Status)
  var rows = sheet.getRange(startRow, 1, numRows, 4).getValues();

  var now = new Date();
  var todayDay = now.getDate();
  var todayMonth = now.getMonth() + 1;
  // ... (todayDateShort not directly used but formats today's date for logging)

This script is used to read the sheet data and also to confirm the present date then cross-check with members birthdays.
The script reads a block of data from the sheet efficiently, from the starting row down to the last row, across four columns (A, B, C, D)(Name, Email, Birthday, Status). This avoids slower, row-by-row fetching.
It then determines today's day and month to check against the members' birthdays.

for (var i = 0; i < rows.length; i++) {
    var rowIndex = startRow + i;
    var name = rows[i][0] ? String(rows[i][0]).trim() : "";
    var email = rows[i][1] ? String(rows[i][1]).trim() : "";
    var birthdayCell = rows[i][2];
    var statusCell = rows[i][3] ? String(rows[i][3]) : "";

    var debugCol = 5; // column E
    var statusCol = 4; // column D

This is the main loop that checks every row of data. Inside the loop, it pulls the data for Name, Email, Birthday, and Status for the current row.

// Parse birthday robustly
    var parsedBirthday = parseBirthday(birthdayCell);
    if (!name && !email) {
      sheet.getRange(rowIndex, debugCol).setValue("Skipped: empty name & email");
      continue;
    }
    if (!parsedBirthday || isNaN(parsedBirthday.getTime())) {
      sheet.getRange(rowIndex, debugCol).setValue("Invalid birthday value: " + String(birthdayCell));
      continue;
    }

    var bDay = parsedBirthday.getDate();
    var bMonth = parsedBirthday.getMonth() + 1;

This converts each birthday cell into a Date object with option to skip if there’s no mail or name and log an error if the date is invalid.
The bDay and bMonth get each cell day and month while ignoring the year.

// Check if already sent this year
    var alreadySentThisYear = false;
    var m = statusCell.match(/✅ Sent on (\d{2}\/\d{2}\/\d{4})/);
    if (m) {
      var sentYear = Number(m[1].split("/")[2]);
      if (sentYear === now.getFullYear()) alreadySentThisYear = true;
    }

    // Not their birthday today?
    if (!(bDay === todayDay && bMonth === todayMonth)) {
      sheet.getRange(rowIndex, debugCol).setValue("Not birthday today (" + pad(bDay) + "/" + pad(bMonth) + ")");
      continue;
    }

    // Already sent this year?
    if (alreadySentThisYear) {
      sheet.getRange(rowIndex, debugCol).setValue("Already sent this year: " + statusCell);
      continue;
    }

This is a crucial part of the code. It prevents duplicates and uses a Regular Expression (.match()) to look for the ✅ Sent on [Date] pattern in the Status column. If it finds a send date that matches the current year, it sets alreadySentThisYear = true to prevent double-sending. This is a critical anti-spam feature.

It also compares the day and month of the member's birthday against today's day and month. If they don't match, it logs the Not birthday message and skips the email.

If it is the birthday, it checks if the email was already sent this year and logs Already sent this year message.

 // Validate email
    if (!validateEmail(email)) {
      sheet.getRange(rowIndex, debugCol).setValue("Invalid email: " + email);
      continue;
    }

    // Build message
    var subject = "Happy Birthday " + name + " from Recyclers Association of Nigeria";
    var plainBody =
      "Dear " + name + ",\n\n" +
      "We extend our heartfelt wishes to you on this special day.\n\n" +
      "Your dedication and contributions to our association and the recycling industry inspire us all. " +
      "As you celebrate another year of life, we pray that it brings you good health, joy, and continued success in all your endeavors.\n\n" +
      "May this new chapter be filled with achievements, happiness, and endless opportunities. " +
      "Keep shining and keep making a difference.\n\n" +
      "Once again, Happy Birthday! 🎉\n\n" +
      "Best regards,\n" +
      "Olatunji\n" +
      "Provost\n" +
      "Recyclers Association of Nigeria (RAN)";

    var htmlBody =
      "<div style='font-family:Arial, sans-serif; line-height:1.6; color:#333;'>" +
        "<p><strong>Dear " + escapeHtml(name) + ",</strong></p>" +
        "<p>We extend our heartfelt wishes to you on this special day.</p>" +
        "<p>Your dedication and contributions to our association and the recycling industry inspire us all. " +
        "As you celebrate another year of life, we pray that it brings you good health, joy, and continued success in all your endeavors.</p>" +
        "<p>May this new chapter be filled with achievements, happiness, and endless opportunities. " +
        "Keep shining and keep making a difference.</p>" +
        "<p><strong>Once again, Happy Birthday! </strong></p>" +
        "<br>" +
        "<p>Best regards,<br>" +
        "<span style='color:green; font-weight:bold;'>Olatunji</span><br>" +
        "Provost<br>" +
        "<span style='color:#2e7d32;'>Recyclers Association of Nigeria (RAN)</span></p>" +
      "</div>";

    // Send email (or test send)
    try {
      if (SEND_REAL_EMAILS) {
        GmailApp.sendEmail(email, subject, plainBody, { htmlBody: htmlBody });
      } else {
        // test: send to TEST_RECIPIENT if available, otherwise to your account
        var toAddress = TEST_RECIPIENT || Session.getActiveUser().getEmail() || email;
        GmailApp.sendEmail(toAddress, "[TEST] " + subject, plainBody, { htmlBody: htmlBody });
      }

      // Log status and debug
      sheet.getRange(rowIndex, statusCol).setValue("✅ Sent on " + Utilities.formatDate(now, tz, "dd/MM/yyyy"));
      sheet.getRange(rowIndex, debugCol).setValue("Sent (" + (SEND_REAL_EMAILS ? "real" : "test to " + (TEST_RECIPIENT || "you")) + ") at " + Utilities.formatDate(now, tz, "HH:mm"));

    } catch (err) {
      sheet.getRange(rowIndex, debugCol).setValue("Error sending: " + err.message);
    }

This script checks the email address to know if it is correct or wrong. A wrong address will log Invalid Email in the debug column.

The script uses both a plainBody (for older email clients) and an htmlBody (for modern formatting). It dynamically inserts the recipient's name for personalization.

The GmailApp.sendEmail service is used to send to an actual mail and also show up in gmail sent items.

The script either logs the success and the date in Column D (Status) or logs the error message in Column E(Debug log), making it simple to troubleshoot any issues.

// Helpers ----------------------------------------------------------------

  function parseBirthday(cell) {
    if (!cell && cell !== 0) return null;
    // If it's already a Date object
    if (Object.prototype.toString.call(cell) === '[object Date]') {
      return cell;
    }
    // If it's a number (rare), try to convert spreadsheet serial to JS date
    if (typeof cell === 'number') {
      try {
        // Sheets date serial -> JS epoch (approx). Works for typical cases.
        return new Date(Math.round((cell - 25569) * 86400 * 1000));
      } catch (e) {
        return null;
      }
    }
    // If it's a string like "25/09/1990" or "25-09-1990"
    if (typeof cell === 'string') {
      var s = cell.trim();
      var m = s.match(/^(\d{1,2})[\/\-\.\s](\d{1,2})[\/\-\.\s](\d{2,4})$/);
      if (m) {
        var d = parseInt(m[1], 10);
        var mo = parseInt(m[2], 10);
        var y = parseInt(m[3], 10);
        if (y < 100) { y += (y > 30 ? 1900 : 2000); } // heuristic
        return new Date(y, mo - 1, d);
      }
      // last resort: try Date.parse
      var dt = new Date(s);
      if (!isNaN(dt.getTime())) return dt;
    }
    return null;
  }

  function validateEmail(email) {
    if (!email) return false;
    var re = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return re.test(email);
  }

  function pad(n) { return (n < 10 ? '0' + n : String(n)); }

  function escapeHtml(text) {
    if (!text) return '';
    return String(text)
      .replace(/&/g, "&amp;")
      .replace(/</g, "&lt;")
      .replace(/>/g, "&gt;")
      .replace(/"/g, "&quot;")
      .replace(/'/g, "&#039;");
  }
}

The last function is the helper functions. They make the script dynamic

• parseBirthday(cell): It tries to convert the value in the Birthday cell into a usable JavaScript Date object.
• validateEmail(email): It quickly checks if the email format is correct using a basic regular expression(regex).
• pad(n): Simply adds a leading zero to numbers less than 10 (e.g., turning 9 into 09) for consistent date formatting.
• escapeHtml(text): Protects the HTML email from error in case the recipient's name contains characters like <, >, or &.

In summary, this script automates what I could have been battling with for weeks and not just weeks but every year.

I have also included a workflow diagram to understand how it works

Comments

[commentators]

I sometimes forget my own birthday ^^

[ecohive]

It is normal. My bank remind me sometimes with their celebratory mail.