Build JSON for Google Calendar API V3 using VBA
I am trying to add an event to Google Calendar using Google API V3. I am not using any of the google libraries, so I am really puzzled as to how to do this. Google documentation tells me that I need to send an "Event Resource" in the request body. I think I created a valid JSON string, but I don't know how to properly prepare it and send it to the so called "request body". What confuses me is how string values ββare wrapped in quotes and not string values ββ- no. Does this mean I need to wrap the whole thing as a string and a double / quadruple quote of each double quote?
Here's the JSON I wrote, but I haven't figured out how to pass this to Google yet, so I haven't been able to test it:
{
"kind": "calendar#event",
"start": {
"dateTime": 04/10/2012 08:00 AM
},
"end": {
"dateTime": 04/10/2012 08:00 AM
},
"attendees": [
{
"email": "myemailaddress@gmail.com",
"displayName": "My Name",
"organizer": True,
"self": True
}
],
"reminders": {
"useDefault": True
}
}
I have a VBJSON code module installed in my Access / VBA database. I can see a function there called StringToJSON that returns a string. I'm still puzzled. When I pass this JSON to Google, will it just be one big string value in my code?
source to share
OK, so I finally figured out how to build and pass my JSON string. I am using VBJSON to generate a JSON string. Remember JSON is case sensitive (or at least Google interprets it as case sensitive). The dateTime key pair is not the same as the datetime key pair and Google will reject the latter.
'Code to create JSON using Dictionary Objects and Collection Objects
Dim d As New Scripting.Dictionary
Dim c As New Collection
d.Add "kind", "calendar#event"
d.Add "summary", "Event Title/Summary"
Dim d2(4) As New Scripting.Dictionary
d2(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 Attendee
d2(2).Add "email", "john.doe@gmail.com"
d2(2).Add "displayName", "John Doe"
d2(2).Add "organizer", True
d2(2).Add "self", True
'Add attendee to collection
c.Add d2(2)
'Second attendee
d2(3).Add "email", "suzy.doe@gmail.com"
d2(3).Add "displayName", "Suzy Doe"
'Add attendee to collection
c.Add d2(3)
'Add collection to original/primary dictionary object
d.Add "attendees", c
'Add more nested pairs to original/primary dictionary object
d2(4).Add "useDefault", True
d.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)
Raw output:
{"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 how do you submit it to google using V3 google calendar API. In V3, you need to use OAuth2.0 so that you need to have a valid access token to add to your url as shown below. You also need to know your CalendarID, which is usually the url of your url. For example, your calendar will look like this: john.doe% 40gmail.com
Dim objXMLHTTP As MSXML2.ServerXMLHTTP
Set objXMLHTTP = New MSXML2.ServerXMLHTTP
Dim sPostData As String
sPostData = JSON.JSONToString(d)
Dim sURL As String
sURL = "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 With
Debug.Print objXMLHTTP.ResponseText
Set objXMLHTTP = Nothing
source to share