With much of the world socially distancing, it's no surprise that online learning has rapidly taken off. Since the start of the COVID-19 pandemic, a record number of students have enrolled in online learning, and even before the pandemic, online learning had been a rapidly growing trend.
For anyone running a course or teaching a class, this has been a drastic and unprecedented change, forcing instructors to learn and adapt to an all-online format—often on the go.
In this tutorial, course instructors will learn how to automate a time-consuming part of their daily tasks: certificate testing. Many online courses offer a certificate after a test is successfully completed, giving students evidence of their accomplishments and hard work.
The dark side of certificate testing, however, is that these certificates are approved, personalized, and sent by the instructor—which can take up to fifteen minutes per student. In this post, we’re going to automate every step of this process, from grading the test to creating a personalized certificate. I run Luhhu, a Zapier-certified agency, and have helped people set up this workflow. By investing 30 minutes into this workflow today, you’ll save hours in the coming weeks.
Let’s get to it!
How to Automate Your Online Testing with Zapier
This tutorial will cover the work you need to do to get started, as well as walk you through all the steps to set up the Zap. Here's what you'll find:
The tools you'll need
Prep work before you get started
Create the Zap that lets you automatically score and create certificates
The tools you'll need
Before starting to build out the Zaps in this guide, you'll want to make sure you have all of the services you need to create it.
We'll be using four different apps and services:
SurveyMonkey to host your quiz or test.
Google Sheets to store student scores.
Formstack Documents to prepare your digital certificate.
Zapier to connect everything and make the process automatic.
Zapier and Google Sheets are both free services; all you'll need is an email to create an account with each. Formstack Documents and SurveyMonkey, on the other hand, are paid services that you will need to sign up for before continuing this tutorial.
Once you have all four accounts created, you'll need to make a file in each one, which we'll explain how to do right now.
Prep work before you get started
Create your quiz in SurveyMonkey
The first step to this process is to create the tests (we call them quizzes throughout this guide) and completion certificates that you’d like to automate. Here’s how to do that.
SurveyMonkey is a service that allows you to quickly create and send out questionnaires. As the name of the site implies, these questionnaires are typically in the form of surveys, though you can easily create quizzes and tests as well.
To get started, log in to SurveyMonkey, create a survey, and choose Start from template. Navigate to the Education templates, choose Quiz - Capitals of the World and click USE THIS TEMPLATE. You can edit the questions to match your course, so long as you ensure that at least one question asks for the student's email (this will be important later on in this Zap).
Once your quiz is saved you're ready to move on to the next step!
Create your certificate in Formstack Documents
With your quiz created, it's now time to build your certificate with Formstack Documents.
If you're not familiar with Formstack Documents, it's a fairly simple service that allows you to create templates for professional-looking documents. These templates can then be automatically filled in with personalized information, allowing you to automate document creation—which is what we're going to do in this tutorial!
To get started, log into Formstack Documents and create a new document. When you do this, Formstack will ask you if you want to import a doc, use a Formstack template, or simply start from scratch. We'll be using a Formstack template, but you can choose whichever is right for you.
If you're choosing a template as we are, choose the Certificate template from the list of options and click Next Step: Choose Delivery Method. You can choose whichever option works best for you here, though we recommend checking Save my documents to Stash and Send my documents to my email address, that way you'll ensure you don't lose your students' certificates.
After clicking Next, Formstack will ask you where the data for your certificates will come from. In our case, that's going to be Zapier, so choose Integrate with an external service and click Finish.
The last thing you need to do in this step is to click your profile icon in the upper-right corner (it will resemble a person's torso) and click API Access. On the page this takes you to, click Click here to create an API Key!, name the key "Zapier", and take a screenshot of the little box that says "Zapier, Key, Secret". This will come in handy later when we’re ready to connect Formstack to Zapier.
And that's it! Your certificate is ready to go.
Create your Google Sheets spreadsheet
Last but not least, you'll need a sheet created in Google Sheets that will be used to store each of your students' test scores and emails.
All you need to do for this is create a new sheet in Google Sheets, add the title Email to the top of Column A and the title Score to the top of Column B. It also might be useful to title the spreadsheet after the quiz you created in SurveyMonkey, just so everything is easy to follow.
Create your Zap
With the prep work out of the way, you're ready to start automating the nuts and bolts of online testing. In this step, we're just going to create the foundation of your Zap, choosing which apps we want to connect and deciding what event will cause your Zap to run.
To do this, sign in to your Zapier dashboard. On the front page, you'll see two fields, one labeled Connect this app… and the other with this one! In the first field, search for "SurveyMonkey" and click it when it appears; in the second, search for "Formatter by Zapier" and select it as well.
Two new fields will appear, When this happens… and then do this! In the first, click New Response Notification With Answers and in the second click Numbers. By choosing these options, we're telling Zapier that when we receive a SurveyMonkey quiz with answers, we're going to format numbers from that quiz.
With all four of these fields filled in, click Use Zap. This will launch the Zap editor, which is where we'll spend the rest of this tutorial.
On this new page, you'll be asked to sign in to SurveyMonkey (assuming you haven't already) to connect it to your Zapier account. Go ahead and click the blue Sign in to SurveyMonkey button. On the page this opens, sign in to SurveyMonkey and select AUTHORIZE.
Now that SurveyMonkey is connected to Zapier, click CONTINUE. In the Survey dropdown menu, you should see the quiz we created earlier in this tutorial. Choose it and then click CONTINUE.
Set up your Formatter step to score the test
Doing that should automatically bring you to the second step of your Zap, which is where we're going to score the student's test and format the results.
The Choose App and Choose Action Event fields should already be filled in from before, so just click CONTINUE.
On the next page, you'll be presented with a field labeled Transform. This field will have several options, but the one we're looking for is Spreadsheet-Style Formula. You can select it by scrolling through the options and clicking it or simply typing it into the search bar.
Once selected, a new field labeled Formula will appear.
In this new field, we're going to enter a formula that is readable by spreadsheet apps like Google Sheets. If you aren't familiar with spreadsheets, this may look a little strange, but it's just a math formula that will divide the correct quiz responses by the incorrect responses and then convert that to a percentage with two decimal places.
Here's the formula you're going to enter:
ROUNDUP(((RESULTS/SCORE)*100),2)
Once the formula is entered, highlight the text that reads "RESULTS" section and in the drop-down that appears search for and select 1.Quiz Results Correct. Then highlight the text that reads "SCORE" and search for and select 1. Quiz Results Total. These are variables that will change to match the quiz being scored, which is what enables us to automate this process. You'll know you have it correct when those fields appear in the white ovals, as shown in the screenshot below.
Once done, click CONTINUE.
Add an action to copy the test score to Google Sheets
Now we're actually going to add two additional steps in your Zap. We're going to copy the student's test score to the Google Sheets spreadsheet we created earlier. There are two different ways this Zap can run, depending on whether the student has previously taken the quiz.
If it's a student's first time taking this quiz, then their email and score won't be in Google Sheets, so we'll need to add them as a new row in your spreadsheet. If they have taken it before, however, then they already have a row in your spreadsheet. So instead of adding a new row, we're going to be updating a previously added row.
Here's how to do both of these—automatically!
Logging scores for first-time test-takers
Click the + (plus sign) at the bottom of the Zap editor to add a new step to your Zap. In the search bar, type and select Google Sheets. In the new field, Choose Action Event, select Lookup Spreadsheet Row and click CONTINUE. This action will search for a row in our spreadsheet; in our case, we're going to be looking for a row containing the student's email.
Next, sign in to Google Sheets and click CONTINUE. You should see a new box with several fields for you to fill in. Don't be too overwhelmed, most of these fields are dropdown menus that we can fill in without having to type anything.
In the first field, Drive, choose the Google Drive where your quiz spreadsheet is located. In the next field, Spreadsheet, select the spreadsheet we created earlier. In the Worksheet field, click the worksheet that holds the columns we created in our spreadsheet (if you only see "Sheet1" as an option, select that).
In the Lookup Column field, choose Email. This means that Zapier is going to search the Email column in our spreadsheet. The Lookup Value, which is the next field, is what Zapier is going to search for in that column. In our case, we want to search for the student's email, which is why we asked for it when creating our quiz in SurveyMonkey. From the dropdown menu, choose the answer from your quiz that contains the student's email.
If the student's email is found in the Google spreadsheet, then the Zap will jump down to the For returning test-takers section below.
If Zapier doesn't find the email, however, then it will add a new row. To do this, scroll down a bit and check the Create Google Sheets Spreadsheet Row if it doesn't exist yet? box.
In each field that appears (which should be Email and Score), we're going to insert the matching variable from previous steps in our Zap.
In the Email field, that means choosing the student's response to the "email" question in your SurveyMonkey quiz. In the Score field, you'll choose the result from the formula we created in our Formatter step.
And that's it! If this is a student's first time taking the quiz, their information will be added to the Google spreadsheet we created at the beginning of the tutorial.
Updating scores for returning test-takers
After clicking CONTINUE and finishing the previous step, go ahead and click the "+" symbol again to add a new step.
Once again, we're going to choose Google Sheets from the dropdown menu. This time, however, we're going to choose Update Spreadsheet Row as our Action Event.
Choose your Google account again, and select the same drive, spreadsheet, and worksheet as before. In the Row field, we want to select the row containing the student's email. To do this, choose Custom, then choose Find or Create Row in Google Sheets, then select Email.
Once again, Zapier will create a field for each of the columns in your Google Sheets spreadsheet: Email and Score. Whatever you put in each of these fields is what Zapier is going to use to replace the existing information. In our case, we only want to update the test score, so that's the only field we'll alter.
In the field titled Score, choose the test score—the output from our Formatter step—from the dropdown menu.
And that's it! You will now have a Google Sheet that is automatically updated with all of your student's emails and most recent test scores.
Create your course certificate
Now it's time to deliver your student's results. Ideally, we would be sending every student a certificate letting them know that they passed the quiz, but of course, not every student will pass it. To account for this, we're going to create two separate paths in the fifth and final step of our Zap, one for when the student passes, and one for when they fail.
To get started, press the + (plus symbol) one last time, type "Paths", and select it when it appears. This will create two paths: Path A and Path B.
Path A: If a tester fails...
Next to Path A, click the EDIT button. Name this field something along the lines of "Fail" to indicate that the student failed the quiz, then click CONTINUE.
This will bring you to a screen where you'll establish the rules for this path. "Rules" are just conditions that determine if this path runs ("If [this] happens, then [that] will happen; otherwise, [that] will not happen"). In our case, we want the Fail path to run when the student makes below a passing grade. If there are 10 questions on your test (excluding the "email" question), then the student might need to get more than six questions correct to be considered passing. We'll work with "6" as our example, but keep in mind that your number will be different if your quiz doesn't have 10 questions.
Under the field labeled Only continue if..., choose 1. Quiz Results Correct from the Choose field dropdown. Under the Choose condition dropdown, choose (Number) Less than. In the Enter value field, type the minimum number of questions a student needs to answer correctly to pass. For our example, that's going to be 7 (one more than 6).
Once this looks correct to you, click CONTINUE and then click the box labeled Do This... by scrolling down.
Here, we're going to send an email to the student letting them know that they failed and offering a link to take the test again, read over study materials, or whatever you would like to write in the message.
In the search bar, type Gmail and select it when it appears. For your Action Event, choose Send Email. Choose your Google account one more time and fill in the email information on the next page. Make sure that in the To field, you choose the Email variable from SurveyMonkey and don't type in a specific email. Once you've filled in all of the fields with your information and email text, you're ready to click CONTINUE and to move on to Path B.
Path B: If a tester passes...
After finishing the Fail path, click the "X" in the top-right corner to go back to the main Zap editor. Scroll back to the bottom of your Zap and click the EDIT button next to Path B. Once again, name this something similar to "Pass" so that you know what it's for, and click CONTINUE.
This time, you're going to choose the same 1. Quiz Results Correct from the Choose field dropdown. In the second field, however, you're going to choose (Number) greater than and for the third field you'll enter one lower than the number of questions a student needs to get correct to pass. In our example, that would be "6" (one less than 7).
After clicking CONTINUE, scroll down and click Do this.... In the search bar, type Formstack Documents and select it. Make sure not to click "Formstack", as this is a separate app.
For your Event Action, choose Create Document Merge and click CONTINUE. Connect your Formstack Documents account by entering the API information we screenshotted earlier in this tutorial. On the next page, choose the certificate document we created earlier under the Document field and select Yes under Download File. This will pull up multiple fields related to your Formstack certificate, which you'll fill in by selecting variables from previous steps in each field's dropdown menu.
Once done click CONTINUE.
And that's it! All that's left to do is turn your Zap on, which you can do by clicking the OFF switch in the top-right of your screen.
Once turned on, you'll automatically receive certificates in Formstack for students who have passed the quiz.
Training minds in trying times
Teaching over the internet is a challenge, especially when you have no preparation for doing so. But it's also a chance to learn more about your craft, adopt new techniques, and pioneer the newest area of education. This tutorial is just a starting point for automating your digital classroom.
By using tools like Zapier to your advantage, you can simplify your approach to teaching in a time where "simple" is in short supply.
Best of luck!
Andrew Davison runs Luhhu, an automation agency and Certified Zapier Expert. They help clients set up automated workflows to improve efficiency.
