JSON to Google Sheets importer written in JavaScript and Java
Posted by jimblackler on Jun 13, 2021
I’ve written about how much I like JSON before. It’s a really convenient way to store and transmit data across many different applications.
I also like Google Sheets, but while you can import CSV data, it’s not easy to import JSON data. This is a shame because I use tools that output data in JSON that would be really convenient to view in Sheets.
I didn’t like any of the workarounds I could find on the web, so I wrote my own. To make it as easy as possible for users, it uses the JavaScript Google Sheets API which means that the whole app can run in a website easily, and my App Engine site doesn’t have to deal with any user data (Authentication is done on the server because the client side authentication isn’t working well with browsers that prohibit third party cookies.) The application is already online, hosted on App Engine at https://jsonworkspace.appspot.com/
The main requirement is that the data is formatted as a JSON array. Each element will be converted to a Google Sheets row, with the columns set from the dictionary keys found in object. For example, pasting the below code into the form..
[
{
"Title": "The Godfather",
"Year": 1972,
"Stars": ["Marlon Brando", "Al Pacino", "James Caan"]
},
{
"Title": "Gone With the Wind",
"Year": 1939,
"Stars": ["Clark Gable", "Vivien Leigh"]
},
{
"Title": "Jaws",
"Year": 1975,
"Stars": ["Roy Scheider", "Robert Shaw", "Richard Dreyfuss"]
}
].. would result in the following Google Sheet being created for the user when the Import button was pressed.
You can see in the above example how an embedded array (”stars’) was converted into named sub-columns in a process I call flattening. A similar process is used on dictionaries. This means that a variety of JSON documents can be imported; the importer is always able to convert the data to a 2D collection of cells. The only limit is the Google Sheets limits on the number of rows and columns.
I hope people find it useful. Feel free to contact me at jimblackler@gmail.com with any queries or comments.
The source is offered under an Apache License 2.0 and is available at https://github.com/jimblackler/jsonworkspace