AT GuidesTeaching and Learning ToolsGoogle Suite Google SuiteHow do I use Google Forms and Sheets to automatically generate custom certificates?

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.

 

Select new folder.
Select create.

2. Create your certificate

You can copy and modify this certificate or create your own using Google Docs.

1.   Select the File menu.

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

Select Make a Copy from the drop down menu.

3.   Name your template

4.   Locate your previously created folder.

5.   Click Select

Select the folder.

6.   Click OK

OK the selections.

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.

Google Docs home button.

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

Select a new Google Form.

5. 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.

Fill in 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.
Google Form's SEND button
Automatic email address collection checkbox

7. Modify your form's responses settings

1.    Click on the Responses tab.

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

Create Spreadhseet button

3.   Select the Create a new spreadsheet option.

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

Create new spreadsheet

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...

Google Sheets 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 HSU Google account.

2. Click Allow to grant autoCrat necessary privileges.

Google account selection
allow autoCrat permissions

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.

autoCrat New 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.

autoCrat job name

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

autoCrat job pdf template

4. Choose your certificate template.

5. Click Select.

certificate template selection

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

autoCrat mapping tags to columns

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.

autoCrat map source data to template

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.

autoCrat file settings

1. Select your folder.

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

2. Click Next.

autoCrat destination folder

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

autoCrat dynamic folder reference
autoCrat merge condition

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.

autoCrat doc and email settings
autoCrat doc and email settings

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.

autoCrat trigger settings
autoCrat enable form triggers

10. 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.

Google Forms Send button
Google Forms embed link button

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

4. Click X.

Google Forms copy embed link

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.

Canvas Page HTML Editor button
Canvas Page with embedded iframe

Your Form should now be embedded in your page!

Google Form in Canvas 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.

Sample Google Sheet row(record)

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.

12. Example Certificate

Google Docs Example Certificate