A dear friend asked for help yesterday. It was the kind of request that intrigued me into action. She had a list of codes that she wanted to automatically assign to participants in an online class once they had reached a certain level. Her initial thought was to include a simple Google Form to collect the necessary data. At this point, she was getting individual notifications and manually emailing each participant their code. It was time consuming and there always seemed to be an email to send just as she began other tasks. She didn’t want her students to be slowed down while waiting on her. She wondered if there was a way to automate this process.
At once, I dug into the problem. After a bit of research and thinking beyond my own knowledge base, I managed to solve this problem. By adding a simple query between the form data and the add-on AutoCrat, we can now use the live data and add individualized information before we send individual documents/emails/PDFs to each person. Since that moment, I’ve thought of tons of other ways to use this data manipulation method. I also thought that if I had all of these ideas, other people may just be interested in this as well. Here how I did it.
Step 1: Create a Google Form
Create your Google Form just the way you want it. In this example, I created a simple form and set up the Google Sheet to collect the data.
Step 2: Create your Email Template
In this example, I created a document called “Project Criteria”. The purpose of this specific document is to assign “random” topics to students as they complete a level of study in class. Notice that I have used the same categories as in my form, but there is a new category called <<Project>>. This category will be added to the data collected from the form before we use AutoCrat to send out the documents.
Step 3: Set up your Response Sheets
Until now, there hasn’t been anything special. It’s very similar to using AutoCrat with any Google Form. Here’s where the “magic” occurs. You want to create another sheet. This is in this sheet we will actually be working. We will be using the Query function to copy the data from the Response sheet to Sheet 2. In this example, the original file has only 5 lines of data. We are going to change the 5 in the D5 cell reference to a number higher than we expect people to submit the form. In our case, I changed D5 to D80.
Now we’re ready to add data to our new table. As new entries are submitted through the form, they will automatically be added to Sheet 2 without overwriting our “Project” information.
Step 4: Set up AutoCrat on Sheet 2
When you set up AutoCrat now, you will need to change the Merge Tab to Sheet 2 so all your tags will map from the second sheet to your template that you created in step 2 above. I have also set a trigger to run on Form Submission. This means that each time the form is submitted, AutoCrat will send the completed template to the person indicated with the new data.
Now you are all set to send much more personalized information to people that complete your Form. This has started me thinking about many different ways to use this in my classroom. But, I would love to hear how you might use this in YOUR classroom.
Here’s the video tutorial I made to go along with this post.