Set Report Style

Spreadsheets present data in a table, while APIs typically send back data in a tree-like structure containing nested JSON data. The API Connector add-on for Google Sheets provides four main report style options for converting these nested JSON responses into a tabular format for Google Sheets.

Contents

• Before You Begin
• JSON Example
• Style 1: Default (Single Row)
• Style 2: Grid
  • Unwind Data
  • Force Rows
  • Repeat Rows
  • Combine Simple Arrays
• Style 3: Compact
• Style 4: Concatenate

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

JSON Example

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"
        }
      ]
    }
  ]
}

Style 1: Default (Single Row)

The default (single row) report style produces 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).

Note that 'single row' style doesn't mean that all data must be printed into a single row; rather, it means that each element of an array gets its own row.

Style 2: Grid

The grid report style keeps each header field in its own column rather than flattening it out to separate columns.

Grid style enables several additional options: unwind, force rows, repeat rows, and combine simple arrays.

Grid style option #1: Unwind data

The "unwind data" option flattens more deeply nested data as well, creating new rows for each value in an array. When grid + unwind is selected, the output will look like this:

The "unwind" algorithm is described in more detail in the MongoDB documentation.

Grid style option #2: Force rows

The "force rows" option checks for the case in which all data is located within objects. One example would be
{"rates":{"btc":{"name":"Bitcoin","unit":"BTC","value":1,"type":"crypto"},"eth":{"name":"Ether","unit":"ETH","value":19.562,"type":"crypto"}}}

A second example would be
{"A":"2","B":"0","C":"2","D":"2"}

These data structures normally result in data displaying in a long row, with a new column for each nested element:

When "Force rows" is checked, API Connector will instead attempt to force data to display in rows by automatically converting these objects into arrays. The output will then look like this:

If the auto-detection doesn't work as expected, or you'd like to override it, use the text field to enter the path to the data (using dot notation) that should be displayed as rows instead of columns. In the first example above, the path would be written as rates, since rates is the name of the object that should be forced to display in rows. In the second example, the path would be a single period, i.e. . , since the object has no name.

Grid style option #3: Repeat rows

Repeat rows copies down the outermost element, filling in empty cells. In this way it is similar to the "unwind" data option, though it is a much less resource-intensive process as it doesn't create a new row for each combination of data. This can be a good solution for data sets that time out when "unwind" is selected.

When selected together, "Repeat Rows" + "Combine simple arrays" are equivalent to API Connector's deprecated grid mode.

Grid style option #4: Combine simple arays

This will reduce total data size by combining values from simple arrays into a single cell. An example of a simple array would be {"colors":["Green","Ginger","Coffee","Grey"]}

With this option set, these values will be displayed like this:

Without this option, each value will instead be expanded into its own row:

Style 3: Compact

Like grid style, compact report style lists 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.

Style 4: 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.