SpreadsheetsReviewed Aug 12, 2019
Spreadsheets are the most widely used "programming" system in the world. While early spreadsheet pioneers, such as VisiCalc and Lotus 1-2-3, no longer exist, many of their early innovations still do in Microsoft Excel, Google Sheets, and Airtable. We review many of the powerful programming-like features of spreadsheets, and point out a few interesting spreadsheet-inspired experiments.
- 👍 Fluid, intuitive, easy to learn
- 👍 Grid layout is a simple data structure
- 👍 Live data updates: no control flow, no running
- 👍👎 Shows data and hides formulas
- 👎 Limited abstraction and re-usability
Spreadsheets offer a variety of ways to reference cells from afar, such as by column and row, via labels, and via lookups. Cell references even work between sheets, and are always kept in sync and reactive to changes. In the following video, I demonstrate a variety of less-well-known referencing techniques, including labels and array formulas. At the end of the video, I show how errors are localized to cells (allowing non-erroneous cells to still work) and propagate those errors to any cells that reference erroneous cells downstream.
Live data intermediates
Where traditional coding tools show the formulas and hide the data, spreadsheets show the data and hide the formulas. This makes it much easier for people to follow the flow of a program. The following photo demonstrates a string-vectorizing algorithm (borrowed from here) where each step of the algorithm is visualized with real data. (For clarity, the formulas for each step are pasted above it.) Note the use of a SQL-like query expression at the bottom-left.
Many spreadsheets, including Microsoft Excel shown below, have a "trace precedents" feature that visualizes how a given cell is related to other cells.
Forms/3 is a visual programming environment built to explore the boundaries of the spreadsheet paradigm.
In Forms/3, graphics can be made interactive to mouse events and their values can be referenced and via other cells, such as in the temperature converter application below:
There is a specific interface in Forms/3 for "unit tests" that turn red when failing. It also tracks the percentage of test coverage for spreadsheet cells; below it shows 37% coverage.
Forms/3 offers scaffolding for creating recursive programs. When it detects a loop in a formula, it infers your intent to create a recursive structure. For example, below the Fibonacci sequence is constructed in this way:
A blast from the past, Lotus Improv was Lotus's attempt to reimagine the spreadsheet to be much more structured and powerful. Despite failing in the marketplace, it had many features that are still not found in any modern spreadsheet.
Apparatus is a spreadsheet-like graphics programming environment for creating interactive diagrams. Shapes can be added and edited directly via a WYSIWYG editor, and their properties can be computed as reactive formulas, a la a spreadsheet. It is an example demonstration of how spreadsheet-style reactivity can be applied in a visual domain. One of its killer features it its intuitive recursion: simply drag the current element from the left elements palette inside itself.
Flowsheets is a compelling exploration of extending the spreadsheet interface to handle scripting tasks. It allows for importing Python libraries, shows both code and data, references data via labels instead of locations, has better abstractions for dealing with collections of data, and renders HTML.
Pane is a live functional programming environment that is optimized to show data and all intermediate values. It plays on the common node-and-wire environment, but switches the paradigm around with the nodes as data and the wires as functions. Like in spreadsheets, it is reactive, priorities showing concrete data, and makes higher-level abstractions intuitive through mouse-based operations.