[[base/Google Apps Script/Google Apps Script|Google Apps Script]] is one of the most underrated and overlooked tools for data scientists. Using just [[Google Sheets]] as a backend database, you can quickly build and deploy (for free!) a [[data-driven web app]]. As the free [[options for deploying data-driven web apps]] continue to shrink (especially after [[Heroku]] removed their free tier), Google Apps Script stands out as an incredible option. Google Apps Script can also be used to extend and interact with [[Google Workspace]] products like Sheets and Forms, automate workflows with scheduled scripts, and create add-ons that can be installed by other users in your organization or the public. You'll need to familiarize yourself with [[JavaScript]] to use Google Apps Script. While [[Python]] is the language of choice for most data scientists (and JavaScript is not the best option for processing large amounts of data), learning JavaScript can be quite helpful for building websites and web apps for all kinds of use cases. For my more robust applications, I often pair a Google Apps Script web app with a [[Google Colab]] notebook to handle the heavier data processing tasks with Python or [[R]]. The best part about Google Apps Script is that it provides a browser-based code editor, stores your scripts in Google Drive for version control, and executes all code on Google's servers. You don't need to install anything or set up a development environment. Just visit [scripts.google.com](scripts.google.com) to get started. ## how I've used Google Apps Script I began using Google Apps Script heavily while working with a client that used Google Workspace and had stringent cybersecurity protocols, precluding any options that did not run entirely on Google servers. While that certainly limited my options, I was happy to discover how useful Google Apps Script could be (especially in combination with notebooks in Google Colab). Here's a few projects I developed with Google Apps Script to showcase its capabilities and maybe inspire you to give it a try. ### Guatemala Data Hub The [[Guatemala Data Hub]] demonstrates how to embed multiple web apps in a Google Site. This is often a great option for sharing single-page web apps. (Google Sites do not play well with multi-page web apps. The sandboxing does not allow top-level navigation control which means the browser back button does not work. It sounds minor but you'll find users get really flustered.) The Guatemala Data Hub hosted the [Data Catalog](https://github.com/eriktuck/gtm-apps/tree/main/apps/data-catalog) and [Map Viewer](https://github.com/eriktuck/gtm-apps/tree/main/apps/map-viewer) web apps. ### Atlas [[Atlas]] is a fully-featured data management solution built in large part with Google services including Google Workspace and Google Apps Script. Check out the [usage](https://eriktuck.github.io/gtm-dms-alpha/pages/usage/overview/) and [design](https://eriktuck.github.io/gtm-dms-alpha/pages/design/Architecture/) sections to better understand how Google Apps Script can be used to automate workflows and set up data pipelines. ### Mule Deer Expert Elicitation app The [[Mule Deer Expert Elicitation App]] is a simple web app I built to help parameterize a state and transition model through expert elicitation. The app collects user inputs on forage value and transition probabilities and translates them to visuals of habitat quality over time, allowing experts to better tune their inputs based on their experience and expertise. Check it out to see how [[Plotly]] and Google Apps Script can work together. ### Pre-obligation Checklist The [[Pre-obligation Checklist]] highlights how just a little Google Apps Script code can help make a Google Sheet a useful template. The client estimated that this checklist would save 27,000 person hours per year, which is an incredible amount for such a simple improvement. It's simplicity is actually its greatest strength. By helping the client see the value of simplicity, I created a tool that could be maintained easily by a non-technical team. ### Activity Location Data Tracker The Activity Location Data Tracker was an ambitious attempt to build a fully-featured geospatial web app with Google Apps Script. During development its functionality was folded into an enterprise app the client was developing separately. A stripped down version was captured in the [Activity Location Data Portal (Public)](https://github.com/eriktuck/gtm-apps/tree/main/apps/activity-location-portal) but never deployed. In the end, I would not recommend building such a robust app in Google Apps Script primarily because of the risk of data loss when multiple users may be using the app at the same time. ### Fast database builder I'll be honest, this was probably not my best idea. Because the previously mentioned client did not have an approved relational database technology, I found myself building databases in Google Sheets fairly often. This is not good practice. To maintain relational integrity, I did my best to normalize the data and follow other database design best practices. However, because there is no mechanism to cascade updates in a Google Sheet (if you delete or update a record in one table the record won't be changed in related tables), I developed a Google Apps Script to automatically cascade changes based on relationships in the database. While it saved me a lot of time in setting up and maintaining database backends for various projects, it was not a product I could share with the client--the entire idea of normalized data was foreign and the behavior of cascading changes was unexpected. *Sometimes its better to know when not to do something than to figure out how to do something.* ## guides To get started with Google Apps Script, try one of my guides. - Develop a time tracking app using Google Sheets and Google Apps Script with [[Time Tracker]]. - [[Develop a web app with Google Apps Script and Bootstrap]] - [[Develop a geospatial web application using Google Apps Script and Leaflet]]