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

Max Brawer
6 min readNov 2, 2020

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…

--

--

Max Brawer

People Tech & Analytics leader @ Level, formerly Twitch, BuzzFeed, Google, Nielsen | Try his apps @ sheetswizard.com