The dark side of spreadsheet scripts
Scripts are powerful—but they have hidden costs.
Developer Advocate at Coda
Let me start by saying that I love scripts: Google Apps Script, VBA, Python, and so on. It’s a magical feeling when you can replace hours of manual work with a few lines of code or automate a process that used to require constant attention. I have written hundreds of scripts over the years for both my personal and professional projects, and spent years publicly advocating for them at Google. The modern knowledge worker faces an onslaught of data management tasks, and scripts are a powerful tool they can use to take back control of their work. However, over time I’ve come to see that scripts can also have a dark side. While the benefits are often immediate and personal, the costs arise much later and are spread out over the larger organization. Like the fabled wish-granting monkey paw, scripts imbue great power but not without a hidden cost. Below, I’ll discuss how scripts can be difficult to adapt, fragile, and pose a risk to the organization.
Blog > Productivity · 5 min read
The only constant is change, and however perfectly a script is written it will eventually need to be updated as the business requirements change. While the mechanics of updating a script usually involve no more than hitting the save button, in practice, the process can be much harder.
The dark side: adaptation
I just need one more column of data.
If the original author of the script is still around, you’ll need to get their time and attention, communicate the new requirements, and likely do some testing to make sure it’s working. In many cases, the author has moved on, and you must find someone else comfortable writing code, which can be a rare skill outside of engineering organizations. Additionally, updating a script usually comes with a degree of archeology. Scripts are notorious for being written quickly, with little attention given to documentation or code organization. Even if you originally wrote the script, figuring out how it works months or years later can be difficult and error-prone.
In addition to changing needs, the foundations upon which a script is built can also change. Spreadsheet columns are rearranged, APIs are deprecated, and operating systems are updated. All software requires constant investment to keep it working as the technology around it evolves, and scripts are no exception. Due to their nature, scripts end up being more fragile in the face of this change compared to other software projects:
The dark side: fragility
It was working yesterday, I think.
- They commonly hardcode values or fail to provide flexibility, as authors often build them as short-term solutions.
- They almost never include test cases or automated testing, which are features even professional developers struggle to prioritize.
- They tend to touch multiple different technologies, as their goal is often to integrate data or workflows across systems.
- They lack a dedicated engineering team that can proactively tackle migrations, instead of waiting for the script to break and someone to notice it.
The dark side: organizational risks
Does anyone know where that script lives?
If you took an X-ray of any large company, you’d find that a ton of critical business operations rely on scripts. However, that sort of X-ray doesn’t exist, so these scripts are often part of a company’s “Shadow IT.” While CTOs have the tools needed to evaluate the cost of switching from AWS to Azure and the cost of switching from Excel to Google Sheets, migrating all of the associated scripts is almost impossible to measure. Even without any migrations, scripts make life complicated for IT. Managing corporate data security and privacy becomes difficult when there is no central way to discover or monitor these scripts. Additionally, transferring ownership of a critical script to IT usually involves a complete rewrite on another platform, as scripting environments usually lack the ability to integrate with the company’s developer tooling.
A happy ending: Packs
- Flexibility from the start: Packs aren’t tied to a specific doc but instead provide generic “building blocks” like formulas, column formats, and buttons. They define a set of inputs and outputs, but the exact source and destination aren’t specified in the code. Instead, you add these elements to your docs and wire them together as needed.
- Single purpose: By default, Packs can only send requests to a single domain. This means that each Pack can only connect to a single app, service, or API. You can integrate across systems by chaining multiple Packs together.
- Visibility and controls: You can easily see which Packs are running in your docs, and your IT team can use the Admin API to monitor and report on Packs across the organization. Admins can set controls for how Packs can be used within the organization, approving individual Packs and allowing you to request new ones.
- Developer-friendly: Packs can start their life in the Pack Studio and later be migrated to use your company’s developer tools. The Pack SDK and CLI allow you to write and run code on your local machine, create test suites, and utilize continuous integration and version control.