Google Apps Script

Introduction

Today, I learned that an IT-savvy colleague of mine used Google Sheets as a lightweight backend and built a simple lookup web application on top of it using Google Apps Script.

  • Discovering this potential was an eye-opener.
  • It got me thinking about how we could leverage Apps Script internally - such as building an in-house drug formulary directory for our hospital - rather than relying on third-party no-code spreadsheet-to-app platforms like Open As App, AppSheet or Wistify.
  • Intrigued, I decided to explore Apps Script further.

Google Apps Script

At a high level, Apps Script is effectively a cloud-native equivalent to Visual Basic for Applications (VBA).

  • Written in standard JavaScript, it is designed to automate workflows and route data across Google Workspace (Sheets, Gmail, Docs, Drive, Calendar) and external REST APIs.
  • Crucially, it can host self-contained mini-web apps directly through its HtmlService - served via an endpoint such as https://script.google.com/macros/s/.../exec - without needing dedicated external hosting (though native custom domain mapping is not supported out of the box).



The Experiment

Aiming to prototype a simple drug formulary lookup web app, I prompted an AI assistant to generate the necessary scaffolding from an existing Google Sheet:

  • Open the script editor directly from the spreadsheet via Extensions > Apps Script.
  • Implement the backend data-fetching logic inside Code.gs.
  • Build the frontend search interface inside Index.html.
  • Deploy the project as a Web App.

After a few minor adjustments, the web app prototype was fully functional - allowing users to seamlessly retrieve and query data directly from Google Sheets.

  • It made me realize how quickly one can bootstrap functional, zero-infrastructure internal utilities without setting up dedicated web hosting.



The Constraints

Digging deeper, however, revealed notable platform constraints.

While Apps Script is an excellent solution for personal utilities or low-concurrency internal tools, it is not built to scale as a public-facing web service:

  • Concurrency Limits: Apps Script caps concurrent executions at approximately 30 simultaneous requests per user context. Exceeding this threshold triggers rate-limit errors (e.g., Service invoked too many times).
  • Execution Timeouts: A single script execution is strictly terminated if it exceeds 6 minutes (360 seconds).
  • Daily Platform Quotas: Daily runtime caps (90 minutes/day on free accounts vs. 6 hours/day on Google Workspace), strict daily email sending limits, and quotas on external UrlFetchApp calls restrict high-volume background processing.



Summary

Provided operations stay within Google’s platform quotas, Apps Script offers an incredible suite of capabilities that can be utilized entirely for free.

  • The ability to both view and edit Google Sheets directly through a web browser transforms a standard spreadsheet into a functional, interactive web application - delivering an experience that simply is not possible out of the box without paid, third-party wrappers.



External Links

Comments

Post a Comment