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:

Animated GIF of Mesh in action.

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:

Excel doesn't provide a way to get unique values using a formula.

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.

Animated GIF of Google Sheets' UNIQUE function breaking if you block it.

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:

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:

How Mesh works

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

Animated GIF of using Mesh to get a list of unique items.

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:

Screenshot of what objects look like in Mesh.

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:

alt text

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:

Future work

Contact

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