Mail Merge Script for Certificates

As webinars continue to change the landscape of information dissemination during the pandemic, along with popular events or event organizers transitioning on this platform, a need for certificates emerged to recognize the efforts of participants. Considering that the university is using this popular education suite, I tried exploring its features, the same combo I used when I was holding international edtech webinars in the college and this was through Apps Script alongside plugins or add-ons. I shared this idea to a colleague of mine and he was able to write a user-friendly Apps Script code that can fully leverage on the email sending capacity of the university education email. Note that I did not write this code but only inserted comments to describe the sections that this script does.

Before you start, you must have a certificate template, preferably in the form of a Word/Pages file where you can generate multiple copies and edit the sections of the certificate manually. There might be other tools or even a more comprehensive Apps Script that can do all of these processes. However, in my setup, I used Pages to create the certificate template, filled out the participant names and other certificate sections, exported it to PDF and then used the built-in Preview app of macOS to simply splice each page by dragging it to the desktop and renaming the filename uniquely. Hence, I already have the generated PDF files for each participant that are for uploading on Drive.

To start with the list of participants, create a Sheet that has the following field names and populate it with the corresponding first name, last name, email address, and certificate ID (your unique identifier for the file) of the participants.

On that sheet, go to Tools > Script Editor, then type the following source code:

// Apps Script code

var EMAIL_SENT = 'EMAIL_SENT'

function sendEmailsWithAttachment() {
  var sheet = SpreadsheetApp.getActiveSheet();
  //1st row of participant records
  var startRow = 2;
  //total number of participant records
  var numRows = 101;
  //total number of designated columns on spreadsheet
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i]
    var firstName = row[0];
    var lastName = row[1];
    var emailAddress = row[2];
    var certificateId = row[3];
    var status = row[4];
    if (status !== EMAIL_SENT) {
      //email subject variable is best used with unique identifiers 
      //such as combination of first name last name and webinar name or date
      //to avoid grouping of emails that have the same email subject
      var subject = 'Webinar Certificate for '+firstName+' '+lastName;
      //message is the overall texts or content of your email
      var message = 'Dear Sir/Madam, \n\nThank you for attending our webinar on Privacy Tips.\n\nCheers,\nEduveloper Team'
      //file refers to the unique file name on Drive
      //example: "Eduveloper Webinars-Privacy Tips-Jan1 Dela Cruz Juan.pdf"
      var file = DriveApp.getFilesByName('Eduveloper Webinars-Privacy Tips-Jan1 '+lastName+' '+firstName+'.pdf');
      if(file.hasNext()){
        //name variable is for the name of the sender, organization, or event organizer
        MailApp.sendEmail(emailAddress, subject, message, {
          attachments: [file.next().getAs(MimeType.PDF)],
          name: 'Eduveloper'
        });
        //spreadsheet will contain EMAIL_SENT status for every successful email
        sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT);
        SpreadsheetApp.flush();
      }
    }
  }
}

It should look something like this screenshot, Save it, then Run it. This will prompt you to authorize the usage of script on your account and just click Accept.

Afterwards, return to your Sheet containing the participant records and you should have an update of a successful email sent for each row.

Enjoy!