Mesh Preview

Mesh is a JavaScript code editor that feels like a spreadsheet. You can get an early version at GitHub. It looks like this:

Mesh is intended to bring the benefits of programming in a spreadsheet to 'regular programming languages', and vice versa.

The problem with Excel

Mesh was started because I couldn't find a way in Excel to output the results of a formula across an arbitrary number of cells.

An example is getting the unique values in a list. Excel doesn't have a built-in formula for this. There is an obtuse array formula, but if you change the number of unique items by adding to the original list, you need to either:

• manually add more cells to the list of unique values (if you remember!), or
• when you first create the list of unique values, cover more cells than you need. This is messy because formulas using that list must account for 'spare' cells.

There are hacky solutions involving hooking into Worksheet_Change and Worksheet_Calculate events, but I couldn't find an easy way to reliably automatically trigger them when Excel normally does a recalculation. More on this in a future page. (This is also why the 'Remove Duplicates' tool is a no-go.)

What about Google Sheets?

Google Sheets can do this via the UNIQUE function, but it will throw an error if another cell is blocking its output (see below).

Google Sheets has other issues:

1. it runs in the cloud, so often can't be used with sensitive data
2. it can't integrate with legacy Excel spreadsheets that use VBA
3. the user interface is slow and... doesn't match my Excel muscle memory.

What about Power Pivot and its languages such as DAX and M?

My (admittedly limited) impression is that Power Pivot is designed for data extraction and analysis, not data generation or automation tasks.

Why not use a regular programming language?

Languages such as JavaScript can easily handle results of arbitrary length, because they do calculations without caring about how the results will be represented.

While I don't mind writing code in a text editor, I think spreadsheets have user interface benefits that traditional programming tools lack:

• a 2D grid makes it easier to arrange elements of a program in a way that is more visually intuitive than the line-by-line format of scripting languages
• I like to be able to click on a cell, make a change, and see the new result
• I like lists and tables to display in proper rows and columns automatically.

The solution: drop location-based referencing

Traditional spreadsheets require a formula and its result to 'live' in one or more cells. Each cell has a unique ID (by default its cell location, or a user-provided name) which can be used by other formulas to refer to that formula's result.

However, for reasons described above, location-based references are inappropriate in any situation where a formula's location may not be unique.

The key is: you can keep a spreadsheet interface while relaxing the requirement that cell locations be unique.

For me, the cleanest way to do this was to drop location-based referencing entirely and just use names as IDs. This meant Mesh could be implemented relatively easily:

• it could use a pre-existing programming language (JavaScript)
• actions in the spreadsheet grid correspond to edits to the user's code
• instructions in the user's code (Mesh.attach) let the software know where to show the calculation results.

How Mesh works

Here is a video of how to get a list of unique items in Mesh:

In Mesh, you define where an object should be, and the program will figure out how to represent it.

For example, by default, an array is displayed as a column of values; Mesh will fill down as far as it needs to and will refresh the cells on each calculation.

An object is shown as a row for each key-value pair:

Because cell locations aren't unique, on occasion, two values may write to the same cell. These collisions affect the UI, but they don't affect the calculation, so you should still get the same results.

In some cases, you need to tell Mesh which display function to use, because it is not easy to infer your intention.

The good stuff

Mesh's 'secret sauce' is that it's written in JavaScript and uses JavaScript as its formula language.

Right now it runs only on Node.JS (via Electron), but it can also be ported to a cloud version and – crucially – Windows' JScript engine, so it could also work with existing Excel and VBA systems via COM, or be compiled via .NET.

In short: unless your work computer is completely locked down, I expect you will be able to run Mesh without a network connection, and without asking IT.

And because Mesh files are just JavaScript files, you can use the Mesh files as inputs to other programs. Here's an example of a webpage consuming a Mesh file:

By using JavaScript, we get a large pre-existing user base, a lower learning curve for new users, and the files can be run in more places than Excel (web browsers, Windows machines, etc).

Caveats

Downsides of the way Mesh works right now:

• unlike spreadsheets, Mesh recalculates the whole file each time, rather than being smart about when it can re-use old calculations
• results may be unexpected if the value assigned to a name changes ('mutates') over the course of running your code
• it lacks Excel's built-in functions (although it gains JavaScript's ones).

Future work

• Continue work on keyboard shortcuts
• Make the error user experience better
• Integrate with Visual Studio Code
• Add support for TypeScript and ClojureScript
• Port to native Windows (JScript)
• Add a cloud version
• Add CSS theme support (spreadsheets finally get custom syntax highlighting!).

Contact

Please contact me via Twitter at @mesh_ide if you'd like to get in touch.