How do I use Google Forms and Sheets to automatically generate custom certificates?

In this tutorial you will learn how to auto-generate individualized certificates using Canvas, Google Forms, and Google Sheets.

1. Create a new folder in Google Drive

This new folder will hold everything you are about to create as well as the generated certificates.

2. Create your certificate

You do not need to "Request edit access" to use either template, please just copy the template to your own Google Drive. Instructions below.

1.   Select the File menu.

2.   Select Make a Copy... from the File drop-down menu.

3.   Name your certificate.

4.   Locate your previously created folder.

5.   Click Select

6.   Click OK

-OR- Create your own certificate using Google Docs 

3. Edit your certificate

In Google Docs, edit your certificate to fit your needs. Use the <<tag>> format for areas that will be auto-completed.

These tags will tell the autoCrat addon where to place the form information in the certificate.

Changes are automatically saved in Google Docs, so click the three bars in the top left corner to return to your Documents.

4. Create your form

Return to the Google Drive folder you created and create a new Google Form.

1) Select New

2) Select the More option from the drop down menu.

3) Select Google Forms

Edit your form

Give your form a meaningful name and description then use the interface to add questions.

You will most likely use the short answer question type.

Make sure to toggle the questions to be Required for any questions that are mandatory or will be used in the creation of the certificate.

6. Modify your form's settings

1. Click on SEND in the upper right corner.
2. Then check Automatically collect respondent's Humboldt State University email address.
3. Click X to close.

7. Modify your form's responses settings

1.    Click on the Responses tab.

2.    Then click on the green button to 'Create spreadsheet'.

.   3. Select the Create a new spreadsheet option.

4.   Edit the name if you'd like, then click Create.

8. Set up your response sheet to use the autoCrat addon

The newly created sheet should have automatically opened in a new tab. If not, open it from the Google Drive folder that we created at the beginning of the tutorial.

1. Click on Add-ons.

2. Click Get add-ons...

In the search bar, type autoCrat and hit the Enter key, then click on the + FREE button.

1. If necessary, log in with your Gmail Google account.

2. Click Allow to grant autoCrat necessary privileges.

9. Create a new merge job in autoCrat

After installing autoCrat, the following window should be displayed. Click New Job to create a new merge job.

If you don't see the pop-up window above, click on 'Add-ons' > 'autoCrat' > 'Open'.

This is how you can get to the autoCrat menu in the future as well.

1. Name your new job - perhaps with the name of the form or course.

2. Click Next.

3. Click on From Drive to select the certificate template you created earlier.

4. Choose your certificate template.

5. Click Select.

6. Now click Next to continue with the selected template.

You will now match the tags in your certificate to columns in your spreadsheet.

Use the dropdown windows to select the proper column for each tag, then click Next.

1. Decide how you would like to name the files (certificates) that are generated by this merge job.

Click on the blue bar with an arrow on the left to see a list of available tags, and right-click them to copy them.

You can paste these tags in the File Name textbox to create individualized certificate names.

e.g. " <<Full Name>> Certificate - <<Course Name>> "

2. Then in the Type dropdown, select PDF.

3. For the Output as field, select Multiple output mode so each person gets their own document, rather than a single document with many pages that only you get.

4. Finally, click Next to continue.

1. Select your folder.

If your folder is not displayed, click the + Choose folder button.

2. Click Next.

No action is required on the next two screens (Steps 6 and 7) so simply click Next twice.

You will now decide how the certificates will be sent to recepients.

The following settings are recommended:

• Share doc? - Yes
• Share doc as - PDF
• Allow collaborators to re-share - No
• Send from generic no-reply address - No

• To: Use the blue bar again to copy the email tag and paste it here
• Cc: None
• Bcc: None
• Reply To: Your email (or alternate desired email) address
• Type in Subject: You can use tags here if you would like to include individualized information in the email subject line
• Type a message: You can use tags here if you would like to include individualized information in the email body

When you are finished, click Next.

1. Select Yes for Run on form trigger if you would like certificates to be generated automatically on form submission.

2. Select Yes for Run on time trigger if you would like certificates to be generated every:

            one hour, two hours, three hours, four hours, six hours, twelve hours, or twenty-four hours

In either case you will be asked to enable triggers, click YES to confirm these changes.

3. Then click SAVE to finish creating your merge job.

0. Add your Form to Canvas

At this point, you have finished setting up the automation process and just need to add your Google Form to a Canvas page.

First, let's get the embed link for your Form.

Go back to your folder and open your Form.

1.  Click SEND.

2. Click on the embed tab denoted by the '< >' symbol.

3. Click COPY to copy the embed link to your clipboard.

4. Click X.

Now, let's embed this form in Canvas.

Navigate to your Canvas course page and decide where you would like students to access this form.

For example, you could add a certificate at the end of each module or at the end of the course.

In any case, you will be embedding the Google Form in a Canvas content page.

1. Inside the page edit screen, click on HTML Editor.

2. Paste the embed link you copied before.

3. Then Save or Save and publish your page.

Your Form should now be embedded in your page!

11. Test your Form

You can fill out the form and submit it to test your automation process.

Immediately after submitting the form, you should see a new row in the Responses spreadsheet.

You should receive an email containing a PDF certificate shortly after submitting the form.

If you chose to trigger the merge job on a timed schedule, this will dictate when the certificate will be emailed.