Extract JSON data in cell a1, into b1, c1, d1 etc.. - Excel

2
votes
{"fields":{"neTlAMGB-4yu9pN":"James","neTlAMGB-wmshSV":"[email protected]","neTlAMGB-m446CO":"london, UK","neTlAMGB-0inhBf":"2016-12-15T12:00:00+00:00","neTlAMGB-TX6WrV":"07891122446699"}}

Ive exported some data from Trello into a CSV, some of the data have come in the above JSON format, if i have this data above in cell A1, id like to split it out into cells : b1, c2, d1, e1, f1, g1.

Is that possible with either some form of extract or regex or is there a JSON parser built into google spreadsheets / excel.

Ive created a demo google spreadsheet here for testing purposes : https://docs.google.com/spreadsheets/d/1rASBSSV4q9hC7IrxJNIFMlBOfYfkdLjJ7oyBVfniOPQ/edit#gid=0

--

Please note ive marked this question as "excel" and well as "google spreadsheets" as ive found in the past the majority of formula based scripts (non VBA) works cross platforms.

1
jsonexcel
Possible duplicate of Import Json in Excel – davejal
@davejal, thanks, but not duplicate as that question is dealing with importing JSON into google drive in the first place using a script, im trying to extract data from a JSON object already in a cell, using a native spreadsheet function – sam

1 Answers

0
votes

I think this actually does what you want

https://github.com/ronle/Json-To-Excel

To operate:

  1. Open the table and click on "Button 1"
  2. Acknowledge the welcome message
  3. Click on "Build TreeView"
  4. The script will create a treeview representing the Json file
  5. Expand the tree and select "colorName" as the key value (place checkmark next to it)
  6. Select the output tab & click the "Parse To Excel" button