Email Mail Merge Custom PDF Certificates with autoCrat for Google

Saturday was our fourth EdCampOKC conference in Oklahoma City and it was great! This year to provide professional development certificates to attendees afterward, our organizer team got fancy and used the free Google Add-On autoCrat.

“autoCrat for Google Sheets” (CC BY 2.0) by  Wesley Fryer 

autoCrat is a powerful, customizable script for Google Forms and Google Sheets which can create merged documents and then even email them to others. In this case, we used it to send customized professional development certificates to each EdCampOKC attendee who filled out our online event evaluation. This is an example of what the emailed certificates looked like, except participants received them as PDF attachments in email rather than as an image:

“Example EdCampOKC Certificate” (CC BY 2.0) by  Wesley Fryer 

We used the following workflow to create these mail merged PDF certificates:

1. Create an online survey using Google Forms. (this was ours)
2. Create a Google Document to use as a certificate template. Use repeating brackets for places where you want to merge submitted spreadsheet data. Example: <<name>> (this was ours)
3. Install the free autoCrat add-on for Google Sheets.
4. On the Google Form, click to create a Google Sheet for the online form results.
5. On the Google Sheet, from the Add-Ons menu select autoCrat.
6. Choose your Google Document as your template file.
7. Select the desired options for the autoCrat script, including “Run autoCrat when new forms are submitted.” You will choose to create a PDF file, save it in a specific folder in your Google Drive, add text to use as an email template (including merge fields, if desired) to send to form submitters, and identify the Google Sheet fields you want to use in your merged Google Doc.

This 13 minute video tutorial demonstrates these steps.

I want to share a BIG shout-out to my Wyoming / Montana friend, Diane Woodard (@dianewoodard), who told me how to do this in May 2015 at her Google Forms session at the WyTECC Conference in Rock Springs, Wyoming. You rock, Diane! 🙂

super Google Forms session by @dianewoodard at #wytecc15 gr8 use of autoCrat Add On https://t.co/SXckPemPpC send customized PD certificates!

— Wesley Fryer, Ph.D. ??? wesfryer.com/after (@wfryer) May 2, 2015

So great to learn from & reconnect with media & #googleEDU Jedi @dianewoodard at #wytecc15 pic.twitter.com/jEsKrvtyET

— Wesley Fryer, Ph.D. ??? wesfryer.com/after (@wfryer) May 2, 2015