The Google Apps Mail Merge Solution You’ve Been Looking For
As we gather more data using Google Forms and Spreadsheets there are a lot of questions out there about how we can reformat and share this data in a more consumable understandable way. In the last couple of months the Maine 207 technology team of Mark Ordonez and Janice Cacciatore and Hank Thiele have tackled this issue on several projects and as a result have created a reproducible Google Script that takes the information off a spreadsheet and ultimately creates a unique document that can be shared with anyone.
To make this easier to replicate we created a sheet in Google Spreadsheets that contains all of the variables you would need to run this process with the data in any Google spreadsheet or gathered through a Google Form. When the variables are used in combination with a Google Script they can replace fields on a Google Document that is used as a template, just as one might do with a desktop office suite. However, in this case the product is multiple letters all placed in a Google Docs Collection that can be shared individually or as a group.
We have already seen several other uses for this such as one example where we helped Dan Rezac take a teacher walk-through form and create individual feedback letters for each teacher observation. We are generating letters to parents and to teachers using data gathered through forms or on shared spreadsheets and disseminating information in a much more readable format than a typical spreadsheet view as is illustrated below:
Typical Spreadsheet view of Data:
Reformatted Merged Document View
For those of you interested in repeating this type of evaluation process here are some instructions, screenshots, and links of an example that will help you recreate it for your own purposes. The easiest way to get started is to make a copy of this Google Docs collection and follow the instructions.
Mark Ordonez – Manager of Data Services Maine Township High School District 207
Janice Cacciatore – Technology Specialist Maine Township High School District 207
3:46 pm
I can understand that I would want to do this if I had no other means to make a mail merge, but with other means to merge data into email, this is no easier than those already established. Please show me something much easier.
11:53 am
From @98d1af67fdd38d59e3dae7fccf3e1869:disqus :
This isn’t merging data into email – it is merging it into unique documents. I have yet to see another tool that takes someone from a Google Form all the way to a merged document.I would love to see something easier – but I have yet to see it anywhere.This is as turn key as we can design at this point.Hank
4:54 pm
Jeff -
This isn’t merging data into email – it is merging it into unique documents. I have yet to see another tool that takes someone from a Google Form all the way to a merged document.
I would love to see something easier – but I have yet to see it anywhere.
This is as turn key as we can design at this point.
Hank
10:50 pm
Thanks so much for this! I have been tinkering with this and now have a great fidelity check rubric for my interventions and an awesome letter with merged data and two stars and instructional suggestions.
11:40 am
Up until this point, the workaround I have been using to do mail merges was simply downloading the spreadsheet as an Excel file and then running the mail merge through a Word document, using the spreadsheet as my data source. It worked really well, but having this built into a Google document will save probably save me a few steps in the long run. I am anxious to try this out w/ some other Google forms we’ve used at my school. Unfortunately, our county has not moved to Google Apps for Education yet, so there will be a few more workarounds I’ll need to come up with. : )