Google App Script to Send Email

google_app_scripts_send_email.js

// Fetch data from the spreadsheet
function fetchDataFromSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var data = [];
  
  for (var i = 2; i <= lastRow; i++) {
    var row = sheet.getRange("A" + i + ":L" + i).getValues()[0];
    data.push({
      empCode: row[0],
      empName: row[1],
      empEmail: row[2],
      mentorEmail: row[3],
      certType: row[4],
      certName: row[5],
      certCompletedDate: row[6],
      certDueDate: row[7],
      certLink: row[8],
      certStatus: row[9]
    });
  }
  
  return data;
}

// Send certification reminders
function hsSendCertificationRemindersEmail() {

  var data = fetchDataFromSheet();
  var currentDate = new Date();
  var currentFormatDate = (currentDate.getMonth() + 1) + '/' + currentDate.getDate() + '/' + currentDate.getFullYear();
  var ccEmail = '[email protected]';
  var bccEmail = '[email protected]';
  
  var emailBatch = [];
  
  data.forEach(function(row) {
    if (!row.empEmail) return;
    
    if (row.certDueDate && row.certStatus !== 'Completed') {
      var subject = `${row.certType} ${row.certName} Certification Reminder`;
      var message = `Dear ${hsExtractFirstName(row.empName)},<br><br>

We trust you are doing well!<br><br>

As a reminder, your goal to attain the ${row.certType} ${row.certName} certification is an important part of your professional development.<br><br>

The due date for achieving this certification is ${hsFormatDate(row.certDueDate)}. Please ensure you're on track with your preparation. If you need any resources or support, don't hesitate to connect with colleagues who have already successfully obtained this certification.<br><br>

Your commitment to learning not only benefits your career but also adds value to our organization. We believe in your abilities and look forward to celebrating your certification.<br><br>

--<br>
Thanks
`;

      emailBatch.push({
        to: row.empEmail,
        cc: row.mentorEmail ? row.mentorEmail : ccEmail,
        bcc: bccEmail,
        subject: subject,
        body: message,
        htmlBody: message
      });
      //Logger.log(`Reminder Mail Sent to: ${row.empName}`);
    }
  });

  if (emailBatch.length > 0) {
    for (var i = 0; i < emailBatch.length; i++) {
      var emailData = emailBatch[i];
      Logger.log(`Reminder Mail Sent to CC: ${emailData.cc}`);
      MailApp.sendEmail({
        to: emailData.to,
        cc: emailData.cc,
        bcc: emailData.bcc,
        subject: emailData.subject,
        body: emailData.body,
        htmlBody: emailData.htmlBody
      });
    }
  }
}

// Format the Date
function hsFormatDate(inputDate, formatType = '') {
  if (!inputDate) return '';

  var formatType = formatType ? formatType : '';

  // Convert MM DD YYYY Date format
  if (formatType === 'mm_dd_yyyy') {
    var dateObject = new Date(inputDate);
    var formattedDate = (dateObject.getMonth() + 1) + '/' + dateObject.getDate() + '/' + dateObject.getFullYear();
    return formattedDate;
  }

  var replaceDate = new Date(inputDate); // Replace with your date
  var outputFormat = "EEE MMM dd yyyy"; // Desired output format

  var formattedDate = Utilities.formatDate(replaceDate, "GMT+0530", outputFormat);
  return formattedDate;
}

// Extract the First Name
function hsExtractFirstName(fullName) {
  if (!fullName) return '';
  var parts = fullName.split(" "); // Split the string by space
  return parts[0] || '';
}



// Send Welcome emails
function hsSendWelcomeEmails() {
  var data = fetchDataFromSheet();
  var currentDate = new Date();
  var currentFormatDate = (currentDate.getMonth() + 1) + '/' + currentDate.getDate() + '/' + currentDate.getFullYear();
  var ccEmail = '[email protected]';
  var bccEmail = '[email protected]';

  var emailBatch = [];

  data.forEach(function(row) {
    if (!row.empEmail) return;
    var ccEmails = row.mentorEmail + ',' + ccEmail; // Set the multiple CC emails

    if (row.certStatus === 'Completed') {
      var formatCertCompletedDate = hsFormatDate(row.certCompletedDate, 'mm_dd_yyyy');
      if (currentFormatDate === formatCertCompletedDate) {
        var subject = `Congratulations on Achieving ${row.certType} ${row.certName} Certification, ${hsExtractFirstName(row.empName)}!🎉`;
        var message = `Dear ${hsExtractFirstName(row.empName)},<br><br>

We trust you are doing well!<br><br>

We are delighted to extend our heartiest congratulations to you on the successful achievement of your ${row.certType} Certified ${row.certName} certification. Your hard work, dedication, and commitment to excellence have resulted in this remarkable milestone, and we couldn't be prouder.<br><br>

This certification is a testament to your expertise in designing robust and scalable ${row.certType} solutions, and it underscores your dedication to professional growth and development. As a company, we recognize the immense value your skills bring to our team and our clients.<br><br>

Certification Link: ${row.certLink}<br><br>

Once again, congratulations on this outstanding achievement. We look forward to witnessing your continued success as an integral part of our team.<br><br>

--<br>
Thanks`;

        emailBatch.push({
          to: row.empEmail,
          cc: ccEmails,
          bcc: bccEmail,
          subject: subject,
          body: message,
          htmlBody: message
        });
       // Logger.log(`Welcome Mail Sent to: ${[row.mentorEmail, ccEmail]}`);
      }
    }
  });

  if (emailBatch.length > 0) {
    for (var i = 0; i < emailBatch.length; i++) {
      var emailData = emailBatch[i];
      
      MailApp.sendEmail({
        to: emailData.to,
        cc: emailData.cc,
        bcc: emailData.bcc,
        subject: emailData.subject,
        body: emailData.body,
        htmlBody: emailData.htmlBody
      });
      Logger.log(`Welcome Mail Sent to: ${emailData.to}`);
    }
  }
}