Extracting values from JSON strings

Questions & Suggestions
1

Hi guys,

im pretty bad with Regexformulars. Im using sheet.best to get data for KBM. Anyone can tell me how i get the data formated from:

[{"Name":"Dennis Eckler","Message URL":"https://www.linkedin.com/in/ACoAADVmW58B6lHWiF06yLeGhRSadi8CnumZWMM","Response":"Response 1","Action":"Archivieren "},{"Name":"Matthias Hoffmann","Message URL":"https://www.linkedin.com/in/ACoAACE4bPIB6kFOuXXPKj_QHbs8poWBzTOYjrk","Response":"Response 2","Action":"null"},{"Name":"Ingo K\u00f6pp","Message URL":"https://www.linkedin.com/in/ACoAAB9I6xkBgunTvwA5QNwptMBA3pLgm4kEiKE","Response":"Response 3","Action":"null"},{"Name":"Christian Schmidt","Message URL":"https://www.linkedin.com/in/ACoAADZrEdQBUeNw3Y1ZCmYrFrjE9zG9H225yKQ","Response":"Response 4","Action":"null"},{"Name":"Ren\u00e9 S\u00fc\u00dfkind","Message URL":"https://www.linkedin.com/in/ACoAAD_OumoBud-xrG0MtgS1WVPyRpGYQRc6A9s","Response":"Response 5","Action":"null"},{"Name":"Julian Jarminowski","Message URL":"https://www.linkedin.com/in/ACoAACkcNncBsDtltg9GNe7kCzZyIzS1U36GLwQ","Response":"Response 6","Action":"null"}]

to:

Dennis Eckler,https://www.linkedin.com/in/ACoAADVmW58B6lHWiF06yLeGhRSadi8CnumZWMM,Response 1,Archivieren
Matthias Hoffmann,https://www.linkedin.com/in/ACoAACE4bPIB6kFOuXXPKj_QHbs8poWBzTOYjrk,Response 2,null
Ingo Köpp,https://www.linkedin.com/in/ACoAAB9I6xkBgunTvwA5QNwptMBA3pLgm4kEiKE,Response 3,null
Christian Schmidt,https://www.linkedin.com/in/ACoAADZrEdQBUeNw3Y1ZCmYrFrjE9zG9H225yKQ,Response 4,null
René Süßkind,https://www.linkedin.com/in/ACoAAD_OumoBud-xrG0MtgS1WVPyRpGYQRc6A9s,Response 5,null
Julian Jarminowski,https://www.linkedin.com/in/ACoAACkcNncBsDtltg9GNe7kCzZyIzS1U36GLwQ,Response 6,null

Basically to get the data in the form name, url, response, action

2

Probably fortunate, as deciding to use regular expressions as a way of extracting values from a JSON string like this would only add to your problems :slight_smile:

For simpler and quicker solutions, see the JSON section of the Keyboard Maestro wiki:

https://wiki.keyboardmaestro.com/manual/JSON

2 Likes
3

The first thing I did was to convert it to Pretty format and display it so I could udnerstand the format myself:

image

[
  {
    "Action" : "Archivieren ",
    "Message URL" : "https:\/\/www.linkedin.com\/in\/ACoAADVmW58B6lHWiF06yLeGhRSadi8CnumZWMM",
    "Name" : "Dennis Eckler",
    "Response" : "Response 1"
  },
…
]

Then index through the array (using J[0] to get the count of entries in the array), and append the entries to a result (with entries %JSONValue%J[Index]{Name}%).

JSON Example.kmmacros (6.1 KB)

Keyboard Maestro Export
Keyboard Maestro Export1354×2842 476 KB

Do note that this is not necessarily going to generate a valid CSV if your entries include any undesirable characters (like commas or quotes).

2 Likes
5

Exactly what im looking for. Thanks for your help!

1 Like