Pages

April 12, 2015

D2L: When a Spreadsheet... Isn't Really a Spreadsheet

Every time I read about D2L and Brightspace and their claims to be all about data, I have to ask: if they are really all about data, why can't I get the data I need out of the system and manage that data in the ways that are useful to me? Like, uh, in a spreadsheet...?

I've ratcheted back my use of D2L to the barest minimum: I use the D2L Gradebook to record the students' progress on their semester-long projects while the students do "Declarations" (true-false quizzes) that record the points for all their other completed work  (because, yes, grading is a FERPA/security thing). Unfortunately, while the Gradebook gives me the total points needed to calculate the grades (and I am glad the students can check their total points at any time), the Gradebook does not give me the data intelligence I need to keep track of student progress and to communicate with the students effectively about their progress.

In this post, I'll explain how I try to "hack" the D2L Gradebook using a text field. This works pretty well for me, and it's taken me a few years to figure it out, step by step, so I'm sharing it here in the hopes that, mutatis mutandis, it might be useful to others who want to be proactive about their students' progress in a course.

And why do I need to hack the thing? Because the D2L Gradebook looks like a spreadsheet, but it's not. Here are the things that drive me crazy:

1) The limited options for sorting and filtering are totally inadequate. This problem is COLOSSAL; all other problems pale in comparison.
2) You cannot adjust the display width of the columns.
3) You cannot format the columns (font size, ad hoc color coding, etc.)
4) You cannot easily create ad hoc columns for labeling and sorting.
5) Text-field columns have a very narrow length limit (I run up against that limit with my system below).
6) You cannot change the order in which the columns display without also changing them for the students.
7) Rules and formulas? Nope.
8) Filter views? Uh, no.
9) Freeze rows? Of course not.
I'll stop there because if I let myself go into the double-digits, I'll be here all afternoon imagining the things I'd like to do with the D2L Gradebook but which I cannot do. Basically, think about everything you might want to do in a normal browser-based spreadsheet like, say, Google Spreadsheets. Safe bet: you cannot do ANY of those things in the D2L Gradebook.

Specifically, on a daily basis, here are the kinds of things I would like to be able to sort for so that I could communicate important reminders and send encouraging emails to specific subsets of students:

1) If multiple assignments are due on a particular day (there are often two items due), I need to send emails to students who have not done BOTH assignments.

2) If there is a choice of assignments on a given day (often there is a choice of two mutually exclusive assignments), I need to send emails to students who have not done one OR the other.

3) Students who are outliers (either way ahead or way behind) need to be filtered out of automatic email reminders: students who are ahead don't need reminders or encouragement emails (they are simply choosing not to do assignments, which is fine), and students who are far behind need more personalized email, not a form email generated by an algorithm.

4) Based on their own personal progress through the semester Project, students get a variety of different email reminders: depending on whether they are doing a Storybook or a Portfolio, whether or not they have free pass(es) remaining (for a Storybook project), whether or not they are doing a revision or a new story, etc.

By using the flag (yep, one flag only) and sorting on a single field, I can then eyeball the other column(s) I need to inspect manually, I can take care of situations 1 and 2 and 3 above. That is really frustrating though; I sometimes make mistakes (because I have to eyeball up to four columns to determine who gets an email on a given day), and it is ridiculous for me to PRETEND to be a computer, applying an automatic algorithm manually, when any self-respecting spreadsheet would make that completely easy to do with filters, sorting, etc.

The hack I want to describe is the system I use for tracking student Project progress using a single text field. I display this text field to the students too; it is a very useful way for them to get a reminder of their current progress. The text field has five component items which I am very careful to enter precisely so that I can then do search-highlights based using Control-F in my browser (about which, see below):

STACK (or not): if a student has turned in an assignment that is waiting for my attention, I add the word STACK (all caps to get my attention) to the start of the field
type of project: Storybook or Portfolio
weeks behind: asterisks indicate number of weeks off schedule
week due: the next week that students have an item due (i.e. a way to track weeks ahead as well as weeks behind)
type of assignment due: revise or add new story

I do not have a way to keep track of Storybook free passes in this field because that is an actual numeric field that goes into their total points, but the free passes are indirectly reflected in the asterisks above (for students doing Storybooks, up to two asterisks get recorded as free pass points). There is other information I would enter in the text field (a one- or two-word story title as a reminder), but the text field length limits won't allow it.

To get a sense of what that looks like, here's a screenshot of what my class looks like right now; as you can see, I convert this to the final grade field when a student is all done! This is a screenshot of the Progress field, along with the two assignment fields that I'm eyeballing to track to see if people have turned in (due now). Here are the ways in which I can use this text field:


Sort: I can sort the field in order to see what assignments I have in the stack to grade (it's a good way to double-check to make sure I don't lose any, and it also assures the student I have their assignment), and I can also quickly sort the Storybooks from the Projects (they are two different types of semester-long projects, based on each student's choice, and require different kinds of assignments, emails from me, etc.). The alphabetical sort works because there are only four strings that can start the field (Storybook, STACK Storybook, Portfolio, STACK Portfolio).

Search on Week/Type: The way Chrome is set up, Control-F search provides great highlighting on the screen AND a count. So, I can check to see who needs to turn in a revision assignment for Week 12 by searching on "Week 12: rev" and send an email accordingly. So too for the different email I sent to people who are supposed to add a new story: "Week 12: add" does the trick. Because I am searching on the week number and type of assignment due, I don't accidentally send emails to the people who are working ahead. I have to be careful to eyeball who is in the stack or not, so after I do the search and select the people who need emails, I then search again (the browser search doesn't clear the checkbox selections thank goodness), highlight STACK to make sure I don't accidentally send to people who have already turned their assignment in.

Search on STACK: I can also search on STACK to get a quick count of number of assignments in the stack for me to grade.

Search on Asterisks: Anyone with three or more asterisks (either project) is potentially in trouble, so I can search on them periodically and send them an encouraging email of some kind. My goal is to make sure that students focus on what they CAN do now to make progress, not fretting about the weeks of work they might have missed. It's always possible to pick up and just keep going with these projects; they are super-flexible that way, but some students get into a kind of self-punishing mode if they are behind, so I need to make sure they know what to do to pick back up and keep going.

Now, it is totally dopey that I am using browser search highlights to do the work for me that the spreadsheet SHOULD be able to do. But at least it works! Communicating with students in a timely and accurate way about all their work in the class, especially their semester-long projects, is incredibly important to me.

What a shame that D2L does almost nothing to help me do that. Every time I use a Google Spreadsheet, I think about what how nice it would be IF the D2L Gradebook were a real spreadsheet. But it's not.

And one of the reasons I use Google Spreadsheets every day is that there are lots of other data items I track re: my students and their projects which I don't even try to do in the Gradebook... so I do that in a totally separate Google Spreadsheet which I maintain manually.

Sigh.