Using Google Sheets To Create Web Applications
How I've used Google Sheets has evolved over time.
First, I started using it as a replacement to Excel, with the added benefit of sharing the document with colleagues, simultaneous editing and version control. Then, I wrote basic macros (scripts) that would work with values on the sheet itself. Finally, I started using triggers that would automate the scripts in the cloud and interact with other web services, hence creating the most basic web apps.
As a beginner coder, I realized the true potential of using Google Sheets as a backend when I was able to pull some invoice data to the sheet for my Finance team, and trigger emails to clients automatically (Note that this feature exists in almost every book-keeping app, but we weren't using one at the start and coding this was fun!).
Some advantages of using Sheets as a platform:
- Easy setup and free to use
- Clean UI
- Google Apps Script
- Triggers and automation
- Connect to external apps
- Serves as a mini-database
1. Easy setup and free to use
Your cloud setup is ready to go in minutes, and you do not need to install any software environment.
Also, upto a limit, there are no costs involved, and its a great way to start creating small web applications.
2. Clean UI
The interface is clean, and is very easy for beginners to start working with. You will not be dazzled by confusing syntax and overcrowded code.
The Script Editor is where you write your code. You can access it from Tools > Script Editor in the menu bar.
3. Google Apps Script
Apps Script is a scripting platform developed by Google in 2009 for app development in the Google product range.
Personally, I love the detailed documentation they maintain covering all the functions with examples. It makes coding in Apps Script a breeze.
4. Triggers and automation
Once you've made a web app, you can easily setup triggers in the cloud that will run your app while you're asleep
This has massive advantages, and is relatively difficult to achieve otherwise without having your device switched on (for free)
Triggers can be scheduled based on Time (hours, days, weeks), or everytime your Sheet is opened.
5. Connect to external apps
You can connect across a range of apps to automate your daily tasks.
Companies like Zapier and IFTTT are pioneers in this already, however there are cases where they can't solve a very specific problem you have.
For example, imagine an app that will fetch invoice data to your Sheet, check every morning which invoices are pending, send a payment reminder email to the client, alert your Finance team on Slack, and create a card in your company Trello board for tracking the task (I know this sounds far-fetched, but you get the idea).
6. Serves as a mini-database
Let's not forget that at it's core, Sheets is a powerful spreadsheet software.
You can pull data from external sources easily, and analyse it using the various functions available.
Here is a project I developed that fetches and analyzes football data.
In the next few articles, I plan to write about some of the simple (but useful) apps that I've worked on.