When the Best Google Sheet is the One You Don’t Make

How to build a more perfect workflow for data updates and changes by coding in Apps Script within Sheets

For well over a decade, Google Workspace (and Sheets) set itself apart by allowing sharing and collaboration in the cloud. I shudder to remember (though my parents still work this way) the life of emailing a file back and forth eternally with INITIALS — FINAL FINAL (2) tacked on the end versus working together at one link.

But what happens when you have a Google Sheet you don’t want to share?

The Best Laid Plans of Mice & Men (and Sheets architects) Oft’ Go Awry

In the Education & IT & HR & Finance worlds of sensitive data, among others, this is daily life. How do I create, read, update and delete sensitive information in a safe and reliable way?

The Problem: Even when people expertly use Sheets for these tasks, everything is still difficult, time-consuming, and unreliable. For example, let’s say you are trying to collect any decision from multiple areas of a business, like “who should we promote?” The intuition in Sheets is often:

• Create a “master” sheet (or our fav business jargon, Source of Truth)
• Have a real human with human feelings spend hours splitting the data up into 5 or 10 or 50 or even 100 individual sheets, each with a subset of the data (different teams for example)
• Worry about tracking & sharing & splitting & ensuring updates to every single of these sensitive sheets
• Let the business type all over them, essentially as data capture forms
• Spend many human hours and human feelings re-compiling all those data while praying that no update or error fix needs to be propagated across all 100 sheets (spoiler: it will)

The paradox is that the more comfortable you are with Sheets, the more likely you are to confidently tackle this and subject yourself to a lot of pain. I had done this for years, and, in fact, my journey with Apps Script began when I automated just one piece of this puzzle with a script called “The Vacuum” that looped through 100 Sheets and copy pasted their info into a master every hour. It saved my wrists from injury but was still slow, not real-time, and risky. I thought I was so cool.

Flossier still, many learn to use IMPORTRANGE to constantly hookup many sheets to a master, which is effective and clever but can expose sensitive data and can leave you in tears when you hit the ceiling on its performance.

Some astute readers will also call out that this really is what a Form is for, or many of the workflow tools out there in Slack and such to capture info (hot take: Google’s use of the exclusive, computer sciencey term “web form” for Forms instead of the word “Survey” probably single-handedly halves their adoption and use). But there are at least two shortcomings that bring our work to a halt:

• 1: The user has to start with a blank page instead of any existing base data. Imagine a response said “promote Max from 4 to 5”, but in reality their existing level was 3, leading to us chase down and validate every submission with a string of emails.
• 2: Lack of validated options. All I want in life is a good dropdown list to ensure people pick structured choices. I used Forms for a recent survey and “cleverly” scripted 500 selections into a dropdown to ensure that names were spelled correctly. The whole program froze.

Let’s Replace 100 Sheets with 1

Cutting to the chase, let me show you the Red Pill: If you go to Tools > Script Editor in any Sheet, you will meet with the Apps Script Editor. You can then click Publish > Deploy as Web App to create your own “website” where users can work with data privately — already we have a huge advantage over Sheets because the experience is just for them: no filter wars with other editors.

The Blue Pill

The Red Pill

I am glossing over a lot of steps to keep my Medium posts nice and readable, but to recap, here’s what the above is doing:

I styled a nice-looking web app in HTML and CSS in Apps Script. Then I asked it to load up the Google Visualization API to make a Table out of Sheets data:

In essence I am just “re-making” the sheet in the browser as a Table. Then you can sort and filter it as you please.

Another huge plus for Google Workspace — I can make row-level permissions because this app knows your email:

Apps Script can do some great security moves. The app runs as Max, so it has access to my Sheets. But it also runs privately on the server side (you can’t see it!) and knows your email. So I get the best of both worlds:

• You can’t see my master sheet, but the app can
• I don’t have to think hard about what to share with whom because I can just filter on email to return only rows you should see

This is as flexible as just typing an address. The App filters where Access contains [your email]

And from there, the sky is the limit:

After you click on a row, I hand-styled this Form. Eat your heart out Forms.

I am not the best UX designer, but above you can see how we solve problems 1 and 2 of Forms: the existing value is right there in your face, side by side with the change you propose — in some cases, a dropdown of valid selections.

When the user submits the form, we once again can program anything we’d like: email alerts, custom mail merge docs…anything. Best of all, the “source of truth” remains the one and only sheet we had to make this entire journey. All changes populate that sheet. One sheet for every manager and user. One sheet for every update. One sheet to rule them all.

I hope this quick and expository post got your brain going on the deeper uses of Apps Script. To learn more, ask questions, or consult on how to build these, reach out to inquiries@maxbrawer.com. P.S. If you do have a use case to split one sheet into 100 based on a field value, I have a script for that too, just ask! And P.P.S., if you have some better table libraries and form controls for me to use that the Google API, I am interested.

These opinions are personal and not reflective of any employer. You may also like my simpler post on how to use Apps Script to send emails from Forms here.