
Google Apps for Education, or #GAFE as they are known on Twitter, include very powerful tools to help educators help themselves get faster at algorithmic tasks. This frees us up to spend more time focusing on heuristic tasks, the ones that require adaptive, responsive decision making in the face of ever changing conditions. At least that’s my take on why we would leverage technology at the substitution end of the SAMR model even though it has been extensively frowned upon.
Perhaps you have asked every student in your school to complete a Google Form about their preferred choice for a specialty club or to collect all of their “How might we…” questions into one convenient alphabetically sorted location. Now you have all their data, or do you? Staring down the ‘Email Address’ column of the resulting Google Sheet, you wonder if you should apply a filter and alphanumerically sort a live Google Sheet. What if an absent student fills in the form at a later date? Will it record properly if you manipulate that sheet before every student has completed it?
You could cross reference the Google Sheet upon which the Google Form is feeding with an alphabetically sorted paper list of student names or you could even go split-screen and do the very same by comparing the window containing the Google Sheet to the window containing the student list. I’ve done this so many times that I know it’s a thing that teachers do; comparing lists of names is a common occurrence in schools. What a waste of time though!
If you’ve ever wondered how to make life easier but didn’t know who to ask, I’ve got you. Here you will find a Google Sheet featuring the feigned Google Form input from all the cats I’ve known and a second tab with the quick magic you need to know who is missing so you can target them directly.
Things you will need:
- a Google Form that automatically collects student email addresses and a linked Google Sheet
- a second Google Sheet tab with an alphabetically sorted list of student names and their email addresses
- a command, for example: =countif(‘Form Feed’!B:B,B2)
What this command means:
Count if (‘range I have indicated’,specific cell). The spreadsheet code above indicates that I have asked a specific cell (C2 on the linked Google Sheet) to look in the entirety of the B column on the first tab to find the thing present in the B2 cell on the active tab. My B2 cell says [email protected] and it turns out that Vinnie Collins has completed this Google Form once. What a good cat!
What stands out in data such as this are the starting points for conversations with students who haven’t done what was requested of them at that time. For example, both Oates cats did not complete the survey as evidenced by their zero counts. Perhaps they were absent? In addition, Princess Rand and Jon Snow have completed the survey twice. Perhaps they changed their mind about their original answer and resubmitted? Finally, Vito Collins has completed the survey three times. He might need some help with how to complete a Google Form or maybe he is just excited. This data lets you quickly know where to being having heuristic conversations with the students in your charge.
@lfarooq – you have no idea how timely this post was for me! We’re in the middle of administering a massive, school-wide form and I needed an easy way to keep track of which students hadn’t filled out the form. When I tried it, it had the bonus effect of showing me that one student had entered another student’s student number in place of her own, which would have put a wrench in the system. Thank you for this!!
Jen
I am so glad you have found this to be useful, Jennifer! For several days after I posted it I heard only crickets, and so thought it was already widely known or not of particular use to anyone. If you don’t mind my asking, with how many students are you using this Google Sheets command string? I assume you can have infinity students and still see it work properly, but I don’t know for certain. Thanks again!
@lfarooq Thanks for this great tip! Super creative and useful. Im going to pass this along to all my teachers.
I am glad you found it to be of use, Justin!