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?
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…