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.

What Can Teachers Automate?

This post is shorter than my prior writing here, and won't be recorded as an audio podcast. I'll be putting out more of these shorter posts, but the longer posts with podcasts will continue as well!

In my personal search for ways to improve my productivity and focus as I integrate new and varied elements into my work life, such as mentoring and consulting with the Modern Classrooms Project, editing their podcast, and writing for this blog, I've been exploring ways to use technology to automate tasks that don't require my full, creative attention.

Teaching is a repetitive process - we plan, we teach, we assess, and we repeat this cycle ad nauseam, taking stock and making changes as necessary. In all three stages, there are clearly elements of the work that require us to think critically and creatively, which cannot be automated. Indeed, one big concern I have in the realm of Ed Tech is that a lot of teacher tools automate the wrong aspects of teaching, effectively removing the teacher from situations in which they really do belong, and nothing is more important to me than that my student facing materials (and my student facing self) be authentic and reasonable. The kinds of automation I'm after are much less flashy and much smaller in scope.

For instance, the process of planning follows nearly the same sequence for each unit (decide topic, research, design summatives, write objectives leading to summatives, create presentations for lessons on objectives, etc.), and therefore we can use templates to pre-populate both the materials we develop and the outline of the plans themselves. We complete the same tasks each time and can therefore reuse the same to-do's or checklists.

Our computers can remember and automatically generate these templates and checklists, saving us valuable time that we can spend actually researching and creating. My two most-used automations at the moment are an action I created using the Mac app Alfred to automatically copy a Google Slides template for a new lesson (it just automates the keyboard shortcuts to make a copy so I don't have to click through the menus), and a macro in the jack-of-all-trades app Keyboard Maestro (also Mac only) that sets up all the required materials for a new MCP Mentee (the document I'll use to give feedback, a dedicated folder for all their materials, and a checklist of every task I'll need to complete for that particular mentee) - all I have to do is type a short string ("set me up an MCP mentee" - it rhymes, and it's saved me hours). In both cases, I skip the overhead of setting anything up and just get right to work. An honorable mention goes to Text Expander, the phenomenal snippet expander I use constantly to save and deploy common written feedback (if I ever type the same sentence twice, it goes into Text Expander).

Automation has other benefits besides saving time, too. Filling in a template entails a much lesser cognitive burden than generating something creative from scratch. Staying consistent by automatically generating our materials (our LMS layout, the format of our documents, etc.) can help our students more easily navigate the logistical aspects of class (which are always the same) and spend more time and focus actually learning.

I certainly don't have all the answers - after all, I'm still learning to teach, and I will be for as long as I am a teacher. There is also degree of computer nerdery here and I'm only recently finding my way into that world, but the investment of time and research into these tools has paid huge dividends for me, so I'll probably write up and post new techniques here as I come across them, and maybe even develop some tutorials. For now, I'm keeping a keen eye on the work I do every day to see which parts of it I can potentially hand off to my computer to do for me.