Variables and simple expressions

Here’s a formula:

10 + 20

If you select a blank cell and type that formula into the formula box you’ll get a new variable with a default name.

You can name variables yourself like this:

totl: 10 + 20

To rename a variable you can select it and click on its name. Undo/redo with ctrl+z and ctrl+Z respectively (or ⌘+z and ⌘+Z if you prefer.)

“The usual” prefix and infix operators are available, with “the usual” priority and associativity:

  • +, -, *, / and - for regular arithmetic,
  • ** for exponentiation and % for modulus,
  • = for equality, >=, >, <= and < for order comparisons,
  • Bitwise operators <<, >>, &, |, ^, ~,
  • Logical operators &&, ||, !,
  • Indexing/lookup operators array[i] and object.field.

There is also an “arrow” operator. If you have tables users and companies, and values in the users.company table column “refer to” values in the companies.name column, then

  • users.company -> companies.address returns the address of each user’s company.

There are also currently two infix “join” operators you probably won’t use:

  • :: “finds” one occurrence of the right-hand-side argument in the left-hand-side array, and returns the item’s index if it’s present.
  • ::: “finds” every occurrence of the right-hand-side argument in the left-hand-side array, and returns an array of indices.

Functions

Users can use previous calculations as “functions” by way of a “what-if” calculation. With cells like,

x: 0

and

square: x * x

the user can re-use the definition of square like so:

five_squared: square(x: 5)

This says, “What would the value of square be if the value of x was 5?”

Users can define more complex functions in similar ways:

hypotenuse: (square(x: a) + square(x: b)) ** 0.5

and then reuse them over and over again.

hypotenuse(a: 3, b: 4)

Defining functions in this way helps keep formulas short, and can reduce the clutter of extra columns or tables for intermediate calculations.

Compound data types

In addition to a small set of primitives (numbers, strings, Boolean variables and nulls), we have “arrays” (columns of tables) and “objects,” (rows of tables) expressable as familiar literals:

[1, 2, 3, "January", "February", "March"]
{ name: "Sally", "favourite colour": "green" }

Worksheets also have object “values” – their named attributes are the names of the cells inside them, and the values of those attributes are the values in those cells. So we can write,

x: sheet2

We can even write

x: sheet2(a1: 12)

which means “What would the sheet sheet2 be if sheet2.a1 was twelve?” This can be useful for debugging – this “what if sheet” can be inspected just like the original to see how the computation worked.

There are also a few “special” input methods for compound data types, in which separate formulas can be entered for each entry. The most powerful of these is the “table”: a 2d table with numbered rows and named columns. Separate formulas can be entered into each cell in the table, and formulas can be entered for whole columns.

Most operators and many functions implicitly “map” over arrays. For instance,

1 + [1, 2, 3]

evaluates to [2, 3, 4]. When more than one operand is an array, the array lengths are typically required to be equal and a “zip” is performed:

[1, 2, 3] * [1, 2, 3]

evaluates to [1, 4, 9].

Importantly, the . operator also maps over arrays. This is useful when referring to a column in a table (normally represented as an array of objects.)

Builtin functions

Many functions below expect one unnamed argument. If more than one unnamed argument is provided, an array is often returned with the function applied to each unnamed argument.

Most functions don’t support object arguments because I’m not yet sure what should happen when you do that.

Builtins include:

  • Constants e() and pi(),

  • Trigonometric functions sin, cos, tan. Each takes an unnamed argument in radians or a named argument degrees: or radians::

    sin(0, pi()/2, pi(), 3*pi()/2)
    cos(degrees: [30, 60, 90])
    tan(radians: pi())
    
  • Inverse trigonometric functions asin, acos and atan2. Each returns an angle in radians, or in "radians" or "degrees" if specified with the optional in: argument:
    atan2(3, 4, in: "degrees")
    
  • log with optional base: parameter.

  • bound takes one unnamed argument, an optional lower: argument and an optional upper: argument. It returns the input value or the closest bound if the input is not between them.
    bound([-10, -5, 0, 5, 10], lower: 0)
    

    (returns [0, 0, 0, 5, 10])

  • flatten turns arrays of arrays (of arrays…) into single, non-nested arrays. Optional depth parameter which defaults to “infinity.”

  • range returns an array of the numbers between zero and one less than the unnamed input value.
    range(5) + 10
    

    (returns [10, 11, 12, 13, 14])

  • filter takes one unnamed array argument and one argument named by: (which defaults to the unnamed argument if not provided.) It returns the elements in the first argument for which the corresponding element of by: is “truthy”.

  • sort takes one unnamed array argument and one argument named by:. It returns the elements in the first argument sorted by the corresponding values in by: (which defaults to the first argument if not provided.)

  • group is best explained by example:
    a: range(5)
    group(a, by: a % 2)
    

    returns [{ by: 0, group: [0, 2, 4]}, { by: 1, group: [1, 3] }].

    That is, it returns a table with two columns: the by column has all the unique values from the by: argument, and the group column has lists of values from the first argument grouped by their corresponding by: value.

  • if takes three arguments – the condition, the result if the condition is true, and the result if the condition is false. Any or all of the arguments can be arrays and are mapped/zipped like mathematical operators.

Some obvious things that don’t take named arguments:

  • sum, average, transpose, toJson, fromJson.