Goto the GitHub Repo

NoSheet

NoSheet is a fast open-source headless calculation engine designed to replace JavaScript spreadsheet libraries.

Who is it for?

Front-end JavaScript and Back-end Node.js developers who need to implement complex calculations and who are starting to hit problems with existing ‘Excel’ spreadsheet engines (see Show me why … below).

Who is it not for?

Anyone who needs a spreadsheet UI/UX or who wants to parse, import, export or execute Excel formulas.

What’s wrong with Spreadsheets?

Absolutely nothing! They are great killer apps; who hasn’t used Excel or Google Sheets to thrash out some complicated calculations?

They are not so great, however, when they are used as software libraries for the purpose of embedding calculations within applications or web front ends.

Show me why …

Lets consider a fairly basic table like the one below. It might be from an invoice or quote; each line item has a cost and a quantity which are multiplied to give a total. The quantity and total columns are also totaled and finally the average unit cost of each line item is calculated (for no good reason other than to use an Excel function other than SUM)

This could be implemented in a spreadsheet as :

Transposing this to a JavsScript spreadsheet, it might look something like this:

const my_spreadsheet = [
    ['Apple', 10, 5, '=B2*C2'],
    ['Peach', 20, 12,'=B3*C3'],
    ['Mango', 30, 7, '=B4*C4'],
    [,'=SUM(B2:B4)',,'=SUM(D2:D4)'],
    [,,'=AVERAGE(C2:C4)']
];

const worksheet = SpreadsheetLibrary.create(my_spreadsheet);

Looks nice and simple doesn’t it?

But lets consider what happens when we need to insert a row for another fruit, a Kiwi.

Whatever we do we will have to at least change the formulas for cells B5, D5 and C7 which will become cells B6, D6 and C8. If we want Kiwis to be at the top of our list then we will have to change the formula in every cell.

When we do this in Excel or Google Sheets the UI automatically handles the formula adjustments for us during row insertion and it all seems effortless.

But what if this is all in JavaScript? You now have to write code to adjust all the spreadsheet formulas on the fly depending on where the new row is inserted so that it looks like this (assuming that we added the new Kiwi row to the top of our spreadsheet).

const my_spreadsheet = [
    ['Kiwi',  13, 7, '=B2*C2'],
    ['Apple', 10, 5, '=B3*C3'],
    ['Peach', 20, 12,'=B4*C4'],
    ['Mango', 30, 7, '=B5*C5'],
    [,'=SUM(B2:B5)',,'=SUM(D2:D5)'],
    [,,'=AVERAGE(C2:C5)']
];

You have essentially swapped one programming problem for another; how to manage complex dependent calculations easily with how to manage complex formulas containing interdependent 2D array indicies easily.

Our simple invoice logic presented as a spreadsheet is now strangely harder to read. Expressed in JavaScript we have lost column definitions so we now have to infer what the data represents; is column B or C quantity or is it price?

We also we now have a trail of cell references that we have to interperate every time we return to our spreadsheet. So, if in six months time, you return to your 33 column javascript spreadsheet (because a customer has reported a bug) and see :

const my_very_large_spreadsheet = [
[ /* 30 previous columns omitted*/, '=IF(B1=3,(AB1/C1)*(N1/H1+D1),(AD1+A1+B1)-AVERAGE(G1:G100))', ...],
];

I’m sure you’ll rub your hands with glee and jump right in!

Surely there must be a better way to do this I hear you say?

NoSheet, there is!

Lets rehash our simple example in noSheet terms:

const invoice_calculations = function(aggregate, row) { 

    /**
     * Calculate the total cost of the row
     */
    row.total   = () => row.cost * row.quantity;

    /**
     * Calculate the total cost of all the rows in the table
     */
    aggregate.total = () => SUM(this.column('total'));    

    /**
     * Calculate the average cost of all the row costs
     */ 
    aggregate.average_cost = () => AVERAGE(this.column('cost'));
};  

let the_invoice = createTable(['item', 'quantity', 'cost'], invoice_calculations);

the_invoice.load([
    ['Apple', 10, 5],
    ['Peach', 20, 12],
    ['Mango', 30, 7],
]);

the_invoice.calculate();

Need to add another row?

the_invoice.append( [['Kiwi', 13, 7]] );
the_invoice.calculate();

Holy Sheet! That’s good!

Well, the sales pitch has worked! Head on over to:

Look at some examples

Go deeper into what the differences between noSheet and a spreadsheet library are

A brief discussion of some advanced features

The documentation

I’m still reading, What’s the catch?

Well good for you… noSheet is not a drop in replacement. It does work differently to a spreadsheet in that it expects you to work with tables (much like a database) and it’s not meant to parse or work with Excel formulas (although as in the example above it’s possible to use Excel formula libraries like Formula.js).

It’s also not a spreadsheet UI/UX; it’s a way to simplify the handling of calculations in code that are generally table-like by providing flexibility and readability.

Take a look at the differences between noSheet and a spreadsheet