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.