Synthoid / ExportSheetData

Add-on for Google Sheets that allows sheets to be exported as JSON or XML.
MIT License
234 stars 46 forks source link

Added a solution for Dictionaries with nested objects #157

Open Garfield1002 opened 1 year ago

Garfield1002 commented 1 year ago

A proposed solution for using dictionaries with nested objects

Corresponding issue: https://github.com/Synthoid/ExportSheetData/issues/151

Implemented solution

A new header type surrounded with [{}]. This will create a new object names after the cell content rather than the column title.

Usage

We can now address the issue raised in 151 with this new header

[{Class}] [{Class}]PSIPL [{Class}]SMCSLU
Class A 9 2
Class B 6 4
Class C 4 6
Class D 3 8
Class E 1 10

Outcome (All examples are done with only the Nested Elements options enabled):

{
  "Sheet1": {
    "Class A": {
      "PSIPL": 9,
      "SMCSLU": 2
    },
    "Class B": {
      "PSIPL": 6,
      "SMCSLU": 4
    },
    "Class C": {
      "PSIPL": 4,
      "SMCSLU": 6
    },
    "Class D": {
      "PSIPL": 3,
      "SMCSLU": 8
    },
    "Class E": {
      "PSIPL": 1,
      "SMCSLU": 10
    }
  }
}

Advantages

We can take this a step further and are able to rework the advanced country example to now include different objects as well as lists

[{Continents}] [{Continents}][{Countries}] [{Continents}][{Countries}][Cities]{#Name} [{Continents}][{Countries}][Cities]Population
North America Canada Toronto 2615000
North America Canada Vancouver 603500
North America Canada Montreal 1650000
North America United States New York 8273000
North America United States Chicago 2697000
North America United States Los Angeles 3849000

Output

{
  "Sheet1": {
    "North America": {
      "Canada": {
        "Cities": [
          {
            "Name": "Toronto",
            "Population": 2615000
          },
          {
            "Name": "Vancouver",
            "Population": 603500
          },
          {
            "Name": "Montreal",
            "Population": 1650000
          }
        ]
      },
      "United States": {
        "Cities": [
          {
            "Name": "New York",
            "Population": 8273000
          },
          {
            "Name": "Chicago",
            "Population": 2697000
          },
          {
            "Name": "Los Angeles",
            "Population": 3849000
          }
        ]
      }
    }
  }
}

Code changes