AutoSheet Solutions

Do you want to send automatic birthday wishes to your friends, family, or colleagues? This Google Apps Script can help you achieve that! By using this script, you can send personalized birthday wishes via email based on the data in a Google Sheet. Follow the steps below to set it up:

Step 1: Set Up Your Google Sheet

Column B: Name
Column C: Date of Birth (format: DD/MM/YYYY)
Column D: Email address

Create a Google Sheet with the following columns:

Step 2: Add the Script to Your Google Sheet

Open your Google Sheet.

Go to Extensions > Apps Script.

Delete any code in the script editor and replace it with the script below

Replace [Your Name] with your actual name.

Step 3: Set Up a Trigger

To run the script automatically every day, you need to set up a trigger.

In the script editor, click on the clock icon to open the triggers page.

Click on Add Trigger.

Choose sendBirthdayWishes function.

Select the event source as Time-driven.

Choose Day timer and set the time for the script to run every day.

You can test the script by manually running it from the script editor. Go to Run > sendBirthdayWishes to ensure everything is working correctly.

function sendBirthdayWishes() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  if (!sheet) {
    Logger.log('Sheet1 not found.');
    return;
  }
  
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var today = new Date();
  var todayDayMonth = today.getDate() + "-" + (today.getMonth() + 1);
  var isLeapYear = (year) => (year % 4 === 0 && year % 100 !== 0) || (year % 400 === 0);

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === '') {
      continue; // Skip empty rows
    }

    var name = data[i][1]; // Adjust column index based on your sheet
    var dobCell = data[i][2]; // Date of Birth column
    var dob;
    if (typeof dobCell === 'object' && dobCell instanceof Date) {
      dob = dobCell.getDate() + "-" + (dobCell.getMonth() + 1); // Format DD-MM
    } else if (typeof dobCell === 'string' && dobCell !== '') {
      var dobParts = dobCell.split("/");
      if (dobParts.length === 3) {
        dob = dobParts[0] + "-" + dobParts[1]; // Format DD-MM
      } else {
        continue; // Skip rows with invalid Date of Birth format
      }
    } else {
      continue; // Skip rows with invalid or empty Date of Birth
    }

    // Handle February 29 case
    if ((dob === todayDayMonth) || 
        (dob === "29-2" && todayDayMonth === "1-3" && !isLeapYear(today.getFullYear()))) {
      var email = data[i][3]; // Email column
      var subject = "Happy Birthday " + name + "!";
      var message = "Dear " + name + ",\n\nI wish you a happy Birth Day!\n\nBest Regards,\n[Your Name]";
      
      // HTML message
      var htmlMessage = '<div style="font-family: Arial, sans-serif; color: #333; line-height: 1.5;">' +
                        '<h1 style="color: #4CAF50;">Happy Birthday ' + name + '!</h1>' +
                        '<p>Dear ' + name + ',</p>' +
                        '<p style="font-size: 1.2em;">I wish you a happy <strong>Birth Day!</strong></p>' +
                        '<p style="color: #FF5722;">Best Regards,<br>[Your Name]</p>' +
                        '</div>';
      
      MailApp.sendEmail({
        to: email,
        subject: subject,
        htmlBody: htmlMessage,
        body: message // Fallback plain text message
      });
    }
  }

}

AutoSheet Solutions

Tuesday, February 25, 2025

How to Automatically Send Birthday Wishes with Google Sheets and Apps Script.

No comments:

Post a Comment