How to Send Birthday Wishes Automatically Using Google Sheet and App Script?

Guide to send emails automatically from google spreadsheet .

Google apps script is one of the powerful tools available entirely for free and has the potentiality to automate your daily tasks. Here am presenting you a scenario where you need to send wishes automatically to a group of people based on birth date. With App scripts mail send function and google sheet we can create the task and make it entirely free of cost

Create the users list on Google Sheet

The idea is to send emails based on the birth date to a group  of people . We have the data with the name and birth date in a google sheet or excel spreadsheet .The NAME column has the name of the person , DOB column specifies his date of birth and in EMAIL ID column we have furnished the email ids.

The sheet we  made will also need the parity column which will check the DOB column with today's date and give a value as true if the day and month matches and false if it doesn't match. Use the below formulae and create a column BIRTHDAY and we will get the values as true or false based on birth date.

=AND(day(B7)=day(today()),month(today())=month(B7))

Creating the Script to Send Mail

To get started with the scripting open the script editor window from the tools menu in google sheets. After opening the same provide a function name for the script, here am creating the function as sendwishes.

After this Copy and paste the below code in the function sendwishes and give the project a name .

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet();
 
  var lastRow = sheet.getLastRow();
  var startRow = 6;

  var msg = "";
 
  for (var i =startRow ; i <= lastRow; i++) {
    if(sheet.getRange(i, 4).getValue()==true) {
       MailApp.sendEmail(sheet.getRange(i, 3).getValue(),
        "BirthDay Wishes", "Hi \n" + sheet.getRange(i, 1).getValue() + "\n\n" + sheet.getRange(1, 2).getValue() + "\n\n" + "With Love,\n" + "Sreenath,\n");
    }
  } 

Save the Script and give permissions to run the script ,once the permission is granted Gmail will send the mail to the recipient with the wishes message. Get script from GitHub.

 

Automating the Process

We have successfully created and run the script for sending email .Now we need to automate the process to run the script daily at specific time for that google gives as the option of triggering the script .We need to open Appscript window . On my projects window we can see the project and we have to trigger the same daily at a specified time. click on the sandwich menu of the script and open triggers

Open Add trigger option and select the details like which script we are about to automate ,when we need to run and how it is supposed to run (time based or while opening spreadsheet) and notification for  failures. and save the trigger. From now on our script will run at the specified time. 

Above script  triggered to run daily between 5am to 6am and will check the column birthday in our sheet and automatically send the mail to respective mail-id.

This same method of scripting and triggering  can be utilized to send automated mails in numerous scenarios like warranty renewal reminders, domain renewal reminders , bulk mails to customers (just need to change the condition) , appointments, get notification on tasks and much more.