The dark side of spreadsheet scripts

Scripts are powerful—but they have hidden costs.

Eric Koleda

Developer Advocate at Coda

The dark side of spreadsheet scripts

By Eric Koleda

Share this article

Blog > Productivity · 5 min read

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.

The dark side: adaptation

I just need one more column of data.

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.

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.

The dark side: fragility

It was working yesterday, I think.

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:

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.

Coda Packs

A happy ending: Packs

Coda docs have many of the features of spreadsheets, with scripts being a notable exception. Instead, we introduced an alternative model that meets many of the same needs: Packs. A Pack is Coda's version of an extension. It adds new powers to your doc, allowing you to customize how it looks, works, and integrates. Packs can be installed from the Gallery or you can build your own. Creating a Pack is just as easy as creating a script: simply click a button or visit pack.new to launch the Pack Studio web editor. Write your code (in JavaScript or TypeScript), hit build, and we take care of the rest. No infrastructure to buy or servers to manage. Packs are different from scripts in a few key ways that address some of the issues I identified above:

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.

Having to build flexibility in upfront means that it’s easier to adapt the Pack to new use cases, which often can be accomplished by reconnecting building blocks in the doc without needing to change the code. Single-purpose Packs tend to be more streamlined, avoiding the sort of spaghetti code that can evolve in sprawling scripts. And the admin controls prevent Packs from falling into the shadows, and the CLI allows IT to adopt and harden Packs as they become more critical to the business. Visit the Gallery to browse the hundreds of Packs already published by Coda and our community, and check out the Pack SDK documentation to learn how to build your own Packs.