Parsing Options & Array Handling in Apipheny

Apipheny, an API connector for Google Sheets, offers flexible options for handling JSON arrays, allowing you to tailor data import to your specific needs.

Array Handling Options

When we have repeated strings/numbers in a JSON response, there are a few different ways to render them:

1. Across Columns: Each array element in one column.
2. Stack in a Row: Each array element in a separate row.
3. In One Cell: All the element arrays in the same cell.

For example, given the JSON response:

{
  "status": true,
  "data": [
    {
      "id": 1,
      "item": {
        "sku": "ZSDSFS23",
        "price": 100,
        "qty": 2,
        "tags": [
          "Cyan",
          "Green"
        ]
      }
    },
    {
      "id": 1,
      "item": {
        "sku": "ZSDSFS23",
        "price": 100,
        "qty": 2,
        "tags": [
          "Cyan",
          "Green"
        ]
      }
    },
  ]
}

Across Columns

Each element of the array is placed in a separate column within the same row.

We can see that each item under the data object has multiple tags. When we render with this parsing option we can see that each of the tags is given a separate column:

The advantage with this option is that we would be saving on the number of detailed rows since all the array elements are shown in a single row. The disadvantage with this rendering is that when there are too many array elements or the number of tags are different for each SKU, we would be having empty cells for some of the SKU’s.

• Advantages: Consolidates data into fewer rows
• Disadvantages: If arrays have varying lengths, some cells may remain empty

Stack in a Row

Each element of the array is placed in a separate row, duplicating the parent data for each element.

The array occupies only one column as shown below. So each array element occupies a different row.

• Advantages: Ensures all array elements are captured without empty cells.
• Disadvantages: Increases the number of rows, which may require additional data processing.

In One Cell

All elements of the array are combined into a single cell, separated by commas.

In this option all the array elements are shown as comma separated values in one cell.

This saves both columns as well as rows. It is a more compact option.

• Advantages: Provides a compact view, saving both columns and rows.
• Disadvantages: May require additional parsing if individual elements are needed later.

Compact the Grid

This feature determines how sibling arrays are displayed relative to their parent data.

Consider an API JSON output as below.

{
  "status": true,
  "data": [
    {
      "id": 1,
      "items": [
        {
          "sku": "AAAA_1",
          "qty": 101,
          "price": 24
        },
        {
          "sku": "AAAA_2",
          "qty": 102,
          "price": 25
        },
        {
          "sku": "AAAA_3",
          "qty": 10,
          "price": 240
        }
      ],
      "taxes": [
        {
          "name": "Federal tax",
          "amount": 12.5
        },
        {
          "name": "State tax",
          "amount": 8.5
        }
      ]
    },
    {
      "id": 2,
      "items": [
        {
          "sku": "BAAA_1",
          "qty": 101,
          "price": 24
        },
        {
          "sku": "BAAA_2",
          "qty": 102,
          "price": 25
        },
        {
          "sku": "BAAA_3",
          "qty": 10,
          "price": 240
        }
      ],
      "taxes": [
        {
          "name": "Federal tax",
          "amount": 12.5
        },
        {
          "name": "State tax",
          "amount": 8.5
        }
      ]
    }
  ]
}

For each order, we two groups of data. Items and taxes. So these two items are siblings under the same order id. When we do not choose the Compact the Grid option, we would see the data in below format.

The items and taxes are show one below the other. The advantage is that the data is less cluttered. But it does occupy more rows. 

When we choose the Compact the Grid option, the data shows up like so:

You can notice that the SKU’s and Taxes start on the same line as the order id. Each group is aligned to its parent. The advantage is that we get a more compact view of data. 

• Advantages: Less cluttered data representation.
• Disadvantages: Consumes more rows.

Inherit Values

Use this feature to inherit data from parent JSON elements to their child elements, so rows representing child elements contain the same values as the rows representing their parent elements.

In other words, enable this option to fill up all the empty cells in your spreadsheet. The empty cells will be replaced with the value from the previous row.

Empty cells can cause issues with reporting and pivot tables, so enabling this option can help resolve any issues like that.

Conclusion

By selecting the appropriate array handling and grid compaction options, you can optimize how JSON data is imported into your Google Sheets using Apipheny, ensuring it aligns with your data analysis and reporting requirements.