AutoCrat: The Greatest Google Mail Merge Script of All Time

I'm not exaggerating.

Check this out:

• Users fill out a form online
• The data from each user merges into an individual Google Doc (from a template)
• Those Google Docs (you can also choose to make PDFs) are organized in a folder in Google Drive
• You can automatically send an email with those attached PDFs or Docs
• This can happen when you run the script or as soon as the user submits the form.

I can think of a million uses for this, particularly in relation to the issues arising with e-signature regulations. One of the biggest headaches for a Technology Department is managing all the paperwork in relation to AUPs (Acceptable Use Policies), new user accounts, and permission slips for COPPA (Child Online Privacy Protection Act) compliance. Some signatures still have to be physical, which means that the data on those forms have to be manually transferred into a spreadsheet or program, after the document is signed.

Picture these two scenarios:

• The old way: Thousands of paper forms are stacked at registration tables. Parents go through the line, filling out their information and signing each sheet. From there, the papers are shuffled around and someone takes a few weeks to painstakingly review the handwritten data on each form and manually input the information into a computer program or spreadsheet. 
• The new way: A row of 3-5 computers are lined up- parents fill out the form, and a PDF is automatically generated. A worker has access to all the PDFs in Google Drive and prints each one as it is created. The document is signed on the spot, placed in a basket, and the worker goes back to the spreadsheet and puts an X in a column (as confirmation that the document has been legally signed). The information can be downloaded as a .csv file and imported directly into the program of choice.

For teachers, this is a great way to send advanced rubrics back to students. For administrators, this is a great way to send back detailed observations to teachers (or even for just simple walk-throughs). For me, this is the perfect solution for a million different issues. Organizing a Tech Academy? Attendees can get a packet of information (with the schedule they chose) on individual documents sent to their emails. Assessing a group of people on technology skills? They can get an advanced summary of their results, as well as descriptions of professional development opportunities.

And one more thing... while you're waiting for the merge to run, you get to stare at a robot- see above. And yes, the mustache moves as you wait.

Alright, enough praising. Let's talk about how it works.

The script: AutoCrat
Where to find more information: This website

---

STEP ONE: Build your form

• If you've forgotten how to do this, click here for instructions.
• Make sure there is a place to collect email addresses!

STEP TWO: If necessary, create formulas to score the results

• If you're just using this script to streamline paperwork, you probably won't need to do this. 
• If you're using this script for grading purposes, you will want to do this. Create a new tab at the bottom of the spreadsheet and pull the data over. (The reason you need to create a new tab is because otherwise your formulas delete when a new user fills out the form).
• In the example I'm using below, I used the ROUND and SUM formulas so that the results would be scaled to one decimal point.

STEP THREE: Create the Google Doc that you will use in the data merge

• Decide where you want the data to end up, and create a tag enclosed in brackets.
• Example: <<results of question one>>
• Make sure you don't use any numbers (unless they are spelled out) in that tag. If you do, the merge might not work properly.

STEP FOUR: Create a new folder in your Google Drive (for the documents to end up)

• Forget how to create a folder? Click here for instructions.

STEP FIVE: Find AutoCrat in the script gallery of your spreadsheet

• Go to your sheet and click "Tools" and "Script Gallery". Do a search for "AutoCrat" and click "Install". Confirm use, and click "Done". 

STEP SIX: Follow the directions in the script (step 1-4)

• I realize those are generic instructions, but the person who created this script did an amazing job of making this incredibly easy to use. If you click "Autocrat" just to the right of the help menu, you can just start going through each step. Start by clicking "Run Initial Configuration".
• Step 1: Select the Google Doc that you created earlier
• Step 2: Select the the sheet you are pulling the data from
• Step 3: Set merge conditions (optional)
• For almost all purposes, go on to step four by going back up to the toolbar and clicking "Autocrat"
• Step 4: Set field mappings (align the Google Doc tags with the columns on your spreadsheet)

STEP SIX (continued): Follow the directions in the script (step 5-6)

• Step 5 is where you can set the merge type. Here are the options:
• Save merged files to Docs (check yes)
• Select destination folder (the one you created earlier)
• Select whether you or creating a PDF or Google Doc
• Choose whether or not you want to save the links to those docs in your spreadsheet (I usually choose yes)
• Email and/or share merged Docs
• If you email the Doc, use the email address variable shown above (it will have a $ symbol above it- simply highlight that text and paste it in the box).
• You can create an email message, but be aware that all the text will be lumped together (see below)
• Decide whether or not you want to send the document as soon as the user sends the form.

Let me know if you think of any other uses for this script below- I'll add them to my list! You can read more about the script here.