0

I am writing a VBA script in Outlook that will add a calendar event to a google calendar each time the event is added to the Outlook calendar. I have this functionality working and this is the code I am using (in the code below, xxxxxxxxxxxxxxx is my google email which is associated with my google calendar and the variable accessToken is a valid token I generate in the OAuth 2.0 Playground while developing):

    ' build the json string which will be sent to the google API
    
        Dim d As New Scripting.Dictionary

        d.Add "kind", "calendar#event"
        d.Add "summary", "Event Title/Summary"
        d.Add "location", Item.Location
        d.Add "id", LCase(Item.EntryID)
        d.Add "description", Item.Subject

        Dim d2(4) As New Scripting.Dictionary

        d2(0).Add "dateTime", Item.Start
        d.Add "start", d2(0)
        d2(1).Add "dateTime", Item.End
        d.Add "end", d2(1)
        
        
        Dim Json As String
        Json = JsonConverter.ConvertToJson(d, Whitespace:=" ")
    MsgBox (LCase(Item.EntryID))
    ' send the json string via POST
    Set httpCall = CreateObject("MSXML2.ServerXMLHTTP")
    Dim sURL As String
    sURL = "https://www.googleapis.com/calendar/v3/calendars/xxxxxxxxxxxxxxx/events?sendNotifications=false&fields=etag%2ChtmlLink%2Cid&pp=1&access_token=" + accessToken
    httpCall.Open "POST", sURL, False
    httpCall.setRequestHeader "Content-Type", "application/json;charset=UTF-8"
    httpCall.Send Json
    Dim sReturn As String
    sReturn = httpCall.responseText
    MsgBox (sReturn)

After I add an event to my Outlook calendar, the above code runs. The result of the first MsgBox looks like this:

enter image description here

This is the value of the event ID in Outlook. I pass this along to google in the hopes that google creates the event in my google calendar with the same event ID.

When google returns, this is the response text that is sent back:

enter image description here

As you can see, google returned a structure with 3 fields, etag, id, and htmlLink. The value in id is the same value I sent so I assumed that would be the event ID on the google calendar.

My next step was to delete the event in Outlook and have the event deleted in the google calendar by using the API. This is the code that I use to issue the delete request:

Set httpCall = CreateObject("MSXML2.ServerXMLHTTP")
Dim sURL As String
MsgBox (LCase(Item.EntryID))
sURL = "https://www.googleapis.com/calendar/v3/calendars/xxxxxxxxxxxxxxx/events/" + LCase(Item.EntryID) + "?access_token=" + access token
httpCall.Open "DELETE", sURL, False
httpCall.setRequestHeader "Content-Type", "application/json;charset=UTF-8"
httpCall.Send
Dim sReturn As String
sReturn = httpCall.responseText
MsgBox (sReturn)

The first message box displays the event ID in Outlook which matches the first image I provided. However, when this code runs, this is what google returns:

enter image description here

Am I correct in concluding that the google event ID is not the event ID I sent? Is the google event ID the eid variable that was returned as part of the htmllink? And if that is the case, then I assume I would need to store that eid value in the Outlook event somewhere and pass that to google on a delete request.

Any clarification would be greatly appreciated.

Jonathan Small
  • 1,027
  • 3
  • 18
  • 40
  • What concerns me is that you use email as `xxxxxxxxxxxxxxx` in the URL: https://www.googleapis.com/calendar/v3/calendars/xxxxxxxxxxxxxxx/... while according to the documentation this should be the calendar id or the keyword `primary`. https://developers.google.com/calendar/api/v3/reference/events/insert Also, do you send a JSON body in the first code snipped? I cannot quite understand what is the body value there. – Alexey Ukolov Jan 04 '23 at 16:02
  • @AlexeyUkolov - I dont actually send xxxxxxxxxxxxx. As I stated in the post, I am sending my google gmail email address. The adding of the event works. I am trying to figure out the delete process. According to the documentation, to execute a delete, I need to pass the event ID. I need to know if google will create the event with the ID I sent in the initial create call or if Google generates their own event ID (which appears to be returned in the json structure I provided an image of). – Jonathan Small Jan 04 '23 at 16:13
  • The event id indeed must be the same as the one that you provided while creating the event. Can you actually see the newly created event in google calendar? I'd suggest to try to use the calendar id instead of the email in the URL: https://developers.google.com/calendar/api/v3/reference/events/delete `DELETE https://www.googleapis.com/calendar/v3/calendars/calendarId/events/eventId` `calendarId string Calendar identifier. To retrieve calendar IDs call the calendarList.list method. If you want to access the primary calendar of the currently logged in user, use the "primary" keyword.` – Alexey Ukolov Jan 04 '23 at 19:40
  • Yes, I see the new event in my google calendar. What I ended up doing was executing this: Dim sEID As String sEID = LCase(Item.EntryID)Set prop = Item.UserProperty.Add("googleEID", olText) prop.Value = sEID Item.Save when I get the response back from google. I was then able to pick up that value with Item.UserProperties.Find and send that value to google. I am now able to delete my events. Thanks. – Jonathan Small Jan 04 '23 at 21:52

0 Answers0