How to Automate Birthday Emails with Google Sheets in 5 minutes

Often small and medium-sized businesses use email to keep in contact with their customer. Email such as seasonal greetings, festival greetings are great; it is even more fabulous when you can personalize the email to each of your customers, such as a birthday greetings email which provides special offers to your customers for a particular month. This will greatly improve customer engagement.

Scenario: Your company has the DOB and Email of customers and wants to send a birthday email greeting to the customers to show your appreciation to them.

Step 1: Prepare your Google Sheet

Prepare your google sheet as below, make sure you had formatted the column DOB to Date format using:

Format → Number → Date

This will tell google sheet that this field is a date field, you can choose the format you prefer such as

dd/MM/yyyy

MM/dd/yyyy

2. Setup Google App Script

Go to Tools → Script Editor to open up and the Authorized Google Apps Script, you will notice a file named

Code.gs

Paste the following code inside:

function sendBirthdayEmails() {
// Get the sheet where the data is, in sheet 'CustomerDb'
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CustomerDb")
//Setting up today date, the date when the function activated
var now = Utilities.formatDate(new Date(), "GMT+8", "dd-MM");
// Get all value of the sheet
var data = sheet.getDataRange().getValues();
// Loop through each row of the sheet
data.forEach(function (row) {
// Check if the date (month and day) are matched to today date, send email when matched
if (Utilities.formatDate(new Date(row[2]), "GMT+8", "dd-MM") === now) {
Logger.log("Send Email")
MailApp.sendEmail({
to: row[3],
subject: "Happy Birthday " + row[1],
htmlBody: "Dear " + row[1] + "," + "<br>" + "<br>" +
"Happy Birthday to you! " + "<br>" + "<br>" +
"<img width='500' src='https://images.pexels.com/photos/3905849/pexels-photo-3905849.jpeg?auto=compress&cs=tinysrgb&dpr=2&h=750&w=1260'>" +
"<br>" +
"Sincerely from," + "<br>" + "DS Company"
});
}
else {
Logger.log("Birthday Not Match")
}
});
}

The code will run through each row of the database, and the row[0], row[1], row[2], row[3] in the code is corresponding to the column A, B, C, D in the datasheet.

Whenever the code found matches between today date and the DOB (exclude the year):

Utilities.formatDate(new Date(row[2]), "GMT+8", "dd-MM") === now

It will fire a function “sendEmail”.

Test the code by saving and click the Run button on top. You will get an email as below:

As the date of writing this article is 30/5/2021, the function will only send to customer with a DOB of 30/5, which is “Manfye Goh 0", customize your email according to your organization's need.

3. Scheduling Daily Checking

The idea of this function is to schedule the

sendBirthdayEmails()

In this case, I would like the function to be triggered every day 10 am — 11 am as it is a perfect period of time to open a Birthday Greeting Email. Lastly, save the trigger, and you have will never miss any birthday greet email to your customer anymore!

This simple setup only took 5 minutes and it will improve your relationship with your customers, try it out!

Final words:

This article demonstrates how to schedule send birthday emails according to the data in the cells, with slight modification and understanding of the code, you can modify it to automate schedule email such as invoice reminder, seasonal greeting, or even schedule a specific email at a specific time.

Do share with me your creativity if you do so.

Lastly, Thank you for your time in reading my article!

Credits and References:

• https://developers.google.com/google-ads/scripts/docs/features/dates
• Featured Image credits: cottonbro from Pexels