Search API Connector Documentation

Print

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 report style options for converting these nested JSON responses into a tabular format for Google Sheets.

report-styles-img1

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 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-img2

Note that 'single row' style doesn't mean that ALL data will be printed into a single row, it means that each element of an array will get its own row.

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

report-styles-img5

Grid style option: Unwind data

When there are multiple layers of nested objects, API Connector may not always identify and repeat all parent elements, which can result in empty cells within the response set. To resolve, tick the "unwind data" checkbox to apply a potentially slower but more powerful flattening algorithm.

Style 3: 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-img3

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.

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

Notes on converting JSON to Sheets

  • When you update the report style, you will be prompted to refresh any saved field mapping you've set in the field editor.
  • 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 you may see values returned as separate columns when you'd prefer to see them as rows. If you encounter this type of issue, experiment with the four provided report style algorithms to find the one that works best, and then leverage Sheets formulas and/or JMESPath expressions to reconfigure the data as desired.

9 thoughts on “Set Report Style”

  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
    • 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
  2. How we go about obtaining data only into a cell and dropping the header? I'd like to use it to update other formulas, but the header is problematic to that. Orders.order_id, and orders.product (from your examples) wouldn't appear in the top row (or at all), is what I'd like to do. Starting off and learning, so I apologize in advance if it's pretty basic.

    Reply
      • Hello Ana,

        Thank you for all the kind help. On this subject, it seems I'm not able to delete the text and leave a blank header using the editor. Any advice?

      • That's odd, I just tested and confirmed it still works on my side. Can you please make sure you enter a blank space before hitting Save?

  3. Hi Ana,

    I am assuming there is a bug in the report style function.
    Grid and compact style giving me same style result which is compact.

    Reply
    • It shouldn't be the same, but there may be something about the original JSON that makes our parser unable to produce the desired structure. If you'd like to send some sample JSON to support I can take a look, we may be able to resolve the issue with a JMESPath expression.

      Reply

Leave a Reply to Ana Cancel reply

Jump To...