Diving Further into Data-Based Teaching: Elapsed Time Since A Student’s Last Submission

TL;DR: I discovered how to use my Google Sheets pacing tracker to monitor how long it's been since a student's last submission! You can check out (and even make a copy of) the fully-functioning spreadsheet here, or you can read on if you want more detail on how this works and some context as to why I wanted this feature and how I use it.

---

Lately I've been thinking about better ways to keep students accountable while also keeping my class (and my demeanor) as friendly and welcoming as possible. The more objective data I keep, the more I can make judgement-free assessments of my students. All this has me thinking about what data I keep (special thanks to Emily Johb for getting the wheels turning on this on Episode 75 of the MCP Podcast), and what sorts of data-based thresholds I'll use to trigger certain actions (like targeted interventions, scheduling in-class checkins, parent contact, requesting that a student attend tutoring, et cetera).

Recently, a new data point occurred to me: it would be useful to keep track of how much time has passed since a students' latest submission. As more and more time passes since a student's most recent submission, it is increasingly apparent that this student is becoming disengaged, and keeping tabs not only on their overall progress but also on the consistency with which they make progress has really helped me identify students who were slipping through the cracks.

Now, I don't have the bandwidth to track these times myself (with the number of students I teach, manually noting the date each time I review a submission feels like a burden), so I started poking around in Google Sheets to see if there was a formula that would do it for me.

What I wanted was a cell that would automatically show the date on which I last updated the a specific row in my tracker, so any completed mastery check that I checked off by a student's name would cause this cell to update with the current date (i.e., the date of the submission), but if a day passed without any updates to the row, the cell would not change - the cell would always show the date of the most recent submission. I could then use date math formulas to subtract that date from today's date and fill another cell with the difference - the number of days since the student's previous submission.

Unfortunately, there doesn't seem to be any built-in formula that perpetually monitors one row and update itself with the date of most recent change to that row. I did, however, find this video that explains how to use Google Scripts (from within Google Sheets) to create a Javascript applet that does.

Frankly, this is way over my head - I can't say I fully understand what's happening here (and I certainly couldn't have come up with this myself), but I tried it out on some mockup spreadsheets (which are linked below), and it does seem to work solidly (and, it's worth pointing out, I feel confident enough in my understanding of the Javascript to conclude that there's no malicious code or anything that would compromise the privacy of the spreadsheet).

Once the applet was in place, I was able to use the date math formulas. In one column, I used the =today() formula to display the current date, and in the next column over I used the =days() formula to subtract the latest submission date from today's date, which gives me the number of days since the last submission. Then I used conditional formatting to flag any students who haven't submitted a lesson in five days or more (for some teachers, that number may seem quite high, but keep in mind that I only see my students twice a week - five days is not an unreasonable lapse between submissions if I don't see a class from Wednesday until the following Monday). It works!

---

If this interests you, you can make a copy of this mockup pacing tracker and try it out (here's the link again) - having already written up the steps (which are below), I can tell you that it's rather involved, and the mockup already works, so you can just make a copy. However, if you're technologically inclined and want to try it in your own sheets-based progress tracker, here are the steps:

1. Create three new blank columns, titled something like "Latest Submission" "Today" and "Elapsed Time"

2. In the Extensions menu, select Apps Script.

Change the text on Line 1 "myFunction()" to read "onEdit(e)" (don't change anything else). Then, copy this code into the editor on line 2 (in between the curly { } brackets):

3. In the editor, note the two instances of "XX." Change the first one to the name of the specific sheet (the name in the little tab at the bottom of Sheets) that contains your three columns. Change the second "XX" to the index of the "Latest Submission" column. If it's Column A, use the number 1. If it's Column G, use the number 5. Mine was Column Y, so I used 25.

If you have multiple sub-sheets, just copy and paste the "if" statement of the code again in the editor (not the "var" statement), and update the name of the sheet in all subsequent instances. Save again.

Back in the spreadsheet, you should now see today's date appear in this column in any row that you update! The hard part is over.

4. In the first row of the "Today" column, type =today(), then corner-drag this formula to every row with a student's progress (i.e., any row in which you want to calculate elapsed time).

5. In the first row of the "Elapsed Time" column, type =days( and click the "Today" cell, then click the "Date of Most Recent Submission" cell, then close the parentheses. Think of it as a subtraction - today's date minus the most recent submission equals the difference, or elapsed time. As an example, if your "Last Submission" is in the Y column, "Today" in the Z column, and you're calculating elapsed time in the AA column, the formula for the first row should read =days(Z1, Y1), and when you hit Enter, it should display the elapsed time.

6. If you want to, you can apply conditional formatting to your "Elapsed Time" column to highlight students who haven't submitted any work in several days.

One thing to watch out for is that some of your "Date Since Last Submission" cells may be blank (if you only just implemented this, and some students haven't submitted any lessons since you activated the applet, no date will display until they do). In this case, I found that the elapsed time displayed as 44,609 days, which, as of the date on which I'm writing this, means that this student submitted their last assignment on January 1st, 1900, which must be what Google Sheets considers the beginning of time.

Also, the automated calculation looks at any and all updates you make to the row, meaning that it doesn't differentiate between an update showing that the student demonstrated mastery and an update showing that the student needs to revise. My priority, however, is to make sure kids are keeping on top of their lessons - as long as they're submitting work consistently, I can use other data (and teaching strategies) to make sure they're revising and actually learning the lessons.

Those issues aside, though, after a few days of submissions from most students, the "Elapsed Time" column should quickly show an accurate readout of the days since each of your students has submitted work. When that number gets too high, you know it's time to kick into gear and use those teacher tricks - conference with the student, reteach, contact home, etc.

I've really been liking this metric of elapsed time since a submission - it's one thing to tell a student "you only have two weeks to complete four lessons," but it carries a little more weight to say "you only have two weeks to complete four lessons, and you haven't completed any lessons for the past seven days!" I encourage you to give it a try! If you think of other data points that would be good to track, I'd love to hear about them and see if I could find a way to automate them into this tracker.

var range = e.range;
var spreadSheet = e.source;
var sheetName = spreadSheet.getActiveSheet().getName();
var row = range.getRow();

if(sheetName == 'XX')
{
var new_date = new Date();
spreadSheet.getActiveSheet().getRange(row,XX).setValue(new_date).setNumberFormat("MM/dd");
}

3. In the editor, note the two instances of "XX." Change the first one to the name of the specific sheet (the name in the little tab at the bottom of Sheets) that contains your three columns. Change the second "XX" to the index of the "Latest Submission" column. If it's Column A, use the number 1. If it's Column G, use the number 5. Mine was Column Y, so I used 25.

If you have multiple sub-sheets, just copy and paste the "if" statement of the code again in the editor (not the "var" statement), and update the name of the sheet in all subsequent instances. Save again.

Back in the spreadsheet, you should now see today's date appear in this column in any row that you update! The hard part is over.

4. In the first row of the "Today" column, type =today(), then corner-drag this formula to every row with a student's progress (i.e., any row in which you want to calculate elapsed time).

5. In the first row of the "Elapsed Time" column, type =days( and click the "Today" cell, then click the "Date of Most Recent Submission" cell, then close the parentheses. Think of it as a subtraction - today's date minus the most recent submission equals the difference, or elapsed time. As an example, if your "Last Submission" is in the Y column, "Today" in the Z column, and you're calculating elapsed time in the AA column, the formula for the first row should read =days(Z1, Y1), and when you hit Enter, it should display the elapsed time.

6. If you want to, you can apply conditional formatting to your "Elapsed Time" column to highlight students who haven't submitted any work in several days.

One thing to watch out for is that some of your "Date Since Last Submission" cells may be blank (if you only just implemented this, and some students haven't submitted any lessons since you activated the applet, no date will display until they do). In this case, I found that the elapsed time displayed as 44,609 days, which, as of the date on which I'm writing this, means that this student submitted their last assignment on January 1st, 1900, which must be what Google Sheets considers the beginning of time.

Also, the automated calculation looks at any and all updates you make to the row, meaning that it doesn't differentiate between an update showing that the student demonstrated mastery and an update showing that the student needs to revise. My priority, however, is to make sure kids are keeping on top of their lessons - as long as they're submitting work consistently, I can use other data (and teaching strategies) to make sure they're revising and actually learning the lessons.

Those issues aside, though, after a few days of submissions from most students, the "Elapsed Time" column should quickly show an accurate readout of the days since each of your students has submitted work. When that number gets too high, you know it's time to kick into gear and use those teacher tricks - conference with the student, reteach, contact home, etc.

I've really been liking this metric of elapsed time since a submission - it's one thing to tell a student "you only have two weeks to complete four lessons," but it carries a little more weight to say "you only have two weeks to complete four lessons, and you haven't completed any lessons for the past seven days!" I encourage you to give it a try! If you think of other data points that would be good to track, I'd love to hear about them and see if I could find a way to automate them into this tracker.