Structuring spreadsheets with the “lish” data model (2017)

Abstract: A spreadsheet is remarkably flexible in representing various forms of structured data, but the individual cells have no knowledge of the larger structures of which they may form a part. This can hamper comprehension and increase formula replication, increasing the risk of error on both scores. We explore a novel data model (called the “lish”) that could form an alternative to the traditional grid in a spreadsheet-like environment. Its aim is to capture some of these higher structures while preserving the simplicity that makes a spreadsheet so attractive. It is based on cells organised into nested lists, in each of which the user may optionally employ a template to prototype repeating structures. These template elements can be likened to the marginal “cells” in the borders of a traditional worksheet, but are proper members of the sheet and may themselves contain internal structure. A small demonstration application shows the “lish” in operation.

Site is down for me. Here's a link to what seems to be the same paper on arxiv:

https://arxiv.org/abs/1801.08603

This is a terrible idea. When someone gives me something like this I have to try and remember that it’s not their fault and that they were probably using some kind of oracle extract.

an example lish:

    [null,
     ["site id", null],
     [null,
     ["site address", " "],
     [["staff", 2015, 2016, 2017],
      ["assistants", null, null, null],
      ["supervisors", null, null, null],
      ["managers", null, null, null]]]]

Does this "lish" data model map into a spreadsheet with simple values or other spreadsheets as the cell values? I have been looking for a good tool to handle manipulation/editing/presentation for this kind of recursive data model.

The abstract mentions an example app but I couldn't find a link to try it.

Here's an article I recently wrote about a work in progress, representing JSON structures in spreadsheets.

https://medium.com/@donhopkins/representing-and-editing-json...

>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!

>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.

Since writing the article, I've used it for a lot of stuff, and added more features, which I hope to get around to writing more about some time! But I'll throw this out now, since it's kinda related to this article.

It now supports automatic style formatting in google sheets, so it shows the structures parsed from the spreadsheet. And it can represent the named regions as a sheet, so you can download the sheets as csv and refer to named regions by name (since they aren't represented in csv's directly, but are very useful for importing blocks of data with flexible numbers of rows and columns as JSON).

Here's the code that runs in both the browser and Google Sheets (implementing the additional formatting of the spreadsheet, and some other stuff).

The problem with scripting Google sheets from JavaScript is that it's very slow. The formatting code is an unoptimized proof of concept, that could be smarter about buffering and making all the formatting changes at once instead of issuing hundreds of tiny little remote procedure calls (which the Google sheets API boils down to) one by one. The one thing that's fast is downloading sheets as csv files, so the part of this code that runs in the browser does that, and parses the csv files into json in the browser instead of in google sheets (which is also implemented, but intolerably slow).

https://github.com/SimHacker/UnityJS/blob/master/UnityJS/Ass...

And here is a typical spreadsheet of JSON data I use to configure JavaScript driven Unity3D applications (check out the other sheet tabs too) -- the JSON spreadsheet stuff in totally independent of Unity3D and the UnityJS scripting extension, but they work great together:

https://docs.google.com/spreadsheets/d/1nh8tlnanRaTmY8amABgg...