Introduction

SpreadsheetDB is a DBaaS (database as a service). It can be queried with an API over HTTP to insert records and to manipulate JSON spreadsheets. To use the API, you will need an account that you can create here.

Cell coordinates format

Usually spreadsheet cell coordinates are composed of a series of letters for the column, and a number for the row, such as"A1" or "RV91". Even though this convention is very user-friendly, it might not be optimal for developers. Indeed, counting columns with letters and having a row index starting at 1 might not be very practical.

That is why SpreadsheetDB also supports an x,y notation with numbers only. Here are a few examples:

You can choose between the x,y and A1 notation when creating or modifying a spreadsheet. Also, when getting a spreadsheet, there is an option the choose the notation. The x,y notation is used in error responses.

This guide will use the x,y notation.

Manipulating spreadsheets

Let's start by creating a "hello world" spreadsheet with POST /api/spreadsheet.

The HTTP body is a JSON object containing a cells field describing each cell of the spreadsheet, in this case, only two. The first one 0,0 is just static text: "hello world". The second one starts by the prefix =. It indicates that the cell contains a formula that can be computed.

Let's now compute it with GET /api/spreadsheet/:name.

The cell 0,0 result is the same as the value, since it is not a forumla. However, the cell 0,1 is a formula, the field result contains the computed result, in this case, 2.

We will now use PATCH /api/spreadsheet/:name to update our spreadsheet by adding another cell that references 0,0.

The cell 0,2 is now added to our spreadsheet. Its formula, 0,1+3 references the result of the cell 0,1. The expected result should then be 2 + 3 = 5. Let's verify.

It is possible to get a spreadsheet without authentication, just by specifying the key in the URL. The key can be found either by getting the spreadsheet with the API, or in the dashboard where spreadsheets are listed.

To export the spreadsheet in CSV, just append ".csv" after the name:

Pushing records

Records are sets of custom data that you can use in your spreadsheets.

Imagine you are doing business in real estate, you have a few employees who buy and sell houses. Jane just made her first deal, she bought an apartment for $90,000 and sold it for $105,000. Let's push a record:

The record field "key" identifies the data (here "deal") while the field "data" is the data itself (information regarding the purchase price, sale price and the commercial doing the transaction), schema free.

It is also possible to push an array of records, let's do that for the rest of the sales:

Note: using an array counts as one POST /record call.

Using records in a spreadsheet

The database now contains records we just inserted. We can aggregate them in a spreadsheet with the help of some functions to get some information:

• The number of deals: COUNT(deal)
• The average sale: AVG(deal.sale)
• The profit: SUM(deal.sale) - SUM(deal.purchase)
• The average margin percentage: (SUM(deal.sale) - SUM(deal.purchase)) / SUM(deal.purchase) * 100

Let's see the result:

Filters

Filters are expressions returning a boolean value. They allow to select specific records when using aggregating functions. They can be specified by cell, by row, by column or by spreadsheet.

The formula COUNT(deal) used in our previous example, counts all the deal records in the database. But with a filter like deal.commercial == "Mike", only Mike's deals will be selected.

Keeping our previous example, we would like to add two columns, each one filtered by commercial.

Let's see the result:

There are 4 types of filters:

• Cell filters - set with the filter field of a cell.
• Row filters - set with the rowFilters of a spreadsheet.
• Column filters - set with the columnFilters of a spreadsheet.
• Spreadsheet filters - specified in the URL at query time with the filter parameter.

Filters can be combined. Consider a spreadsheet with the filter a > 8 on row 4 and the filter b == "foo" on column 6. The computed filter on cell 4,6 will be a > 8 && b == "foo".

Filters can use parenthesis (), && operator and || operator.

Date and time

In SpreadsheetDB, a date is represented as an Unix epoch in miliseconds.

It is possible to use date and time functions to filter records over a specific period of time. Let's consider plenty of records with this format:

{"key":"sale", "data": {"date": 15234423432, "value": 42}}

Here are a few examples of datetime filters we can use:

Datetime functions can also take a second parameter to specify a timezone.

Examples:

• MONTH(sale.date, "Europe/Paris")
• DAY(sale.date, "US/Pacific")
• HOUR(sale.date, "Asia/Seoul")

If no timezone is specified, functions use UTC by default.

Ranges

Ranges can also be aggegated in SpreadsheetDB with the following format: FUNCTION(<first cell>:<last cell>).

Let's see an example.

The cell 1,0 computed the sum of the range 0,0:0,2 (0,0 + 0,1 + 0,2) which is 6.

A range can only be on a single row from left to right, or a single column from top to bottom.

A complete list of functions that can aggregate ranges can be found on the dedicated page.

External requests

Since version 1.4.0, SpreadsheetDB handles external requests. The function JSONGET() allows to make HTTP calls to services that reply with JSON data. The JSON can then be parsed to extract a specifc field, all from inside a cell.

Using the Wikimedia API, we can get the pageviews for the CRISPR article of March 2017.

Let's now use this URL to include the number of views in a spreadsheet.

For more details, check out the JSON functions.

SpreadsheetDB.js

SpreadsheetDB.js is a JavaScript library that allows you to compute spreadsheets directly from a web page.

To do so, you need the name and the key of your spreadsheet. The key can be retreived either from a GET request or from your dashboard, on the spreadsheets list.

<script src="https://www.spreadsheetdb.js/js/spreadsheetdb.js"></script>
<script>
    spreadsheetdb.get({
        spreadsheet: "sales",
        key: "ZP3v0tmbZ1EMlcybMAgYnxiMKektlz0beTw5dJ72ABEME2NgtU",
        cb: functions(error, spreadsheet) {
            if (error !== undefined)
                return console.error(error);

            // use spreadsheet
        }
    });
</script>

The callback takes two arguments. The first one, error is the same object returned by the API in error case. The second argument, spreadsheet is the same object returned by GET /spreadsheet/:name. Always check error before using spreadsheet.

SpreadsheetDB.js is on Github and the full documentation can be consulted there.

SpreadsheetDB.js handles special parameters to build graphs: begin and end. They are coordinates to top-left and bottom-right cell, allowing to select only a part of your spreadsheet.

"demo" spreadsheet

With this spreadsheet, we'll use B2 as begin cell and E4 as end. See Cell coordinates format for more information about coordinates.

spreadsheetdb.get({
    spreadsheetdb: "demo",
    key: "AWhmmPOHffPh68Es1TZKe46Nu0SRsPrSCz4OfIlkO0rKZW3RFS",
    begin: "B2",
    end: "E4",
    cb: function(error, res) {
      // ...
    }
});

When begin and end are specified, the callback is called with a special object instead of the full spreadsheet:

{
    "columns": ["2014", "2015", "2016", "2017"],
    "rows": [
        {
            "label": "Alice",
            "data": [17, 15, 12, 10]
        },
        {
            "label": "Bob",
            "data": [6, 7, 10, 16]
        },
        {
            "label": "Eve",
            "data": [10, 16, 11, 15]
        }
    ]
}

This format makes it easier to build a chart or a table.

Using the same spreadsheet, here are some examples with various chart libraries:

• Chart.js
• Flot

spreadsheetdb.get() also handles a refresh parameter, that calls the callback every n miliseconds. This allows you to build "live" graphs that get refreshed regularly. To easily test a live graph, use the RAND() function.