Build JSON For Google Calendar API V3 Using VBA

OK, so I finally figured out how to build and pass my JSON string. I'm using VBJSON to build the JSON string. Please remember that JSON is case sensitive (or at least Google interprets it case sensitive). A pair with the key dateTime is not the same as a pair with the key datetime and Google will reject the latter.

'Code to create JSON using Dictionary Objects and Collection ObjectsDim d As New Scripting.DictionaryDim c As New Collectiond.Add "kind", "calendar#event"d.Add "summary", "Event Title/Summary"Dim d2(4) As New Scripting.Dictionaryd2(0).Add "dateTime", "2012-04-14T16:00:00.000-04:00"d.Add "start", d2(0)d2(1).Add "dateTime", "2012-04-14T18:00:00.000-04:00"d.Add "end", d2(1)'First Attendeed2(2).Add "email", "john.doe@gmail.com"d2(2).Add "displayName", "John Doe"d2(2).Add "organizer", Trued2(2).Add "self", True'Add attendee to collectionc.Add d2(2)'Second attendeed2(3).Add "email", "suzy.doe@gmail.com"d2(3).Add "displayName", "Suzy Doe"'Add attendee to collectionc.Add d2(3)'Add collection to original/primary dictionary objectd.Add "attendees", c'Add more nested pairs to original/primary dictionary objectd2(4).Add "useDefault", Trued.Add "reminders", d2(4)'Now output the JSON/results'This requires the VBJSON module (named just JSON, a module, not a class module)Debug.Print JSON.JSONToString(d)

The unprettified output is this:

{"kind":"calendar#event","summary":"Event Title\/Summary","start":{"dateTime":"2012-04-14T16:00:00.000-04:00"},"end":{"dateTime":"2012-04-14T18:00:00.000-04:00"},"attendees":[{"email":"john.doe@gmail.com","displayName":"John Doe","organizer":true,"self":true},{"email":"suzy.doe@gmail.com","displayName":"Suzy Doe"}],"reminders":{"useDefault":true}}

And then here's how you submit it to Google using V3 of the Google Calendar API. In V3 you have to use OAuth2.0 so you need to have a valid Access Token to append to your URL as shown below. You'll also need to know your CalendarID which is usually your email address URL encoded. For example, your calendarid will look like this: john.doe%40gmail.com

Dim objXMLHTTP As MSXML2.ServerXMLHTTPSet objXMLHTTP = New MSXML2.ServerXMLHTTPDim sPostData As StringsPostData = JSON.JSONToString(d)Dim sURL As StringsURL = "https://www.googleapis.com/calendar/v3/calendars/{mycalendarid}/events?sendNotifications=false&fields=etag%2ChtmlLink%2Cid&pp=1&access_token={my oauth2.0 access token}"With objXMLHTTP    .Open "POST", sURL, False    .setRequestHeader "Content-Type", "application/json"    .Send (sPostData)End WithDebug.Print objXMLHTTP.ResponseTextSet objXMLHTTP = Nothing