Automatically issue Teachable course certificates with Apps Script

In this post I’m going to show you how you can setup a system to automatically issue certificates to students who complete an online course on the Teachable platform, using Google Apps.

First, it captures course completion data in a Google Sheet. Second, it creates a PDF certificate based on a Google Doc template. And third, it emails that certificate out to the student via Gmail.

It has the advantage that it’s free and doesn’t involve any other paid third-party tools.

It has the disadvantage that there’s quite a few steps involved to get it all hooked up.

Process

Teachable course certificates Apps Script process

Basic webhook example

Before trying to connect to Teachable, it’s a good idea to understand what’s happening with a quick example. Here we’ll add some Apps Script to our Google Sheet and demonstrate how data can be piped into our Google Sheet from an external website.

1. Create a new spreadsheet

Create a new Google Sheet, and add a single header for this test, like so:

Basic Google Sheet

2. Add the Apps Script

In your Google Sheet, click the menu:

Tools > Script Editor

(New to Apps Script? Check out my beginner guide first.)

This opens up a new browser tab for writing Apps Script code. Remove the code that is displayed initially and copy in this code:

Save and give your project file a name, like Teachable Certificates.

3. Publish as web app

From your Script Editor window, click on the menu:

Publish > Deploy as web app...

This launches a popup window and there’s a few things we need to do here:

  • Set the Project version to “New” and enter a message like “Version 1”
  • Leave the “Execute the app as” set to “Me”
  • Set the permission to Anyone, even anonymous

deploy as web app

Click Deploy

When you do this for the very first time, you’ll need to follow the prompts to Review Permissions and authorize the script:

Google Apps Script permission

followed by this screen where you click Allow:

Google Apps Script permission

If you don’t see this screen, then don’t panic!

You have an extra step and need to click the small Advanced button at the bottom left first, and then on “Go to Teachable Certificates (unsafe)”. This is additional security layer from Google because this app hasn’t been verified by Google.

On the following popup screen, copy the “Current web app URL” before clicking OK. It’s formatted like this:

https://script.google.com/macros/s/{script-id}/exec

Keep a copy of this as we’ll need it shortly for testing and we’ll use it again for the Teachable setup.

Important Note: Anytime you make a change to your code you’ll need to re-publish the web app, following these steps, for the changes to take effect. The “Current web app URL” doesn’t change however, so all you need to do is click Update. It’s the same URL for the updated code.

4. Test it

Open the website hurl.it, which we’ll use to simulate a Teachable post request.

Copy your web app url from Step 3 above into the Destination. Change the type from GET to POST in the Destination section too. Include a test parameter, so your setup looks like this:

hurl.it website

Hit Launch Request

Return to your Google Sheet and you should see an output like this:

Webhook data in Google Sheet

where you have data like so:

{"parameter":{"value":"ben1"},"contextPath":"","contentLength":10,"queryString":"","parameters":{"value":["ben1"]},"postData":{"type":"application/x-www-form-urlencoded","length":10,"contents":"value=test","name":"postData"}}

Great!

So that’s a very basic example to show how data can be received from a different website and displayed in our Google Sheet.

Now we can parse this data to extract the contents we want, by changing this line:

var data = JSON.stringify(e);

to this line:

var data = JSON.parse(e.postData.contents);

This simply digs into that data packet above to find the postData piece and within that finds the contents piece. It’ll give us a new output as follows:

"value=test"

which is the parameter we entered on the hurl.it site.

Automatic Teachable course certificates

How does this work?

We collect the data from Teachable in exactly the same way as the example above, only Teachable sends it automatically anytime a course completion event is triggered. The script is only marginally more complex, to parse the data packet and only grab the fields we want.

Separately, we create a Google Doc template for our certificate.

When a new course completes, the data arrives in our Google Sheet with the student name, date and course name.

Then the script creates a copy of the certificate template, adds in the new student name, date and course name, and saves it as a PDF and emails it to the student via Gmail.

Lastly, the copied template document is deleted so our Drive doesn’t get cluttered up with them all.

The way we’re going to approach this is setup all the logic for creating and sending the certificates first, and then come back and connect Teachable at the end, following the basic example script above.

1. Create a new spreadsheet

Setup the Sheet with the following headers:

Teachable Google Sheet

Add in a row of data for testing (note, it’s important that your date looks like this so it matches the data received from Teachable):

Teachable Google Sheet data

Also, the final column stays blank. The script will auto-populate that with a date to show it’s completed the process of sending a certificate.

2. Create the certificate template

Create a new Google Doc and set the orientation to landscape.

Add a heading and any other text you want to show, and include the following text fields:

{{student_name}}
{{course_name}}
{{date}}

These are placeholders for the student names, course names and completion dates that we’ll pass along automatically.

This is what my template looks like (click to enlarge):

Draft Teachable certificate Google Doc

Feel free to format the template to look pretty!

Make a copy of the template URL, as we’ll need to add that to our script below.

3. Setup logic for creating and sending certificates

We have the Sheet with data ready to go and we have the certificate template, so it’s time to wire the whole shebang up.

Open the script editor or your Google Sheet, by clicking the menu:

Tools > Script Editor

Delete the code that’s there and add the following code:

Change the TEMPLATE_URL to your own Google Doc template URL, by removing everything between the quotation marks (“…”) and inserting your the url you copied above between the quotation marks (on line 4).

Change the certificate title to whatever you want (on line 5).

Underneath this code, add the following code for the sendCertificates function, which is the heart of our script:

In a nutshell, it retrieves any rows of pending certificates (ones that don’t have a date in the final column yet), formats the date nicely, creates the certificate, emails it to the student and deletes it from our Drive.

You’ll notice that it calls on two other functions, createCertificate and emailCertificate, so we’d better create those.

So underneath, paste the following code for the createCertificate function, which handles the mechanics of creating the certificate:

This in turn calls a function called updateCertificate, so let’s add that:

And finally, add the emailCertificate function, which handles sending this newly created file through Gmail.

You’ll want to modify the message in here to your own copy. This is what’s shown in the email which the student will receive.

That’s it!

We can now test it.

4. Test system works

At the top of the script editor window, select onOpen function (1) and hit run (2):

Run onOpen menu

As before, follow the prompts to Review Permissions and authorize the script:

Apps Script authorization

followed by:

Apps Script authorization

Your Apps Script program will then run:

Script running message

Return to your Google Sheet and you should see a new custom menu. Boom!

Click the new menu:

Certificates > Send Certificates

If everything goes to plan, you should receive an email (to the email address in your test data) with a PDF certificate, as shown in the following screenshots.

This is what the email looks like when it arrives (click to enlarge):

Send email with apps script

The body of the email (click to enlarge):

Send email with apps script

The PDF certificate attachment (click to enlarge):

Send email with apps script

So that’s looking great!

You’ll also notice that the final column of your Sheet is now populated with a date to confirm a certificate has been issue and when.

5. Connect to the Teachable webhook

Add this final piece of code to our script file:

This is very similar to the basic example we started this article with.

Next, from your Script Editor window, click on the menu Publish > Deploy as web app...

Copy the URL as we’ll need that for our Teachable webhook below.

Login into Teachable and go to the Settings > Webhook > New Webhook

Click to enlarge:

Teachable webhook

Copy in your web app URL from above. It’ll look like this:

https://script.google.com/macros/s/{script-id}/exec

Select “Course Completion” so your webhook should look like this:

Teachable webhook

You can leave the “Send as a JSON Array” option unchecked, i.e. leave it as a grey X.

Click Create Webhook

Hooray! That’s it, we’re set to go!

When someone completes your courses, you’ll see data added to your Google Sheet. When you’re happy, you can then press Send Certificates from the custom menu and send them to students!

Apps Script send certificates
Left: your screen showing Teachable data. Right: student receiving and opening email

Testing trick: since the data fields match, we can set the webhook to New Enrollment temporarily, and then go and test it by registering for one of our courses. It’ll trigger the webhook so we can see the data show up in our Sheet:

Output data from Teachable webhook

You can set it back to Course Completion once you’ve ready.

6. Setting to automatic (optional)

I actually prefer to leave my function set to manual, so I can open my Google Sheet, glance at all the new course completions, check there are no errors, and then, with a single click, run the code to send all the certificates out automatically.

However, you can add a trigger to run this program once a day (or once an hour if you needed it more frequently) to completely automate the process.

Click on the menu:

Edit > Current project's triggers

and in the popup click on “No triggers set up. Click here to add one now.”.

Then select the sendCertificates function and set the frequency to once a day:

Apps Script triggers

And that’s it set for automating Teachable course certificates.

Once a day, your script will look to see if any new Teachable course completion data has arrived (remember, that bit is always automatic) and if there is any new data, it’ll just fire off the new certificates and add a date to show it’s completed.

Resources and next steps

The full code can be found here on GitHub

Teachable documentation on webhooks

There are other webhooks like enrollments, transactions and lecture completions, which could be used to build other useful tools for the Teachable school owner. More to come in the future!

One thought on “Automatically issue Teachable course certificates with Apps Script”

  1. hi ben,

    very similar to this app, but need to display approved response on another web link that too with search option within that page.

    thank you !!

Leave a Reply

Your email address will not be published. Required fields are marked *