SubRowColumns

The subRowColumns option in the SheetFast API serves a specific function during the data transformation process, particularly when dealing with nested or hierarchical data structures. Here's a detailed explanation of what it does:

subRowColumns Option

The subRowColumns option is used to specify columns that contain nested data (typically lists or arrays) that should be expanded into separate rows. This is particularly useful when you have data that includes sub-items or detailed records within a single cell, and you want to flatten these nested structures into a tabular format.

How It Works

1. Identification of Nested Columns:
   • The columns specified in the subRowColumns option are identified as containing nested data.
2. Explosion of Nested Data:
   • The nested data within these columns is exploded, meaning that each element in the nested structure is expanded into its own row. This transforms the nested structure into a flat tabular form where each nested element gets its own row.
3. Data Alignment:
   • When the nested data is expanded, other columns in the original row are duplicated to align with the expanded rows. This ensures that each new row created by exploding the nested data maintains the context provided by the other columns in the original row.

Example

Consider the following JSON data structure:

{
  "name": "John Doe",
  "projects": [
    {
      "projectName": "Project A",
      "duration": "3 months"
    },
    {
      "projectName": "Project B",
      "duration": "6 months"
    }
  ]
}

If projects is specified in subRowColumns, the data will be transformed as follows:

name	projectName	duration
John Doe	Project A	3 months
John Doe	Project B	6 months

Usage in Code

When configuring the subRowColumns option in the payload, it might look like this:

{
  "options": {
    "subRowColumns": ["projects"]
  },
  "sheets": [
    {
      "name": "Example Sheet",
      "rows": [
        {
          "name": "John Doe",
          "projects": [
            {
              "projectName": "Project A",
              "duration": "3 months"
            },
            {
              "projectName": "Project B",
              "duration": "6 months"
            }
          ]
        }
      ]
    }
  ]
}

This configuration tells the API to process the projects column by expanding its nested data into separate rows in the resulting spreadsheet.

Handling Multiple subRowColumns

You can specify multiple columns in the subRowColumns option to be exploded. This is useful when you have multiple nested arrays within your data that need to be expanded into separate rows. However, it's important to note that all specified sub-arrays must be of the same size. This ensures that the expansion process aligns the data correctly across the new rows.

Example

Consider the following JSON data structure with multiple nested arrays:

{
  "name": "Jane Smith",
  "projects": [
    {
      "projectName": "Project X",
      "duration": "4 months"
    },
    {
      "projectName": "Project Y",
      "duration": "5 months"
    }
  ],
  "tasks": ["Task 1", "Task 2"]
}

If both projects and tasks are specified in subRowColumns, the data will be transformed as follows, provided both arrays are of the same size:

name	projectName	duration	tasks
Jane Smith	Project X	4 months	Task 1
Jane Smith	Project Y	5 months	Task 2

This configuration tells the API to process both the projects and tasks columns by expanding their nested data into separate rows. If the sub-arrays are not of the same size, an error will be triggered.

subRowDroppedColumns Option

The subRowDroppedColumns option specifies which columns should be dropped (removed) from sub-rows after the specified subRowColumns are exploded. When a column is listed in subRowDroppedColumns, it will only appear in the first row of each exploded group. This is useful for columns that contain repetitive or redundant information that does not need to be duplicated across multiple rows.

Example

Given the input payload:

{
  "sheets": [
    {
      "name": "Sample Sheet",
      "rows": [
        { "id": 1, "values": [10, 20], "category": "A", "drop_me": 100 },
        { "id": 2, "values": [30, 40, 50], "category": "B", "drop_me": 200 },
        { "id": 3, "values": [60], "category": "C", "drop_me": 300 }
      ],
      "options": {
        "headers": {
          "id": "ID",
          "values": "Values",
          "category": "Category",
          "drop_me": "Drop Me"
        },
        "title": "Sample Data",
        "subRowColumns": ["values"],
        "subRowDroppedColumns": ["drop_me"]
      }
    }
  ]
}

Data after Exploding values and Dropping drop_me in Sub-Rows

ID	Values	Category	Drop Me
1	10	A	100
1	20	A
2	30	B	200
2	40	B
2	50	B
3	60	C	300