Displaying Aggregate Data in Google Sheets

Our students are required to have their student ID on when they come into the building each day. This is mainly for security, but their ID also allows cafeteria, library, and other office staff to scan their ID for various clerical tasks.

When a student *doesn’t* have their ID, they’re issued a temporary wristband so faculty and staff know they’ve checked in for the day and that their record has been updated. The update process involved a paper copy made at a cart with wristbands being passed to a secretary who would then look up the student and add it to a spreadsheet before adding it to our SIS. Dang.

The head secretary (Deb) and I worked out a Google Sheet that does all of that (minus the SIS portion, but that’s coming soon).

I’ll outline the functions below, but you can [grab a copy of a blank sheet to play with](https://docs.google.com/spreadsheets/d/1MHrQVM0tPql8gx2tKfFlJwc5teNyDx8-LJ1FRulKFv0/copy) if you’d like to tinker or improve.

Raw info in…

At it’s simplest, the staff member at the check in kiosk has to enter the date and the student’s (supposed) ID number. This sheet populates the rest of the workbook.

…rich info out.

Once the date and ID have been put in, some behind-the-scenes magic happens. This isn’t ideal, but for right now, we’re using a hidden sheet with a master record of student ID’s and their associated name. I would like to make this a web-based API call, but it isn’t possible (not a technical issue…internal) right now.

  1. A staff member enters the student ID into the `raw` data sheet. The ID is cross-referenced in the `lookup` sheet and the name is displayed. This is to avoid students giving the incorrect ID.

  2. The `noID` sheet gathers unique IDs from the `raw` list so we can aggregate the number of times they’ve come in with an ID. The student name is again pulled from the `lookup` list.

  3. A simple `countif` formula counts the number of times an ID shows up in the list and aggregates. Those cells also change color using conditional formatting depending on the number of entries.

  4. Consequence data is filled in automatically depending on the number of entries a student has.

Disaggregating the Aggregate

For reporting, it’s helpful to know the actual incident dates. Going through each row, one by one, looking for a student name (even using the Find tool) can take a while, especially in large sheets. So, we turn to Google Apps Script to do it for us.

The comments in the script should give you an idea of what’s happening, but if you didn’t follow, here’s the general idea:

– Select an ID you want to look up.

– The function looks for that ID in the `raw` sheet. All matched dates are stored in an array.

– Once the loop is finished, the full array is passed to a function which adds the note to the selected cell.

– You can now see the individual dates a student had no ID by hovering over their ID.

It’s done case-by-case and only when the script is selected in the menu so your sheet isn’t trying to constantly update. The idea being that you only use the script when you need to know the dates a student didn’t have an ID when they came in.

What do you think? Leave a comment below, or better yet – make it better and write up your version with a link back here.


[flickr photo by Owen P](https://flickr.com/photos/owenphilipson/26858459235) shared under a Creative Commons (BY) license

Old Post Revisit – The New Education Economy of “Free”

This is a new post format I’m going to try out from time to time. I don’t remember where I saw the original idea, but that person suggested going back, reading old posts, and then commenting on changed perspectives as a method of reflection.

I use [Alan Levine’s](http://www.twitter.com/cogdog) [PHP script to dig up wold WordPress posts](http://stackoverflow.com/a/27569816/2278429) by simply visiting [blog.ohheybrian.com/random](http://blog.ohheybrian.com/random) and re-reading. Try it yourself for some vintage bennettscience.

_If you’d like perspective, [here is the original post](http://blog.ohheybrian.com/2014/10/the-new-education-economy-of-free)._


Paying for software continues to be a four-letter word in EDUland. Some of the reasons free services are important are legitimate (I only have so much of my own money to spend) and some aren’t (I’m a teacher, help me out). In particular,

We need to differentiate between the free sharing of ideas and the free sharing of products.

Also, there seems to have been a rise in EDU-ratti who suggest that you give away lessons and your custom materials away for free as long as you remember to buy their book on 50 ways to do that. There cannot be a double standard.

Alan (he’s shown up a lot in my posts lately) [says it much better than I do](http://cogdogblog.com/2016/10/flickr-cc0/):

I get hired to provide them as a service, despite the fact people can use my stuff for free. You make a living from providing services, consulting, ideas, not from digital stuff. If I was a pro photographer, I would hope to make money from the service, not the products. I would get more clients if they can find examples of my work in the world.

Looking back at the original article, I think that was the point I was trying to make. It’s not selling the product (the book, curriculum…whatever) that’s worth the cost, it’s the **service you provide in creating that product.**

The distinction is very small, but again, it gets to the idea that we have to value the time and effort – the service – that goes into creating any product, be it a book or software.

Consider the tools you’re using now. If it’s free, there is usually an option to send some love to the developers through a donation. If not, send them a note and ask how you can buy that person a beer.