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 between the Sheets

Update Dec 2021: I am gearing up to teach a course on People Analytics to help others break into this world and grow — please fill out this form to show initial interest!

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…