Draft notes, Aug 5 2019
If spreadsheets supported computations with side effects, they could be used for a broader range of applications than they are today.
Imagine that a finance firm has a spreadsheet where stock prices are fed through a series of aggregation formulas to create charts of market activity used by traders. Here are some ways that spreadsheet could be extended with actions that have side effects:
The catch is that the purely functional computation model is a key reason that spreadsheets are easy to create and understand. By introducing side effects, we risk losing this ease of creation and understandability, and we could easily end up reinventing imperative programming. The challenge is: can we maintain much of the ease of use of spreadsheets, while extending their applications?
Below I'll analyze the pros and cons of a few existing solutions to this problem, and hint at some goals for an improved solution.
In Airtable, you can make web API calls via integrations called "blocks". A couple examples:
After populating an email column with people's work emails, you can use the Clearbit API to enrich the records with more information about them from the internet.
You can mass send text messages with a templated message, one message per row in a database table:
These APIs only get called when a human manually pushes a button in Airtable. For example, in the Clearbit example, there's no way to automatically enrich new records that get added to the table later on; a human must explicitly trigger a new API call.
This is a very simple mental model because a person directly triggers actions. It's always clear exactly when the API is being called. When data in the spreadsheet is updated by an API call, it's similar to a person just manually entering new data.