Last updated on January 30, 2017
Over the weekend, I went hunting for a gradebook to incorporate in my class website. I was looking for something intuitive (for me) and accessible by individual students (obviously I don’t want them to see everyone’s grades). I hoped to use Google sheets, as I already run activities and assignments through Google Forms in class and I’m encouraging the use of Google Docs for students’ group writing projects this semester.
I didn’t quite find what I was looking for, but I did find lots of piecemeal instructions that allowed me to create what I needed. In particular, I found some elements’ of Anthony’s Google Sheet/Script Editor app helpful (the code is, alas, too buggy to use) and I appreciated the assistance of a few good souls at Stack Overflow who suggested fixes for the code. (Javascript is, unfortunately, a bit beyond me at the moment.)
In the end, I cobbled something together using basic functions in Google Sheets. As my dissertation project touches on the Internet’s potential to produce collaboration and encourage transparency about how ideas, objects, and knowledge are produced, I thought I’d share what I’ve made in the hopes of demystifying the process and improving it through collaboration with yet-unknown others.
The files below can be used as templates. These are particular to the needs of my class, but should provide a decent foundation that can be customized for any classroom. There is a “Master Gradebook” that compiles the grades of all students and an “Individual Grades” template that can be endlessly reproduced and adjusted depending on the number of students you have. If you’re comfortable with Google Sheets/Excel, feel free to download and open up the grade books and start adjusting them to suit your own needs.
If, however, you’re learning Sheets/Excel as you go (like me), here are some of the nifty formulas that make things work in the spreadsheet.
- SUMPRODUCT: On the first sheet of the Master Gradebook (‘Master’), I calculated weighted grades using the SUMPRODUCT function rather than a basic mathematical formula – i.e., [(E3*5+F3*15+G3*5+H3*15+I3*5+J3*15+K3*10+L3*15+M3*15)/100]. Not only is the SUMPRODUCT formula cleaner, it also updates automatically if you choose to change the weight of a grade.
- TRANSPOSE: The second sheet in the Master Gradebook is the ‘Transposed’ sheet. I wanted student’s grades to display on a single sheet in the Individual Grades sheet, so transposing information from rows to columns seemed like the way to go. The information remains the same, it just changes how you view it.
- LOOKUP: The LOOKUP function allowed me to create a grade scale and then transform the percentages into letter grades. It took a little finagling to get it to work (the trick is to sort in ascending order in the percentages column), but it’s fantastic to see the formula switch things over automatically as the grades are adjusted.
- IMPORTRANGE: This is the thing that makes it possible to create individualized grade sheets from the master sheet. The function utilizes the Spreadsheet Key and an established range from another spreadsheet to filter data into individual sheets. To share with individual students, I will send an email invite to each student from her/his Individual Grades spreadsheet.
The rest of the formulas in the spreadsheet are very simple mathematical functions (sums, division, etc.) that can be adjusted as you go.
Please Note: changing the text formatting in the ‘Transposed’ sheet on the Master Spreadsheet will not transfer to the Individual spreadsheet. You’ll have to manually format the text in that sheet. Adding or subtracting cells/rows in the ‘Transposed’ sheet does, however, does directly affect text formatting in the Individual spreadsheet. That is, it completely destroys all of your formatting below the point where you added the row. I haven’t quite figured out how to fix that yet (except to just remove all text formatting), but I’ll update this post and the document as I can.
If you have questions regarding how something works (or if something stops working), leave me a note in the comments. I’d also love to hear suggestions for improvements from people more spreadsheet savvy than myself. You can add comments to the spreadsheet itself or leave me a note below.
