Group same column names into array

[peterwarbo]

I have a sheet with a structure that looks like this:

Song | Year | Genre | Genre | Genre
Smells like teen spirit | 1991 | Punk | Alternative rock | Grunge

What I would like is to have a json that combines the genres to a single array like:

{
    "Song":"Smells like teen spirit",
    "Year": 1991,
    "Genre": ["Punk","Alternative rock","Grunge"]
}

Is this possible? Or is there another way to achieve what I'm trying to accomplish?

[peterwarbo]

I just noticed I can name the columns [genres]{#1} [genres]{#2} [genres]{#3} to achieve this but then I only get one object as a result when I have more than a 100 objects...

Guess this relates to #49

[Synthoid]

If you are using nested elements it is important that your keys have correct formatting to export properly. In this case, you need to make sure each key starts with {#SHEET}{#ROW}. Alternatively, you could use the Nested array prefix option on the sheet name to handle that for you automatically.

If the keys are not formatted correctly each row will overwrite the previous row as the sheet is exported.

[peterwarbo]

I'm not sure how to use {#SHEET}{#ROW} ?
I tried renaming the columns but couldn't get the desired result.

[Synthoid]

What settings are you using?

[peterwarbo]

JSON, Ignore empty cells, Nested elements

[Synthoid]

Try enabling the Nested Array prefix option under Advanced JSON and adding NA_ to the front of your sheet name. I think that will do what you want.

[peterwarbo]

Not able to get it to work :(

I've shared the document so you can see what might be wrong: https://docs.google.com/spreadsheets/d/1CQPgTgTzXgyHN9U259BGRAqayMe41Oh47L3BOn88C2c/edit?usp=sharing

[Synthoid]

I formatted your keys to get what I think is your desired output. Give that a try and let me know if things look right.

[peterwarbo]

Ah that did it. Thanks!

When I was fiddling around with it earlier I was able to get the "id" column as a key for the song object, but not anymore.

["1": {....}, "2": {...}]

Is this still possible with this approach?

[Synthoid]

If you want to use the ID field as a key for each row, you'll have to adjust your approach a bit. instead of using {#SHEET}{#ROW} for all of your keys, you'll want to preface them with {#id}.

For example: {#id}[Genres]{#1}

This format tells the key that its value should be mapped to the first element of an array called Genres inside an object with a name matching the id field in this row.

That should get you the desired output.

[peterwarbo]

Thanks for your quick response.

I tried doing above but wasn't able to get the desired results.

I prefixed id column with id ({#id}id) and the output I got was this:

{
  "Music - Eng": [
    {
      "id": {
        "id": 0
      },
      "song": "Three Little Birds",
      "artist": "Bob Marley",
      "year": 1977,
      "album": "Exodus",
      "genres": [
        "Reggae"
      ]
    },...
]
}

The desired output I would like is this:

{
    "Music - Eng": [
    {
        "0": {
            "id": 0,
            "song": "Three Little Birds",
            "artist": "Bob Marley",
            "year": 1977,
            "album": "Exodus",
            "genres": [
                "Reggae"
            ] 
        }
    }...,
    ]
}

[Synthoid]

Hmm, there may be a way to do what you want, but it would involve some creative key formatting on your part. I'd recommend looking over the wiki page for Nested Elements to get a sense of what is currently possible.