Representing and Editing JSON with Spreadsheets
I’ve been developing a convenient way of representing and editing JSON in spreadsheets, that I’m very happy with, and would love to share!
[This is a work in progress but I’m publishing it now to get feedback!]
I‘ve been successfully synergizing JSON with spreadsheets, and have developed a general purpose approach and sample implementation that I’d like to share. So I’ll briefly describe how it works (and share the code and examples), in the hopes of receiving some feedback and criticism.
Here is the question I’m trying to answer:
How can you conveniently and compactly represent, view and edit JSON in spreadsheets, using the grid instead of so much punctuation?
My goal is to be able to easily edit JSON data in any spreadsheet, conveniently copy and paste grids of JSON around as TSV files (the format that Google Sheets puts on your clipboard), and efficiently export and import those spreadsheets as JSON.
So I’ve come up with a simple format and convenient conventions for representing and editing JSON in spreadsheets, without any sigils, tabs, quoting, escaping or trailing comma problems, but with comments, rich formatting, formulas, and leveraging the full power of the spreadsheet.
It’s especially powerful with Google Sheets, since it can run JavaScript code to export, import and validate JSON, provide colorized syntax highlighting, error feedback, interactive wizard dialogs, and integrations with other services. Then other apps and services can easily retrieve those live spreadsheets as TSV files, which are super-easy to parse into 2D arrays of strings to convert to JSON.
This is a fresh new reimplementation of some old ideas I’ve been happily using for years with CSV files and Python for many years, plus some new ideas focused on JSON, JavaScript and Google Sheets. Now it’s general purpose, JSON oriented, written in JavaScript, integrated with Google Sheets and also Unity3D, and I’m releasing a simple library that works in browsers, Google Sheets and node.js, as free software.
Here is an example spreadsheet, which includes several sheets of different structures, and a script to convert the spreadsheet to JSON. The examples are for configuring a UnityJS app, a JavaScript<=JSON=>C# scripting bridge I’m developing for Unity3D, but the idea of editing JSON in spreadsheets is independent and useful on its own, especially synergistic with Google Sheets.
The first sheet named “world” is the top level object, which configures a Unity3D app scripted in JavaScript (that’s UnityJS, another story, but just think of this JSON as an arbitrary but non-trivial example). So the world is a big fancy JSON structure used by JavaScript code to create and configure a bunch of prefabs.
As you can obviously see, you simply write your JSON expressions as an indented outline with type names before the values, putting keys, types and values in separate indented cells.
object
tileRows number 10
tileColumns number 10
materialTiling object
x number 4
y number 4
materialOffset object
x number 0.5
y number 0.5
…You can use the “sheet” pseudo-type to make references to objects, arrays, etc, defined in other named sheets of the same spreadsheet:
…
texturePaths sheet texturePaths
prefabMap sheet prefabMap
bowConfigs_outline sheet bowConfigs_outline
bowConfigs sheet bowConfigs_table
…There’s another sheet named texturePaths that contains an array of strings:
array
string Joao Paulo/Textures/Abstract_001/Abstract_001
string Joao Paulo/Textures/Abstract_001/Abstract_002
string Joao Paulo/Textures/Abstract_001/Abstract_003
…And the prefabMap sheet is just a map of different types of content, directories and file names. You can represent any kind of JSON structures in the spreadsheets, and it’s much easier to edit than raw JSON text.
There are also some convenient ways of compactly representing repetitive structured data. One common type is a 2d array of elements all the same type, which you can make with the “grid” pseudo-type:
…
tileName grid string 10 10
Hex_Sand Hex_Sand Hex_Magma Hex_Sand …
Hex_Grass_Dry Hex_Magma Hex_Sand Hex_Sea …
… … … … …That’s a 10x10 grid of strings. You can also make grids of other types that will fit into one cell. For example, you could make a grid of strings or numbers or booleans, but not arrays or objects, since they don’t fit in a single cell.
But you could make a grid of the “sheet” pseudo-type, with a grid of sheet names, each sheet each containing any type or pseudo-type of object.
It also supports a “json” pseudo type that lets you put an arbitrary JSON expression into one cell.
Notice that the values of the cells themselves may be calculated by spreadsheet formula, which can call any function and refer to any cells in any other sheet. In this example formula, each tile name is calculated by randomly choosing from a range pointing to all the hex tile names in the prefabMap:
=INDEX(prefabMap!$E$5:$E$26,
RANDBETWEEN(1, COUNTA(prefabMap!$E$5:$E$26)))Better yet, you can use named ranges to mark up rows, columns and grids of cells in the spreadsheet to use in expressions like:
=INDEX(roads, RANDBETWEEN(1, COUNTA(roads))
Another great way of compactly representing repetitive JSON structures in a spreadsheet is to use a table whose headers specify the structure, key names and types of the JSON arrays and objects.
This is essentially what Console.table does with an array of objects, but with rows and columns exchanged, and supporting arbitrarily nested arrays and objects.
The “bowConfigs_outline” sheet has a conventional outline formatted array of two JSON objects (rainbow bow configurations) that have the same structure. It is 175 rows, and 9 columns (1575 cells, many empty, with many repeated types and key names).
array
object
bowStart number 0
bowEnd number 0.8
bowHeight number 30
startWidth number 1
endWidth number 0
widthMultiplier number 5
fromLocalOffset object
x number 6
toLocalOffset object
x number -6
…The “bowConfigs_table_compact” sheet shows the compact table representation of that same structure, with headers that implicitly describe the shape of the structure so there is one value per column, with no rows or columns of wasted space. It is only 4 rows (a row with the “table” type, a row with the headers, plus one row for each structure), and 58 columns (232 cells, few empty, with no unnecessarily repeated types or key names).
Lining all the values of the same name and type up in vertical columns makes it really easy to view and edit the values, and apply spreadsheet formula to dynamically calculate them! It would be much harder to track down and apply a formula to each “bowEnd” property in the outline format.
(Shown here as comma separated values since the headers contain spaces and that would be confusing.)
The top row is a series of columns of tokens, including [ and ] for arrays, { and } for objects, and keys and types for the object and array elements. It’s like a simple horizontal schema that specifies the JSON structure, key names, types and columns, so that nothing needs to be repeated, and no space is wasted.
table
,{ bowStart number,bowEnd number,bowHeight number,startWidth number,endWidth number,widthMultiplier number,fromLocalOffset { x number },toLocalOffset { x number },lineRenderer/startColor { r number,g number,b number },lineRenderer/endColor { r number,g number,b number },lineRenderer/alignment string,lineRenderer/widthCurve { animationCurveType string,keys [ { time number,value number }, …
,0,0.8,30,1,0,5,6,-6,0.8782983063,0.7984165253,0.0370873959,0.7169641118,0.7843719274,0.3921475355,View,Keys,0,1,0.25,0.2,0.5,0.5,0.75,1,1,0,Blend,0,1,0.25,0.5,0.5,1,0.75,0.5,1,1,0,0.9368487271,0.6433703118,0.198860128,0.25,0.4861432977,0.5704963395,0.6107422953,0.5,0.9640410996,0.08846161285,0.05927839517,0.75,0.1199717053,0.2262674866,0.7876422776,1,0.6955264667,0.01858220912,0.7418451801
…—
You can write comments and intermediate values and formulas off to the right of the JSON data, or in other spreadsheets, which the JSON data may ignore or depend on.
The spreadsheet gives you enormous amounts of power to dynamically process the resulting JSON! So you can publish spreadsheets connected to live data, and import them as JSON.
Some people laugh at me when I say I prefer spreadsheets to outliners or even text editors. I’d love it if there were a decent tree structures JSON editor as powerful and ubiquitous as Google Sheets, but I’ve never heard of one. Tell me if you have, please!
Userland Frontier (later Radio Userland) came close to that ideal, predating and then adapting to both XML and JSON, but it never quite hit the mark of what you can easily do with a spreadsheet.
Philosophy
The goal is to leverage the spreadsheet grid format to reduce syntax and ambiguity, and eliminate problems with brackets, braces, quotes, colons, commas, missing commas, tabs versus spaces, etc.
Instead, you enjoy important benefits missing from JSON like like comments, rich formatting, formulas, and the ability to leverage the spreadsheet’s power, flexibility, programmability, ubiquity and familiarity.
I’ve tried to apply the The Zen of Python to the design:
Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren’t special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one — and preferably only one — obvious way to do it.
Although that way may not be obvious at first unless you’re Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it’s a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea — let’s do more of those!
Before coming up with a name, one question to answer is how to position these ideas? A library? A convention? A standard? A general purpose idea that could be implemented in many different ways?
For example, Markdown is a simple but not-so-well-defined standard that suffers from not actually being standard across its many different implementations, each interpreting it differently and supporting different features.
And there’s also the general problem of CSV and TSV spreadsheets being almost but not quite entirely incompatible.
I think there’s some value in defining a minimal set of “tags”, and allowing extension tags (even a macro language that lets you define your own extension tags, much in the same way OpenLaszlo lets you define classes that you can use as xml tags).
I have stuck very closely to JavaScript nomenclature (“number”, “object”, “boolean”, etc). But not completely resisted the temptation of defining synonyms (“float” is a synonym for “number”, but there is also “integer” (but not a synonym “int”) just because those actually perform different type conversions (parseFloat -vs- parseInt). But there are no gratuitous synonyms for shorthand like “bool” for “boolean”, “dict” for “object”, etc.
I’m developing a Google Sheets extension that will provide pop-up menus, colored syntax highlighting, and error feedback, as well as tools and documentation to help people learn to read and edit JSON in the spreadsheet.
I’d eventually like to develop it into a macro templating engine for JSON (or higher level structures that happen to be represented as JSON). Kind of like Genshi (the XML/Python templating system that I’ve used a whole lot and love for its simplicity).
Genshi’s predecessor was an XML templating system named Kid, and they are more-or-less compatible. Genshi and Kid are similar to but much simpler and better than the venerable Zope Page Templates with its “TAL” (Template Attribute Language) and “METAL” macros. What they all have in common is that they used XML namespaces with attributes and tags to mark up XML templates, with special attributes and tags for looping, conditionals, function and template definitions, etc.
But normal JSON doesn’t give you a way to mark up JSON structures with “out of band” information the way XML does, which Genshi depends on.
However, by representing JSON in a spreadsheet, it becomes possible to represent metadata and out of band markup like control structures, by using adjacent cells off to the left or right, or even whole rows!
So you can wrap conditional and repeated rows in “if” and “loop” constructs, that are dynamically evaluated on the fly to produce the JSON! And you can define and call named templates with parameters, of course.
The challenge is to come up with a simple, convenient syntax for representing JSON templates in spreadsheets, that’s sleek and transparent like Genshi, but not clumsy and unwieldy like Zope Page Templates.
