Embedded Files
autoCrat is a sheets add-on that handles the dreaded mail merge; it takes the information in the spreadsheet and inserts it into a readable document that you design, names the document, and stores it in the folder you choose in your Google drive.
If you choose, it will also automatically send an email with that document attached. That's a lot for one little computer script to do! More about the email here.
Hang in there through this, because practice using this tool will really pay off--especially combined with the pre-filled form!
- Click here to install the add-on. Authorize it to run. autoCrat will add 4 columns to the right of the last column of your spreadsheet–the one we set up as our first step–so that it can post the link to the new merged doc–or PDF–and record if the email was successfully sent. Don't delete any columns from your spreadsheet. You can delete rows, but not columns.
- You need a template document to insert the responses into. You'll probably want the respondent's name at the top, perhaps with the school logo. Copy the question from the form for the first name. Type two "less than" symbols, and between them, paste in the question you just copied, then two "greater than" symbols; ex., <<FIRSTNAME>>. This is known as a <<Merge Tag>>.
- Click here to make a copy of my sample template document.
- Here's another example: click here to make a copy of a rubric designed to be sent to a student.
- Click here to make a copy of a progress report template, to be sent to a parent. You need a template before going on to the next step. However, you can edit the template later if needed.
Go back to your form, fill in the questions, and submit it for a test. Did your responses show up in a new sheet of the spreadsheet with your data on a page labeled "Form responses"? That's a relief!
- From the sheet labeled "Form responses," under Add-ons, launch autoCrat. Click "New Job" and give the job a name. You will probably only need 1 job, but you can have several, each with different settings. (BTW, each job will add 4 columns to the right of your last column. These log what the script does.) Click "Next."
- Choose the template from your drive. autoCrat calls this "Step 2."
- autoCrat will attempt to "map the tags." If your question on the form is exactly the same as the word or words between the << >> symbols on your template, it will do the job for you. Otherwise, use the drop down to match the tag to the column you want the info to come from.
- Now it asks for the file naming pattern. autoCrat calls this "Step 4." I choose something like: <<Last Name>>, <<First Name>> Q4 2017-18 <<Homeroom>> nemo or dory
- Now it asks which folder in your drive to store the docs or PDF in. This folder can be shared with someone who needs to access the merged information.
- Next is the "Dynamic folder reference." I skip this. Next!
- Set the merge condition. This also is optional. I would set a condition if I were testing my setup--something like "Name=McCormack." Then only a response with that name will be merged. Possibly you might want to run the form for grade 7 only, etc. Otherwise, skip it. Next!
- Save. When ready, run the job by clicking on the "play" triangle. If there are errors, click the pencil to edit the job. I always have errors, so I always run a test. So far, we've completed the merged document, but have not emailed it. If you're going to print the docs and hand out the paper, you're finished with autoCrat. If you want to send the merged document attached to an email, get started on this list of steps.
- Great list of links about autoCrat.
These 2 slides demonstrate how merge tags work. They're from autoCrat is autoMagic slideshow from ISTE2018, an awesome presentation by Asher Scott and Elizabeth Mason, New Visions for Public Schools.
video tutorial for:
Report abuse