3,013 reads

How to Automate Birthday Emails with Google Sheets in 5 minutes

by Manfye GohJune 7th, 2021

Too Long; Didn't Read

How to Automate Birthday Emails with Google Sheets in 5 minutes. Set up Google App Script to send birthday emails to customers with a DOB of 30/5. Scheduling Daily Email Greeting Email triggers can be triggered every day 10 am — 11 am as it is a perfect period of time to open a birthday greeting. Goh Pharmacist and Software Engineer in Malaysia, graduated from Master in Data Science and Analytics and Analytics at the University of Malawi University of Malaysia, where he is a Pharmacist.

People Mentioned

Company Mentioned

Coin Mentioned

featured image - 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

according to your preferences. In this case, my sheet name will be “CustomerDb”. You can view my example of google sheet .

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

on the left of your screen and the code area on the right of your screen.

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='//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()

to run every day to check the birthday in the database. It is easily done as Google App Script offers a few types of triggers according to the user's needs.

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!