Automating Birthday Emails with Google Sheets and Apps Script

Introduction: In today’s digital age, personalization is key to maintaining connections, whether it’s with friends, family, or colleagues. One way to add a personal touch is by sending birthday greetings. However, manually keeping track of everyone’s birthdays and sending emails can be time-consuming and prone to errors. Fortunately, with the power of Google Sheets and Google Apps Script, we can automate this process, ensuring that everyone receives their birthday wishes right on time.

Understanding the Problem: The task at hand is to create a script that reads data from a Google Sheets spreadsheet containing names and birthdays, checks if any birthdays match the current date, and sends automated birthday emails to those individuals.

Solution Overview: We’ll utilize Google Sheets as our data source, where each row represents a person, with columns for their name and birthday. Google Apps Script will serve as our automation tool, enabling us to read data from the spreadsheet, compare birthdays with the current date, and send personalized emails.

Assuming your spreadsheet has two columns: one for the name and one for the birthday date, here’s how the data might look:

A	B
Name	Birthday
John	05/08/1990
Alice	05/08/1985
Bob	05/09/1978
Sarah	05/09/1995

With this data structure, the script will read each row, extract the name and birthday, and compare the birthday to today’s date. If the birthday matches today’s date, it will send a birthday email to the specified email address.

Make sure to replace "email@example.com" with the actual email address where you want to send the birthday emails.

Once you have the spreadsheet data set up like this, you can run the sendBirthdayEmails() function, and it will send birthday emails to the corresponding people if their birthday matches today’s date.

Step-by-Step Implementation:

Set up the Spreadsheet:
- Create a Google Sheets spreadsheet with columns for “Name” and “Birthday”.
- Populate the spreadsheet with the names and birthdays of the individuals you want to send birthday emails to.
Write the Apps Script:
- Open Google Apps Script editor from your Google Sheets.
- Write a function to fetch data from the spreadsheet, compare birthdays with the current date, and send emails accordingly.
- Utilize the SpreadsheetApp and MailApp services provided by Google Apps Script for reading spreadsheet data and sending emails, respectively.
- Use the Utilities.formatDate() function to format dates consistently.
Test the Script:
- Run the script manually to ensure it behaves as expected.
- Verify that birthday emails are sent only to individuals whose birthdays match the current date.
Schedule Automated Execution:
- Set up a trigger within Google Apps Script to automatically run the birthday email script at a specific time each day.
- This ensures that birthday emails are sent without manual intervention, saving time and effort.

Conclusion: By leveraging the capabilities of Google Sheets and Google Apps Script, we’ve successfully automated the process of sending birthday emails. With this solution in place, you can maintain meaningful connections with friends, family, and colleagues by delivering personalized birthday greetings right on time. This not only saves time but also adds a personal touch that strengthens relationships in today’s digital world.

Remember, automation doesn’t just streamline tasks; it enhances the human touch by ensuring that no special occasion goes unnoticed. So, why not give it a try and make someone’s day with an automated birthday email?

function sendBirthdayEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet10');
  const dataRange = sheet.getDataRange();
  const data = dataRange.getValues();
  const today = new Date();
  data.forEach(function(row) {
    const name = row[0];
   const birthday = new Date(row[1]); // Convert cell value directly to a Date object
    // Set the year of the birthday to the current year to compare only month and day
    birthday.setFullYear(today.getFullYear());
    if (isSameDay(birthday, today)) {
      MailApp.sendEmail(EMAIL, "Happy Birthday!", "Happy Birthday, " + name + "!");
    }
  });
}
// Function to check if two dates are the same day
function isSameDay(date1, date2) {
  //Logger.log(date1.getMonth() +' '+ date2.getMonth() +' '+  date1.getDate() +' '+  date2.getDate());
  return date1.getMonth() === date2.getMonth() && date1.getDate() === date2.getDate();
}

Share this:

Related