Automate birthday wish email from Gmail using Google Sheet and GSrcipt

Automate Bday Wish using gmail

Problem domain: In organisation people have to manually check the birthday from calendar to wish the concern.
A good solution is to set up an automated mailer for the wishes to send by email to your colleagues with CC to HR.

Same automation concept can be utilized in other problem domain.

Let’s Start :

Step 1: Create a new google sheet and save  DOB, name, and email of your team/organization/Friends, etc
Choose Drive →Select New →Select Google Sheet
[[If you don't have these details then you can collect it using google form. To create a form, follow these steps:
Choose Drive →Select New →Select more →Select Google Forms]]

Finally you will have a excel sheet which may looks like this.

Copy the url upto slash to use in Google Script.

Step 2: Now create an app script, that sends the email to the birthday person.
Choose Drive →Select More →Select Google Apps Script



Step 3: Now create the script as shown. Source code you can copy.



Here is the source code to copy:
function sendBdayWishes(){
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/16fbf4eQrFQ4CbMPZVLF-Qk_IZspP2-ex2z1YW0/"); //  Sheet Url must have slash at end
var sheet = ss.getSheetByName("Sheet1");// Make Sure Sheet name matches at the bottom
var cDate = new Date(); //Present Day,
for(var i =2 ;i<=sheet.getLastRow(); i++){
  //loop through each row
var bDate = sheet.getRange(i,2).getValue(); // get Date from SpreadSheet 2nd column
  //now check if any date is matching
if(cDate.getDate()==bDate.getDate()){
if(cDate.getMonth()==bDate.getMonth()){
var name = sheet.getRange(i,1).getValue();

var toMail= sheet.getRange(i,3).getValue() + "," + "vinodkotiya@gmail.com" ; // get email from SpreadSheet 3rd column
var htmlBody =  "Dear " + name + "
We wish you an amazing year that ends with accomplishing all the great goals that you have set! No one could do a better job than the job you do. We thank you for all your efforts!

Enjoy your birthday! Cheers! 

-Team BIFPCL"  ;
MailApp.sendEmail(toMail,'Happy BirthDay '+name,' ' ,{htmlBody : htmlBody});// send to myself to test
sheet.getRange(i,4).setValue("Bday wishes sent");
}
}
}

}

There is a play button to test the script and remove any errors.
Suggestion: To test the app give a date of birth as present day of testing for your name.

Step 4: Give the Authorization to run the Script
The first time you run it, Google requires you to authorize it. An “Authorization required” window will pop up asking for your permission. Select “Review Permissions.”
It will then ask for a Gmail account. Click on your account name and click “Allow”.
It will run the script and send on an email on the specified date. Now we can automate this process by trigger.

Step 5: Now create the trigger to automate the process.
Click on the Clock button in the App Script code →Click on + Add Trigger
A new window will open. Add a new trigger like this which will run on daily basis at midnight once.




That's it. It will automatically send bday wish to concerned person at 1 AM after triggering daily.

-Written by
Vinod Kotiya