Representing and Editing JSON with Spreadsheets (2018) | Hacker News Reader

Representing and Editing JSON with Spreadsheets (2018)

medium.com

76 pointsfanf224 comments

When I see things like this, I try to think about:

  - this relative to my existing tools
  - think about what tools you I wish I had
  - think about what tools that other people from other disciplines wish they had

Tools and habits and thinking patterns are interwoven. Changing tools might seem, in some cases, to be a waste of time, relative to a rigid, short-term performance metrics. However, changing tools can change your thinking patterns, which can really help with long-term growth.

Totally this.

SpreadON (which I'm now calling this tool) comes from working with people who are quite comfortable and proficient with spreadsheets, use them for modeling, calculating, and storing data, and have a large investment in certain tools. I didn't want to make them change the way they worked or switch from using their favorite tools (like Excel), but I wanted to be forward-compatible with other tools (Google Sheets). And I definitely didn't want to ask them to write raw Python or JSON data structures.

I came up with seed of the idea more than a decade ago, working on a nutritional analysis and questionnaire system, which was configured with huge tables of structured data about nutritional ingredients, questions, and other structures, each including numbers, arrays, and dictionaries, all with the same structure.

We needed to be able to represent what is essentially a whole bunch of identical JSON structures in a spreadsheet, in a way that was compact, easy to produce, edit, and check.

So I came up with a dead simple way of formatting the spreadsheet headers to declare nested dictionary and array structures with numeric values, and implemented a parser in Python that read a CSV file and returned an array of nested Python dicts and arrays and numbers. (This was before JSON became so popular, but Python data structures are essentially the same as JSON, just calling them dicts instead of objects).

After the row of headers, each subsequent row represents a dict, and the corresponding headers were either names of the dict keys, or special markup like "begin array <name>", "end array <name>", "begin dict <name>", and "end dict <name>". (The name was optional if you were inside an array, but would typically be the numeric array index). The columns under the begin/end array/dict headers were left blank.

That worked great, and we're still using it. But using spreadsheets to represent repetitive structured data did (as you say) change my thinking patterns, which led me to implement a more advanced version that let you represent JSON data types, by adding a type after the dictionary key name (or array index), so the parser knew what type to convert the spreadsheet cell string to. (I object to guessing like YAML, or even parsing JSON syntax: just write the data type declaration once, then you don't have to use punctuation like quotation marks around strings, etc.)

SpreadON has a tag called "table" that implements this idea of DRY-ly and compactly representing repeated structures by declaring key names, dictionaries, arrays and structures in the headers, but it has a more convenient JSON-ish syntax using [ ] { } instead of begin/end array/dict, and it lets you interleave the structural syntax with the name and data type declarations in the headers, so it doesn't waste valuable columns on structural declarations (a disadvantage of the previous technique). But you can still leave blank columns, if that makes it easier to read and edit, and you can even use them for comments.

But there are also many cases when you want to represent structured but non-repetitive JSON data in spreadsheets, so I came up with a tree-like syntax for that, which is basically like pretty-printing typed JSON into the cells of the spreadsheet, but is more repetitive (declaring the data type for each cell, instead of guessing it like YAML), leaves more unused space (but which can be used for comments), and is not as compact and easily editable as rows and columns as the "table" representation. It's really great for writing JSON configurations of nested dictionaries, where keys aren't typically repeated, values have different types, and you might want to add comments or intermediate calculation formulas in the unused cells to the right.

I also implemented a "grid" tag for representing two-dimensional arrays of identically typed values, so you don't have to write out nested arrays, or repeat the data types for each cell. Besides not having to repeat the same data type for every cell, there are many obvious advantages to representing data as dense two-dimensional grids in spreadsheets (it's easy to edit, use formulas, and integrate with other data sources), so I wanted to support that well.

An elaboration of the "grid" tag is the "region" tag, which points to a named region by name, and declares it a data type. That lets you store the raw data in another sheet independently, easily adding rows and columns, without requiring you to insert and delete rows and columns in tree-structured sheets of JSON referring to it. You can also extract rows and columns and cells of a named region, transpose the region, flatten the region to row major or column major 1-dimensional arrays, get the number of rows and columns, etc.

Spreadsheet users are accustom to working with named regions, and also to being able to format the cells in various ways, which can be useful meta-data in itself. However, when you export a spreadsheet as a CSV/TSV file (or download a Google Sheet via its URL as CSV/TSV), you lose all of the named regions and formatting information.

To solve that problem, I wrote a Google Sheets script that exports all the named regions, as well as any layers of formatting information you're interested in, into parallel sheets (with corresponding named regions) that you can download. That way, the JSON consumer has access to all of the named regions as well as the formatting information from the spreadsheet! The named regions are necessary in order for the client to convert the CSV file into JSON, if it uses any "region" references. But they can also be used directly. The formatting information can be converted into parallel 2D JSON arrays with the "region" tag, and used along with the numeric or string values of the cells.

It eventually became obvious that there needed to be a better way to automatically define the named regions in spreadsheets, instead of creating and maintaining them by hand. So I made a way of defining user-defined named tags, which you could place next to regions you wanted to capture, which it could scan for empty cells as delimiters, and it will create named regions of rows and columns and grids relative to the position of the user-defined named tag. It also lets you capture named regions of formatting information. The user-defined tags and the regions they produce are defined in another sheet in "table" format, so you can create and edit them easily. (Kind of hard to explain here without an example, but it looks pretty obvious, works well in practice, and has been quite useful.)

This has all been the result of sitting down with the people who know how to use the tools, and finding out how they use them, which features are important, what they're willing to do, and discovering a fertile common ground between the worlds of spreadsheets and JavaScript/Python programming where we can both work together.

I made a Google spreadsheet add-on [1] that contains 3 built-in options for converting nested JSON to tabular format.

Method 1: drills into nested json objects and arrays and returns the key names as headers, key values as rows. In cases with multiple nested values, they get returned into multiple columns, differentiated by a number, e.g. orders > products > 1, orders > products > 2.

Method 2: same as above but returns all nested data into a single column, e.g. a single column named orders > products. This can break the association between JSON elements, but is more convenient for certain types of analysis.

Method 3: concatenates all the elements of each nested object into a single cell, separated with pipes.

In some cases (e.g. when the primary object of interest is nested inside another object) there are still problems recognizing what keys should represent rows vs columns, but in my tests the above 3 algorithms cover most of the use cases for spreadsheets.

[1] https://mixedanalytics.com/knowledge-base/report-styles/

You could put the nested data into a separate sheet with a unique identifier for the row. Then include a foreign key back to the original sheet's primary id.

Interesting idea, thanks. There's often multiple levels of nested data, would you want to see each level in a new sheet?

Absolutely! It's relational data that could map to a defined relational schema, so why not?

As I described on another reply, SpreadOn not only supports a tree structured format, but also has tags for referring to entire sheets by name, for declaring grids of identically-typed data, for referring to named regions and extracting subsets of them, and compactly defining tables of repeating structures, which you can mix on a case-by as they make sense. (You can't embed a grid in another grid since it requires more than one cell, but you can make a grid of sheet references, since the sheet name only takes one cell and can be put into a grid.)

There is no "one best way" to represent JSON data in spreadsheets, because JSON data comes in all sizes and shapes, so SpreadON tries to support many different useful formats that you can link together, with a simple straightforward syntax that can be easily extended without breaking existing documents.

Haha I did something similar at my company. Our customers are heavy spreadsheet users, so decided to create a generic Angular component that can take semi-arbitrary json and display it in a spreadsheet (handsontable).

The code flattens a deeply nested json object into a bunch of 'rows', similar to Spark's explode or Mongo's unwind.

The objects representing the flattend rows have their getters and setters hooked to the original json object. That way when you edit the spreadsheet the json is automatically updated, with parent values shown as merged cells for array child values.

The code was completely bonkers and I ripped it out later...but must say it was fun to write and see it doing its thing

This looks awesome! Fun timing, too - I'm working on a related problem, trying to unlock multidimensional data in a similar way to how the spreadsheet unlocks data for non-technical stakeholders. So many businesses run on spreadsheets for good reason, but it's getting rare to be able to cleanly crush a data dump into a CSV and let an analyst or exec crunch away on it.

I'm sure it's confirmation bias, but I've been seeing tons of stuff on HN related to what I've been tinkering on, and it's really awesome to see other potential competitors thinking about the same problem and know I'm not way off in left field. :)

When using VBA and Excel, I've found it easiest to just stick to XML for my REST calls and not try to deal with JSON. Talk about adding features to JSON to make it richer just makes me think everything goes around in circles.

What I'd like to see is an LLVM back-end that generates VBA, but I may never get around to it myself.

Everything goes around in circles because we hit walls and obstacles, work around them and move on. Then at a later date, when we hit new walls and obstacles, we re-visit old areas, many times to find that they are no longer problems (faster hardware, more network capacity, algorithmic breakthroughs), and so the industry half-pivots in a new direction.

Everything old is new again. Wash, rinse, repeat.

I've meant to contribute to this discussion and answer questions, but this article is more than a year old, and I've been it regularly and made lots of progress since then, I feel I should writing a fresh new article about it, including some actual documentation and examples.

I'll post it to HN as soon as I'm done, and put a link to it here. And I would appreciate hearing from anyone who's interested in or working on similar ideas, so we can collaborate to combine our forces! (Email in profile.)

It also needs a good name! (As a rule of thumb, I try not to get bogged down in naming things when I should be spending my time actually working on them and using them, to figure out what they actually are before naming them. But it's been more than a year and now I want to talk about it more mellifluously.)

I was calling it "JSONster", but that sounded too monstrous, don't describe it, and nobody gets the Napster reference these days.

So I tried to think of better name by repeatedly pounded my fist against my forehead, which gave me a headache, when suddenly a homeopathic solution to my naming problem sprang to mind: "SpreadON", short for "Spreadsheet Object Notation".

Elevator Pitch:

SpreadON applies JSON to spreadsheets like butter to waffles, so it melts into the nooks and crannies.

Marketing Ploy:

SpreadON: Apply directly to the spreadsheet. SpreadON: Apply directly to the spreadsheet. SpreadON: Apply directly to the spreadsheet.

https://www.youtube.com/watch?v=f_SwD7RveNE

https://en.wikipedia.org/wiki/HeadOn

>HeadOn is the brand name of a topical product claimed to relieve headaches. It achieved widespread notoriety in 2006 as a result of a repetitive commercial, consisting only of the tagline "HeadOn. Apply directly to the forehead", stated three times in succession. Originally sold as a homeopathic preparation, the brand was transferred in 2008 to Sirvision, Inc., who re-introduced the product with a new formulation.

>HeadOn's notoriety came in part because of its advertisements on cable and daytime programming on broadcast television which consisted of using only the tagline "HeadOn. Apply directly to the forehead", stated three times in succession, accompanied by a video of a model using the product without ever directly stating the product's purpose.

Damn. I was going to do this soon. Guess I'll just have to do it even harder...

EDIT: I already do this a ton at work, but I haven't yet made a tool for going from json to srpeadsheet.

How are you doing it at work? Would love to pick your brain about the workflows you're seeing for some customer research purposes :)

The route I was planning on going is more of an "someone smart sets it up for layman's" use case.

Basically, you outline the type defs for your json, then how you want that json to be displayed as csv.

I wrote some stuff in previous HN discussions about outliners and spreadsheets, and also some stuff about Dave Winer's Frontier, which I'll quote and link to here:

https://news.ycombinator.com/item?id=20773851

DonHopkins 44 days ago [-]

I love the collaborative features of Google Docs and Google Sheets.

The thing that's missing from "Google Docs" is a decent collaborative outliner called "Google Trees", that does to "NLS" and "Frontier" what "Google Sheets" did to "VisiCalc" and "Excel".

And I don't mean "Google Wave", I mean a truly collaborative extensible visually programmable spreadsheet-like outliner with expressions, constraints, absolute and relative xpath-like addressing, and scripting like Google Sheets, but with a tree instead of a grid. That eats drinks scripts and shits JSON and XML or any other structured data.

Of course you should be able to link and embed outlines in spreadsheets, and spreadsheets in outlines, but "Google Maps" should also be invited to the party (along with its plus-one, "Google Mind Maps").

More on Douglass Engelbart's NLS and Dave Winer's Frontier:

https://news.ycombinator.com/item?id=20425970

https://news.ycombinator.com/item?id=20780928

DonHopkins 43 days ago [-]

One thing an outliner lets you do that you can't do with something like Wave or a tree structured discussion group is to arbitrarily rearrange the tree.

You're right, you can represent tree-structured outlines in Word or Docs (or JSON in Excel or Sheets as I described here [1]), but it's clumsy and not well supported by the user interface.

[1] Representing and Editing JSON with Spreadsheets: https://medium.com/@donhopkins/representing-and-editing-json....

Where Frontier really shines is in its user interface and feature set, which makes navigating and creating and editing outlines very easy and efficient.

Frontier's main use was (tree structured) content management and scripting, and making websites is a popular application of that. It was extremely useful for making tools, and like Emacs, its power came from its extensibility.

Its pre-web predecessors [2] were ThinkTank (which started on the Apple ][ with a keyboard based interface) and MORE (which added a mouse-based drag-and-drop interface, that made it much easier to use without spoiling the ease of use of the keyboard interface, and also formatted graphics for making charts and slide shows).

[2] MORE (application): https://en.wikipedia.org/wiki/MORE_(application)

Beyond obvious stuff like content management, blogging, and scripting, I think there are many other killer applications of programmable outliners (just as emacs and spreadsheets have many applications), some old hat, and others undiscovered!

I wrote some more [3] about Dave Winer's work on Frontier, and linked to some screencasts he made that show how he uses it to organize his thoughts (about the history of outliners, in this case, which is beautifully self-referential).

I think the most important point that comes through in Dave's demos is that the operating system and user interface shell should support generic outlining and scripting at a very basic, built-in, ubiquitous level. But I believe Windows, OS/X, iOS and Android have a hell of a long way to go!

[3] https://news.ycombinator.com/item?id=20672970

Dave Winer's second outliner screencast:

https://www.youtube.com/watch?v=mgUjis_fUkk

Dave Winer's the many lives of Frontier screencast:

https://www.youtube.com/watch?v=MlN-L88KScw

Dave Winer on The Open Web, Blogging, Podcasting and More:

https://www.youtube.com/watch?v=cLX415mHfX0

>UserLand's first product release of April 1989 was UserLand IPC, a developer tool for interprocess communication that was intended to evolve into a cross-platform RPC tool. In January 1992 UserLand released version 1.0 of Frontier, a scripting environment for the Macintosh which included an object database and a scripting language named UserTalk. At the time of its original release, Frontier was the only system-level scripting environment for the Macintosh, but Apple was working on its own scripting language, AppleScript, and started bundling it with the MacOS 7 system software. As a consequence, most Macintosh scripting work came to be done in the less powerful, but free, scripting language provided by Apple.

>UserLand responded to Applescript by re-positioning Frontier as a Web development environment, distributing the software free of charge with the "Aretha" release of May 1995. In late 1996, Frontier 4.1 had become "an integrated development environment that lends itself to the creation and maintenance of Web sites and management of Web pages sans much busywork," and by the time Frontier 4.2 was released in January 1997, the software was firmly established in the realms of website management and CGI scripting, allowing users to "taste the power of large-scale database publishing with free software."

https://news.ycombinator.com/item?id=20425970

DonHopkins 85 days ago | parent | favorite | on: I was wrong about spreadsheets (2017)

The thing that's missing from "Google Docs" is a decent collaborative outliner called "Google Trees", that does to "NLS" and "Frontier" what "Google Sheets" did to "VisiCalc" and "Excel".

It should be like the collaborative outliner Douglass Englebart envisioned and implemented in his epic demo of NLS:

https://www.youtube.com/watch?v=yJDv-zdhzMY&t=8m49s

Engelbart also showed how to embed lists and outlines in maps:

https://www.youtube.com/watch?v=yJDv-zdhzMY&t=15m39s

Dave Winer, the inventor of RSS and founder of UserLand Software, originally developed a wonderful outliner on the Mac originally called "ThinkTank" and then "MORE", which later evolved into the "Frontier" programming language, and ultimately the "Radio Free Userland" desktop blogging and RSS syndication tool.

https://en.wikipedia.org/wiki/Dave_Winer

https://en.wikipedia.org/wiki/UserLand_Software

More was great because it had a well designed user interface and feature set with fluid "fahrvergnügen" that made it really easy to use with the keyboard as well as the mouse. It could also render your outlines as all kinds of nicely formatted and stylized charts and presentations. And it had a lot of powerful features you usually don't see in today's generic outliners.

https://en.wikipedia.org/wiki/MORE_(application)

>MORE is an outline processor application that was created for the Macintosh in 1986 by software developer Dave Winer and that was not ported to any other platforms. An earlier outliner, ThinkTank, was developed by Winer, his brother Peter, and Doug Baron. The outlines could be formatted with different layouts, colors, and shapes. Outline "nodes" could include pictures and graphics.

>Functions in these outliners included:

>Appending notes, comments, rough drafts of sentences and paragraphs under some topics

>Assembling various low-level topics and creating a new topic to group them under

>Deleting duplicate topics

>Demoting a topic to become a subtopic under some other topic

>Disassembling a grouping that does not work, parceling its subtopics out among various other topics

>Dividing one topic into its component subtopics

>Dragging to rearrange the order of topics

>Making a hierarchical list of topics

>Merging related topics

>Promoting a subtopic to the level of a topic

After the success of MORE, he went on to develop a scripting language whose syntax (for both code and data) was an outline. Kind of like Lisp with open/close triangles instead of parens! It had one of the most comprehensive implementation of Apple Events client and server support of any Mac application, and was really useful for automating other Mac apps, earlier and in many ways better than AppleScript.

https://en.wikipedia.org/wiki/UserLand_Software#Frontier

http://frontier.userland.com/

Then XML came along, and he integrated support for XML into the outliner and programming language, and used Frontier to build "Aretha", "Manila", and "Radio Userland".

http://manila.userland.com/

http://radio.userland.com/

He used Frontier to build a fully programmable blogging and podcasting platform, with a dynamic HTTP server, a static HTML generator, structured XML editing, RSS publication and syndication, XML-RPC client and server, OPML import and export, and much more.

He basically invented and pioneered outliners, RSS, OPML, XML-RPC, blogging and podcasting along the way.

https://en.wikipedia.org/wiki/RSS

https://en.wikipedia.org/wiki/OPML

https://en.wikipedia.org/wiki/XML-RPC

Is there something like this but for YAML?

I was going to say, this looks like YAML! Using blank columns for indentation.

Part of SpreadON's design philosophy is to completely avoid the guess-work of YAML, by explicitly declaring the data type as a type name tag. This also enables extension tags for other formats and features like grids of identically typed data, sheet and named region references, etc. And also to avoid the inconvenient syntax of JSON as much as possible.

It addresses the most-complained-about problems with JavaScript: no comments, and no trailing commas. You can use the unused cells to the right as comments (or even intermediate calculation formulas), and not only is there no inconsistency about not being able to put a comma after the last element of an array or object, but it actually doesn't require any commas at all, no quotation marks around string, no backslashes in strings, no spaces or tabs for indentation, nor any other syntactic syrup of ipecac that JSON requires but YAML foolishly tries to avoid by guessing. Each cell is a separate syntactic token, so you don't need commas or spaces or tabs to separate them.

Graph, not tree

JSON can only represent trees. JavaScript data can represent graphs, but JSON is a subset of that.

Reply on news.ycombinator.com