Grille: Google Spreadsheet CMS

Grille is a simple yet powerful tool for extracting data from Google Spreadsheets and transforming it into an easily consumable form.

Grille provides an extensible mechanism for storing and retrieving old versions of data.

Purpose

Retrieve application data from Google Spreadsheets and store in memory. It's sort of like a Content Management System, but if you're storing HTML or WYSIWYG content you're probably using the wrong tool. Instead, use this for storing data which needs to be easily configured, e.g. part catalogs or application tuning data.

This does not allow for persisting data back into Google Spreadsheets.

Since data is stored in-memory, lookups are fast and don't require callbacks. I/O is only needed when building a new content version.

Example Code

var Grille = require('grille');
require('redis'); // Defaults to storing data in Redis, can be overridden

var grille = new Grille('1r2SaVhOH6exvevx_syqxCJFDARg-L4N1-uNL9SZAk04');

grille.load(function(err) {
  console.log(grille.content.keyvalue.author);
  // run application
});

// Whenever you want to update Grille to use the lastest data
console.log('old version', grille.version);
grille.update(function(err) {
  console.log(grille.content.keyvalue.author);
  console.log('new version', grille.version);
});

Spreadsheet Configuration

Follow along with the Demo Spreadsheet

At a minimum your spreadsheet needs a meta worksheet.

The spreadhseet above will generate the following data structure:

Publishing Google Spreadsheet

For Grille to be able to access the data in your spreadsheet, you need to perform a few steps:

1. Click File | Publish to the web...
2. Publish the document
3. Copy the long code in the URL (letters, numbers, hyphens, underscores) and dismiss box
4. Click Share in the upper right corner of the screen
5. Click the Advanced link in the lower right corner of the dialog
6. In Who can access click the Change... link for the first item
7. Click the first option, On - Public on the web

The String you copied during step 3 is the Google Spreadsheet ID that you'll need for use with Grille.

Example Meta Worksheet

The meta worksheet tells Grille how to parse your content. It is loaded prior to all other sheets being loaded.

The id column correlates to the worksheet (tab) name to be loaded (if it's not listed it's not loaded).

The collection column tells Grille which top-level attribute the data for that worksheet should be stored at. Note that you can use . for specifying deeper nested objects.

The format column tells Grille which method to use when converting the raw worksheet into a native object.

As a convention, all worksheets specify data types as the second row. I suggest using Data Validation (like in the example worksheet).

id | collection | format --------------------|---------------|--------- string | string | string people | people | hash keyvalue_string | keyvalue | keyvalue keyvalue_integer | keyvalue | keyvalue level_1 | levels.0 | array level_2 | levels.1 | array level_secret | levels.secret | array

Example Hash Worksheet

This will likely be the most common format you use. Data is loaded into an object where each key is the value in the id column. The id column should be a number or a string and each row should have a unique value.

id | name | likesgum | gender --------|-------------------|-----------|------ integer | string | boolean | string 1 | Rupert Styx | FALSE | m 2 | Morticia Addams | TRUE | f

Hash Output

{
  "people": {
    "1": {
      "gender": "m",
      "id": 1,
      "likesgum": false,
      "name": "Rupert Styx"
    },
    "2": {
      "gender": "f",
      "id": 2,
      "likesgum": true,
      "name": "Morticia Addams"
    }
  }
}

Example KeyValue Worksheet

KeyValue worksheets provide a simple collection for looking up data.

Since each worksheet can only contain a single data type, I recommend using multiple sheets for different types and merging them together. Simply set the resulting meta collections for multiple sheets to be the same (see above) and they will be merged together as expected.

id | value ------------|----------------- string | string title | Simple CMS Demo author | Thomas Hunter II

KeyValue Output

{
  "keyvalue": {
    "author": "Thomas Hunter II",
    "title": "Simple CMS Demo"
  }
}

Example Array Worksheet

Array worksheets are great for building 2D arrays of data. A current eyesore is that each column needs to be name col-*.

id | col-1 | col-2 | col-3 | col-4 --------|-----------|-----------|-----------|------- integer | string | string | string | string 1 | A | B | C | D 2 | E | F | G | H 3 | I | J | K | L 4 | M | N | O | P 5 | Q | R | S | T 6 | U | V | W | X

Array Output

{
  "level": [
    [ "A", "B", "C", "D" ],
    [ "E", "F", "G", "H" ],
    [ "I", "J", "K", "L" ],
    [ "M", "N", "O", "P" ],
    [ "Q", "R", "S", "T" ],
    [ "U", "V", "W", "X" ]
  ]
}

Example Complete Output

This is the complete output from the example spreadsheet:

{
  "keyvalue": {
    "author": "Thomas Hunter II",
    "hours_in_day": 24,
    "seconds_in_minute": 60,
    "title": "Simple CMS Demo"
  },
  "levels": {
    "0": [
      [ "A", "B", "C", "D" ],
      [ "E", "F", "G", "H" ],
      [ "I", "J", "K", "L" ],
      [ "M", "N", "O", "P" ],
      [ "Q", "R", "S", "T" ],
      [ "U", "V", "W", "X" ]
    ],
    "1": [
      [ "A", "B", "C", "D" ],
      [ "E", "F", "G", "H" ],
      [ "I", "J", "K", "L" ],
      [ "M", "N", "O", "P" ],
      [ "Q", "R", "S", "T" ],
      [ "U", "V", "W", "X" ]
    ],
    "secret": [
      [ "A", "B", "C", "D" ],
      [ "E", "F", "G", "H" ],
      [ "I", "J", "K", "L" ],
      [ "M", "N", "O", "P" ],
      [ "Q", "R", "S", "T" ],
      [ "U", "V", "W", "X" ]
    ]
  },
  "people": {
    "1": {
      "gender": "m",
      "id": 1,
      "likesgum": true,
      "name": "Thomas Hunter II"
    },
    "2": {
      "gender": "m",
      "id": 2,
      "likesgum": false,
      "name": "Rupert Styx"
    },
    "3": {
      "gender": "f",
      "id": 3,
      "likesgum": true,
      "name": "Morticia Addams"
    },
    "4": {
      "gender": "m",
      "id": 4,
      "likesgum": false,
      "name": "Lurch"
    }
  }
}

Data Types

Grille supports the following list of data types:

Name | Examples ----------------|---------------------- integer | 1, -2, 99999 json | [1, 2, 3], {"a": "b"} string | Banana boolean | TRUE/FALSE float | 1.2, 99.9, 2 array | [1, true, "blah"] array.integer | [1, 2, 3] array.string | ["first", "second"] array.boolean | [true, false] array.float | [1, 1.1, 1.2]

I recommend using data validation on the second row of a worksheet to enforce these (see example spreadsheet).

Complex Instantiation Example

Here's a complete example of how to instantiate a Grille instance, showing all of the default values:

var Grille = require('grille');

var grille = new Grille(['SHEET-ID-1', 'SHEET-ID-2', 'SHEET-ID-3'], {
  storage: new Grille.RedisGrilleStorage({
    host: '127.0.0.1',
    port: 6379,
    current: 'grille-test-current',
    collection: 'grille-test-collection'
  }),
  transform: function(content) {
    return content;
  },
  timeout: 5 * 1000,
  parallel: 5,
  retry: 3
});

Note: If you'd like to override the storage mechanism, check out the RedisGrilleStorage object for reference.

Multiple Spreadsheets

Grille has support for multiple spreadsheets. Simply provide an array of Spreadsheet IDs instead of a single ID.

Each document should have its own meta tab, and the data from each sheet will be combined into the same object.

var grille = new Grille([
  '1r2SaVhOH6exvevx_syqxCJFDARg-L4N1-uNL9SZAk04',
  '11_2RBdN37Q-LawzfFEJBlF3JfeDX5tC1Rp0QdAvAvoc'
]);

Limitation's / Gotcha's

• Column names cannot have underscores (Google API Limitation)
• Can't have columns named content, save, del, title
• Loading data is slow and can timeout for larger spreadsheets

Classes

• Worksheet: Represents a single tab in a Google Spreadsheet
• Spreadsheet: Represents a Google Spreadsheet
• Grille: Persists and loads data related to a Spreadsheet
• RedisGrilleStorage: Example storage engine