Representing and Editing JSON with Spreadsheets (2018)

I once came up with a system that worked pretty well for an array of mostly similar objects. I use a jsonpath like column names, so putting the values A and B in a column named "some.property.items[0]" results in

    [
      {"some": {"property": {"items":["A"]}}},
      {"some": {"property": {"items":["B"]}}}
    ]

To avoid very long column names, values starting with "[" or "{" are parsed as json, so putting {"a": 1} in a column named C results in [{"C":{"a": 1}}]. Doesn't cover all edge cases, but worked well enough form my use case, which was providing a batch upload interface for a giant json (instead of a bunch of web forms) for excel savvy power users, allowing them to batch upload semi-structured data. I used jsonschema for validation on submit, worked well enough.

I do think this can be useful for collaboration with non technical users. At an old job we did our translations in google sheets, but the copy on our website was in JSON. So I wrote a script to load the CSV, split it up, then add quotes and commas to make it valid JSON.

The one step I never automated was the export to CSV from google sheets. It might be possible but they’re API was a bit opaque. Maybe it’s easier now

Curiously enough, the page does not show any example of the resulting JSON.