Merging Data in Google Docs

Some rights reserved by mag3737

One of the power features that I’ve always loved in Microsoft Office is the ability to merge data from Excel into individual Word documents. I used to use this extensively with my Excel Gradebooks when I was a classroom teacher: writing comments for each student in a column, recording the various criteria scores, and then merging all of that data on to a template that included the original assessment criteria and task-specific clarifiers. It took a bit of time to set up, but it was powerful assessment data.

As I’ve made the move to Google Docs, I’ve often lamented the missing ability to merge data. I put it down to ‘one of those things’ that I would have to live without in order to use the collaborative power of GDocs.

Yesterday, in our weekly tech team meeting, we were discussing how we handle the reporting of loss or damages. This is an area that has caused much angst amongst all parties involved: students, parents, teachers, admin, technicians. What could we do to make the system more user-friendly and more efficient?

The idea of students filling out a Google Form came up. While it is a great way to collect information from the students, it’s not a great way to view all that data. Cells and cells of text in a spreadsheet is not my idea of a good time. What we needed was a way of getting all of that information on to a document – an individualized report for each incident.

Back to the idea of merging data…

I started searching for a solution. I asked Twitter. I asked the IT teachers if they thought they could write the java script necessary. Finally, I decided to check the script gallery on Google Spreadsheets. Jackpot.

autoCrat by Andrew Stillman does exactly what we need.  Amazingly, it was updated just a few days before I searched for it!

Here’s what I’m hoping to set up in the next week or so:

  1. Students will come into the Tech Office and use a dedicated computer to fill out a Google Form to report loss or damage. That information automatically gets sent to a Spreadsheet.
  2. autoCrat will automatically generate a Doc for each entry as it is created, pulling the information from the Spreadsheet. This document will be emailed to both the student and his/her parent.
  3. The Doc will be created in a folder that is shared by all technicians, using a naming convention based on the name of the person filling out the form and the date it was created.
  4. As the issues are resolved, technicians will write in how and when the problem was resolved and the Doc will be transferred to another shared folder for archiving. The ‘how’ and ‘when’ will also be copied back to the spreadsheet.
  5. Because all of that information is in the spreadsheet, I’m hoping to be able to easily collect stats about the types of problems that have been fixed, average turnaround time, etc.

While this is more of a systems implementation, I can see autoCrat being used in the classroom. I’m not sure how yet, but I think it can be used to collect and share formative assessment, for students, parents and teachers.

How do you think you might be able to use autoCrat?