There's a time every year when I'm in the middle of a lecture and I receive a call from a friend asking 'Hey Machn, there's this event on Saturday, can you create an online voting system for us to use?".
Most often this happens a day or two before their occasion. So yeah, when I was first asked to make a voting system, I figured out a way to do in a simplest possible manner. And I've been continuing it ever since.
It's been an amazing journey creating a program in a very short period of time to accomplish this task. Most often the application I've used is just a Google Form linked with a spreadsheet having a couple of mathematical functions.
Why try to reinvent the wheel when you have a car?
These are the specifications I mostly receive for voting systems
- There are codes people can use to vote
- Codes can be used only once
- Votes done using wrong codes should be discarded
- Realtime scoreboard
- People can sms to a specific number to vote
In this article I'll share the simple process of accomplishing the above specs using Google Forms + Sheets.
First you need to create a google form and open the spreadsheet linked to it.
1) Generate a list of codes
For this purpose you can easily write a small program or use an online generator like this one. You need to decide the length of the codes, the acceptable characters to be used. When this is done it's just a matter of copying them to another sheet of same linked google sheet.
2) Formula to count the number of occurrences of a particular code
This formula counts the number of occurrences iterating through the list of responses.
3) Determining the first occurrence of the code
We need to locate the first use of a specific code. To do so, use the following formula. This step is needed because we need do disregard if a code is used multiple times. (people do try to vote the same person using the same code -_- So vote will be counted only once. )
function is used to keep the cells blank when a code is not used. (otherwise it'll display N/A)
Returns the requested information about the specified cell.
Returns the content of a cell, specified by row and column offset.
4) Getting the voted team of a particular response
Up to now, we have successfully identified the first occurrence of a code. This step is to identify to whom the person has voted using the code.
INDIRECT: Returns a cell reference specified by a string.
5) Creating a Graph & Statistics of the Votes Realtime
Now it's just a matter of graphically representing the percentage of responses.
And to have an insight as to what percentage invalid votes have been received I used these formulae.
As you can see, Google Sheets/MSFT EXCEL is a powerful tool you can use to create handy applications. There are loads of supported functions which you can use.
I have created a starter template for you to get started.
- The form can be located here https://entc.ml/vote_form2
- And view the spreadsheet https://entc.ml/vote_sheet
Play around with this and you'll figure out how it works in no time!
I hope you'll be able to create your own voting system with this provided tips. If you have any question let me know below.