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

Google Workspace applications

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

By using Apps Script, you can make integration script to any platform that have the SDK integrated to Apps Script. To be more technical, you can also use Apps Script to connect to any database system supported by JDBC or to make API calls to your own internal platform. The Apps Script syntax is similar with Javascript, so it wouldn’t be hard to implement the Apps Script if you have prior experience with Javascript. You can read the full official documentation here

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
The final Datastudio Dashboard

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.

Apps Script used to extract and load the current data to history data 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.

Conclusion

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.

Lastly, I hope that this case study could be an example for everyone that want to build any simple automation solution. Google Apps Script can also bring an exposure for anyone that started to learn programming (esp. Javascript) to build a useful product and as an alternative for cloud based VBA Macro solution.

Hope this article helps you. Thank you for reading!

palugada engineer