Building a Custom Web App from Google Sheets

I’m working with a group of teachers to run a mock election this week. They had a basic Form set up for students to vote along with two other “pie-in-the-sky” ideas:

  1. Assign electoral votes by homeroom to simulate the Electoral College.

  2. Have a live-update view of the election teachers could show in their classrooms.

## Electoral Votes

To set up the electoral system, we created a database sheet in the workbook. We listed each teacher in the school and then assigned an electoral value. The next eight columns are used to tally votes per candidate per homeroom. I was able to do this using the `COUNTIFS` function to match the candidate **and** the homeroom teacher.

Another sheet was added to do the electoral tallies. This formula is a little more complex. I needed to grab the data from each row, find the max, and then check which candidate matches the max column. My final formula was:


Broken down:

  1. If all candidates are 0, leave as 0 (it was assigning electoral votes to all three before this)

  2. If 1 is false, check if the `MAX` value for that row is the same as the candidate header.

  3. If 2 is true, assign the electoral votes. If false, leave as zero.

It works well and calculates instantly, which is really nice. The columns are then summed by candidate.

## Popular vote

The electoral vote took care of the presidential candidates, but I still needed popular vote totals for all of the candidates. That was a simple `SUM` function pulled from Sheet 2 which aggregated votes by homeroom. I didn’t need to worry about cross-checking against candidates because I was just interested in the totals.

Now that the tallying is done, I turned my attention to the live updates. My first testing was with a published chart from the spreadsheet. You can publish them without exposing the underlying data, which makes sharing results much simpler. But, it turns out, that leaving the chart options as “automatically publish updates” means anywhere from 10-30 minutes later. Not great for a live-update.

After some digging, I settled on the fact that a custom chart built with the Google Visualization API would be the best way to immediately show poll data.

## Coding the Chart

This took much longer than I expected because the Google Visualization API expects you to send a `QUERY` call to get data from a live spreadsheet to draw the chart. I’ve not used the Query language much, and in the end, my data wasn’t structured well enough in the spreadsheet to easily get what I wanted.

Using Google Apps Script, I was able to grab the spreadsheet data and create my own array to then give to the API for the chart.

## Displaying Popular Votes

The teachers also wanted to display the popular vote for all of the elections (President, Governor, Senate, House) next to the electoral data. This wasn’t too bad and I was able to set up a simple HTML table (using CSS `display: table`) to load the live data using AJAX.

Instead of having teachers reload the page every few minutes, the last two lines on the client-side code will re-run each script with fresh data, updating the site on it’s own.

## Why use a web app?

This is definitely more complicated than it could have been. But, I went with a web app approach for two main reasons:

  1. Simplicity for the _user_

  2. Data security

They’re collecting some demographic information that they don’t want published, which is one of the problems with just sharing the sheet out with the entire school staff. They also wanted to make sure the vote wasn’t damaged by someone accidentally changing some of the cell calculations.

The nice thing about running a web app is that I can still require a school district login to see the page. Additionally, even if the page were public, it hides the original spreadsheet key because you don’t need it in the client-side scripts. So that spreadsheet data is relatively safe from prying eyes.

Watch for a follow up post with the election results and a wrap up on how using the app went.

    • *The featured image is a [flickr photo]( “Mock elections 2010”) shared by [Two2Travel]( under a [Creative Commons ( BY-NC-SA ) license](

Defining the Problem

Consider the train problem:

A train is barreling down the tracks. At a fork, you have the option of flipping the train to the left branch or the right branch. On the left side is someone you know, tied to the rails. On the right, there are five strangers.

Which way do you throw the switch?

A philosophy professor posed the question to his two-year old.

The great thing about this is that he was still solving a problem…it just wasn’t the expected problem.

I think a lot of frustration can come up in a classroom comes from ambiguity. I may have a problem and anticipated solution, but if that isn’t clear to students, conflict can pop up when they take a different path. Even more, defining specific solutions to problems worth discussion is a poor teaching method.

Next time you’re given an unexpected solution, consider the validity of a different point of view.

Maybe it IS About the Video…

In 2011, I wrote a post in which I strongly declared that Flipped Learning [is not about the videos](

But what if it is?


flickr photo shared by cogdog Creative Commons Attribution BY license.

The whole idea of Flipped Learning is moving from directing instruction in front of the community to allow active learning practices to prompt learning experiences in students. In order to do that, time has to be reclaimed somewhere…something during the class has to be sacrificed.

For many, the way to do that is through video.

So, it _is_ about the video, but only in the sense that it allows the teacher to explore other methods of interacting with students within the context of the class. The transition from instruction in the room to instruction as a recording is a small leap; accessible to most teachers where they are _now_ and one that can lead to deeper learning opportunities.

Obviously, the video is only an enabler of change, not the cause. There is still an active decision made by the teacher to change their classroom practice for students. It is not the only way to effect change in practice, but it is (or seems to be) quite popular.

Beyond offloading instruction, the video doesn’t do much. Without taking steps to improve the learning experiences students have in the classroom, then no, nothing has changed. Use that time to push yourself and students into new experiences and allow those videos to help support the change.

Another Free App is Closing

News came down this week that [Zaption](, a popular web app that layers quiz question and other interactive features on top of videos, is closing it’s doors on September 30th.

Unlike some of the [other]( [recent]( closures, [Zaption is being purchased by a finance firm]( (Workday) located in San Francisco. The terms of the sale aren’t public, but my guess is that Workday wants to expand it’s learning service to include interactive features for their client base. It adds value to something they’ve already built out and saves the hassle of licensing fees.

Here’s the kicker: Zaption, as far as I can tell, has no information in their [Terms of Service]( or [Privacy Policy]( about what happens to user data. Nothing came up when I searched the page for **sale, sold, acquire, acquired, bankrupt, bankruptcy purchase, purchased, asset, assets, or close.**

User data is valuable in most cases. The immediate concern is that user data will be transferred in this sale with no prior disclosure. I’ve reached out to Zaption via Twitter asking for clarification on the plan for that data. Given the buyer and Zaptions offering, I’m hoping data transfer isn’t in the deal and that the purchase is for the codebase.

There are two important things to remember:

  1. “Free” always has a cost associated with it.

  2. Read the Privacy Policy and Terms for the apps you use.

What happens to data in these situations is important. If you’re looking for a good place to start, [Bill Fitzgerald]( worked on a project with Common Sense Media to create a [Privacy Policy Browser]( covering the conditions for some popular educational websites and apps. Check it out and see what you’ve agreed to. Being informed is important.

The Line Between High Expectations and Impossible Expectations

I absolutely hate teaching bonding. The abstract nature of atoms, the minutiae of nomenclature, and the details of writing formulas bog students down and I struggle to meet their needs. So, we do POGILs, simulations, speed dating, labs, and drills. Lots of time is spent trying to correct patterns of work to meet the learning objectives.

This year, I just can’t seem to meet those goals. I feel like I’m at my wits end and I’m just ready to move into something else for the plain sake of mixing it up a little bit.

I know it’s not my fault entirely. I know I can rely on the multiple short assessments – formative and summative – that I’ve given over the last three weeks (almost) checking on progress. I know I’ve recovered and retaught major points of confusion.

I also know I can’t force students to do something they’re patently disinterested in doing.

Standards based grading is a double-edged sword in that regard. They’ve done plenty of work, but there is still a major lack of understanding of the main ideas, so I cannot report, through the grading system, that they’ve learned the objective. Ethically, I’m not willing to cross that line. At the same time, I question the level of expectation I’ve set up as students work to demonstrate what understanding they have. Am I expecting too much?

The line between high expectations and impossible expectations is thin. Trying to walk it is an exercise in rationalization and stubbornness.

    • *_The photo in this post is a [Public Domain]( photo of a [Penrose Triangle]( It looks like it should exist, but in reality, is an impossible shape._

I Need to Remember to Post Answer Keys

Everything I do in class is geared toward building understanding. I want students to be able to both complete the task and understand _why_ they’re completing it. Learning is more than a collection of disconnected skills. Especially in chemistry, the more you see the interconnectedness, the easier it is to learn.

Today, I gave a quiz that went less then spectacularly. We’ve finished a chapter on periodic table organization and have moved into ionization and simple bonding. We’ve talked about valence electrons, how to find them, whether or not an atom is gaining or losing those electrons, and finally, how to find the ion charge. We also practiced it in a lab yesterday.

Today, we fell flat pretty quickly.

As we looked back over the last few days of work, I told them that before every quiz, I can usually accurately pick out who will do well and who won’t based on work leading up. It seemed to surprise them that yes, I do know when work is simply copied and handed in. To illustrate that it isn’t uncommon, I closed my eyes and asked everyone to silently raise their hand if they’d ever done that. (Of course, most hands were slapped back down on tables or knees…not so silently…)

I gave my students the GIGO example – if I don’t have accurate information, I cannot teach effectively. When I walk by and offer help, it isn’t random. But they have to choose to accept the offer.

I found, years ago, that posting answer keys around the room while they’re working significantly reduces the desire to just copy it down and turn it in. First off, because I usually won’t take the work up. Secondly, they know there’s no pressure on being perfect. I can still assess their learning (and they can easily _self assess_, which is more important anyways) and adjust as we go.

Lately, I haven’t posted keys. It could be laziness, forgetfulness, or a combination of any number of things.

The fact of the matter is I’m still fighting a resistant culture. We’re nearly there in some classes – a culture of learning as process, not as destination – but in others, we probably won’t make it this year.

I still have 17 school days until summer break, so we’ll keep the gas pedal down and see what happens.

Minimum Viable Input

In software, there’s a lot of discussion about the “minimum viable product” when you’re designing something: what is the bare basic you can deliver to customers that will solve a problem? It helps define the focus and set development priorities for the first weeks.

I think there’s a similar process in learning. I have to have a minimum viable input from students in order to teach effectively. I try to design lessons that are low barrier for entry, ones that allow students to engage with an idea without being bogged down in the details. It takes some amount of effort and the bar is _just_ above what’s comfortable.

I haven’t been receiving that minimum input from students lately. And as a result, we’re struggling. Hard.

There seems to be the expectation that if learning doesn’t happen in class, I’ll drop everything and teach it later. Some are learning the hard way that it doesn’t work that way. When we’re together, I want to engage _together_. I can be flexible, but it’s a two way street.

I’ve had some discussions with students. The nice thing about standards based grading is that it’s less of a numbers game (mathematically impossible to pass, etc.) It’s harder – it’s a learning game.

_Learning must happen._

Learning an entire semester’s worth of material in four weeks is hardly realistic, but I’ll support the ones who give it a try.

I hope there’s a larger takeaway, whatever the outcome.

Assessing the Assessed

State testing. There are no words.

Week two of six is halfway done. Meanwhile, I keep teaching and I keep assessing. Most of it is formative, informing the learning process and my instructional cycle. Looping back to rehash ideas that are still elusive; being transparent about why we’re doing what.

Yes, there is still a need for demonstration of learning.

_Yes, you have to take this test today. Show me what you know._

There comes a point where I feel like I’m just chasing shadows to justify my own work.

I know that’s not true, but it sure feels like it some days.

Question Mark Answers

We played Jeopardy today in class (with some major upsets coming during final Jeopardy…quite exciting) to review atoms and the periodic table organization. It’s a super-short chapter that sets up a foundation for bonding, naming, and chemical reactions. It’s also nice because it’s mostly review (or should be) of 8th grade physical science.

There’s still new stuff I throw at them, like valence electron location and Bohr model electron shells.

During Jeopardy, I try to give thought-provoking questions to get students to see patterns and interconnections between ideas. A favorite line of mine is, “There are only so many ways I can ask these questions.” This is painfully obvious as we go through more games and skill building activities.

I started to see a lot of:



2 shells and 3 valence?

on the whiteboards.

I love the question mark. Sure, I’d love it if they were confident and could just spout some of this stuff (because it makes the rest of the year much, much easier to digest). But, I’m more happy that we’re finally approaching a point of being willing to venture logical responses rather than, “I don’t know.”

It was also super fun to see the excitement when a ventured answer was, believe it or not, correct.

And it only took until April 25th. Maybe next year, we can hit this on April 24th. I’ll call that a win.

Why Are Questions So Scary?

I remind my students constantly that I can’t help if they don’t do one of two things: 1) Ask me a question when they’re confused, or 2) get something wrong on an assignment. I need to see their thinking, and those two methods – along with my questioning – are the best indicators of strengths and weaknesses.

Lately, it’s spiraled into something much more confounding. Students are stuck, but they refuse to ask _any_thing. Even when I give a freebie, anything-goes offer. When I come by to prompt, they admit to being stuck, but then don’t do the small task to get _un_stuck. So there they remain. And nothing gets done.

And so we spiral.

It’s hyperbole, but I think they feel like kids in the car in Jurassic Park when I come by.

The fear of being wrong – searching for the right answer every time – is something I’ve tried to combat all year long, but it’s still got hold of most of my students. So many are afraid to be wrong, that they’re paralyzed and can’t take the help, even when offered outright. It’s a safety thing…I don’t know if they don’t feel safe because of my teaching style or because of peers…but it’s something that needs to be worked out somehow.

How do you help students get over the initial hump of just asking a question? Even if it’s something as simple as, “What’s the charge of a proton?” A small door like that would allow me to build their confidence and point to small, accomplishable tasks which will help them progress on their own.