How to create a fully functional voting system (If you’re in a hurry)
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.
Intro
First you need to create a google form and open the spreadsheet linked to it.
Figure 1: (Creating a spreadsheet to be linked with the google form)
1) Generate a list of codes
For this purpose you can easily write a small program or use an online generator likethis 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.
Figure 2:
Figure 3:
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.
Figure 4: (Testing the functionality)
Figure 5: (Testing the functionality)
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. )
iferrorfunction is used to keep the cells blank when a code is not used. (otherwise it'll display N/A)
cell:Returns the requested information about the specified cell.
index:Returns the content of a cell, specified by row and column offset.
Figure 6:
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.
Figure 7: (testing in action)
Figure 8: (testing in action)
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.
Figure 9: (Adding a chart and testing the final outcome)
Figure 10: (Adding a chart and testing the final outcome)
