Share
Explore
Rambles
Coda Formula Language
Thesis: Coda's focus on tables allows us to leverage many of the same strengths as a defined schema.
Est. publish date:
Potential titles:
This seems familiar: A look into Coda's formula language
Our formula for Coda's formula language
How Coda improved the formula language standard with readability and access
Excel is arguably the most popular programming language in the world. For so long, Excel had the power and flexibility to build solutions that people needed—but with limitations. When Coda was founded, we wanted a formula language that felt familiar to makers coming from other tools, and overcame the constraints of other tools when possible.
As perhaps , Coda was designed around tables. More specifically, relational database tables that provide data with clear structure and purpose.
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.
Looking at most spreadsheets, you can pick out several ranges that should actually be tables, but often the tool used doesn't give the ability to define them. Like all modern data-driven software that is built on database tables, Coda's focus on tables allows us to leverage many of the same strengths as a defined schema.
Coda's formula syntax.
By assigning names, both tables and columns are easily reference-able in the formula language, moving away from E1:E15 sheet syntax to something much more readable, like [Purchases].[Total Cost]. And with that structure came new keywords that make writing formulas easier, like thisRow and thisTable, which helps convey the relative intent of a formula. For example, while you might see Total Price calculated as B2 + C2 in a spreadsheet, you would write thisRow.Price + thisRow.Tax in Coda.
On a very basic level, our formula syntax is defined as:
Unfortunately, composing many different functions together in this way can lead to hard-to-follow-nesting, resulting in everyone's favorite game of "find the matching parenthesis." For example, the following formula is trying to format a list of owners for a project:
TextJoin(", ", TRUE, SORT(UNIQUE(G2:G100)))
This format is great for people who think about where they want to end and can work backward. Alternatively, this format can be difficult to write when you start with your data and think about how to modify it.
Dot chaining.
In reality, most formula editing experiences do not work well when you try to jump back to the front of a formula and try wrap your existing formula in another formula. So we created a new way of writing formulas: dot chaining. With dot chaining, the syntax for a formula changes to:
<primary parameter / data you are operating on>.<formulaName>(<other parameters>)
You can start by thinking about your data and writing the set of mutations you want to apply in a fashion that almost feels like writing a sentence.
Now our example formula transforms from:
TextJoin(", ", TRUE, SORT(UNIQUE(G2:G100)))
To a readable:
[Project.Owners].Unique().Sort().Join(',')
We can even edit this formula without making it feel much more complicated. Say we wanted to pull a list of just project owners that are developers, we could easily modify the formula to:
[Project.Owners].Filter(Job=Developer).Unique().Sort().Join(',')
While dot chaining is my personal preferred method of writing formulas, both approaches have merits. And since the two methods are compatible, Coda supports both.
Everything is connected.
But making the formula easier to write and understand isn't enough. We also believe you should have a complete view of your data. And in order to have that view, you need the ability to connect all of your data—both inside and outside of your doc.
Every table, column, section, control, and formula has a name, making it accessible in the formula language. Reference these objects in a single doc, across documents (with Cross-Doc), and with outside data (using Packs).
Keeping all of your data connected allows you to have a single source of truth. This prevents tedious tasks of syncing data across references, tracking down errors caused by missed updates, and helps improve your productivity.
Always up to date.
Addressability also provides us a deep understanding of how various parts of a Coda doc relate to each other. We use that understanding to recalculate formulas to reflect the current state of world. The formulas in a document and row references help build a map of how each piece is connected—all kept in the dependency graph of your document.
To see this in action, let's look at a simple formula:
thisRow.Price + thisRow.Tax
This formula tells us that the current column depends on the Price and Tax column—if the price or tax in this row changes, this specific row needs to recalculate.
As data changes, a queue of changed values forms, and recalculation begins. During that process, dependencies are checked and recalculated as well. But we're not done there. That dependency is checked for dependencies and so on until we reach a calculation that has no dependencies or loops back on a calculation we've already queued for recalculation.
While searching all of these dependencies, we are building a directed graph of the recalculations. What needs to be recalculated is important, but we also need to consider the order of recalculation. Once the graph of formulas to recalculate is complete, we start recalculating all required cells for a given formula. As soon as the entire process completes, it begins again.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.