Build JSON For Google Calendar API V3 Using VBA

Question

I'm trying to add an event to a Google Calendar using the Google API V3. I'm not using any of Google's libraries so I'm really baffled as to how to do this. Google's documentation tells me that I need to send an "Events Resource" in the request body. I think I've built a valid JSON "string" but I have no idea how to properly prepare it and send it in the so-called "request body". What confuses me is the way that string values are wrapped in quotes and non-string values are not. Does this mean that I'll need to wrap the entire thing as a string and double/quadruple quote each double quote?

Here's the JSON I've written but I haven't figure out yet how to pass this to Google 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 do have a VBJSON code module installed in my Access/VBA Database. I see a function in there called StringToJSON which returns a string. I'm still baffled. When I pass this JSON to Google, will it merely be one big string value within my code?

Your date format is off I think: the Google examples look like this - `"created": "2011-05-23T22:27:01.000Z"` From VBA you could send that json using xmlhttp and POST. — Tim Williams, Apr 10 '12 at 16:19
So do I put quotes around the date? I'm confused about quotes and data types within JSON. Is JSON ultimately just a string data type? — HK1, Apr 10 '12 at 16:54
Look at the examples on the Google documentation: that's how your json should look. I would not say JSON is a "data type" - more like a data exchange format. You should spend some time here: http://www.json.org/ — Tim Williams, Apr 10 '12 at 17:05
Yes - in VBA it's just a string, so you'll have to double-up all the quotes. — Tim Williams, Apr 10 '12 at 17:53

HK1 · Accepted Answer · 2012-04-14T04:07:48.077

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 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)

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.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

Build JSON For Google Calendar API V3 Using VBA

1 Answers1