Friday, October 28, 2011

Using Google Scripts to Move Data Like a Bulldozer - Forms to Documents

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.

Our initial purpose of attempting this was not that exciting of a project, however it proved a concept that we believe will make gathering and sharing data in Google Docs a more powerful, dynamic, and interactive process. What we initially set out to do was take a spreadsheet with information about school payments and create letters that would be mailed out to individual families. This has developed into this repeatable process:

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.

Posted by:

Dr. Henry Thiele - CTO Maine Township High School District 207
Mark Ordonez - Manager of Data Services Maine Township High School District 207
Janice Cacciatore - Technology Specialist Maine Township High School District 207

Wednesday, October 05, 2011

Gmail Message Translation

Here is a tech tip I just sent out to my staff:

I wanted to share a neat feature that Gmail Labs offers us for communicating with our students, parents, and community. You have the ability to turn on "Message Translation" which will use Google Translate to convert a message from another language to English (or to another language of your choice). Here are the simple steps to turn this on in Gmail (link).
Of course, it isn't as good as having a live native speaker translate for you, but it will allow you to receive messages in 58 different languages (and growing) and it translated. I showed this to a group of parents last night and they were empowered with the idea that they could reach out, in a language where they are comfortable writing, to someone at school. Even understanding that it might not be perfect, but getting a general idea across was possible, brought tears to one mother's eyes.
Please take a moment to set it up and ask a student or co-worker to help you try it out. 

Is anyone else using this? Successes? Drawbacks?