Imagine you were given the task to design a new language ー outlining all of the nouns and verbs, establishing the rules of punctuation and grammar, and trying to predict what the common sentence structures would be or the corner cases that might cause confusion. It’s a bold task.
For the past 30+ years, spreadsheets have had the power, ubiquity, and flexibility to build solutions that people needed to run their teams — but it regularly bumped up to limitations. How many hours have been spent decoding VLOOKUP or counting the number of curly braces when closing a nested IF formula? If we’re honest, it’s not much of a surprise given spreadsheets were designed off the use case of an accountant’s
Our vision and intent for Coda’s formula language was always clear:
To build a new programming language that blends the best of spreadsheet-like formulas with modern programming languages.
And with that, we enumerated a few key principles to guide our design choices:
(1) Familiar to spreadsheet users first, programmers second
Priority one was to make our formula language feel familiar to makers coming from other tools like a spreadsheet, but design it based on how we communicate with our docs and data ー not how accountants might have communicated with their financial ledgers or how engineers communicate with computers.
To that end, we anchored Coda to behave very much like a spreadsheet. Formulas live in the same surface as the data, which is quite different from how most programming languages work — where code and logic is written in a text editor and then compiled and executed against data stored somewhere else (e.g. in a database).
As you change the products that get ordered, or the quantity, everything updates live. You didn’t have to create a database and a separate web server. And if you want to add a status for every order to mark when it’s fulfilled or being worked on, it’s as easy as adding a column in a spreadsheet.
Further, many of the common formulas from spreadsheets work similarly in Coda:
Today’s date: =Today() →
If statements: IF(Orders.Count() > 6, "So many orders!", "Not enough orders!") →
We knew that real applications needed real tables ー tables where rows and columns are distinctly different. We needed the relational database table ー one that has the ability to reliably join against other tables at scale through primary keys, foreign keys, and indices.
For example, looking at most spreadsheets, you can easily spot several ranges that should actually be tables:
By anchoring Coda to tables rather than location-based grids, the formula language defaults to referencing the names of the objects in your docs (e.g. the Orders table or the Tasks table) rather than a set of location-based references (e.g. A1:D15 or F1:I5). This has a major benefit in that when you’re writing formulas, you’re doing so in a way that sounds more human: “Take the Orders table, filter it to Helmets, and sum the Quantity column.”
The dot operator
Another subtle, but very impactful choice we made was to introduce
For example, to sum up the cost of Jacket orders in the example above, you’d write a spreadsheet formula like:
=SUMIF(B2:B15, "Jacket", D2:D15)
You wouldn’t know it from just looking at the formula, but B2:B15 represents the column of product values and D2:D15 represents the cost. And you have to read it a bit out of order: “Sum something if the range of cells that has products is equal to “Jacket”. Oh, that something was the cost range.”
With chaining, you can read it left-to-right:
“Take the orders table, filter it to where the product is a jacket, take the cost, and sum it.”
(3) But more powerful than spreadsheet formulas
With that base, we wanted to extend how you could use formulas. For starters, we wanted to get them out of the grid, allowing you to add formulas anywhere, like in your document text, to define conditional formats, or (my personal favorite) to
Above all else, just like the rest of Coda, the formula language needed to be a composable building block that could be used interchangeably across scenarios and throughout the rest of the product. Tangibly that means the same formula would need to work the same way if it was used in the column of a table, as part of a filter, or in a conditional format.
But more deeply, a composable formula language would mean that there could be multiple ways of accomplishing the same task. For example, take the scenario of creating an order number for the example table above. There are a few approaches you could take ー all equally “correct” depending on your scenario:
(A) Using RowID()
One approach would be to use the RowId() formula combined w/ a format like this:
The benefit of this is that you’re guaranteed that every row gets a unique, immutable ID. But, that also means (by definition), that you can’t change it. So if you add 4 rows accidentally and delete them, your Order Ids will keep incrementing.
There are no rows in this table
(B) Incrementing the max value in the default value
Alternatively, you could set your order ID by incrementing on the max ID value in the current table. For example, here I’ve created an Alt # column which has a formulaic default value:
thisTable.[Alt #].Max() + 1
Which as you add rows, increments the value by 1. This way, if you delete rows or manually want to override the order #, you can.
Alt Order ID
There are no rows in this table
The true test of a building block is whether you can creatively compose different parts in different ways to solve a problem.
This all said, just like any language, we know Coda’s formula language is not perfect and that it will evolve over time. Makers like yourself will use it, introduce new patterns, uncover gaps, and help us stretch the boundaries for how we use it to get work done.