Skip to content
Turn spreadsheet data into a structured, dynamic API.
JavaScript Shell Dockerfile
Branch: develop
Clone or download
breezykermo Merge pull request #38 from forensic-architecture/issue/blueprints-js…
…on-formatting-#30

Issue/blueprints json formatting #30
Latest commit 13f93a2 May 23, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.github copy template from timemap Nov 4, 2018
docs writeup architecture doc Dec 20, 2018
scripts change permissions to encrypt.sh Dec 29, 2018
src multiple resources collapsed into single blueprint May 23, 2019
temp Add empty temp folder to repo Nov 11, 2018
test amend serverProcess test to update first Dec 15, 2018
.env.enc encrypt with maintainer secret May 23, 2019
.env.example new server test with precommit hook to secure .env files, set up CI e… Dec 14, 2018
.gitignore adding regex to get rid of encryption information when sharing betwee… Dec 18, 2018
.travis.yml encrypt with maintainer secret May 23, 2019
CODE_OF_CONDUCT.md Add code of conduct Nov 2, 2018
CONTRIBUTING.md modify docs to reflect new config Dec 19, 2018
Dockerfile Clean master commit Oct 31, 2018
LICENSE.md
README.md update readme with new wiki May 15, 2019
package.json refactor travis encrypt to manual process before push Dec 18, 2018
yarn.lock leverage ava's async features to ensure order in server_process tests Dec 14, 2018

README.md

Datasheet Server

Turn spreadsheet data into a structured, dynamic API.

Datasheet Server is released under the MIT license. Build Status

Overview · Configuration · Quickstart

Datasheet server makes resources from a spreadsheet available as a structured API.

  • Manage structured data without developers. Allows anyone to dynamically manage data, while simultaneously making this data available in a reliably structured format for frontend interfaces and other programmatic applications.
  • Designed for a dynamic workflow. References data in a spreadsheet source as a ground truth, but adds a layer of indirection that keep API routes from breaking when changes are made.
  • Customisable data transformation. Easily create new blueprints to specify the API structure that should be presented from source data.
  • Extensible architecture. Currently supports Google Sheet as a source and a REST-like query language, but structured modularly with an intention to support other sources and query languages.

Overview

Datasheet server is a Node server developed at Forensic Architecture to make data that is being dynamically modified by researchers concurrently consumable for programmatic applications as an API. We use spreadsheets extensively to keep track of information during our investigations, and we often want to make this data available via structured queries to make it available in a frontend web application, a game engine, or another use case.

Querying data directly from spreadsheets is brittle, as it relies on the maintenance of a rigid structure in the sheets at all times. By putting Datasheet Server as a proxy that sits in between source sheets and their consumers, it is possible to dynamically modify sheets without breaking applications. A data admin can then use Datasheet Server to ensure that applications always receive eligible data, without foregoing the spreadsheets as sources of truth.

To see how to get a local instance of datasheet server running in practice, see this wiki explaining how to use it to feed data from a Google Sheet to a local instance of Timemap.

Design Concepts

The codebase currently only supports Google Sheets as a source, and a REST-like format as a query language. It is designed, however, with extensibility in mind.

Sources
A source represents a sheet-like collection of data, such as a Google Sheet. A source has one or more tabs, each of which contains a 2-dimensional grids of cells. Each cell contains a body of text (a string).

Resources
The data from sources are made available as resources, which are structured blocks of data that are granularly accessible through a query language. Resources are the outfacing aspect of Datasheet Server, and represent the only kind of data that can be queried by applications. Each resource is configured with one or more query languages. (Currently only a REST-like query language supported.)

Blueprints
Blueprints are a data structure that represent the way that infromation from sources are to be turned into resources. For each tab in a source, there is a corresponding Blueprint. Blueprints are created through a blueprinter function invoked on the raw data from a source tab.

Blueprints are JSON objects. There have two forms:

  1. desaturated -- describes the resources and query languages available on data from a source tab.
  2. saturated -- both describes resources available on data from a source etab, and contains that data.

A desaturated Blueprint can be saturated by retrieving its data from the server's model layer, which stores tab data from sources.

A JSON catalogue of the available blueprints (desaturated) in a server is available at /api/blueprints.

Configuration

Copy the example environment file:

cp .env.example .env

Inside this file, you will need to modify at least the SERVICE_ACCOUNT_EMAIL and SERVICE_ACCOUNT_PRIVATE_KEY fields. These fields refer to the credentials of a Google service account. Google requires that developers create these when attempting to access their services programmatically, so that they can attribute requests to users. Service accounts also contain identity information, which means that asset owners can allow certain service accounts access to certain sheets, just as one might differentially grant certain users access to certain cloud assets.

Once you have created a service account, create and download an API key for that account. The JSON file for the API key that you download when you create it contains both a service account private key, and an email associated with the service account: add these respectively in the strings in .env for SERVICE_ACCOUNT_PRIVATE_KEY and SERVICE_ACCOUNT_EMAIL.

The last thing to do is to grant the service account access to the sheet that datasheet-server is pulling from. You can add a service account to a sheet as you would any other Google user: just enter the email address associated. (Note that this step is not necessary if you are accessing a publicly available sheet.)

Other configuration options, such as the port at which the server will expose resources, are also modifiable from the .env file.

Sources

Sources are specified in src/sheets_config.js. Datasheet server currently only supports Google Sheets as a source.

Google Sheets

| sheets | A list of objects, one for each sheet that is being used as a source. Each sheet object has a name (String), an id (String), and a tabs (object) field, which are explained below. | object |

Each Google Sheet being used as a as source requires a corresponding object in sheets. The object should be structured as follows:

Option Description Type
name Used to refer to data served from this source string
id The ID of the sheet in Google. (You can find it in the address bar when the Sheet is open in a browser. It is the string that follows 'spreadsheets/d/'). string
tabs An object that maps each tab in the source to one or more Blueprinters. All of the Blueprinters in the blueprinters folder are available through a single import as at the top of example.config.js.
To correctly associate a Blueprinter, the object key needs to be the tab name with all lowercase letters, and spaces replaced by a '-'. For example, if the tab name in Google Sheets is 'Info About SHEEP', the object key should be 'info-about-sheep'.
The value should be the Blueprinter function that you want to use for the data in that tab. If you require more than one endpoint for a single tab, you can support multiple blueprinters by making the item an array. See the example of a configuration object below.
TODO: no Blueprinter is used by default.
object
Example Configuration Object
import BP from './lib/blueprinters'

export default {
  googleSheets: {
    email: 'project-name@reliable-baptist-23338.iam.gserviceaccount.com',
    privateKey: 'SOME_PRIVATE_KEY',
    sheets: [
      {
        name: 'example',
        id: '1s-vfBR8Uy-B-TLO_C5Ozw4z-L0E3hdP8ohMV761ouRI',
        tabs: {
          'objects': BP.rows,
          'fruit': [BP.columns, BP.ids],
        }
      },
    ]
  }
}

## Quickstart Clone the repository to your local:

git clone https://www.github.com/forensic-architecture/datasheet-server

Follow the steps in the configuration section of this document.

Run with Docker

To create a new instance of the server with Docker installed, ensure that you have followed the steps in the quickstart guide above, then and build an image locally. (Note that Docker must be installed):

docker build -t datasheet-server .

You can then run the container and make available the relevant port (4040 by default):

docker run -d -p 4040:4040 datasheet-server

If running on a cloud server, you'll probably need to zero out the host IP:

docker run -d -p 0.0.0.0:4040:4040 datasheet-server

Run locally

Install dependencies:

yarn # npm install

And run development server:

yarn dev # npm run dev

Contribute

Code of Conduct

Please read our adopted code of conduct before contributing, so that you can understand what actions will and will not be tolerated.

Contributing Guide

Read our contributing guide to learn about our development process, how to propose bugfixes and improvements.

Community

If you have any questions or just want to chat, please join our team fa_open_source on Keybase for community discussion. Keybase is a great platform for encrypted chat and file sharing.

License

Datasheet Server is distributed under the MIT License.

You can’t perform that action at this time.