Report Styles

The API Connector add-on for Google Sheets provides four report style options for converting JSON responses into a tabular format for Google Sheets.

report-styles-img1

CONTENTS

Let’s consider the following simplified example, with 2 orders each containing 2 products. The underlying JSON looks like this:

{
  "orders": [
    {
      "order_id": "order123",
      "products": [
        {
          "id": 111,
          "name": "Product A"
        },
        {
          "id": 222,
          "name": "Product B"
        }
      ]
    },
    {
      "order_id": "order456",
      "products": [
        {
          "id": 333,
          "name": "Product C"
        },
        {
          "id": 444,
          "name": "Product D"
        }
      ]
    }
  ]
}

DEFAULT (SINGLE ROW)

The default (single row) report style will produce separate columns for the nested product data, each identified with a number. This ensures that all values can be associated with their parent value (in this case, the order ID).

report-styles-img3

COMPACT

The compact report style will list all associated values in a single column, and will return data into the first available empty cell. This breaks the association with the parent element (the order ID) but is convenient when you’re primarily interested in counting, processing, or matching up elements within the nested line items themselves, since the data will be displayed in a single column rather than split among separate columns. It can also be useful if you’re NOT interested in those nested elements, since it will produce fewer columns to work around.

report-styles-img4

CONCATENATE

The concatenate report style will concatenate all the elements of each nested object together. This will produce a super-compact report with all nested elements in a single cell.

report-styles-img5

If you want to split pipe-separated cells back out again in Sheets, you can use a formula like this: =split(B2,"|").

GRID

The grid report style is similar to compact style in that it will keep each header field in its own column rather than flattening it out to separate columns. However, unlike compact style, it copies down each parent element to retain the relationship with its nested JSON elements.

Grid style is the only report style that changes the underlying data rather than just reconfigure the way it’s displayed.

NOTES ON JSON CONVERSION TO SHEETS

JSON is a format for transferring text values using the following data structures:

  • Objects, which 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.
    { "symbol": "SPY", "name": "SPDR S&P 500", "price": 289.8 }
  • Arrays, which are ordered list of values. They start and end with brackets, and each value is separated by a comma.
    [ 
    { "symbol": "SPY", "name": "SPDR S&P 500", "price": 289.8 }, 
    { "symbol": "AAPL", "name": "Apple Inc.", "price": 193.32 } 
    ]

API Connector includes built-in ‘JSON to csv’ logic to flatten and convert these JSON responses into a tabular format for Google Sheets. However, some APIs are designed with formats that, say, nest all data objects into a parent object, or push keys and values into a single array. In those cases API Connector has no way to recognize which nested values are primary dimensions vs. attributes, so you may see values returned as separate columns when you’d prefer to see them as rows.

If you encounter this type of issue, you should experiment with the four provided report style algorithms to find the one that works best, and then leverage Sheets formulas to reconfigure the data as desired.

3 thoughts on “Report Styles”

  1. HI this has been really helpful. but I was wondering for multiple orders is it possible if it can merge the cells. That way it becomes really easy to read multiple products in an order.

    That way when we use banded rows its very easy to read.

    Reply
    • The trouble with merging cells is that there are 20,000+ different APIs, all with different JSON structures, and API Connector has no way to know when it’s products or IDs or something totally different that doesn’t make sense to be merged. With that said, I’m very close to releasing a new report style that should help! It will copy down outer elements to form a grid, so in this example, the order ID will be copied down for each product in the order. I think that will address your request.

      Reply

Leave a Comment