Send personalized appreciation certificate to employees

Problem statement

Manually creating customized employee certificates in Google Slides can be tiresome. Having to download, attach and send them to each individual can get equally cumbersome. More times than not, the chances of one employee getting an appreciation certificate of another is not just likely but almost certain if the list gets long enough.

Demo

Codebase

You can access the entire script on my GitHub repository here or make a copy of my original script from this link here; however, here's the actual code for reference -

var slideTemplateId = "SLIDE-ID-GOES-HERE"; // Demo: https://docs.google.com/presentation/d/1bFj09xI7g_kbA76Xb60tYyxVdi-zrpm6zQ6gu696vKs
var sheetId = "SHEET-ID-GOES-HERE"; // Demo: https://docs.google.com/spreadsheets/d/1cgK1UETpMF5HWaXfRE6c0iphWHhl7v-dQ81ikFtkIVk
var tempFolderId = "TEMPORARY-FOLDER-ID-GOES-HERE";

function createCertificates() {
  var template = DriveApp.getFileById(slideTemplateId);
  
  var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var headers = values[0];
  var empNameIndex = headers.indexOf("Employee Name");
  var dateIndex = headers.indexOf("Date");
  var managerNameIndex = headers.indexOf("Manager Name");
  var titleIndex = headers.indexOf("Title");
  var compNameIndex = headers.indexOf("Company Name");
  var empEmailIndex = headers.indexOf("Employee Email");
  var empSlideIndex = headers.indexOf("Employee Slide");
  var statusIndex = headers.indexOf("Status");
  
  for (var i = 1; i < values.length; i++) {
    var rowData = values[i];
    var empName = rowData[empNameIndex];
    var date = rowData[dateIndex];
    var managerName = rowData[managerNameIndex];
    var title = rowData[titleIndex];
    var compName = rowData[compNameIndex];
    
    var tempFolder = DriveApp.getFolderById(tempFolderId);
    var empSlideId = template.makeCopy(tempFolder).setName(empName).getId();        
    var empSlide = SlidesApp.openById(empSlideId).getSlides()[0];
    
    empSlide.replaceAllText("Employee Name", empName);
    empSlide.replaceAllText("Date", "Date: " + Utilities.formatDate(date, Session.getScriptTimeZone(), "MMMM dd, yyyy"));
    empSlide.replaceAllText("Your Name", managerName);
    empSlide.replaceAllText("Title", title);
    empSlide.replaceAllText("Company Name", compName);
    
    sheet.getRange(i + 1, empSlideIndex + 1).setValue(empSlideId);
    sheet.getRange(i + 1, statusIndex + 1).setValue("CREATED");
    SpreadsheetApp.flush();
  }
}

function sendCertificates() {
  var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var headers = values[0];
  var empNameIndex = headers.indexOf("Employee Name");
  var dateIndex = headers.indexOf("Date");
  var managerNameIndex = headers.indexOf("Manager Name");
  var titleIndex = headers.indexOf("Title");
  var compNameIndex = headers.indexOf("Company Name");
  var empEmailIndex = headers.indexOf("Employee Email");
  var empSlideIndex = headers.indexOf("Employee Slide");
  var statusIndex = headers.indexOf("Status");
  
  for (var i = 1; i < values.length; i++) {
    var rowData = values[i];
    var empName = rowData[empNameIndex];
    var date = rowData[dateIndex];
    var managerName = rowData[managerNameIndex];
    var title = rowData[titleIndex];
    var compName = rowData[compNameIndex];
    var empSlideId = rowData[empSlideIndex];
    var empEmail = rowData[empEmailIndex];
    
    var attachment = DriveApp.getFileById(empSlideId);
    var senderName = "CertBot";
    var subject = empName + ", you're awesome!";
    var body = "Please find your employee appreciation certificate attached."
    + "\n\n" + compName + " team";
    GmailApp.sendEmail(empEmail, subject, body, {
      attachments: [attachment.getAs(MimeType.PDF)],
      name: senderName
    });
    sheet.getRange(i + 1, statusIndex + 1).setValue("SENT");
    SpreadsheetApp.flush();
  }
}

Setup

1. file setup
   • open google slides and select the 'employee certificate' template from the gallery
   • create a google sheet with the required employee details as show here
   • add a folder in google drive to store all the temporary files (slides) that the script would create for individual employees
2. make a copy of this script or copy the entire codebase and paste in a new google apps script file
3. add the relevant slide, sheet and folder ids in the first 3 lines of the script and finally, run the createCertificates function, followed by the sendCertificates ones to start seeing the setup in action

Gotchas

• in case you trigger the emails from the same functions as that of creating the certificates, you may see the pdfs in the email having the same content as the original template (i.e. instead of the personalized name on the slide, you might end up seeing 'employee name')
  • to avoid this, you need to break the operation in 2 steps as indicated in the setup process (step 3)