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.

(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 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. )

iferror function 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.

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.

(A screenshot of a voting system i built)

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.

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.

(Testing the final outcome)