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 = "

" + "

Dear " + escapeHtml(name) + ",

" + "

We extend our heartfelt wishes to you on this special day.

" + "

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.

" + "

May this new chapter be filled with achievements, happiness, and endless opportunities. " + "Keep shining and keep making a difference.

" + "

Once again, Happy Birthday!

" + "
" + "

Best regards,
" + "Olatunji
" + "Provost
" + "Recyclers Association of Nigeria (RAN)

" + "

"; // 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, "&") .replace(//g, ">") .replace(/"/g, """) .replace(/'/g, "'"); } } ``` 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.| ![Trigger.png](https://images.hive.blog/DQmWA5wWvsP3rGK1DBSngYYMdpsk7WKujytWVh9nFLSoUNH/Trigger.png) * 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 = "

" + "

Dear " + escapeHtml(name) + ",

" + "

We extend our heartfelt wishes to you on this special day.

" + "

May this new chapter be filled with achievements, happiness, and endless opportunities. " + "Keep shining and keep making a difference.

" + "

Once again, Happy Birthday!

" + "
" + "

Best regards,
" + "Olatunji
" + "Provost
" + "Recyclers Association of Nigeria (RAN)

" + "

Topics