Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee

Automated PDF Certificated Creator That Is Emailed to the Attendee Google Apps Script

Google Apps Script: DriveApp, PropertiesService, SpreadsheetApp, GmailApp, SlidesApp. Google Slides, Google Sheets.

Have you ever wondered how people create certificates en mass and send them out automatically? You are in the right place.

In this tutorial, we are going to:

  1. Create Certificates of Attendance for multiple attendees using Google Slides and a list of attendees in Google Sheets.
  2. Send those certificates as an attached PDF to the attendees.

We’ll set it up so it is super user-friendly with a handy menu in your Google Slide template so that all you have to do is to update your Google Sheet of names each time you run the course and then click a few buttons.

Google Apps Script Menu Items in Google Slides

Also, we will run an example so you can see how it all works and what you need to do to set it up.

For the coders out there, I think I have documented the Google Apps Script code enough for you to figure out how to quickly implement your own project. However, I have also added a smalls discussion of some parts of the code at the end.

This is a standalone tutorial. However, it draws from two main tutorials if you want to explore those first (Though it is not essential):

Let’s get cracking.

Preparing your Certificate Google Slide

Take a look at the Certificate of Attendance created in Google Slides:

Click to expand image.

If you want to play along, you can grab this Google Slide from here:

Certificate of Attendance: Warp Slippers

Just go to File >  Make a copy to create a version of your own to play with.

In each certificate, I have 3 things I want to change for each person receiving the certificate. I have indicated this by adding double curly braces and a key reference:

  1. Name: {{name}}
  2. Attendance: {{attendance}}
  3. Grade: {{grade}}

Our handy code will draw from these key references and replace them with our information in our Google Spreadsheet.

You can make these key references anything you want, but I recommend that you make them logical and have them match your column headers in your Google Sheet of attendees.

As an aside. I have added in attendance and grades into this to; 1, show you what it is capable of doing and 2, because I kinda feel that certificate of attendance courses should display grades and attendance under certain conditions. That’s just my soapbox and if you want to add to the discussion on this check out my Twitter post on the topic!

Once you have designed your certificate and added in all the items you want to change for each unique attendee you are done with the Google Slide for now.

Preparing your Google Sheet merge data

Open a new Google Sheet and create a header row in row 1 and then add all your data for each attendee in the following rows.

Take a look at my example.

Click to expand

If you click to expand the image above you will see that my header row consists of the following:

  • Name
  • ID
  • Email
  • Attendance
  • Grade

As you probably have already figured out, we are not using an ID in our certificate, though we could if we wanted to. We might use it for our file name for our document.

Also, we won’t be using the email on our certificate, but we will be using it to send our PDF certificates to attendees.

It doesn’t really matter how many extra columns of data you have. You just need to make sure that the header is on the first row and take note of the columns you want to use (More on this in a moment).

If you are playing along, you can access the file here:

Student Certificate Detials

Just go to File >  Make a copy to create a version of your own to play with.

The Code

You will be adding two Google Apps Script files to your Google Slide certificate. To access the Google Apps Script editor from your slide go to Tools > Script Editor in your Google Slide menu.

Code.gs

There will be a file there already called Code.gs. Copy and paste in the following code (There is a copy item in the menu of the code block below as you hover over it).

The Code.gs file is your main run file that will create your menu, run your mail merge to create all your certificate and then email them off as PDFs.

Map.gs

Next, in the Script Editor go to File > New > Script file. Name the file Map.gs.

New Script file for Google Apps Script Certificate code

Copy and paste in the following code into this new file.

Now save your Google Apps Script Project. You will be prompted to rename your project. I often just rename the project to the same name as the Google Slide it is attached to, but this isn’t necessary.

The Map.gs file stores all the relationship data between your Google Slide key references and your Google Drive applicants info.

Set up the Code for your Project

Once you have the code copy and pasted in place, we will need to make some changes to some key areas.

Maps.gs

First, let’s navigate to Maps.gs. When you create your own certificate, you will have to edit the array of values you find in the script. Essentially, we want to connect a target item like {{name}} in our Google Slide, with our Name column in our Google Sheet. You will need to do this for each time you want to input into your Slide.

So if I want to pair my slide key reference to my Sheet, I would need to edit the following:

The sheet key refers to the header name in your Google Sheet. The col key refers to the column number. In coding the column number often starts at zero. So if the name is in column A then the column would be zero.

The doc is the key reference on your Google Slide that you put in double curly braces {{}}.

Google Apps Script map object connecting merge sheet to google Slide template
Click to expand

If you have fewer references, just delete out the object in the braces. If you want more, copy and paste a previous one and update the values to match your needs.

Once you have updated all your references, move onto the next step.

Code.gs

Global variables

For your own project, you are going to have to update two locations in your Code.gs file. I have highlighted them and named them in the Code for reference above, but for conveniences, let me add the chunk you need to edit below so I can explain them.

You can find this on lines 29 – 36 of your Code.gs file.

Here you find a list of variables:

  • TEMPLATE_ID : This is the file ID of your Google Sheet. You can find the file ID in the URL of your Google Sheet. Each file ID is unique. Copy the file ID in and paste it between the single quotation marks replacing the current ID.
Google Slide file ID location
Click to expand
  • SS_ID : This is the file ID for your Google Sheet of attendees. Go to your Google Sheet and copy the file ID from the URL and replace the one that is there.
  • SHEET_NAME : This is the name of the sheet found on the sheet tab at the bottom left of your Google Sheet. In this example, the sheet is named Merge. 
  • FILE_NAME : This is the name of the file you want to create. This is an array that contains any value from your mappedDocToSheet variable in your Map.gs file. This will create a certificate file for each applicant that contains their ID and their Name. You can add or remove these file name reference items to create your own custom file name.
File name creation for certificate merge in Google Apps Script
Click to expand.
  • EMAIL_COL : This is the email column in your Google Sheet. Remember here that the column starts at zero. In our example, the email column is column 2.

Writing your email message

The final thing you will need to edit is the message that you are going to email along with your PDF to your attendees.

You will find this from lines 170 to 180 of your Code.gs file.

You will need to update two variables. Both of these variables are contained with backticks (`). These are different from single quotation marks (‘).

  • subject : This is the subject line of your email. You can delete out all the text within the backticks here and replace it with your own subject line.
  • body :  This is the main content of your email. We haven’t gone with any fancy HTML here just some standard text. If you want to create a new line then use the forward-slash followed by ‘n’ (\n). If you want to use HTML in your email message, check out this tutorial:

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Once you have made all your modifications save the script, go back to your Google Slide certificate page.

Running the Code

Getting the new menu

Google Apps Script Menu Items in Google Slides

To access the new menu items all you need to do is refresh your Google Slide document.

Creating the Certificates

To create the certificates go to Certificate maker > Create certificates. The first time the code runs you will get a list of warnings. This app isn't verified Google Apps Script

First, click on advanced. The window will expand.

Google Apps Script Click Advanced Got To unsafe

Then click Go to Cert of Attendance – Warp Slippers (Unsafe). This will open a new window with a list of all the things the code will access and provide authorisation for the script to use.

OAuth permissions Allow Google Apps Script

Hit Allow. 

You may have to go in and click the menu item again to run the code the first time.  This authorization process will only run once.

So what’s with all the warnings? Well, Google is doing the right thing here and trying to help protect you from any nefarious code. This process will occur on any new code project that requires special authorisation. If you don’t trust the author of the code or can read the code to check it isn’t doing anything naughty simply don’t click Allow. 

Once the code is running it will start to create duplicates of the template updating them with your Google Sheets info and renaming the files. This will generate a list of Google Slide certificates in the same directory as your template.

Google Slide Certificate Creation Drive file directory

Give it a little time to work it’s magic before you open a file. Then open one up to check it all worked as planned:

Automate Google Slide Certificate of Attendance with Google Apps Script

Great you have your list of Certificates.

Emailing the Certificates as PDFs

Once the certificates have been created, it’s time to email them to the attendees. In the menu bar go to Certificate maker > Sendcertificates. This will email out all the certificates in your list.

The attendee will receive an email similar to this:

Automated email of Certificate of Attendance Google Apps Script
Click to expand

You might have noticed that the attached certificate has been converted to a PDF.

Here is what it looks like:

PDF attachment of Certificate of Attendance Google Apps Script
Click to Expand

One thing to keep in mind is that Google Apps Script does set some limits on how many emails you can send or the runtime of your script. It is worthwhile to check the quotas out if you intend on producing hundreds of certificates and sending them.

That’s it all done. You have automated your certificate!

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

A Little Summary of Highlights for the Coders

A full rundown on the mail merge process can be found in a previous post, Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge). About 85% of the code is the same and is a good use case for reusability of code.

So what did I change?

Well, I needed to send out an email once the Google Slides were created for each attendee. I had planned on doing it all in one runtime … and then I looked at how long that was taking each attendee and thought better of it. I also rationalised that it would probably be better to have a pause between the certificate creation process and the email process should the user wish to make any changing.

Emailing the attendee as a separate process.

Emailing the attendee as a separate process had its own interesting challenges. Most notably, how was I going to find the attendees file again quickly and match it to their email? In the end, the fastest way I thought to do this was to sort the attendee’s email along with the file ID of their newly created Google Slide certificate in the PropertiesService.

PropertiesService allows you to store small amounts of data in key-value pairs that are connected directly to the script.

My first addition to the script to implement this was to clear any preexisting data in the Properties Service (Line 61):

PropertiesService.getScriptProperties().deleteAllProperties();

Then, at the end of the loop iterating through each row of merge data, I stored each newly generated file id as a key and it’s associated email as a value inside the script PropertiesService.  (lines 88-89)

This is a pretty neat way of storing small amounts of data.

Then when it was time to email the attendee. I called the PropertiesService class getting all the properties within and then looped through each property grabbing the key as the file ID and the value as the email. (Lines 161-168)

Converting the Slide to PDF was super easy

I had done this previously some years ago and completely forgot how to do it. Thus spending far too long looking at blob files and whatnot only to discover for the second time that it was a super easy process and one I could do during the email structuring phase inside the GmailApp class with the help of a DriveApp method called getAs.

The getAs method allows converting the file type, particularly of a Google file, into something else like a PDF. So once I had the file name of the Google Slide I simply called getAs on it and changed the mime type to PDF and bang! I had myself a PDF. The conversion was pretty quick also.

When you send an email with GmailApp you can send a bunch of optional object data along with it. This optional data includes an attachment. So during the attachment phase, I simply converted the Google Slide to a PDF and it was shipped off as a PDF.

Pretty cool, hey?

Conclusion

This is the second mail merge-type script I have created with similar code and one of many types of merging I have built a tutorial for on this site. The code is quite easy to modify for not only other slide projects but for other document merging projects.

Go out there and give it a try. I would love to hear in the comments about what projects you have adapted this too.

If you enjoyed this tutorial, please click like. And if you want to say up to date on what I am creating next, please subscribe (Top right of the page).

~Yagi

3 thoughts on “Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee”

  1. Hi,

    I am very new to programming and found your code very helpful. I am trying to automate one part of my daily work using your code. However I am stuck on the email body of this code. So request your suggestions.

    I am trying to add the Name after “Hello” and add few more data from the columns of the spreadsheet in the body part but failed to do that. I have used for loop but it is giving no result.

    Is there any way to call some values direct in the body part of the email.

    let body = Hello Course Attendee, \n
    Congratulations on completing the Transdimensional Warp Slippers Training Course. We hope that you enjoyed the course
    and improved your skills. \n
    Please find your Certificate of Attendance attached. \n\n
    Respectfully, \n
    Yagisanatode: rainer

    How to add a static image in the email body for all the emails.
    I have checked one of your tutorials
    https://yagisanatode.com/2019/05/27/google-apps-script-create-an-html-email-reminder-service-from-google-sheet-data/

    But I failed to do so.

    Request your help.

    Thanks!

    1. Hi Deepak,

      You could store more than the email in the properties service (Line 88). For example:
      .setProperty(newDoc.getId(),[row[emailCol],row[column for users name], etc);
      Then in your emailNewSlideAsPDF() function you will need to use JSON.parse on your properties service properties just after line 162 so that you have an array. Imagine we set that to the variable prop. The email value will be prop[0].

      Next in your email text, you will be able to insert those email values by something like this, Hi ${prop[1]}, ....

      Have a play around with it. This should point you in the right directions.

      Cheers,

      Yagi.

Leave a Reply