Designing the Coda formula language

Explore

Designing the Coda formula language

How we combined the pliability of spreadsheet formulas with the functional power of modern programming languages.

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.

If a language ー like English or Spanish ー defines the way we interact and communicate with other people, then programming languages represent the rules for how we interact with computers or machines. And at Coda, we’ve long argued that the spreadsheet formula ー not the likes of JavaScript or Python ー is the most popular programming language in the world.

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

physical ledger⁠

ー not all the ways we use them today.

The first principles of a modern language

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).

And while the gap has closed a little bit as

reactive frameworks⁠

have become more popular for application development (like

React JS⁠

), few have matched the immediacy or tactility of a spreadsheet where the logic and data live in the same plane. So just like a spreadsheet, in Coda data and formulas live together.

For example, imagine a table of orders (the data) and a few formulas that calculate different numbers related to it:

Order #

Product

Quantity

Unit cost

Total Cost

Ord-1

Assembly

$30

Ord-2

Helmet

$75

Ord-3

Goggles

$45

$90

Ord-35

Ord-28

Ord-29

Ord-30

There are no rows in this table

⁠

The number of orders: =Orders.Count() →

7

⁠

The total cost of all orders: =Orders.Total Cost.Sum() →

$195

⁠

And the total cost of

@Assembly⁠

orders: =Orders.Filter(Product=Assembly).Total Cost.Sum() →

$30

⁠

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() →

8/20/2024

⁠

If statements: IF(Orders.Count() > 6, "So many orders!", "Not enough orders!") →

So many orders!

⁠

Summation: =SUM(1,2,3,4) →

10

⁠

And

many others⁠

(2) Make it read like a sentence

The second principle around our formula language has roots in our choice of

Tables, not Spreadsheets⁠

ー specifically to build Coda around tables:

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

chaining⁠

into the formula language, modeled after the popular pattern we see in JavaScript (among other languages). Chaining allows you to combine a series of functions together in a linear progression like a sentence, rather than count parentheses inside out.

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:

=Orders.Filter(Product ='Jacket’).Cost.Sum()

“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

define the default values for columns⁠

in a table. Formulas aren’t just for cells.

We also started to introduce more powerful concepts common in programming languages and applications:

Things like “live” formulas that recalculate without you making a change: =Now() →

8/20/2024 12:12:32 PM

⁠

Or formulas that return rich objects like the current user: =User() →

@Anonymous User

⁠

(only if you’re logged in!)

Or functions like a

map⁠

: =List("Dog", "Cat").FormulaMap(Upper(CurrentValue)) →

DOGCAT

⁠

Pack formulas

But ultimately, there needed to be a pattern for extensibility to make sure that Coda worked well with the rest of the world around your doc, not just what was in it. That’s where

Pack⁠

formulas come in. With them, you can:

Get the current

weather⁠

: =Weather::Current(60045).Temperature →

40

⁠

degrees

Grab the sale price about a

Walmart product⁠

: =Walmart::Product([URL]) →

⁠

Easily

translate between languages⁠

: =GoogleTranslate::Translate('We live another day.', 'Chinese (Traditional)') →

我們住另一天。

⁠

Or generate an

MD5 hash⁠

: =Crypto::MD5('Foobar') →

89d5739baabbbe65be35cbe61c88e06d

⁠

That’s just a

small taste⁠

, and we’re only just getting started.

Action formulas

Lastly, we wanted to introduce a capability that makes programming languages powerful which is not possible in spreadsheets: the ability to take action.

Coupled with buttons, formulas in Coda can do things ー both in your doc and in the broader world. For example:

Delete all “Jacket” orders →

Delete Jackets

⁠

=Orders.Filter(Product=Jacket).DeleteRows()

Order #

Product

Quantity

Unit cost

Total Cost

Ord-1

Assembly

$30

Ord-2

Helmet

$75

Ord-3

Goggles

$45

$90

Ord-35

Ord-28

Ord-29

Ord-30

There are no rows in this table

⁠

Or you might send a

text message⁠

, send

an email⁠

, or create a

new calendar event⁠

(4) While keeping it composable

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:

=Format("Ord-{1}", thisRow.Rowid())

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.

Order #

Ord-1

Ord-2

Ord-3

Ord-35

Ord-28

Ord-29

Ord-30

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 #

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.

What’s next?

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.

What do you think should come next?

⁠

Let us know

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.