Using Google Sheets as the Backbone of My School Election Apps
Managing school elections can be challenging, and while Google Forms is a great tool for collecting responses, I wanted more customization for my app. That’s why I chose Google Sheets.
Google Sheets is free, easy to use, and allows for real-time calculations. This means I can create a tailored solution that fits our school’s needs perfectly. By using Google Sheets, I can streamline the election process, making it more efficient and engaging for both voters and organizers.
Preparing the data structures
To effectively manage our data using Google Sheets, it’s essential to define clear and organized data structures. This involves creating separate sheets for different types of data relevant to your project. For instance, in the context of an election system, you might consider the following sheets:
Note: The Vote Count column utilizes a formula in the spreadsheet. You can copy the formula from my own setup for accurate counting.
=IF(C2 = “head”, COUNTIF(voters!B$3:B$102, A2), IF(C2 = “vice”, COUNTIF(voters!C$3:C$102, A2)))
As you can see, I’m using a token column to validate voters and maintain fairness in the process. This token helps track each voter’s participation.
Additionally, I’ve created charts to visualize the voting results effectively. Here’s how I set it up:
And here’s how it looks:
By using this approach, every piece of voter data directly impacts the vote count for each candidate. This means that as votes are recorded, the results are updated in real-time, ensuring that the charts reflect the most current data.
Creating Google Apps Script as a REST API
Google Apps Script allows you to extend the functionality of Google Sheets and create web apps, including RESTful APIs. This enables you to interact with your data programmatically. Here’s how to set it up:
- On your sheets see right top and click
Extensions > Apps Script, it will be open code editor - This is the boilerplate of the code (you can find the full code at the end of the article):
The ssId variable retrieves the current active spreadsheet. It's important to note that you may encounter an error if you access your code outside of Extensions > Apps Script.
The Election class will handle all get and update operations on the spreadsheet. Additionally, the doGet function is a built-in function in Google Apps Script that serves our application as an HTTP service.
3. Deploying apps
To deploy your script, simply click the Deploy button at the top right of the code editor. Then, select New Deployment. You can follow my settings to ensure everything is configured correctly.
Next, click Deploy. Google will prompt you to authorize the app; make sure to grant the necessary permissions.
If the deployment is successful, you’ll see a message indicating that the Web App URL has been generated. This URL serves as the endpoint you can use to retrieve data or register votes.
If you visit the URL, make sure to append ?p=candidates to the end before accessing it. This will display the candidate data in the format shown below:
Frontend
For the frontend, you can use any framework you prefer. However, I’ve created my own using Alpine.js and Bootstrap. You can access my site at:
Conclusion
Google Spreadsheets is a fantastic tool for managing data, but it has limitations when handling heavy loads due to restrictions set by Google. You can access the spreadsheet (including the Apps Script) and the frontend repository using the following URLs:
