NoSheet is not a direct analogue of a spreadsheet library, here are the main differences:
Think in terms of tables and aggregates
Spreadsheets ask you to put your formulas in a 2D grid of cells indexed by rows and columns. Beyond this you are free to layout your worksheet however you want.
noSheet works with tables of data where a formula defines the calculation for each element in a column. Tables can have aggregates (or more generally fields) defined by functions that have access to all the rows in the table.
It’s all JavaScript Baby!
Your formulas are all defined as JavaScript arrow functions.
() => row.quantity * row.unit_cost;
Bring Your Own Excel Formulas
There are no built-in Excel functions. Your formulas can do anything JavaScript can do and you can use any of the other Excel-lent (sorry, couldn’t resist) formula libraries out there.
There is no ‘autocalculate’
You tell noSheet when to recalculate.
Facets, Functions and Data
A facet is a collection of formulas. The formulas in a facet will form a library of calculations that ‘do a job’, that ‘job’ may involve calculating new column entries from the table data and/or aggregating the table data.
As an example, consider an invoice, it will have at least three columns; item, quantity, cost. In your library of facets you may have an invoice facet that calculates the subtotal of each line (quantity * cost) and finally a table aggregate that adds all the subtotals to give a grand total.
const invoice = function(table, row) {
/*
This statement is executed for every row and will calculate the row subtotal.
The subtotal column doesn't need to be defined prior to the facet being used - the new column
will be automatically created and populated for each row.
*/
row.subtotal = () => row.quantity * row.cost;
/*
This is a table aggregate, it is only calculated once after all rows have had their subtotal calculated.
It uses the 'column' method of the context to get all the subtotal values from the rows as an array
and then passes them to a 'SUM' function.
*/
table.grand_total = () => SUM(this.column('subtotal'));
}
A table may use multiple facets from your library. In this way you can mix and match the facets used to do calculations that is task dependent. So, if for instance, you want to see the gross margins for your invoice you can simply add the ‘gross margin’ facet from your library.
Precendents and Dependents
As the formulas are defined in JavaScript they aren’t parsed by noSheet (why do a job already being done by the compiler?) and consequently no start-up heavy calculation dependency tree is built.
noSheet does however, due to the way facets are executed, enforce formula precedency by making sure that any terms (column names, aggregates/table fields) used in the table facets have a prior definition.
Execution Order
Facets are executed in the order passed to the createTable function, and, as they are JavaScript functions the formulas within are executed sequentially as defined:
//This facet is executed second
const facet2 = function(table, row) {
row.e = () => row.d + 1; //3rd formula calculated
};
//This facet is executed last
const facet3 = function(table, row) {
row.f = () => row.e + 1; //4th formula calculated
};
//This facet is executed first because it is first in the parameter list
const facet1 = function(table, row) {
row.c = () => row.a * row.b; //first formula calculated
row.d = () => row.c + 1; //2nd formula calculated
};
createTable(['a','b'], facet1, facet2, facet3),
So, for instance, you couldn’t define a facet like the one below as delivery is not defined prior to its use:
This will throw an error:
row.total = () => (row.quantity * row.cost) + row.delivery;
row.delivery = () => lookup(row.country_code);
This won’t:
row.delivery = () => lookup(row.country_code);
row.total = () => (row.quantity * row.cost) + row.delivery;
Similary, this will throw an error:
const facet1 = function(table, row) {
row.total = () => (row.quantity * row.cost) + row.delivery;
}
const facet2 = function(table, row) {
row.delivery = () => lookup(row.country_code);
}
createTable(['a','b'], facet1, facet2),
This won’t:
createTable(['a','b'], facet2, facet1),
Brute Force Calculations
noSheet deliberately eschews expensive formula parsing to build dependency trees in order to be as simple as possible.
This means that when a data point is changed the entire noSheet table is recalculated (in fact you will need to tell noSheet when to recalculate most of the time).
This is by design and it means that no matter what is changed within your data (and where it is changed) you can be sure of a consistent execution time frame.
This is particulary important for the web where you don’t want your spreadsheet to grind to halt just because a user happened to change that cell that almost every other calculation depends on.