Google Sheets – Parse unsorted comma separated values from one column, into separate columns. The values are not always in the same order

Three example rows from that column are shown below.

{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2024-03-03', 'issuing_country': 'GBR'}

{'nationality': 'DEU', 'document_type': 'national_identity_card', 'date_of_expiry': '2020-11-19', 'issuing_country': 'DEU'}

{'gender': 'Female', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2024-01-22', 'issuing_country': 'FRA'}

My desired outcome would be:

gender | document_type | document_type | date_of_expiry | issuing country | nationality | national_identity_card | 

e.g.

Female | driving_licence | 2024-03-03 | GBR | NULL | NULL 
NULL   | national_identity_card | 2020-11-19 | DEU | DEU
.
.
.

Any help would be great 🙂

Answer

Indeed it looks like JSON. You could replace all the ' with " and paste the records here: https://codebeautify.org/jsonviewer

The complete string for the examples you provided would be:

{
  "example": [
    {
      "gender": "Female",
      "document_type": "driving_licence",
      "date_of_expiry": "2024-03-03",
      "issuing_country": "GBR"
    },
    {
      "nationality": "DEU",
      "document_type": "national_identity_card",
      "date_of_expiry": "2020-11-19",
      "issuing_country": "DEU"
    },
    {
      "gender": "Female",
      "nationality": "FRA",
      "document_type": "passport",
      "date_of_expiry": "2024-01-22",
      "issuing_country": "FRA"
    }
  ]
}

After that, clicking the button JSON to CSV generates the values sorted by columns like this:

gender,document_type,date_of_expiry,issuing_country
Female,driving_licence,2024-03-03,GBR
,national_identity_card,2020-11-19,DEU
Female,passport,2024-01-22,FRA