Recently I've been working on kind of the converse of this problem with JSON and spreadsheets, and I'll briefly describe it here (and I'll be glad to share the code), in the hopes of getting some feedback and criticism:
How can you conveniently and compactly represent, view and edit JSON in spreadsheets, using the grid instead of so much
punctuation?
The goal is to be able to easily edit JSON data in any spreadsheet, copy and paste grids of JSON around as TSV files (the format that Google Sheets puts on your clipboard), and efficiently import and export those spreadsheets as JSON.
It's especially powerful with Google Sheets, since you can run JavaScript code in them to import, export and validate JSON, and other apps can easily retrieve those spreadsheets as TSV files, which are super-easy to parse into 2D arrays of strings to convert to JSON.
This is a new reimplementation of some old ideas I've been happily using for years with Python and CSV files, plus some new ideas. But now it's general purpose, JSON oriented, written in JavaScript, integrated with Google Sheets, and I'm releasing it as open source once I get it into working shape.
Here is an example spreadsheet, which includes several sheets of different structures, and a script to convert the spreadsheet to JSON.
The first sheet "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 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 just 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 1
tileColumns number 2
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:
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.
Also, there are 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:
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 themselves may be calculated by spreadsheet formula, which call any function or refer to any cells in any other sheet. In this example, each tile name is calculated by randomly choosing from a range pointing to all the hex tile names in the prefabMap:
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.
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
How can you conveniently and compactly represent, view and edit JSON in spreadsheets, using the grid instead of so much punctuation?
The goal is to be able to easily edit JSON data in any spreadsheet, copy and paste grids of JSON around as TSV files (the format that Google Sheets puts on your clipboard), and efficiently import and export those spreadsheets as JSON.
It's especially powerful with Google Sheets, since you can run JavaScript code in them to import, export and validate JSON, and other apps can easily retrieve those spreadsheets as TSV files, which are super-easy to parse into 2D arrays of strings to convert to JSON.
This is a new reimplementation of some old ideas I've been happily using for years with Python and CSV files, plus some new ideas. But now it's general purpose, JSON oriented, written in JavaScript, integrated with Google Sheets, and I'm releasing it as open source once I get it into working shape.
Here is an example spreadsheet, which includes several sheets of different structures, and a script to convert the spreadsheet to JSON.
https://docs.google.com/spreadsheets/d/1nh8tlnanRaTmY8amABgg...
The first sheet "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 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 just write your JSON expressions as an indented outline with type names before the values, putting keys, types and values in separate indented cells.
You can use the "sheet" pseudo-type to make references to objects, arrays, etc, defined in other named sheets of the same spreadsheet: There's another sheet named texturePaths that contains an array of strings: 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.Also, there are 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:
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 themselves may be calculated by spreadsheet formula, which call any function or refer to any cells in any other sheet. In this example, 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)))
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.
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).
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.
...