Next week I’ll be presenting at Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) doing a session on ‘Hacking stuff together with Google Spreadsheets’ – other online spreadsheet tools are available. As part of this session I’ll be rolling out some new examples. Here’s one I’ve quickly thrown together to demonstrate UNIQUE
and FILTER
spreadsheet formula. It’s yet another example of me visiting the topic of electronic voting systems (clickers). The last time I did this it was gEVS – An idea for a Google Form/Visualization mashup for electronic voting, which used a single Google Form as a voting entry page and rendering the data using the Google Chart API on a separate webpage. This time round everything is done in the spreadsheet so it makes it easier to use/reuse. Below is a short video of the template in action followed by a quick explanation of how it works.
Here’s the:
*** Quick Clicker Voting System Template ***
The instructions on the ‘Readme’ tell you how to setup. If you are using this on a Google Apps domain (Google Apps for Education), then it’s possible to also record the respondents username.
All the magic is happening in the FILTERED sheet. In column A cell 1 (which is hidden) there is the formula =UNIQUE(SORT(LOWER(ALL!C2:C)))
. This returns a list of unique questions ids from the ALL sheet. If you now highlight cell D2 of the FILTERED sheet and select Data > Validation you can see these values are used to create a select list.
The last bit of magic is in cells D4:D8
, which uses the COUNTIFS function to filter and count the response values based on the selected question ID:
=COUNTIFS(ALL!C:C,$D$2,ALL!D:D,C4)
Simple, yes?
[FREE] Receive SMS messages in a Google Spreadsheet for a textwall, voting and more (UK only) JISC CETIS MASHe
[…] to generate a response graph. You might also want to look at how I’ve used a Google Form for Hacking stuff together with Google Spreadsheets: A simple electronic voting system, at the very basic level you’ve got a free SMS textwall to play with. If you do come up with any […]
[Failed] Embedding live data from Google Spreadsheet with Apps Script ContentService in PowerPoint using oomfo JISC CETIS MASHe
[…] my simple electronic voting system hack as a data source I was able to reuse this example on Stackoverflow on how to create a rss feed […]