I recently started working as a part-time mall security guard while I pursue full-time post-graduate studies in cybersecurity. Given that I have past engineering and programming experience, my supervisor asked if I could build an automated key control log in Google Workspace. I was only too happy to oblige (I get to build things! Yay!).

My employer is an outlier among office software users in that they use Google Workspace, and so this would be my first exposure to building tools for it. Previously, I had built many scripts, tools, and even applications in Microsoft Office using the Visual Basic for Applications (VBA) programming language. That's how I got my start in the computing field.

So I was pleasantly surprised when I discovered how incredibility powerful office automation in Google Workspace is.

First before proceeding, as the good developers we are we must understand the requirements, so here they are:

1. The key control log is to be used to record and track to whom building keys have been assigned to. The security operations center (SOC) is responsible for maintaining control of all the keys in the mall. Keys are typically assigned to contractors, tenants, or internal employees for various tasks.
2. Each key will have a QR code attached that the application is to scan in or out as keys are returned and assigned respectively.
3. The application is to be built in Google Workspace with a Sheets spreadsheet serving as the data store.
4. The user interface is to be accessed from an iPad mounted inside the SOC public access window (you know those windows with a hole at the bottom where we pass the keys through, a guardhouse booth)
5. When assigning keys to contractors, the name, company and phone number of the person the key is assigned to is to be recorded. For inhouse employees only the name.
6. The application is to send an automatic email to the supervisor once a key has been assigned and not returned for more than 12 hours.
7. All guards are to have read access to the key control log sheet, dispatchers and the supervisor are to have read/write access.

Originally my supervisor wanted to buy a separate barcode scanner and attach it to the iPad. However I've built applications that use off-the-shelf barcode scanners before and they typically work in an identical fashion to a keyboard. You have to click into the target text box, pickup the scanner, point and scan the barcode, at which point the barcode value is populated into the text box.

Since we were already using a mounted iPad with a rear facing camera, I envisioned a smoother experience where the user starts the assign key or return key workflow, is automatically prompted to scan their security badge, then the key's QR code, and the values are automatically submitted without having to click into text boxes or pressing enter. I suggested these changes to my supervisor and he agreed once I demonstrated the smoother workflow and better physical ergonomics.

If we were using Microsoft Office, the show would have stopped at the words "QR code" and "iPad". Building a modern UI, integrating device cameras, finding a QR code scanning library or writing one, and deploying cross-platform to a tablet device are not easily done in Office/VBA, if at all. Given the requirements, I would have told my supervisor that we were better off building a standard web application or desktop application, which would have required getting the IT department involved for either provisioning servers or installing build tools. Basically a no-go given our budget and the scope of duties of our department.

These kind of in-department office automations are extremely high value but typically fail to launch once the requirements exceed the traditional "enter data into spreadsheet, automate the processing or piping around of said data in VBA, and write the results out somewhere".

Imagine my surprise when I began to research Google Workspace and Apps Script (Google's office automation language, identical to JavaScript) and found that you can build what's called a Google Web App. With Google Web Apps you can build browser frontends utilizing all the contemporary luxuries of ES6 JavaScript, the browser API, the libraries, everything. You can also write server-side ES6 JavaScript code, which is what interacts with Google Sheets, gmail, etc. The server-side code even allows scheduling what are called triggers, functions that run periodically. No more "please leave this spreadsheet open at all times"!

The result is, as shown below, a simple browser based QR code scanner that once you begin the "Assign Key" or "Return Key" workflows via button press guides you through scanning a security badge and then the key itself. Prompts guide the user at every step and also display any errors that may occur, such as scanning a key that does not exist in key inventory. To scan a QR code one simply places the code behind the mounted iPad's rear camera and lines up the image on the screen.

Overall, this took about 16 hours of total effort to build, including some back and forth iterations between myself and my supervisor to take this from prototype to the finished application.

By relying on an existing JavaScript library for the QR scanning, on HTML for input data validation, simple CSS for the styling, simple function calls to make requests to server-side code using the google.script.run client API and on Google Web App triggers for the automatic emailing feature, I was able to quickly build an effective automated key control log for a security team that manages over 100 sets of keys without allocating budget and without having to involve the IT department, all within their existing office software package. The team now has full visibility on the state of each key at all times.

This result completely changed what I considered possible for corporate office automation using conventional office software packages. A developer with knowledge of basic HTML, CSS, JavaScript and the Google Apps Script environment could build quick and effective office automation tools, all without involving IT departments or outside software vendors.

Google Workspace extends the efficient frontier of office automation. It raises the threshold of what is possible in office automation to the point where it can do nearly anything a conventional web application can do, just at a smaller scale of data.