Build Your No (Low)-Code App with Google Workspace and Google Apps Script
Nowadays, we started to automate every aspect of our life. Building business automation solutions could be hard if we plan to build it from scratch. As an alternative, we can use solutions offered by many SaaS companies that fit our needs, but sometimes we cannot find any existing solution that really fit our business requirement (might be too broad or too specific). Beside that, the pricing plan offered by the SaaS platorm may not fit your current budgeting. In such condition, Google Workspace could be a solution!
Google Workspace — formerly GSuite, is an integrated solution of applications that you can use to deliver business solution. The overall solution seems to be ordinary until you utilize the Apps Script
The other interesting thing about this thing is the trigger. With trigger, you can execute your script every event executed. These events are events that emitted from the Google Workspace apps, for example: execute the Apps Script function every Google Form submit. Google Apps Script also supports time-based trigger, so you can arrange scheduled events to trigger the execution of your function.
Case Study: COVID-19 Monitoring Analytics
In this case, I tried to utilize Google Workspace to build COVID-19 tracing dashboard and analytics using Google Forms, Google Sheet, Google Data Studio, and Google Apps Script. This dashboard is a giveback initiative from Kreasi Nusantara for the society around the office. The requirements for this tracing dashboard are:
- Residents can see the current condition of Covid spread within the residence with filters by tower, level, and condition
- Residents can also see the previous days condition and see the case update day by day using graphs
- Internal users (Gugus Tugas) can do further analytics with the data, for example: minimum, maximum, and average number of days the residents hospitalized or self-quarantined until they recovered from Covid
To support these requirements, this is the final architecture used to build the platform
In this scenario, resident submitted the Covid case report by themself using Google Forms. The forms result stored inside Google Sheets. In the response’s Google Sheet, we added some additional columns, such as Verification Status (internal user will set the record as Verified if the case is verified after verification via phone), and current status (self-quarantine, hospitalized, recovered — this status will be updated day by day by internal users).
Connecting the Dots
Doing the Google Forms to Google Sheets connection is not so hard. To do it, open the form, enter the Responses tab, open the menu and press Select response destination. You can choose or create a Google Sheet document to store the response.
After setting up a sheet as Google Forms response storage, you can add a new Google Data Studio data source to your new sheet. Then you can insert any components you want (scorecards, graphs, etc) with your data source.
Note: this post won’t cover the details about Google Data Studio functionalities. You can DYOR to explore more about the Google Data Studio features.
Another note: If you have multiple sheets inside your Google Sheet and will use more than one sheet inside your Data Studio, create one for each sheet!
The tricky part is to support the second and third requirement, because the Form Response sheet will only store the current state of records. To support these requirements, we need to store the record history day by day so we can retrieve the data state on any given date. To do this we won’t use any fancy ETL tools, but we will use the Google Apps Script instead. This script will be executed every midnight WIB to store the current data to different sheet.
To create a script for your Sheet, go to Tools > Script Editor. Then you will enter the Google Apps Script windows. Here is the example script I used to extract the current data sheet and load it to the history sheet.
After creating the script, we need to set the scheduled trigger to execute the script every midnight WIB. To do this, enter the Triggers menu (clock icon) on Apps Script. Then create a new time-driven trigger and set the desired schedule. Here is an example for a trigger that fires event every midnight WIB
After creating the trigger, you can wait and check if the trigger executes well with the scenario.
This example solution could be improved for more complex use case (ex: using BigQuery for history data analytics). Nevertheless, this solution should be enough for a simple use case.
In certain condition, Google Workspace with Apps Script could be a good choice to build a simple (or maybe more complex) automation solution. For example: Event Registration Google Form with payment (use onSubmit GForm trigger to generate invoice and use serverless function, ex: Firebase Cloud Function to create a callback API from payment providers) and many other use cases.
Hope this article helps you. Thank you for reading!