Google Sheets Lesson 9 – Autocrat Add-on

In this final lesson, we will compile all the information we need into a new tab called “Merge data” and then we will send that out to all the students with the mail merge Add-on called Autocrat.Click here to view Example 9, then make a copy so you can play with it. First we review a bit by using a QUERY in E1 to pull in all of the data from our Unique tab. Next we’ll put ARRAYFORMULA statements in cells A1:D1 so that we can match certain values in SIS Lookups with the email values in column E.It’s a good practice to enter in values like this by matching against a key, rather than assuming that the order of email addresses will remain the same. This way, if someone sorts the Unique sheet or the Sorted sheet differently, the final data remains intact.

Finally, we click Add-ons, then launch Autocrat. If you haven’t used it before, you will need to add the Add-on found here.  Select New Merge Job, then Drive and pick out the template called Google Sheets Presentation – Peer Feedback Template. If for some reason that doesn’t work, make a copy of the template and then select that.

In the next screen, you will see the <<tags>> in the merge template matched up against headers on the spreadsheet. In this case, they should all match, but if not this is where you assign them.

For a name convention, use the variables to generate unique names for each file (eg. Peer Evaluations for $fullName). Click Advanced Settings and pick out a folder for the merged docs. Then click Save.

That should have created a new Merge Job – now you just need to click Run Merge.  You can Preview it as well and see what sorts of docs will be created by your code. You can also change the Autocrat settings so that parents receive copies of the emails, as well as other tweaks.

THAT’S IT!!! CONGRATS!!!

Google Sheets Lessons 1-9

Click on a lesson below for a written tutorial demonstrating the lesson.

• Lesson 1 – CONCATENATE
• Lesson 2 – VLOOKUP
• Lesson 3 – ARRAYFORMULA
• Lesson 4 – formatting ARRAYFORMULA
• Lesson 5 – QUERY
• Lesson 6 – UNIQUE
• Lesson 7 – INDEX & MATCH
• Lesson 8 – conditional INDEX & MATCH
• Lesson 9 – Autocrat Add-on

Examples / Challenges