Hey guys!
So today I wanted to talk to you about Autocrat, a Google Sheets Add on that has saved us major amounts of time and energy!! Autocrat creates PDFs and shared documents using spreadsheet data. This is a very powerful tool! Essentially, you'll create a template with <<merge tags>>, then use information from a spreadsheet to create individual PDFs or shared files. It's so great!
I first learned of Autocrat about three years ago when I needed to send 120 middle schoolers their course schedules during July, since it was the first time they got to choose their electives and we wanted to make sure that the schedules worked for everyone. It was amazing! I was able to merge the files at the touch of a button AND it emailed to the parent quickly and easily. Perfect.
Soooooo when we were told our kiddos had to take their diagnostic tests, EACH with a DIFFERENT username and password, we were concerned. How would we accomplish this? Would we make every teacher do it? They are already overworked and stressed!!
We are 100% virtual right now. Which means everything is done via Google Classroom and Google Meet. Some poor friends of mine screenshot each student's login cards and individually set up Google Classroom assignments FOR EACH ONE. I'm talking upwards of 25 kids per class, with three different logins. INSANE. And I feel so bad that I didn't remember Autocrat at the time she was telling me about this. 😬😬😬
I think they will forgive me though! I've offered to help them next time!
I've created a video tutorial, that I will upload to YouTube, but I've also written out the steps to using Autocrat here. Let's get started!
Okay! So first you'll have to gather the data you will be using. This can be really simple if your software offers CSV or Excel exports of student data. For example, we use DRC Insight for our Diagnostic Testing. Once you set up the testing sessions, random usernames and passwords are generated. Then, you can download a CSV file containing all of that information (and more). You may need to remove a bunch of columns and reformat your spreadsheet until you have it the way you want it. We also use IO Assessment (formerly EADMs), which unfortunately doesn't offer an Excel export (that I could find!) but we were able to export the necessary information from our SIS, JCampus and create the needed information.
So my spreadsheet for this tutorial looks something like this:
We are giving four assessments, two of which use different logins, and the other two will use the same login for each part (Science and SS). So I've named them Assessment A, B, and C.
IMPORTANT: Make sure you have a column in your spreadsheet titled Email Address. This is where you will put your parent email addresses!
Your spreadsheet can contain as much data as you would like. If you need to be more specific you can be. It totally depends on your needs!
So after you have all the data you need, you will now need to create the template that will merge with all your information and create your PDF or other shared file.
I like to use Google Slides. It's easier to manipulate the text boxes and other items you place on the template.
Setting up your template is easy. All you have to do is include all necessary information you want your students or parents to know. So in the example I've made above, I've included the student name, grade, and the names of all three assessments and their login credentials.
Then, I've added the <<merge tags>>. These MUST MATCH the headers of the columns on your spreadsheet. They are case sensitive, and you must include any spaces.
You do not have to use EVERY SINGLE header from your spreadsheet; you only include the ones you want.
Format your template however you want, change the font, font size, color, etc.
Go back to your Google Sheet, and install the Autocrat Add-on.
Click on Add-ons, then Get Add-ons, then search for Autocrat. 
Install the add on. It takes a few moments to appear on the Add-ons menu.
Once it loads, click on Launch it to run the dialogue to set it up.
Click on New Job.
You'll run through the setup program.
Choose your template that you set up in Step 2.
Make sure that your merge tags all match up with your source data. If they don't match, you'll have to fix it on your template, then click Back, and then Next again.
Here you will name how your files will be saved. You can use Merge Tags again in the file name so you can personalize each individual file. Trust me you don't want 450 files in your drive allllllllll named "Benchmark Testing Login Credentials" - what a nightmare!
I like to save it as a PDF, rather than Google Slides. IMPORTANT: You must select Output as Multiple. Otherwise you have to type every single email address separately.
Choose the folder where you want all of the files to be saved as.
You can skip the next two screens (they don't apply to this setup).
This screen is super important. Make sure to choose Share doc and Send from generic no-reply address if you have G Suite for Education.
Scroll down and you'll see where you type the email that you will send to everyone. Make sure to use <<Email Address>> on the To line. Again, you'll be able to use Merge Tags to personalize the email body if you want.
Click next, and then Save. The trigger step is if you are automating this process, which will be a different tutorial!
And that's all of setup!
You'll get to click on the PLAY button when you are ready to do the merge!
The Merge will run.
The Merge can take from a few seconds to about 10 minutes (or longer, I guess if you have lots of files). The spreadsheet will populate with the merged file and let you know the status of each merge.
And that's it! Once it's done, you'll have the rest of the time to accomplish other things on your to do list.
Occasionally you may get an undeliverable email meaning that the email address you've put in the spreadsheet doesn't work. So all you need to do is delete the email address and fix it, then delete the information in the Merged Doc rows (rows N-Q in my shot above), then rerun the Autocrat job. Easy peasy.
So you'll be sending out emails that look like this:
And the attachment will look like this:
So there you have it! Autocrat is a great way to automate your workload!
Try it out, test it out, see if it will work for you. You can run lots of merge jobs. I think I read somewhere you can send up to 1500 emails per month, but I'll have to do some more research.
So let me know what you think! Have a great week!
