Wednesday, August 25, 2021

Free Webinar: Automated Certificates with Google Tools

Do you need to take attendance and generate certificates for meetings, trainings, and other events? 

There are lots of options out there to address this need, but to help out I have created a free system using Google tools to automate the entire process of creating sessions, taking attendance, generating and emailing certificates, and viewing multiple reports.

I hosted a webinar to show a demonstration of the system in use, and explain how to get your own copies of the files and set up everything for your own use.

See below for the recorded video, as well as detailed resources for the session.


🎞️ Recorded Video (90 minutes)

Or you can watch on YouTube at - https://www.youtube.com/watch?v=ZJoX_PNorzU


🧰 Session Resources


👀 Overview

Organizations need to:
  • Track attendance for meetings, trainings, events, and more.
  • Generate certificates of completion for attendees.
  • Analyze attendance data and evaluations.
There are lots of products to help with this. For a free alternative, I developed a system using Google Forms, Sheets, and Docs, and the Autocrat and Form Ranger add-ons.


📄 Templates

Use the links below to make copies of the templates. Note: When you make a copy of the Google Sheet template, you will also automatically get a copy of the associated Google Form template.

☑️ Setup Steps

Create folders to hold files
  • Create a folder in Google Drive to hold everything, such as "Certificates".
  • Create a subfolder inside of that folder to hold all the merged certificates, such as "Merged Certificates".
Make copies of the template files
  • Make a copy of the Attendance Sheet Template (Google Sheets link)
  • Make a copy of the Certificate Template (Google Docs link)
  • When you made a copy of the Google Sheet template, that also automatically created a copy of the associated Google Form.
  • Locate the copied Sheet, Form, and Doc (most likely located in the root of your Google Drive) and move all three files into the main certificates folder you created earlier.
  • Feel free to rename the Sheet, Form, and Doc however you like.
Edit the Google Doc certificate template
  • Open the Google Doc certificate template you copied and moved earlier.
  • Edit as needed to personalize for your organization.
  • Do not edit any of the placeholders which are designated as <<placeholder>>
  • Feel free to edit other portions of the Doc such as:
  • The "Sponsored by" section
  • The approval people at the bottom
  • The logo for your organization
Setup Autocrat
  • Open the attendance Google Sheet you copied and moved earlier.
  • If you have not installed Autocrat yet, do that now by clicking "Add-ons" then "Get Add-ons" then locate Autocrat then install.
  • Once Autocrat is installed, launch it by clicking "Add-ons" then "Autocrat" then "open".
  • When Autocrat opens, you will see a merge job titled "Certificate Merge".
  • Click the pencil icon to edit the job.
  • This will open a 9-step wizard to edit the settings for the job. (This is a one-time process.)
  • Step 1: Name your merge job - Do not change the job name.
  • Step 2: Choose template - Click the "From drive" button to locate your copy of the Google Doc certificate template.
  • Step 3: Map source data to template - No changes should need to be made here, but feel free to scroll down to verify that each field is mapped.
  • Step 4: File settings - No changes should need to be made here. If you do wish to change the "File name" be sure to not change any of the placeholders.
  • Step 5: Choose destination folder - Click the trashcan icon next to the "Merged Certificates" folder to remove that folder. Then click the "+Choose folder" button and select the subfolder you created to hold the merged certificates.
  • Step 6: Add dynamic folder reference - No changes should need to be made here.
  • Step 7: Set merge condition - No changes should need to be made here. The merge condition should be left as "Approved" equalling "YES".
  • Step 8: Share docs & send emails - Feel free to edit the email subject and body to customize for your organization. However, be careful not to change any of the placeholders. No other changes should need to be made here.
  • Step 9: Add/remove job triggers - The trigger to automatically run the merge will need to be reset. For the option "Run on form trigger" you will need to select "no" to turn it off and then select "Yes" to turn it back on. When asked if you want to enable form triggers, select "Yes".
  • Click "Save" when all done.
  • You can now close out of the Autocrat window.
Manage sessions
  • You can now begin adding sessions to the Google Sheet.
  • Click on the tab titled "Sessions".
  • Add sessions by typing in data for the following columns:
  • Title - The title of the session
  • Date - The date when the session is held in format MM/DD/YYYY
  • Time - The start and stop time for the session (in whatever format you wish)
  • Hours - How many contact hours will be awarded for this session. Enter this as just a number. Decimals are allowed.
  • Objectives - The goals for the session. If you want line breaks for each objective, press "Ctrl" and "Enter" (Windows PC) or "Command" and "Enter" (Mac)
  • Code - The unique code that attendees will need to enter to prove they attended the session. This code will need to be given to the attendees by the presenter at some point during the session. There are no special requirements for the code, but a short word or a 3 or 4 digit number should work well.
  • Followup Message - This is optional. If you want to include a message specific to this session with the certificate, enter that here. This could include links for resources, reminders for the next meeting, or such. Again if you want line breaks for each objective, press "Ctrl" and "Enter" (Windows PC) or "Command" and "Enter" (Mac)
  • Do not make any changes to columns A and B which are in red. These columns are auto-populated with unique ID information for each session.
  • If you want a session to be available for attendees to get a certificate, click the checkbox in the "Active" column.
  • If a session is no longer available, or not yet available, simply uncheck the checkbox in the "Active" column.
  • Do not delete rows when sessions are no longer needed as this will make them unavailable for your reports. Instead simply hide old rows by right clicking on the row number and choosing "Hide row".
Edit the attendance form
  • Open the Google Form that was created earlier when you copied the Attendance sheet template.
  • You can customize the Form by editing the following items:
  • Title
  • Description
  • List of school districts or organizations
  • Theme - color and header image for the Form
  • It is not recommended to change any of the other elements of the Form, or to add or remove any questions, as the merge relies on the specific structure and fields already included in the Form.
Setup Form Ranger
  • If you do not have the Form Range add-on installed you will need to do that now by clicking the 3-dots button in the top right, then "Add-ons", then select "Form Ranger" and install.
  • Once Form Ranger is installed, launch it by clicking the Add-ons button (puzzle piece icon), then "Form Ranger", then "Start".
  • You will now need to set up Form Ranger to auto-populate the "Session" question. (This is a one-time process.)
  • In the Form Ranger panel, locate "Session" in the "Questions list".
  • Check the box for "Populate from range".
  • Click the "+" button to add a new range.
  • Select your copy of the Attendance Sheet.
  • From the "Sheet name" drop-down menu choose the sheet titled "Active".
  • For the "Range name" enter "Active".
  • When done click "Save and populate question".
  • In the Form Ranger panel, for the "Auto-repopulate questions" section turn on the option for "Every hour".
  • The Form will now automatically pull in the current list of active sessions once every hour.
  • If you need the Form to refresh the sessions sooner than that, you can simply reopen the Form Ranger add-on at any point to force it to repopulate.

🔗 Share the Form Link

Now that everything is set up, you can give people the link to your Attendance Form.
  • From the Form, click the preview button in the top right (eyeball icon).
  • This will open up the live version of the Form.
  • Copy the web address for the live version of the Form.
  • You can provide this link to the attendees for any of your sessions. You can share the link as is, or with a URL shortener (such as Bitly), or as a QR code, or such.
  • When any attendee completes the Form, the merge will run and they will be automatically sent their certificate of attendance.

🛠️ Maintaining the System

As needed you can edit the Sheet and Form from time to time.

In the Google Sheet you can:
  • Add new sessions on the "Sessions" tab.
  • Activate and deactivate sessions by checking or un-checking the checkboxes in the "Active" column.
  • Hide old sessions by right-clicking on the row number and choosing "Hide row".
In the Google Form you can:
  • Force a refresh of the session list (if you can't wait for the automatic hourly refresh) by reopening Form Ranger.

📊 Viewing Reports

As attendee submissions are collected in the Google Sheet, you can view reports on the blue tabs at the bottom of the Sheet. The following report tab are available:

Session Attendance
  • This report displays who attended a specific session including their name, email, and organization.
  • To generate the report, enter the ID number for the session at the top. You can get the ID number from the "Sessions" tab.
User Attendance
  • This report displays all of the sessions that a specific user has attended including the date, time, title and hours.
  • To generate the report, enter the user's email address at the top.
Organization Attendance
  • This report displays all attendance records for a specific school district or organization including attendee names, dates, times, titles, and hours.
  • To generate this report select the desired organization from the drop-down menu at the top.
Session Eval
  • This report displays the evaluation data for a specific session.
  • The data includes the number of submissions for each rating (1 to 5 scale), the overall average rating, and any comments provided by attendees.
  • To generate the report, enter the ID number for the session at the top. You can get the ID number from the "Sessions" tab.
Organization Count
  • This report displays a list of every school district or organization, along with a total of how many attendees have completed sessions from each organization.
  • This report is a pivot table and displays automatically without you needing to enter anything.

🩺 Troubleshooting

Below are some common issues that may be encountered when using the system.

Wrong code
  • The attendee may enter the session code incorrectly. You can check this on the "Form Responses 1" tab in the Sheet.
  • The code they entered will be in the "Code" column, the actual code will be in the "Real Code" column, and the "Approved" column will show if they entered the correct code.
Wrong email address
  • Typos are common, even with someone's own email address.
  • You can check the email they entered on the "Form Responses 1" tab in the "Email Address" column.
Autocrat failed
  • From time to time the Autocrat add-on might not run properly.
  • You can check this by looking at the last four columns in the "Form Responses 1" tab, which are the merge results columns.
  • If these columns are not all filled in, then Autocrat did not run properly.
  • To rerun Autocrat as needed, first delete the 4 cells in the merge results columns for only the rows that did not run properly.
  • Then click "Add-ons" then "Autocrat" then "Open".
  • Now click the "Run Job" button (looks like a triangular play button) to manually start the merge.
Sessions are not updating
  • If the sessions from your Sheet are not showing up in the Attendance Form, you can check a coupe things.
  • First, make sure you have checked the checkbox in the "Active" column on the "Sessions" tab.
  • Next, Form Ranger should pull in the active sessions once per hour, but you can manually force this by going to the Attendance Form, clicking on the Add-ons button (puzzle piece icon), then "Form Ranger", then "Start".


Post by Eric Curts. Connect with me on Twitter at twitter.com/ericcurts 

No comments:

Post a Comment