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?

A Criterion Based Gradebook

The Problem

I’ve searched everywhere for a digital gradebook solution that can handle the rigors of criterion-based assessment. The MYP isn’t predicated on percentages (how can you give an 84% for an English essay anyway? How does it differ from an 86%?) but rather descriptors of performance. A mark of 4 out of 8 doesn’t mean the student got half of the things correct; it corresponds to a description of the work. A good description of the nuances of MYP assessment can be found here (.pdf).

Since I couldn’t find a decent ready-made solution I decided to create one. I’ve tailored it to the needs of my school: we are a tablet PC school so I thought it would be nice to use the stylus to input the marks. I’ve also created several iterations for different MYP subjects to fit with their specific criteria and grade boundaries. The Math version is linked below. It’s nothing fancy; just an Excel document with a few macros (nothing malicious, I promise!). It gets the job done, though.

The Walkthrough

Summative Grades – This is for the major summative tasks. Each task may be assessed on more than one criterion so it is important that you input date and title for each criterion used.

Formative Grades – This is where homework can be recorded. You can also assess classwork on specific criteria or record results from quizzes. I was thining of the old +, √, – method here and used a numerical equivalent.

ATL Skills – Approaches to Learning, for the un-MYP among us, are specific study skills that are explained in detail through the program. I found it useful to track these ATL skills to better provide reporting data.

The Macros

At the end of each reporting period the teacher is required to determine at what level each student is performing for each criterion. To aid this, I’ve set up a simple sort macro which groups all of the same criterion grades together in chronological order. You can then return it to its original order by using the date sort. It’s probably a good idea to put in the reporting period headers and date first before sorting by criteria so that you have a place to put your final assessment.

The Disclaimer

Like all work on this site, these gradebooks are shared under a Creative Commons 3.0 Non-Commerical Share Alike license. If you find ways of improving upon this, I would love to know!

MYP Gradebook Math;
MYP Gradebook Language A
MYP Gradebook Language B
MYP Gradebook Humanities
MYP Gradebook Science

Mr. Hamada – Master of the Obvious

You mean I can tag individual posts in my Google Reader? Why did it take me so long to see this?  So, as I’m reading dy/dan or The Number Warrior or Math Stories and am inspired by their ideas, I can 

From My Google Readerimmediately tag them with the appropriate class name so that I can find it again when I need it? 

Did anybody else know about this?  Master of the Obvious, indeed…