JSON Transformation to Rows/Columns

JSON Transformation to Rows/Columns

Last Updated On July 02, 2019
You are here:
< Back

The API Connector add-on for Google Sheets works with APIs that return results in standard JSON (JavaScript Object Notation) format. JSON is a format for transferring text values using the following data structures:

  • objects, e.g. { “symbol”: “SPY”, “name”: “SPDR S&P 500”, “price”: 289.8 }
  • arrays, e.g. [ { “symbol”: “SPY”, “name”: “SPDR S&P 500”, “price”: 289.8 }, { “symbol”: “AAPL”, “name”: “Apple Inc.”, “price”: 193.32 } ]

Objects are un-ordered collections of name/value pairs. They start and end with curly braces, and each name/value pair is separated by a comma.
Arrays are ordered list of values. They start and end with brackets, and each value is separated by a comma.

CONVERSION TO TABULAR FORMAT


As JSON API responses aren’t specifically designed for spreadsheets, the API Connector add-on uses logic to convert JSON responses into a tabular format for Google Sheets. The primary logic is to drill down into objects and arrays to the innermost key-value pairs. For each of these pairs, the key is printed as the column header, and the values populate each subsequent row. Also, to help keep associated values together, if an array contains only primitive values (no arrays or objects), they will be concatenated into a single cell. Below are some example JSON responses, in increasing level of complexity, showing more detail on exactly how this all works.

EXAMPLE 1

In this simple example, there is a key named ‘symbolsList’, where the value is an array containing 2 objects. Each object contains three key-value pairs (‘symbol’, ‘name’, and ‘price’).

Each key (‘symbol’, ‘name’, and ‘price’) becomes a column header, including the path to that key, e.g. symbolsList » symbol. The values are displayed in subsequent rows. This is the resulting Sheets output:

symbolsList » symbolsymbolsList » namesymbolsList » price
SPYSPDR S&P 500289.8
AAPLApple Inc.193.32

EXAMPLE 2

In this next example there are several objects containing a total of 6 key different inner keys.

Here, API Connector drills into the arrays and objects to surface each key (and its path) as its own column, e.g. posts » id. The values for each key are printed in the rows below. This results in a Sheets output like this:

posts » idposts » titlecomments » idcomments » bodycomments » postIdprofile » name
1Post 11some comment1typicode
2Post 22some comment1
3Post 3

EXAMPLE 3

Many APIs will fit into a format like the above, and return data neatly into Google Sheets. However, some get really complex. Now that we have the basic idea, here’s an example API that contains data in multiple nested objects and arrays of different sizes. API Connector’s logic will process it as above, but it won’t totally work in Sheets due to the structure of the source data.

The parsing code will drill into each nested key-value pair to display the keys into their own columns, and, as before, the values are shown on subsequent rows. However, in the first top-level object there are 3 values for ‘ingredients » malt » name’, but only 1 for ‘name’. Therefore the above would be returned as follows:

namevolume » valuevolume » unitmethod » mash_temp » temp » valuemethod » mash_temp » temp » unitmethod » mash_temp » durationingredients » malt » nameingredients » malt » amount » value
Paradox Islay20litres65celsius90Extra Pale4.50
Riptide25litres66celsius75Caramalt0.55
Carafa Special Malt Type 10.33
Maris Otter Extra Pale5.36
Dark Crystal 350-4000.71
Carafa Special Malt Type 30.18

This output visually links together some elements that actually don’t belong together (e.g. Caramalt is an ingredient of Paradox Islay, not Riptide). While there are some possible methods to handle this, all have their own drawbacks:

  1. change the output logic so the Riptide values don’t start until row 4.
    Issue: having big gaps in the data isn’t ideal for a spreadsheet, and this problem would be even more severe with very large data sets, where the subsequent sets of data would be pushed out of view entirely.
  2. copy down values so there are no gaps.
    Issue: duplicating data can have some unexpected side effects. For example, if we duplicated the values in ‘method » mash_temp » temp » value’, we’d have new issues when we tried to calculate averages on that field.
  3. display values as an array within a cell rather than on subsequent rows. In this case, it would mean a single cell containing [‘Extra Pale’, ‘Caramalt’, ‘Carafa Special Malt Type 1’] on row 1. The same thing would need to be done for the ‘ingredients » malt » amount » value’ key.
    Issue: Names & values are no longer in columns where they can be easily linked together. In addition, forcing certain elements to reduce their row count may not make sense at all in other APIs.

This case shows the current limitations of viewing JSON APIs in a spreadsheet paradigm.

Comments:0

Leave a Reply

Your email address will not be published.