AutoCrat: Automation for Google Form Responses

Screencap from the Google Workspace Market, featuring Autocrat's name, logo, and the text "Flexible, easy to use document merge tool that creates PDF or shared Documents from spreadsheet data."

Automation Makes Life Easier!

In a recent meeting, someone suggested that each upcoming newsletter have a short reaction or reflection from a large body of members. But the amount of work involved in coordinating, collecting, and standardizing all the input seemed onerous.

Fortunately, I had an easy solution: AutoCratAutoCrat is an add-on for Google Sheets that can fill a pre-made template with data from a spreadsheet. Since Google Forms can automatically push submissions into a Google Sheet, AutoCrat can turn Form submissions into certificates, preformatted text, or any number of other types of output.

You can choose whether you want the output to be a Doc, a Slide, or a PDF, whether or not you want to automatically email the output (on Form submission, on a timer, only on your say-so, or not at all), and even whether you want an email recipient to have the ability to share the output with others. 

Digression: Google Forms and Sheets

For educators, it’s hard to find a free software tool that’s more valuable than Google Forms. They provide educators with an easy way to gather input from students and staff in a wide variety of ways. 

I have several different Forms running as part of my school library program. Each connects to a Google Sheets spreadsheet that automatically notifies me when someone submits a new Form entry. 

Screencap of a Google Sheet's Tools menu, with an arrow pointing to "Notification settings" and an arrow from there to "Edit Notifications"

To set Sheet notification settings, go to the Tools menu and select “Notification settings – Edit notifications”.

Screencap of the Notification Rules pop-up menu.

In the pop-up that appears, choose when you want to be notified.

One collects Readers Advisory requests. Another collects book purchase requests. A third collects “Would You Rather…?” poll ideas. Forms are really easy to create, and they make collecting data and information a breeze. They standardize the inputs, making it easier to review submissions. And with notifications turned on, I get an email any time a staff member or students submits a response on my Forms without having to separately monitor a bunch of spreadsheets.

Google Sheets and Add-Ons

Information submitted to a Form can easily and automatically be sent to Google Sheets spreadsheet. And that’s where some real magic can happen. 

The array of formulas (and array formulas) available within a Sheet allow quite a bit of flexibility and automation of data handling. Spreadsheets are great for sorting and re-arranging information. 

And with Google’s productivity tools, there’s also the bonus of Add-Ons. These are third party programs, formulas, and macros that offer additional functionality to your Google applications. If you haven’t explored Add-Ons, you really should look them over – different Add-Ons are available for each of the main Google productivity tools. But for now, we’re going to focus on a personal favorite of mine: AutoCrat. 

What is AutoCrat?

The New Visions For Public Schools Cloud Lab developed AutoCrat. AutoCrat allows for some great automation of Google Form input and Google Sheets data. 

Because of its many features, Autocrat can be a little confusing to use at first. But once you get a handle on its abilities, I have no doubt it will become a regular part of your toolkit!

AutoCrat is very popular with educators, and it’s been around for years, so there are a TON of online video tutorials that walk you through set up and use. Autocrat has evolved over the years, so if you seek online assistance, keep an eye on the date the help was posted. Here’s a nicely done and fairly recent tutorial for first time AutoCrat users.

AutoCrat for PD Certificates

One way I use Autocrat is to automate Professional Development certificate delivery to workshop attendees. After the workshop, attendees complete a short Form. Their name is pushed into a pre-formatted Google Doc. 

Screencap of the Google Doc template for an NJASL PD certificate.

A sample of a PD certificate template. Note the “tags” – text surrounded by angled brackets. AutoCrat detects these and replaces them with information from the spreadsheet.

AutoCrat takes the filled-in Doc template, converts it to a PDF, and automatically emails each respondent a personalized PD Certificate. 

Standardized, Pre-Formatted Text Responses

I also use Autocrat to create standardized submissions of book reviews from staff and students for my biweekly library newsletter. When staff and students complete the Form, their responses are converted into a block of text in a Google Doc that has been pre-formatted. This makes the text ready to copy-paste right into my newsletter.

This is where Forms and AutoCrat can really shine. 

Did You Know Forms Does That?

In addition to short answer questions about title and author, my Form for this task “forks”. A respondent selects from a drop-down option indicating if they are staff or student. Each option brings respondents to separate sections of the Form, providing each group with custom fields to complete. 

Screencap of a Google Form question. The question reads "I'm a"; the menu indicates the answers are dropdown options. Option 1 reads "Student" - after the option, the "Go to section based on answer choice is enabled; for this choice, respondent "Go to section 3 (For Students)". The other answer choice is "staff member"; for this choice, respondents "Go to section 2 (For Staff Members)".

For example, the staff section includes their subject area and grades taught, while the students’ section only includes their grade. 

AutoCrat’s Merge Conditions & Multiple Jobs

When users submit their reviews, the text they entered is automatically pushed into a running document – one for Staff, one for Students – where it is formatted for me. The template italicizes book titles, and adds bold formatting to certain portions of the text. Different sections of the response are in different font sizes. The final product is preformatted text that’s ready to copy and paste into my newsletter. 

Having different Student and Staff response options is possible because AutoCrat allows users to set “merge conditions”. If the condition is met, AutoCrat runs as directed; if the condition is not met, AutoCrat does nothing.

Screencap of the AutoCrat "Set merge condition" menu, which is set up as follows: Only merge row if: Column (dropdown menu choice selected is "I'm a") equals (text box completed with "staff member").

An example of a merge condition. This tells AutoCrat that if a respondent says they are a staff member, their response goes into the Staff Member Book Review template, which has different fields than the Student Book Review template.

Because you can set AutoCrat up to run multiple times within a single Sheet, you can get it to do different tasks for different types of submitted data. This allows the submissions from Staff to go to a different place than my submissions for Students. 

Screencap of AutoCrat's starting menu, which shows two Jobs have been set up - "STAFF Text for Book I Liked" and "STUDENT" Book I liked text".

Because the student book review template has different fields than the staff template, I set up a separate template for each. AutoCrat can only handle one template per job. But it can handle multiple jobs, which means information can be sent to multiple templates, depending on merge conditions.

Output: Pre-Formatted, Standardized Text

The results from both the students and staff responses to this Form go into Student and Staff Responses Docs, respectively. I can then go in and copy-paste from the Doc right into my newsletter. This saves a lot of time trying to move the responses cell-by-cell from a Sheet or Form. 

Screencap of the template for staff book reviews. Reads as follows: <<How I want to be credited>> - <<Department(s) or assignment(s)>> <<Grade(s) I work with (if applicable)>> <<Title of the book I liked>> by <<Author>> I think the audience for this book is: <<The audience for this book is:>> “<<What did you like about this book?>>”

The template for staff book reviews. Each bit inside angled brackets is a tag that AutoCrat seeks and replaces. Don’t let the tags scare you! AutoCrat auto-recognizes tags if they exactly match a Sheet’s column headings. To save myself the step of having to match tags to columns, I copy-paste my Form questions right into the tags in the template.

Screencap of a Google Doc that contains book reviews submitted by two staff members; both are formatted in the same fashion - Staff name, assignment, grades; Book title (in italics) by Author"I think the audience for this book is:" followed by one or more choices from a menu of "Middle school students", "High school students", "Adults" A text block from the submitter about the book and/or their responses to the book.

Example of submitted, formatted book reviews ready to copy-paste (Staff member names modified for privacy).

Email Merge

In addition to all the automated file creation possibilities, AutoCrat can also be used as a straight-up email merge program. The text of the email can use tags, allowing for “automated personalization” of messages. And if you set the Trigger for a specific time, AutoCrat sends your emails at the most convenient, least intrusive part of the day. 

How Are You Using / Might You Use AutoCrat?

I’m sure there are school librarians out there already using AutoCrat to make their lives easier. What are some ways you’re taking advantage of AutoCrat’s automations? 

And I hope there are some folks out there whose minds are aglow with the possibilities AutoCrat offers. What are some ideas you have for using this handy tool? 

I hope both groups of readers will share their thoughts in the comments!

mm

Author: Steve Tetreault

After 24 years as a classroom English Language Arts teacher, Steve became a school librarian in January 2022. He has earned an M.Ed. (2006) and an Ed.D. (2014) in Educational Administration and Supervision, and completed an M.I. degree in Library and Information Science (2019). He is certified as a teacher, school library media specialist, supervisor, and administrator. He is an old dog constantly learning new tricks!



Categories: Technology

Tags: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.