tlhunter / node-grille

Google Spreadsheets CMS

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

node-grille's Issues

EventEmitter and state machine

Prevent duplicate executions with a simple state machine.

Make registry an event emitter.

Gracefully handle empty rows

No ID? No data!

Rename 'array' to 'matrix', create a new type 'array' which is similar to 'hash'

Hash returns an object keyed by ID and with an ID property.

Matrix will then behave the same way Array currently behaves.

Array will return an array of objects containing the same data that Hash would return.

Allow dot notation in keyvalue id fields?

If the id has a dot, the data could be imported as an object, e.g.

KEY | VALUE
w   | Hello
x.y | Goodbye
x.z | See Ya

gives:

{
  "w": "Hello",
  "x": {
    "y": "Goodbye",
    "z": "See Ya"
  }
}

Could be dangerous though. What is someone has an x and an x.y?

Multiple Filtered Output Objects

Sometimes different data will be required by a server application and its client applications. This could be to reduce data sent over the wire or even for security purposes.

Need a clean solution to describe multiple output objects with filtered properties.

grille is broken

• currently using an old version of the Google API
• https://github.com/theoephraim/node-google-spreadsheet#google-spreadsheet
• need to update dependency

CMS payload data should be immutable

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/freeze

Parse XML in unit tests

Instead of hitting google's servers...

redis pain

First of all, thanks, I also like the idea to be able to test game levels with a simple spreadsheet :)
I didn't want to deal with spreadsheet auth, so I node-grilled it.

I made it work but had lot of trouble with redis on glitch.
Finally, it worked using a post on their forum
But it means you need to check if redis-server is really running almost all the time (glitch reset).

Best would be to have an option to OPTIONALLY use redis and switch to local json files for instance.
If file names are static (one per spreadsheet) and stored in .env file, you could also directly call the json file from any client without exposing it.

Support dot notation for collection importing

Column data type for not importing

Make a column data type which prevents the column from being imported into the CMS.

Differentiate between object and json types?

This could allow json to be any valid JSON, defaulting to null.

object would then have to be a strict subset of JSON, defaulting to {}.

Handle multiple Google Spreadsheets per Grille instance

This should only change the constructor signature to also allow an array:

var grille = new Grille('SHEET_ID');
var grille = new Grille(['SHEET_ID1', 'SHEET_ID2']);

Each sheet will still have their own meta tab, and the resulting object will be a merging of both sheets data.

Rename Types

Should make the UI less scary for non developers.

array.integer => integers
array.string => strings
array.boolean => booleans
array.float => floats

perhaps:
json => object

Itemized conversion errors/warnings

When parsing a document we should produce a list of warnings and errors for human consumption.

This way, when a content editor makes changes they can know exactly what went wrong.

Add transform callback during initialization

Add a transform callback which allows last-minute transforming of data (prior to saving freezing and saving in database). This will allow for more advanced data conversion which couldn't otherwise be described declaratively.

Here's an example:

var transform = function(content) {
  content.level.map(function(level) {
    level.gold += 100;
  });
};
var content = new Grille(id, storage, transform);

// transform = function(content) { return content; } // Default Behavior

Array sheets shouldn't require ID column or even NAME row

It should simply take tabular data as inputted into the sheet and import it that way.

The type row should still be required, however.

Make brackets optional for array.*

If the first character in an array. field is a bracket, then treat it as a JSON string and parse it like we do today.

Otherwise, split the string on commas and then convert each field into the expected type. This will have no side effects for integers, floats, or booleans.

If strings are complex, e.g. contain commas, then the author should continue to use brackets/quotes/JSON styntax. Won't need to support quotes for strings without brackets, that'll be unnecessarily complex.

Arrays and Objects without data should default to empty

If a column is set to type array, and a row is missing data, default it to [].

If a column is set to type object, and a row is missing data, default it to {}.

Might be merging old data when renaming attributes

If a row name changes and a rebuild happens and the old name no longer exists in the new data, the old one seems to stick around.

Remove grille.get() and just provide access to the raw object

It's a dumb idea.

Also, if the server sends the whole object to the client, it's now two separate patters the developers need to use to access data.

Attach version number to data payload

data.version = version

Add Auth support

Caching and versioning of data

I was going to "leave this up to the reader", but it's vital enough that it should really be in the module.

Loose/Strict Validation Modes

Specify a Loose/Strict flag when instantiating Grille.

Loose Mode

• Empty JSON -> {}
• Empty Array -> []
• Empty Number -> 0
• Boolean isn't TRUE/FALSE -> false

All conversion issues should be displayed as warnings as specified in #12.

Strict Mode

Encountering any of the above should result in a thrown error and data not importing/creating a version. All conversion issues should be displayed as errors as specified in #12.

Retry failed sheet lookups

Google API likes to die. A lot. If a sheet HTTP request fails, retry a few times.

IDEA: Allow empty meta.collection, import to root

If the collection column is empty on the meta sheet, import the data directly to the root object.

Use Case: I normally have a "dictionary" object for simple key/value pairs. Instead, these pairs could be attached directly to the root.

Dangers: A key with the name xyz and no collection would override a collection with the name xyz.

Rename sheet/registry classes, they're confusing

Come up with a name, make NPM package

If google takes longer to respond than the timeout, async callback executes twice