Google Sheets Student Response System

What Now?

It's important to note that this works just fine with form responses – you don't have to use the text input I wrote about earlier. You can always go back and add it later. Either way you collect information, we can begin analyzing some of the data.

The whole idea is to have a platform-independent polling system. So, this is built with flexibility and bare-bones functionality in mind. It isn't complex in the way it pulls in student responses, nor is it meant to be. However, there are some tools in place to help you actively identify problem areas.

Setup

An idea I'm borrowing from Andy Schwen is to utilize a central class list. Create a new spreadsheet and fill in student names. I sort mine by last name, first initial. You need the spreadsheet key from this document.

https://docs.google.com/spreadsheets/d/RANDOM-LETTERS-AND-NUMBERS/edit#gid=0

Copy the key and paste it into the cell which asks for it.

Your class list will automatically populate for you. That's all you need to do on this page.

Responses

The Responses tab is meant to be an overview for you. So, if you have a way to extend your desktop across the projector rather than duplicating, you'll have the back-door information.

This page has a response vs. confidence graph that updates as students submit their answers. If you have your class list set (see above), you'll also be able to sort by class period using the dropdown menu. This will import those students and assign an ID number. Students respond with that ID number rather than their name so you can display comments (read on).

If they make a comment, you can also read it and see who asked that particular question or submitted a particularly salty comment.

Finally, you are given an overall confidence (average of all answers) as well as a chart mapping the student's confidence score for each item they choose. It's also broken down by student to aid you in feedback and conversation setting. You could also use this for grouping students on the fly to discuss the question.

Results

This is the page you want to show live to students, and it's meant to go through three steps. At first, the ID boxes at the top are all red. As responses come in, the box will turn green. There isn't any way to prevent someone from submitting the incorrect ID number or a duplicate, so duplicates will turn yellow. You can then follow up individually as needed.

When you click on the “Get Responses” button, a pie graph of all the submitted answers is displayed. This is meant to spur conversation and show the beginnings of a pattern.

Clicking on “Set Answer” allows you to submit the multiple choice answer and change the graph. Rather than the pie chart, it shows the confidence score and the number of each answer submitted. Again, this can lead to conversations with your class about how confidence can correlate with making the right selection.

Clicking “Reset Form” clears all the responses from the form, resets the charts, and clears the Responses tab of data so you can ask another question.

This isn't meant to collect persistent data – it's meant for flexibility and high-level insight. Hopefully, it's helpful to you. Please leave comments on this post (also linked from the template) with bugs, functionality improvement requests, or other feedback. This is definitely a pet project, but I'll try my best to maintain it as it gets used.