One of the things I’ve been paying more attention to this year for my AP students has been their multiple choice performance on our in-class drills, quizzes, tests, and final exams. I’m collecting this data as a way to trace each student’s improvement over the year and see if I can spot any patterns in their performance and offer suggestions for improvement.
To track this, I set up a Google Spreadsheet for each class period (I have two this year) and then within each spreadsheet created a separate sheet for each assessments, noting the type of assessment, date, and results. The upside of this arrangement is that I can include a lot of data within each spreadsheet, tracking not only multiple choice result, but also performance on the written sections, and any extra credit earned. I could also have extra columns to re-weight the sections of an assignment if need be. The other perk was that this arrangement interfaced nicely with Socrative and Google Forms assessments, allowing me to easily port that data into a new sheet for each new assessment.
As I’ve gotten deeper into the year, however, I realize that tracing change over time for individual students is a bit of a nuisance. I chatted with a student the other day about her performance on essay writing, and while I was able to hunt down that info by clicking from sheet to sheet, it was a cumbersome process that didn’t give me a quick sense of her performance over the course of the year.
I also realized that I’ve entered data is a somewhat haphazard way, not always putting students names in the same order or in the same cells, and sometime entering students as “FirstName LastName” and other times entering students as “LastName, FirstName.” All of which goes to highlight the fact that I greatly enjoy digital humanities work, but I’m stronger on the “humanities” side of all this stuff than I am at the “digital” in some cases. Sigh.
Here’s a sense of what things look like (with both delightfully alliterative randomized names and randomized data):
So, I’m now realizing that it would be incredibly helpful to have a single sheet that traced each student’s performance on all these assessments over the course of the year. It would be super ideal is that sheet could then get auto-populated with new data each time I created a new sheet and entered new data about student performance.
I’m sure there’s a way to set this up, but I don’t know if there’s a pre-programmed script that will let me port data associated with each student into a new spreadsheet, or if there are a set of formulae that I need to enter in a new sheet to have this material carry over.
Potentially (though there are likely much better ways to set this up; I’m very open to suggestions) that type of page might look like this:
In my semi-useful natural language searching around the web I found a few Google forum discussions that seemed useful, but in attempted to enter the formulae, I didn’t have much success. Are any of these actually useful?
- Auto populate from master spreadsheet to other spreadsheets
- Spreadsheet – Automatically copy and paste from one sheet to another based on content
- Moving Row of Data To New Sheet Based on Choice within that Row?
So, if anyone has suggestions about how I might go about accomplishing this, or if there is a great plug-in script to help me port this data from a bunch of separate sheets to one single sheet, I’d greatly appreciate the insight!