gradebook automation with TextExpander and Keyboard Maestro

i'm teaching my writing seminar course for the fifth time this semester. you would think that would mean that the class is running at peak efficiency. in terms of what goes on in the classroom, it is. but there's one big difference behind the scenes: the last time i taught the course (in 2011), i hadn't yet been converted to TextExpander, Hazel, or Keyboard Maestro. and now i have them all at my disposal.

the old system: a sliding rubric spreadsheet


i teach a writing seminar, so the bulk of the assignments are essays. i give letter grades on these, but don't assign points to them. however, by the time the semester is done, students have written 6 or 7 short essays. i could eyeball 6 letter grades and try to come up with a fair average for everybody, but i know it wouldn't come out equal. so for equanimity and ease of computation, i convert all the letter grades to numbers on the back end. i could just apply a fixed rubric and be done, but i like to have a little bit more control over the grade distribution — again, without inadvertently creating bias towards one student or another.

to accomplish this, i set up a two-table spreadsheet in Numbers, one with students' scores, and one with a rubric. last year i waited until the entire semester was over and then ran a bunch of manual find-and-replace commands to link each grade cell to the corresponding rubric cell. the result is the only actually useful implementation of Numbers' slider cells that i've ever found. as you can see easily from the conditional formatting that i've put on the "paper average" column, tweaking individual paper grades triggers related changes in the course grades.

the automation: enter TextExpander

this semester, i decided that i didn't want to wait until all the papers had been collected and graded to be able to see the quantitative results. to do that, i needed a fast way to create a reference to the rubric cells. there's a mousey way (select the grade cell, type =, click the rubric cell, type enter), but it's slow and error-prone. instead, i decided to write a quick TextExpander script snippet that would give me a fill-in field where i could just type in the grade and have the cell reference generated automatically. it's not the prettiest thing ever — just a manual lookup in a hash — but it's fast and effective. if i accidentally mistype a grade, it returns no output, which is better than mis-clicking a rubric cell or bringing down the wrath of Numbers' formula editor in more creative ways. now i just type "gbg" (for gradebook grade) and i get this form:

gradebook snippet.gif

in testing, i can enter a full column of grades for my 18 students in 60–90 seconds. way better than waiting until May to sort it out.

more column-filling: attendance

i also keep a separate Numbers spreadsheet for attendance. i admittedly print this spreadsheet out (blasphemy, i know!) because making tickmarks with a pencil as my students enter the room is still the quickest, least intrusive way of keeping track. at the end of each week, i transfer the information back to my spreadsheet, which counts up absences and tardies and counts them against my students' allowable absences (3 per semester; tardies count as 1/3 of an absence).

again i was looking for the fastest way to transfer this information. that involves marking absences X and tardies T and then filling in the rest with checkmarks ✔. the simplest manual solution is to fill the whole column with checkmarks and then insert the X's and T's, but all the extra symbols are pretty visually distracting. then i wind up doing a lot of copy-pasting and working with Numbers' very finicky drag-to-fill operation.

it's almost certainly overkill, but i decided to build a Keyboard Maestro macro to do the filling for me. the method is simple:

  1. grab the contents of the current cell
  2. see if it matches T or X
  3. if not, fill it with ✔
  4. move down one cell
  5. repeat until it hits the bottom (current cell = ✔)

the format of the macro is below. i'm sure there's a better way (perhaps involving Applescript?), but i was happy for the practice building some more complex macros. and i'm guessing that it could be adapted to several other data-entry scenarios where the application isn't directly scriptable.

km attendance macro.gif

it's all about repetition

teaching and learning are all about repetition. for the instructor, there are always tasks that have to be iterated across students, or across class meetings. the best we used to be able to do was build muscle memory. now we can build tools.

a couple other things that i've automated so far this year that i may post on later include exporting Keynote slides to PDFs and sharing them, and batch-processing assignment files downloaded from course management software. even if i'm not coming out time ahead, it keeps the process fresh.