This requires VBA-JSON (https://github.com/VBA-tools/VBA-JSON). In Excel, open Visual Basic (alt+F11). Select tools -> References and select Microsoft XML, v6.0 Create a new module and paste this. You can customize it as needed. Public Sub GetRegistrations()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim strURL, strRURL, eID As String
eID = {your event ID}
strEURL = "https://api.constantcontact.com/v2/eventspot/events/" & eID & "/registrants?limit=500&api_key={Your API Key}"
Dim objrequest As New MSXML2.XMLHTTP60
With objrequest
.Open "Get", strEURL, False
.setRequestHeader "Authorization", "Bearer " & "{Your Token}"
.send
End With
strPostResponse = objrequest.responseText
Dim eParsed As Dictionary
Set eParsed = JsonConverter.ParseJson(strPostResponse)
For a = 1 To eParsed("results").Count
rID = eParsed("results")(a)("id")
strRURL = "https://api.constantcontact.com/v2/eventspot/events/" & eID & "/registrants/" & rID & "?api_key={Your API Key}"
Dim objrequest2 As New MSXML2.XMLHTTP60
With objrequest2
.Open "Get", strRURL, False
.setRequestHeader "Authorization", "Bearer " & "{Your Token}"
.send
End With
strPostResponse2 = objrequest2.responseText
Dim rParsed As Dictionary
Set rParsed = JsonConverter.ParseJson(strPostResponse2)
Debug.Print JsonConverter.ConvertToJson(rParsed, Whitespace:=2)
For b = 2 To rParsed("sections").Count
Cells(LR + a + 1, 1).Value = eParsed("results")(a)("first_name")
Cells(LR + a + 1, 2).Value = eParsed("results")(a)("last_name")
Cells(LR + a + 1, 3).Value = eParsed("results")(a)("email")
Cells(LR + a + 1, 4).Value = rParsed("sections")(3)("fields")(1)("value") 'these can be updated depending on your requirements
Cells(LR + a + 1, 5).Value = rParsed("sections")(2)("fields")(6)("value")
Cells(LR + a + 1, 6).Value = rParsed("sections")(2)("fields")(7)("value")
Cells(LR + a + 1, 7).Value = rParsed("sections")(3)("fields")(2)("value")
Cells(LR + a + 1, 8).Value = rParsed("sections")(3)("fields")(3)("value")
Cells(LR + a + 1, 9).Value = rParsed("sections")(3)("fields")(6)("value")
Cells(LR + a + 1, 10).Value = rParsed("sections")(3)("fields")(7)("value")
Next
Next
Range("M1").Value = "Last Updated " & Format(Now(), "MM/DD/YYYY")
Application.ScreenUpdating = True
Application.Calculation = xlCalculateAutomatic
End Sub In this particular event, the rParsed sections look like this: rParsed("sections")(1) is Custom1 rParsed("sections")(2) is Personal Information rParsed("sections")(3) is Business Information rParsed("sections")(4) is Custom2 I don't know if that will be true for every event. In my case, I have this running in a spreadsheet -- I call the sub on open, and I added a button to process it on demand.
... View more