{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Agenda","Resources","JSON spec","Survey structure","Schema","Collection","Dictionary","Recordset"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Agenda","index":0,"n_rows":81,"n_cols":6,"rows_kept":81,"cut":false,"hidden":false,"merged":[]}
["https://www.meetup.com/London-Excel-Meetup-Group/events/272870552"]
[]
["Tuesday, September 15, 2020"]
[]
["Convert JSON Data to Normalised Excel Tables (JSON Flattening)"]
["Hosted by"]
["Alan Murray and Tea Kuseva"]
["London Excel Meetup Group"]
["Public group"]
["Tuesday, September 15, 2020"]
["6:00 PM to 8:00 PM GMT+1"]
[]
["Online event"]
[]
["Link visible for attendees"]
[]
["Details"]
["Come along to this London Excel Meetup to hear Patrick O'Beirne on 'converting JSON data into normalised Excel tables' using a Survey Monkey source."]
[]
["Here are some words from Patrick;"]
[]
["JSON text is commonly returned when you get data using a REST query from an API such as MS Azure, HMRC or Surveymonkey,"]
["You will want to create normalised tables to analyse the data conventionally in Excel but deeply nested JSON is awkward to work with."]
[]
["My sample data source will be a file saved from a Surveymonkey survey_details query as documented in"]
["https://developer.surveymonkey.com/api/v3/#surveys-id-details"]
[]
["Topics"]
["(1) using the free jsonlib class"]
["(2) the recursive loop to parse the nested structure"]
["(3) outputting to flat tables"]
[]
["There will be time for Q and A, after the presentation."]
[]
["Enrol now to learn some new skills and get involved with other nice Excel folk."]
[]
["This is an online event and it will start at 18:00 UK time."]
[]
[]
["Patrick O’Beirne, Systems Modelling Ltd"]
["pob @ sysmod.com"]
["http://www.sysmod.com "]
["http://ie.linkedin.com/in/patrickobeirne"]
["http://sysmod.wordpress.com"]
["@ExcelAnalytics "]
[]
[]
[]
[]
["Programme"]
["Background: Survey analysis of website Top Tasks run by Gerry McGovern."]
["Today's example: study for hse.ie. Preview of survey to explain structure."]
["Surveymonkey used to supply 'database format' xls files eg:"]
[null,"surveys.xls","questions.xls","questionoptions.xls"]
["In 2014 SM changed to REST API returning JSON text."]
["Example API request:",null,null,"https://developer.surveymonkey.com/api/v3/#surveys-id-details"]
["Example file"]
[]
["JSON specification overview"]
["What we have:",null,"JSON of Survey structure diagram"]
["What we want:",null,"Database Schema"]
["Quick look at data in Power Query: one record/list at a time can be returned to Excel."]
["We'll use VBA to convert JSON to Dictionary and Collection objects ;"]
["and then convert them to Recordsets to get to Excel tables"]
["Overview of concepts:",null,null,"Collection","Dictionary","Recordset"]
["Convert JSON text to a VBA JSON Dictionary"]
["   Set Jlib = New ClsJsonLib : set obj = Jlib.parse( \"{\"\"name\"\":123}\" ) "]
["Recursive method to process the JSON object:",null,null,null,"RecurseJson","testRecurse"]
["Single step the code:",null,null,"testSurvey_Details"]
["Step through this demo sub to illustrate the recursion:",null,null,null,null,"#DebugJsonIndent"]
["If there is time, put a Debug>Watch on the json object and drill into it in the Watch window."]
["The FlattenJson sub calls these subsequent routines"]
["First pass: collect all the fields from all the records in the json into recordsets."]
["Need to scan the whole file: some records may use only a partial set of fields and may find more later"]
["Explain why we need to generate ID keys",null,null,null,null,"JsonDataToRecordsetsWithIDs"]
["Write the recordsets to worksheets"]
[]
["Bonus: re-combine these very atomic tables into sheets"]
[" more like the original Surveymonkey 'database format xls'",null,null,null,null,"testQuery"]
[]
["Q & A"]

{"sheet":"Resources","index":1,"n_rows":23,"n_cols":1,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["My sample data source will be a file saved from a Surveymonkey survey_details query as documented in"]
["https://developer.surveymonkey.com/api/v3/#surveys-id-details"]
[]
["#testJsonfile"]
[]
["https://api.surveymonkey.net/v3/docs#getting-started"]
["Registering creates a draft app with an access token which you can use immediately in private apps. You only need OAuth for public apps. Requests are subject to rate limits."]
["With CreateObject(\"MSXML2.XMLHTTP\")"]
["   .Open \"GET\", \"https://api.surveymonkey.net/v3/surveys/{Id}/get_survey_details\", False"]
["   .setRequestHeader \"Authorization\", \"bearer \" & ACCESS_TOKEN"]
["   .setRequestHeader \"Content-Type\", \"application/json\""]
["   .Send CVar(sRequestBody)     ' used for other queries, empty for survey_details"]
["   strData = .Responsetext ' is the json data returned"]
["End With"]
[]
[]
[]
[]
[]
["Surveymonkey support : https://stackoverflow.com/questions/tagged/surveymonkey"]
[]
["Download the JSON Class library: http://www.sysmod.com/clsJSONLib.cls"]
["Also present in this workbook"]

{"sheet":"JSON spec","index":2,"n_rows":13,"n_cols":2,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
[null,"JSON is built on two structures: a record and a list:"]
["a.","A record is a set of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array. We render it into VBA as a Scripting.dictionary."]
["b.","A list is an ordered list of values. In most languages, this is realized as an array, vector, list, or collection. We render it into VBA as a VBA.Collection"]
["c.","A record structure is represented as a pair of curly brackets surrounding zero or more name/value pairs (or members).  A name is a string.  A single colon comes after each name, separating the name from the value.  A single comma separates a value from a following name."]
["d.","Record: { \"field1\":value, \"field2\":value,…}"]
["e.","Value can be a simple data type or an object or an array."]
["f.","A JSON value MUST be an object, array, number, or string, or one of the following three literal names: false, null, true. The literal names MUST be lowercase.  No other literal names are allowed."]
["g.","value = false / null / true / object / array / number / string"]
["h.","An array structure is represented as square brackets surrounding zero or more values (or elements).  Elements are separated by commas."]
["i.","Array: [ element, element, ….]"]
["j.","There are escape characters such as \\r\\n and hex codes, but that's basically it. The ClsJsonLib .toString method converts these to the corresponding characters.."]
[]
[null,"https://tools.ietf.org/html/rfc7159"]

{"sheet":"Survey structure","index":3,"n_rows":16,"n_cols":10,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["The structure is nested as follows:","The task is to flatten all this into normalised tables of questions and their answer options."]
[]
["Survey record: Dictionary","title","nickname","language","folder_id","category","..etc…","pages"]
[]
[null,null,"Pages list: Collection of Page records"]
["Page record: Dictionary",null,"title","description","position","category","..etc…","questions"]
[]
[null,null,null,"Questions list: Collection of Question records"]
["Question record: Dictionary",null,null,"id","position","visible","headings","answers"]
[]
["Answers record: Dictionary",null,null,null,"choices","other"]
[]
[null,null,null,null,null,"Choices List: Collection of Choice records"]
["Choice record: Dictionary",null,null,null,null,"position","visible","text","quiz_options","id"]
[]
["Quiz_options record: Dictionary",null,null,null,null,null,null,"score"]

{"sheet":"Schema","index":4,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["This is what the resultant database structure should look like"]

{"sheet":"Collection","index":5,"n_rows":16,"n_cols":2,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["VBA.Collection Object"]
[null,"Set coll = New Collection"]
[null,"coll.Add Item:=(variant), Key:=(string)"]
[null,"Function CollectionKeyExists(coll As Collection, key As String) As Boolean"]
[null,"    On Error Resume Next"]
[null,"    coll.Item key"]
[null,"    CollectionKeyExists = Err = 0"]
[null,"End Function"]
[]
[null,"Dim item as Variant"]
[null,"For each item In coll"]
[null,"    ' …"]
[null,"Next var"]
[]
[]
["More info:","https://excelmacromastery.com/excel-vba-collections/"]

{"sheet":"Dictionary","index":6,"n_rows":19,"n_cols":2,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Dictionary Object"]
["Early binding: add a reference to Microsoft Scripting Runtime"]
[null,"Dim dict as Scripting.Dictionary"]
[null,"Set dict = new Scripting.Dictionary"]
[]
["Late binding:","Dim dict as Object"]
[null,"set dict = CreateObject(\"Scripting.Dictionary\")"]
[]
[null,"dict.Add Key, Value"]
[null,"dict(Key) = Value"]
[null,"dict.Exists(Key)"]
[null,"Value = dict(Key)"]
[null,"Beware of auto-add feature. An invalid Watch expression can inject unwanted items."]
[null,"Dim key As Variant"]
[null,"For Each key In dict.Keys "]
[null,"   Debug.Print key, dict(key)"]
[null,"Next key"]
[]
["More info:","https://excelmacromastery.com/vba-dictionary/"]

{"sheet":"Recordset","index":7,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Recordset Object"]
["Early binding: add a reference to Microsoft ActiveX Data objects library"]
[null,"Dim rs as ADODB.Recordset"]
[null,"Set rs = new ADODB.Recordset"]
[]
["Late binding:","Dim rs as Object"]
[null,"set rs = CreateObject(\"ADODB.Recordset\")"]
[]
[null,"rs.Fields.Append FieldName, lFieldType"]
[null,"rs.AddNew"]
[null,"Set oField = rs.Fields(FieldName)"]
[null,"oField.Value = CLng(var) or CDbl or whatever the type is"]
[null,"rs.Update"]
[null,"Range.CopyFromRecordset rs "]
[]
[]
["More info:","https://blogs.msmvps.com/xldynamic/2010/10/10/vba-has-no-class/"]