Parsing JSON to seperate columns

[Chris Davies]

I am trying to convert JSON data that is provided by the Audit log for Office365 for creation of Teams into separate columns so I can use the data more effectively:

The data looks like this:

{
    "CreationTime": "2018-04-26T16:31:29",
    "Id": "xxxxxxxxxxxxxxx",
    "Operation": "GroupAdded",
    "OrganizationId": "xxxxxxxxxxxxx",
    "RecordType": 4,
    "UserKey": "i:xxxxxxxx@live.com",
    "UserType": 0,
    "Version": 1,
    "Workload": "SharePoint",
    "ClientIP": "xx.xxx.xxx.xxx",
    "ObjectId": "https:xxxxxx.sharepoint.com",
    "UserId": "sxxxx.xxx@xxx.com",
    "CorrelationId": "xxxxxxxxxxxx",
    "EventSource": "SharePoint",
    "ItemType": "Site",
    "Site": "xxxxxxxxxxxxxx",
    "UserAgent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/66.0.3359.117 Safari\/537.36",
    "ModifiedProperties": [{
        "Name": "Name",
        "NewValue": "SharingLinks.1cxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxx"
    }]
}

I want the macro to separate the different information into their own columns.

I have tried this solution - https://www.mrexcel.com/forum/excel-questions/980302-parse-json-values-excel-cells.html#post4704611

But I can't get it working.

Any help much appreciated!

[#2]

Not sure about macro, as variant you may do that with Power Query. For this particular sample generated script looks like (and attached)

let
    Source = Json.Document(File.Contents("C:\Test\sample.json")),
    ToTable = Record.ToTable(Source),
    TransposeTable = Table.Transpose(ToTable),
    PromotHeaders = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true]),
    ChangeType = Table.TransformColumnTypes(PromotHeaders,{{"CreationTime", type datetime}, {"Id", type text}, {"Operation", type text}, {"OrganizationId", type text}, {"RecordType", Int64.Type}, {"UserKey", type text}, {"UserType", Int64.Type}, {"Version", Int64.Type}, {"Workload", type text}, {"ClientIP", type text}, {"ObjectId", type text}, {"UserId", type text}, {"CorrelationId", type text}, {"EventSource", type text}, {"ItemType", type text}, {"Site", type text}, {"UserAgent", type text}, {"ModifiedProperties", type any}}),
    ExpandModifiedProperties = Table.ExpandListColumn(ChangeType, "ModifiedProperties"),
    ExpandRecord = Table.ExpandRecordColumn(ExpandModifiedProperties, "ModifiedProperties", {"Name", "NewValue"}, {"Name", "NewValue"})
in
    ExpandRecord

for real data you shall expand lists for each field