
Structuring spreadsheets with the “lish” data model (2017) - open-source-ux
https://oro.open.ac.uk/50150
======
JadeNB
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.

~~~
JadeNB
The paper doesn't seem to explain why 'lish'. I originally thought 'list' \+
'hash', but that doesn't seem to be consistent with their definition of the
data model.

~~~
twic
> The model that we propose instead is called the “lish”. (The name is a
> portmanteau of “list” and “hash table” since in its fully specified form it
> shares some characteristics of both; it is the list-like properties that we
> focus on in this paper

~~~
JadeNB
> > The model that we propose instead is called the “lish”. (The name is a
> portmanteau of “list” and “hash table” since in its fully specified form it
> shares some characteristics of both; it is the list-like properties that we
> focus on in this paper

Thanks! I skimmed the paper, and obviously I missed that. (I dunno why I
didn't just search for 'hash'.)

------
mafm
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](https://arxiv.org/abs/1801.08603)

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

------
dustingetz
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]]]]

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

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

------
DonHopkins
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...](https://medium.com/@donhopkins/representing-and-editing-json-with-
spreadsheets-8f8832db5239)

>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...](https://github.com/SimHacker/UnityJS/blob/master/UnityJS/Assets/StreamingAssets/sheets.jss)

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...](https://docs.google.com/spreadsheets/d/1nh8tlnanRaTmY8amABggxc0emaXCukCYR18EGddiC4w/edit#gid=0)

