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?

7 thoughts on “Merging Data in Google Docs

  • May 20, 2012 at 2:19 pm
    Permalink

    @Susan Sedro Thanks Susan! That does look like an elegant solution. As more and more schools go to GApps, these scripts are going to be invaluable.

    I think autoCrat has a similar email feature but I haven’t had the chance to explore it (yet).

    I’d love to hear your thoughts on formative assessment. I have some half-baked ideas but I can’t really articulate them yet!

    Reply
  • May 30, 2012 at 10:18 pm
    Permalink

    I had seen autoCrat come up somewhere in my social media stream when I saw this post. Love the idea. I have just used autoCrat for our end-of-year sign out for departing faculty. It automatically emailed them a PDF generated from a GoogleDoc & Spreadsheet data with their hardware they need to return before permanently leaving the school. They can print and have it signed off. Love the idea of sharing the collection/folder with the saved output files with others. We’ll definitely be exploring this one more. autoCrat FTW!

    Reply
  • May 30, 2012 at 10:42 pm
    Permalink

    @Jay Atwood Great idea! I think this script is going to save me so much time in the long run! Here’s another idea I just had for use at our school: progress reports. Currently teachers use a MS Word template and then forward it along to the appropriate people who then PDF it and email it to parents. What if we set up a Google Form that teachers typed all of the relevant information into and the PDF was generated automatically? It would give us the added benefit of being able to do some quick analysis regarding progress reports sent home since all of the data would be shared in one spreadsheet (in addition to individual documents).

    Reply
  • June 3, 2012 at 11:37 am
    Permalink

    Awesome! We’ve been looking for ways to automate a few similar things and this seems like a great option! Will have to look into it more to see how it can work for us, but right now I’m thinking it could be a great option for formative or self-assessment where students are reflecting on their work via a Google Form, which is feeding into a spreadsheet for the teacher (with all students information) and then out again for an individual document for the student with their self-assessment alongside teacher feedback. Not sure if this would work, but it would be awesome!

    Reply
    • June 8, 2012 at 9:23 am
      Permalink

      That’s exactly what @mjhamada just did with a bunch of teacher data, self assessment data and peer assessment data! It worked like a charm, with a few minor hiccups. The only tiny quibble that I have with it is that it isn’t a super-fast process… Autocrat was able to process 3 – 5 documents per minute.

      I’m wondering now if we could use autocrat to automate the progress report process that we use. Instead of writing something in a Word template and then .pdf-ing it, teachers could just fill out a Google form with the relevant information and comment. Autocrat will automagically place the information in the template and email a PDF copy to the administrator and the teacher for review. Not only would it automate the process, but it would also allow us to collect some data on the progress reports that we do send out since all of that information would reside in one master spreadsheet. I know it’s not an elegant student information system solution, but it is easy to set up, easy to use and free!

      Reply
  • Pingback: Speedgeeking at VTC2013

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge