Using Autocrat to Create Certificates of Completion AND Email Them to Parents

Ok. This isn't for the tech hesitant, but don't be afraid to give it a try. This is one of those things that takes some prep, but the magic pay off at the end is pretty darn cool. You'll use this skill again. 

Here is what you need to make this happen. Consider this your supply list:

1. A list of all student names

2. A list of a parent email for each student

3. The Autocrat add-on in Google Sheets

4. A copy of the Certificate of Completion

Alright, got the supplies? Let's get cooking.

Step One: Create a new spreadsheet and in the A column add a header that says Name and in the B column as a header that says Parent Email. You can title this Certificate of Completion 2020 if you'd like. 

Step Two: In the A column, add all of the student names and the B column, add the corresponding parent emails. 

Now that your spreadsheet is ready, time to make some magic. 

Step Three: Click on Add-ons and and choose Autocrat. If you don't have it yet, choose get Add-ons and search for Autocrat. Then click launch.

Need to know: Autocrat performs mail merges. This is done through something called a tag and you use <<tag>> to map where data will go. So think of the arrows as directing the data. You'll see this language in the Autocrat process. 

Step Four: Select New Job in the Autocrat pop-up window and give the job a name such a cert 2020 or cert practice if you want to practice first. Then press next. (and on that topic, I'll link my practice spreadsheet for you to make a copy and practice with that first if you'd like)

Step Five: You need to find your certificate of completion slide. You should make your own copy because you need to put a <<tag>> on it. In the space for the student name, type <<Name>> (this needs to match the A1 header on your spreadsheet exactly. If yours is lower case, make the <<tag>> lower case. 

Step Six: In Autocrat on the spreadsheet, select your certificate template and hit next.

Step Seven: Time to map the <<tags>>. First where is says Merge Tab, choose Sheet1 from the dropdown (this is the name of the tab on your spreadsheet) and below that you want the <<tag>> from the certificate to map to the Name column. Then hit next. 

Step Eight: I know, would doing this manually be faster? No! Stick with me! On the next screen in Autocrat, you will use <<tags>> again for some wizardry. You can name each file this will create with the student name! So type in <<Name>> Certificate of Completion for the file name and change the type to PDF. Leave it as is for output. Now hit next. 

Step Nine: Choose where in Drive you want a copy saved. But we will also be emailing this to parents in the next step. Skip the page that asks about the dynamic folder and skip the set merge condition

Step Ten: On the 8th Autocrat step, you will now pick yes to share the file and then scroll down for the superpower of this add-on. You'll draft the email that will send with the certificate and it will all be customized with your tags. 

Type in the email message you'd like each parent to receive with the certificate. Be sure to use the <<tags>> to make the message customized. See my example here. Now if you want to be super clever, you can add Column C on your spreadsheet with Parent Name and use that as a <<tag>>! Note that I added the <<tag>> for <<Parent Email>> and this matches my column B. And I used the <<tag>> for <<Name>> to add in the student name in the email subject and message. Fill this out and hit next. 

Step Eleven: (Nearly done) Skip Add Remove triggers, and hit Save. You have successfully programmed your Autocrat mail merge. Now it is time to run this! You'll see a window pop up with your Autocraft job. Look for the play button. Once you hit play, the program will run and autofill the certificates with your students' names, make a PDF for each student, put a copy in Drive for you, and send a file to the parents with the custom email! 

Items to consider:

• Run a test with my sample spreadsheet. Put your email address on it so you can receive one to make sure it works. 
• You can also run it without sharing first to make sure the certificates create correctly. To do this, on the step where you said Yes to sharing the doc, change it to no. You can click the pencil to edit the job or delete and start over. 
• Remember, this feels long as you play and try this out, but what you'll be learning can carry over to future projects. There are some cool applications for student recognition during the year, or individualized feedback to parents. This can connect to a Google Form and send reports to parents based on a form getting completed.