DIY EdTech Project: Collecting Student Data with Google Sheets

By Keith Michael Howard | Last Updated: October 2, 2021

When I started teaching in 2007, there wasn’t much data floating around my classroom, and collecting student data was a piece of cake. Nowadays, there’s more than enough data to give your calculator a toothache.

What’s the best way to deal with data that sprawls like Los Angeles across the LMS, online workbook, external apps, and other web-based resources?

 

In the past, I gritted my teeth through manually collecting all of the students’ data and putting it in a spreadsheet for them. While my heart was in the right place – there were severe problems with this approach. 

Collecting student data manually is tedious and time-consuming.

I don’t want to know how many hours I’ve spent copying and pasting numbers from one place to another.

Collecting little bits of data sprinkled around the internet like fresh-cracked pepper on a fried egg.

The exact amount of time spent is a mystery, but I’m confident that all of it was inefficient.

Not necessarily a waste, but, the added toll on my “non-teaching” hours was wearing me down and I wasn’t seeing much return on my investment. 

Students didn’t find the data I collected to be meaningful.

The other issue is that the result of these mundane hours spent doing CTRL+C and CTRL+V finger yoga was not appreciated widely by the students. I found their reaction to the data I collected was mostly indifferent.

I was hoping the data would be formative, but instead, it seemed punitive. All I could do with it was show the students what they already knew. For the students that hadn’t been active learners outside of the classroom – it was already too late. 

Manually collecting the data was creating a significant delay between when an assignment was missed or a series of tasks were incomplete.

What this means is that neither the students nor I could use the data to catch issues quickly. Essentially, the data I was sharing with them had gone stale. 

Another related issue: students struggling to organize course material links.

When I question students about incomplete outside-of-class tasks, some respond, “Sorry teacher, I couldn’t find the link.”

On the surface, being unable to find one link doesn’t seem like a large problem, but what if it starts happening over and over?

Now, we have crashed our maiden-voyage cruise ship into one of distance education’s great icebergs: autonomous learning ability.

Linn (1) describes this as learners’ ability to use high levels of judgment and deduction in organizing time, selecting learning materials, developing learning habits, and creating attainable goals.

The students who are talented in these areas do well in online courses. But what about the others?

For them, their lack of organizational skills and the sheer volume of links was a nightmare, and many of them seemed demotivated by it.

It was much harder for them to get into the flow of learning: the links added another layer of confusion onto an already challenging course. 

Three Meaty Course Problems: 

So, how can we make this easy for the teacher to check the students’ data on their asynchronous activities or homework without a delay?

How can the data collection become meaningful to students?

And how can we get all the links together in one place, so it’s easier for the students to get into the flow of learning?

The DIY EdTech Solution for Collecting Student Data.

Instead of playing catch up with the data and chasing it all over the internet – we are going to build a place where students keep track of their own task completion that has all the links required to complete said tasks. 

I wanted the students to collect it as I had a hunch that it would be more meaningful if they were the ones keeping track, but I also knew that I couldn’t expect them to become spreadsheet experts overnight, or even have an interest in it.

So it needed to be simple.

It needed to be something easy for them to do. 

This link will take you to the Collecting Student Data: Build a Study Tracker with Google Sheets doc, which will walk you through exactly how to make one and how I use it to collect meaningful data without inflating my “non-teaching” hours.

You can also make a copy of this DIY EdTech Study Tracker template here if you’d like to speed up the process.

FAQ

How much extra work is this?

It depends on your course links. If you are using an LMS that generates new links for each course shell then you’ll have to redo the links for each new class. If your links don’t change then you can set it up once and use copies from then on. On average, I would say this takes me about 20 minutes per week to set up and maintain.

How do I know my students aren’t lying about tasks they’ve completed?

This works in my classroom context with teaching young adults, but a certain level of maturity is required. This may not be suitable for less mature learners. If I do suspect that a student is misrepresenting what they’ve done, I can always go back and check the data from those tasks to see if things match up.

How do you get students to remember to tick off the boxes?

I send them the link at the beginning of every class and tell them to check off what they have done. I find this repetition to be helpful to raise the students’ awareness of the need for consistent effort in their self-study time.

With an ‘edit’ link, what happens if a student deletes everything?

Not a problem. Simply access the revision history in Google Sheets to restore the previous version.

What happens if a student doesn’t tick any boxes?

That’s a pretty good indication that there’s some sort of issue going on. If you can catch it early, it’s a good opportunity to reach out to the student and see if they’re having issues with organization or motivation. The aim here is to promote autonomy, not punish for a lack thereof.

How do you get your students to “buy-in” to using this?

Repetition and praise. After I send them the link at the beginning of class, I share my screen and complement students who are being conscientious of their studies.

 

References

(1) Linn, M. (1996). Cognition and distance learning. Journal of the American Society for Information Science, 11, 825–842.

Hi, I'm Keith, an education technology innovator and host of The New Curriculum Specialist. But I wasn’t either of those things 2 years ago. Then 2020 happened. To adapt, I learned a little bit of code and things have really taken off from there. Not that bad for a guy who failed Math 6 times.

Recommended Reading