cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jt1103
New Member

Syncing Google Calendar events to Teams (Outlook): Update flow deletes all events whenever a Google event is modified

Hi, I'm having some strange behavior with a flow I've been building that I'm hoping someone can help with.

 

I'd like my Teams status to show busy when I have a Google Calendar event. To do this, I created 2 flows:

  1. A manually triggered flow to copy all existing Google Calendar events to Outlook
  2. An automated flow to update Outlook events every time Google Calendar modifies, deletes, or creates an event

The first flow works flawlessly and the second seems to initially, but then the following happens:

  1. A Google Calendar event triggers the flow (Good)
  2. The flow pulls all Outlook events (Good I guess, though this could probably be more efficient?)
  3. All calendar events are deleted (Bad)

Through troubleshooting, I've identified the following:

  1. This happens even if only a single event is modified
  2. I know that the Update path is the reason the events are being deleted because every run log that deletes all events only shows that path being triggered
  3. I know it is not an organizational config causing the behavior because events are not deleted when the flow is off

Any ideas as to what my problem might be?

 

Here is what the flow looks like:

jt1103_0-1612391946132.png

Note: the date functions are: 

  • formatDateTime(triggerOutputs()?['body/Start'],'yyyy-MM-ddTHH:mm:ss')
  • formatDateTime(triggerOutputs()?['body/End'],'yyyy-MM-ddTHH:mm:ss')

(If you are on this thread because of a timezone error you receive doing something similar, the above function worked for me, not the solution that is posted everywhere involving the convertToUtc function) 

 

Here is an example of the payload from Google that is triggering the update flow:

{
  "headers": {
    "Vary": "Origin,X-Origin,Referer",
    "X-XSS-Protection": "0",
    "X-Frame-Options": "SAMEORIGIN",
    "X-Content-Type-Options": "nosniff",
    "Alt-Svc": "h3-29=\":443\"; ma=2592000,h3-T051=\":443\"; ma=2592000,h3-Q050=\":443\"; ma=2592000,h3-Q046=\":443\"; ma=2592000,h3-Q043=\":443\"; ma=2592000,quic=\":443\"; ma=2592000; v=\"46,43\"",
    "Timing-Allow-Origin": "*",
    "x-ms-apihub-cached-response": "true",
    "retry-after": "90",
    "Cache-Control": "no-transform, must-revalidate, max-age=0, private",
    "Date": "Wed, 03 Feb 2021 21:33:35 GMT",
    "Location": "https://domain/apim/googlecalendar/shared-googlecalenda-5397d472e-868f-4d18-9587-059d-1717370e/trigger4/calendars/name@domain.com/events?singleEvents=false&triggerstate=2021-02-03T21:33:35.232Z",
    "Content-Length": "1654",
    "Content-Type": "application/json; charset=UTF-8",
    "Expires": "Wed, 03 Feb 2021 21:33:35 GMT"
  },
  "body": {
    "kind": "calendar#event",
    "etag": "\"3224776028634000\"",
    "id": "0mvlnjjdl2j02aksdfsvi9p5601u_20210204T223000Z",
    "status": "confirmed",
    "htmlLink": "https://www.google.com/calendar/event?eid=MG12bG5qamRsMmowMmFrc2p2adsdsXVfMjAyMTAyMDRUMjIzMDAwWiBqb3NlcGgudGVybWluaUBwd2MuY29t",
    "created": "2020-01-30T14:52:08Z",
    "updated": "2021-02-03T21:33:34.317Z",
    "summary": "Sync T&E",
    "colorId": "9",
    "creator": "name@domain.com",
    "organizer": "name@domain.com",
    "start": "2021-02-04T22:15:00+00:00",
    "end": "2021-02-04T22:45:00+00:00",
    "recurringEventId": "0mvlnjfjdl2j02aksjvi9p5601u",
    "originalStartTime": {
      "dateTime": "2021-02-04T22:30:00+00:00",
      "timeZone": "America/New_York"
    },
    "transparency": "transparent",
    "iCalUID": "0mvlnjjdlds2j02aksjvi9p5601u@google.com",
    "sequence": 3,
    "attendees": "name@domain.com",
    "reminders": {
      "useDefault": true
    },
    "eventType": "default",
    "actionType": "updated",
    "description": "",
    "location": "",
    "endTimeUnspecified": false
  }
}

 

Here is the raw input to the Update Event (V4) function:

{
    "host": {
        "connectionReferenceName": "shared_office365",
        "operationId": "V4CalendarPatchItem"
    },
    "parameters": {
        "table": "AAMkADExN2ViYjY5LWVlNzQtNDllYS1iNzUwLTY4NjcwNjhhM2QyYwBGAAAAAADyHkHCLLZOT6GKfoV-f5BcBwDqFmio6oBqQqMqEZO_jTd5AAAAAAEGAADqFmio6oBqQqMqEZO_jTd5AAB-KDVNAAA=",
        "id": "AAMkADExN2ViYjY5LWVlNzQtNDllYS1iNzUwLTY4NjcwNjhhM2QyYwBGAAAAAADyHkHCLLZOT6GKfoV-f5BcBwDqFmio6oBqQqMqEZO_jTd5AAAAAAENAADqFmio6oBqQqMqEZO_jTd5AAEl1-0iAAA=",
        "item/subject": "Sync T&E",
        "item/start": "2021-02-04T22:15:00",
        "item/end": "2021-02-04T22:45:00",
        "item/timeZone": "(UTC) Coordinated Universal Time",
        "item/body": "<p></p>",
        "item/showAs": "busy"
    }
}

  

and here is the matching raw output of the Update Event (V4) function:

{
    "statusCode": 200,
    "headers": {
        "Pragma": "no-cache",
        "Vary": "Accept-Encoding",
        "x-ms-request-id": "eec87a7f-85e0-42e7-946c-4eecb36cdd2b",
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "X-Content-Type-Options": "nosniff",
        "X-Frame-Options": "DENY",
        "Cache-Control": "no-store, no-cache",
        "Set-Cookie": "ARRAffinity=074fdd32eea883e954fdec8b4146efc72d5253f72cee7d8c0b464a23abd6400e;Path=/;HttpOnly;Secure;Domain=office365-domain.net,ARRAffinitySameSite=074fdd32eea883e954f8ec8b4146efc72d5253f72cee7d8c0b464a23abd6400e;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-domain.net",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "true",
        "Date": "Wed, 03 Feb 2021 21:33:41 GMT",
        "Content-Type": "application/json; charset=utf-8",
        "Expires": "-1",
        "Content-Length": "1522"
    },
    "body": {
        "subject": "Sync T&E",
        "start": "2021-02-04T22:15:00.0000000",
        "end": "2021-02-04T22:45:00.0000000",
        "startWithTimeZone": "2021-02-04T22:15:00+00:00",
        "endWithTimeZone": "2021-02-04T22:45:00+00:00",
        "body": "<html>\r\n<head>\r\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">\r\n<meta content=\"text/html; charset=us-ascii\">\r\n</head>\r\n<body>\r\n<p></p>\r\n</body>\r\n</html>\r\n",
        "isHtml": true,
        "responseType": "organizer",
        "responseTime": "0001-01-01T00:00:00+00:00",
        "id": "AAMkADExN2ViYjY5LWVlNzaQtNDllYS1iNzUwLTY4NjcwNjhhM2QyYwBGAAAAAADyHkHCLLZOT6GKfoV-f5BcBwDqFmioa6oBqQqMqEZO_jTd5AAAAAAENAADqFmio6oBqQqMqEZO_jTd5AAEl1-0iAAA=",
        "createdDateTime": "2021-02-03T21:13:04.6834271+00:00",
        "lastModifiedDateTime": "2021-02-03T21:33:42.0624676+00:00",
        "organizer": "name@domain.com",
        "timeZone": "UTC",
        "iCalUId": "040000008200E00074C5B7101A82E008000000006546A95C71FAD601000000000000000010000000ADA9E951275C3E4BB2D01EEA604348CE",
        "categories": [],
        "webLink": "https://outlook.office365.com/owa/?itemid=AAMkADExN2ViYjYsdaQtNDllYS1iNzUwLTY4NdjcwNjhhaM2QyYwBGAAAAAADyHkHCLLZOT6GKfoV%2Ff5BcBwDqFmio6oBqQqMqEZO%2BjTd5AAAAAAENAADqFmio6oBqQqMqEZO%2BjTd5AAEl1%2F0iAAA%3D&exvsurl=1&path=/calendar/item",
        "requiredAttendees": "",
        "optionalAttendees": "",
        "resourceAttendees": "",
        "location": "",
        "importance": "low",
        "isAllDay": false,
        "recurrence": "none",
        "reminderMinutesBeforeStart": 15,
        "isReminderOn": true,
        "showAs": "busy",
        "responseRequested": true,
        "sensitivity": "normal"
    }
}
1 REPLY 1
StefanM83
Helper I
Helper I

Hi @jt1103 ,
thank you for the exhaustive post, but I still can't get it to work.
I do get 


The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.

using your formulas for start and end date 

 

That is what happens:

 

When an event is added, updated or deleted from a calendar - INPUT

 

{
    "host": {
        "connectionReferenceName": "shared_googlecalendar",
        "operationId": "OnChangedEventInCalendar"
    },
    "parameters": {
        "calendar_id": ".......@group.calendar.google.com"
    }
}

 

 

When an event is added, updated or deleted from a calendar - OUTPUT

 

{
	"headers": {
		"Vary": "Origin,X-Origin,Referer",
		"X-XSS-Protection": "0",
		"X-Frame-Options": "SAMEORIGIN",
		"X-Content-Type-Options": "nosniff",
		"Alt-Svc": "h3=\":443\"; ma=2592000,h3-29=\":443\"; ma=2592000,h3-T051=\":443\"; ma=2592000,h3-Q050=\":443\"; ma=2592000,h3-Q046=\":443\"; ma=2592000,h3-Q043=\":443\"; ma=2592000,quic=\":443\"; ma=2592000; v=\"46,43\"",
		"Timing-Allow-Origin": "*",
		"x-ms-apihub-cached-response": "true",
		"retry-after": "90",
		"Cache-Control": "no-transform, must-revalidate, max-age=0, private",
		"Date": "Sat, 04 Sep 2021 09:48:31 GMT",
		"Location": "https://europe-002.azure-apim.net/apim/googlecalendar/shared-googlecalenda-..../trigger4/calendars/......@group.calendar.google.com/events?triggerstate=2021-09-04T09:48:31.857Z",
		"Content-Length": "1256",
		"Content-Type": "application/json; charset=UTF-8",
		"Expires": "Sat, 04 Sep 2021 09:48:31 GMT"
	},
	"body": {
		"kind": "calendar#event",
		"etag": "\"3261......\"",
		"id": "....",
		"status": "confirmed",
		"htmlLink": "https://www.google.com/calendar/event?eid=.....",
		"created": "2021-09-04T09:26:33Z",
		"updated": "2021-09-04T09:48:29.444Z",
		"summary": "test flow",
		"description": "afdsaf",
		"creator": "....@gmail.com",
		"organizer": "....@group.calendar.google.com",
		"start": "2021-09-04T09:30:00+00:00",
		"end": "2021-09-04T10:30:00+00:00",
		"iCalUID": "....@google.com",
		"sequence": 0,
		"reminders": {
			"useDefault": true
		},
		"eventType": "default",
		"actionType": "updated",
		"location": "",
		"attendees": "",
		"endTimeUnspecified": false
	}
}

 

 
Action 'Create_event_(V4)' failed - INPUT

 

{
    "host": {
        "connectionReferenceName": "shared_office365",
        "operationId": "V4CalendarPostItem"
    },
    "parameters": {
        "table": ".......",
        "item/subject": "Privater Appointment ",
        "item/start": "formatDateTime(triggerOutputs()?['body/Start'],'yyyy-MM-ddTHH:mm:ss')",
        "item/end": "formatDateTime(triggerOutputs()?['body/End'],'yyyy-MM-ddTHH:mm:ss')",
        "item/timeZone": "(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna",
        "item/body": "<p>test flow</p>",
        "item/showAs": "tentative",
        "item/sensitivity": "private"
    }
}

 

 

Action 'Create_event_(V4)' failed - OUTPUT

 

{
    "statusCode": 400,
    "headers": {
        "Pragma": "no-cache",
        "x-ms-request-id": "6b047d61-1787-442c-a0e4-943de61f6bb5",
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "X-Content-Type-Options": "nosniff",
        "X-Frame-Options": "DENY",
        "Cache-Control": "no-store, no-cache",
        "Set-Cookie": "ARRAffinity=de3b10a934af9f1a63b114c049a04afeade855bfd755f5c58629d86d56d64502;Path=/;HttpOnly;Secure;Domain=office365-we.azconn-we-01.p.azurewebsites.net,ARRAffinitySameSite=de3b10a934af9f1a63b114c049a04afeade855bfd755f5c58629d86d56d64502;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-we.azconn-we-01.p.azurewebsites.net",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "false",
        "Date": "Sat, 04 Sep 2021 09:48:31 GMT",
        "Content-Length": "389",
        "Content-Type": "application/json",
        "Expires": "-1"
    },
    "body": {
        "status": 400,
        "message": "The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.\r\nclientRequestId: 6b047d61-1787-442c-a0e4-943de61f6bb5",
        "error": {
            "message": "The string was not recognized as a valid DateTime. There is an unknown word starting at index 0."
        },
        "source": "office365-we.azconn-we-01.p.azurewebsites.net"
    }
}

 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,417)