So, it appears that Microsoft pulled all the Microsoft Flow templates that used to work flawlessly and give you two way sync between Office 365 and Google Calendar.
This might not sound like a massive issue. That is unless you use Google Assistant or Alexa to deal with your day to day Calendar stuff. “Hey Google, remind me where my next meeting is” is rather crap with no information.
So, as a stopgap while Microsoft get their act together and publish some working templates, here is the answer….
Firstly, start with this template on the Flow website. Edit your calendar entries for the Office 365 and Google Calendars you want to sync up. But then you will get an error…..
Flow save failed with code ‘OpenApiOperationParameterValidationFailed’ and message ‘Input parameter ‘newEvent’ validation failed in workflow operation ‘Create_an_event’: The parameter with value ‘”@triggerOutputs()?[‘body/Start’]”‘ in path ‘newEvent/start’ with type/format ‘String/date-no-tz’ is not convertible to type/format ‘String/date-time’.’.
Which is a pretty sucky message to get…. and it took me ages to figure out how to fix it. Until I found a kind forum post hidden away on a site somewhere (which I cannot since relocate). But essentially the answer to fix the above is to remove some items in this section:
You remove the body/Start and body/End entries then add replacements for those, looking at the image below should assist…..
You add two expressions:
convertToUtc(triggerOutputs()?['body/start'], 'GMT Standard Time')
convertToUtc(triggerOutputs()?['body/End'], 'GMT Standard Time')
You can then remove the bottom part of the description to get rid of a load of rubbish metadata that is pulled over otherwise.
That worked for me anyway! 🙂
When I enter the expressions here I get an invalid expression error. Do you have any solutions?
When you copy from the web, the single quote is actually a different character (angled single quote). Replace these characters with the regular single quotes.
Thanks for pointing that out. I have put in preformatted mode now, does that work?
This great, this made me get runnign again !
have been strugling with this a few time without success !
Now the next thing is:
I have made a flow that would be able to also update and delete posts from outlook to google.
But, as the “make event” lacks the “Event ID” is not required, it gets stuck when we try to do a update !
Any solution for that ?
Hello. Many thanks. Been looking everywhere for a solution for this! One thing driving me nuts though is that when the events sync from Outlook to Google they are always out by an hour (e.g. I set an appointment for 3pm in Outlook, shows up as 2pm in Google). I think this is something to do with British Summer Time perhaps? Have tried lots of variations on the expressions to get it to work but no joy. Any solutions appreciated. Thanks.
Exactly the same problem as you @nick.
I live in Australia and its always off by an hour.
Any luck with getting around this?
Did you work out what to change it to? I tried “convertToUtc(triggerOutputs()?[‘body/start’], ‘AUS Eastern Standard Time’)” which didn’t seem to work?
Me too have the same problem.. any help??
Thank you, it is working now.
Nick, Ahmed and Eric, I had the same problem.
Did some search on the internet.
I changed ‘GMT Standard TIme” to ‘Central Europe Standard TIme’ but same problem because of the summertime ?
I changed it to “Azores Standard Time”And now it seems to be ok.
Not the most beautiful solution, but it works for me 🙂
Dateline Standard Time:::(UTC-12:00) International Date Line West
UTC-11:::(UTC-11:00) Coordinated Universal Time-11
Aleutian Standard Time:::(UTC-10:00) Aleutian Islands
Hawaiian Standard Time:::(UTC-10:00) Hawaii
Marquesas Standard Time:::(UTC-09:30) Marquesas Islands
Alaskan Standard Time:::(UTC-09:00) Alaska
UTC-09:::(UTC-09:00) Coordinated Universal Time-09
Pacific Standard Time (Mexico):::(UTC-08:00) Baja California
UTC-08:::(UTC-08:00) Coordinated Universal Time-08
Pacific Standard Time:::(UTC-08:00) Pacific Time (US & Canada)
US Mountain Standard Time:::(UTC-07:00) Arizona
Mountain Standard Time (Mexico):::(UTC-07:00) Chihuahua, La Paz, Mazatlan
Mountain Standard Time:::(UTC-07:00) Mountain Time (US & Canada)
Central America Standard Time:::(UTC-06:00) Central America
Central Standard Time:::(UTC-06:00) Central Time (US & Canada)
Easter Island Standard Time:::(UTC-06:00) Easter Island
Central Standard Time (Mexico):::(UTC-06:00) Guadalajara, Mexico City, Monterrey
Canada Central Standard Time:::(UTC-06:00) Saskatchewan
SA Pacific Standard Time:::(UTC-05:00) Bogota, Lima, Quito, Rio Branco
Eastern Standard Time (Mexico):::(UTC-05:00) Chetumal
Eastern Standard Time:::(UTC-05:00) Eastern Time (US & Canada)
Haiti Standard Time:::(UTC-05:00) Haiti
Cuba Standard Time:::(UTC-05:00) Havana
US Eastern Standard Time:::(UTC-05:00) Indiana (East)
Turks And Caicos Standard Time:::(UTC-05:00) Turks and Caicos
Paraguay Standard Time:::(UTC-04:00) Asuncion
Atlantic Standard Time:::(UTC-04:00) Atlantic Time (Canada)
Venezuela Standard Time:::(UTC-04:00) Caracas
Central Brazilian Standard Time:::(UTC-04:00) Cuiaba
SA Western Standard Time:::(UTC-04:00) Georgetown, La Paz, Manaus, San Juan
Pacific SA Standard Time:::(UTC-04:00) Santiago
Newfoundland Standard Time:::(UTC-03:30) Newfoundland
Tocantins Standard Time:::(UTC-03:00) Araguaina
E. South America Standard Time:::(UTC-03:00) Brasilia
SA Eastern Standard Time:::(UTC-03:00) Cayenne, Fortaleza
Argentina Standard Time:::(UTC-03:00) City of Buenos Aires
Greenland Standard Time:::(UTC-03:00) Greenland
Montevideo Standard Time:::(UTC-03:00) Montevideo
Magallanes Standard Time:::(UTC-03:00) Punta Arenas
Saint Pierre Standard Time:::(UTC-03:00) Saint Pierre and Miquelon
Bahia Standard Time:::(UTC-03:00) Salvador
UTC-02:::(UTC-02:00) Coordinated Universal Time-02
Mid-Atlantic Standard Time:::(UTC-02:00) Mid-Atlantic – Old
Azores Standard Time:::(UTC-01:00) Azores
Cape Verde Standard Time:::(UTC-01:00) Cabo Verde Is.
UTC:::(UTC) Coordinated Universal Time
GMT Standard Time:::(UTC+00:00) Dublin, Edinburgh, Lisbon, London
Greenwich Standard Time:::(UTC+00:00) Monrovia, Reykjavik
Sao Tome Standard Time:::(UTC+00:00) Sao Tome
Morocco Standard Time:::(UTC+01:00) Casablanca
W. Europe Standard Time:::(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
Central Europe Standard Time:::(UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
Romance Standard Time:::(UTC+01:00) Brussels, Copenhagen, Madrid, Paris
Central European Standard Time:::(UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb
W. Central Africa Standard Time:::(UTC+01:00) West Central Africa
Jordan Standard Time:::(UTC+02:00) Amman
GTB Standard Time:::(UTC+02:00) Athens, Bucharest
Middle East Standard Time:::(UTC+02:00) Beirut
Egypt Standard Time:::(UTC+02:00) Cairo
E. Europe Standard Time:::(UTC+02:00) Chisinau
Syria Standard Time:::(UTC+02:00) Damascus
West Bank Standard Time:::(UTC+02:00) Gaza, Hebron
South Africa Standard Time:::(UTC+02:00) Harare, Pretoria
FLE Standard Time:::(UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius
Israel Standard Time:::(UTC+02:00) Jerusalem
Kaliningrad Standard Time:::(UTC+02:00) Kaliningrad
Sudan Standard Time:::(UTC+02:00) Khartoum
Libya Standard Time:::(UTC+02:00) Tripoli
Namibia Standard Time:::(UTC+02:00) Windhoek
Arabic Standard Time:::(UTC+03:00) Baghdad
Turkey Standard Time:::(UTC+03:00) Istanbul
Arab Standard Time:::(UTC+03:00) Kuwait, Riyadh
Belarus Standard Time:::(UTC+03:00) Minsk
Russian Standard Time:::(UTC+03:00) Moscow, St. Petersburg
E. Africa Standard Time:::(UTC+03:00) Nairobi
Iran Standard Time:::(UTC+03:30) Tehran
Arabian Standard Time:::(UTC+04:00) Abu Dhabi, Muscat
Astrakhan Standard Time:::(UTC+04:00) Astrakhan, Ulyanovsk
Azerbaijan Standard Time:::(UTC+04:00) Baku
Russia Time Zone 3:::(UTC+04:00) Izhevsk, Samara
Mauritius Standard Time:::(UTC+04:00) Port Louis
Saratov Standard Time:::(UTC+04:00) Saratov
Georgian Standard Time:::(UTC+04:00) Tbilisi
Volgograd Standard Time:::(UTC+04:00) Volgograd
Caucasus Standard Time:::(UTC+04:00) Yerevan
Afghanistan Standard Time:::(UTC+04:30) Kabul
West Asia Standard Time:::(UTC+05:00) Ashgabat, Tashkent
Ekaterinburg Standard Time:::(UTC+05:00) Ekaterinburg
Pakistan Standard Time:::(UTC+05:00) Islamabad, Karachi
Qyzylorda Standard Time:::(UTC+05:00) Qyzylorda
India Standard Time:::(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi
Sri Lanka Standard Time:::(UTC+05:30) Sri Jayawardenepura
Nepal Standard Time:::(UTC+05:45) Kathmandu
Central Asia Standard Time:::(UTC+06:00) Astana
Bangladesh Standard Time:::(UTC+06:00) Dhaka
Omsk Standard Time:::(UTC+06:00) Omsk
Myanmar Standard Time:::(UTC+06:30) Yangon (Rangoon)
SE Asia Standard Time:::(UTC+07:00) Bangkok, Hanoi, Jakarta
Altai Standard Time:::(UTC+07:00) Barnaul, Gorno-Altaysk
W. Mongolia Standard Time:::(UTC+07:00) Hovd
North Asia Standard Time:::(UTC+07:00) Krasnoyarsk
N. Central Asia Standard Time:::(UTC+07:00) Novosibirsk
Tomsk Standard Time:::(UTC+07:00) Tomsk
China Standard Time:::(UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi
North Asia East Standard Time:::(UTC+08:00) Irkutsk
Singapore Standard Time:::(UTC+08:00) Kuala Lumpur, Singapore
W. Australia Standard Time:::(UTC+08:00) Perth
Taipei Standard Time:::(UTC+08:00) Taipei
Ulaanbaatar Standard Time:::(UTC+08:00) Ulaanbaatar
Aus Central W. Standard Time:::(UTC+08:45) Eucla
Transbaikal Standard Time:::(UTC+09:00) Chita
Tokyo Standard Time:::(UTC+09:00) Osaka, Sapporo, Tokyo
North Korea Standard Time:::(UTC+09:00) Pyongyang
Korea Standard Time:::(UTC+09:00) Seoul
Yakutsk Standard Time:::(UTC+09:00) Yakutsk
Cen. Australia Standard Time:::(UTC+09:30) Adelaide
AUS Central Standard Time:::(UTC+09:30) Darwin
E. Australia Standard Time:::(UTC+10:00) Brisbane
AUS Eastern Standard Time:::(UTC+10:00) Canberra, Melbourne, Sydney
West Pacific Standard Time:::(UTC+10:00) Guam, Port Moresby
Tasmania Standard Time:::(UTC+10:00) Hobart
Vladivostok Standard Time:::(UTC+10:00) Vladivostok
Lord Howe Standard Time:::(UTC+10:30) Lord Howe Island
Bougainville Standard Time:::(UTC+11:00) Bougainville Island
Russia Time Zone 10:::(UTC+11:00) Chokurdakh
Magadan Standard Time:::(UTC+11:00) Magadan
Norfolk Standard Time:::(UTC+11:00) Norfolk Island
Sakhalin Standard Time:::(UTC+11:00) Sakhalin
Central Pacific Standard Time:::(UTC+11:00) Solomon Is., New Caledonia
Russia Time Zone 11:::(UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky
New Zealand Standard Time:::(UTC+12:00) Auckland, Wellington
UTC+12:::(UTC+12:00) Coordinated Universal Time+12
Fiji Standard Time:::(UTC+12:00) Fiji
Kamchatka Standard Time:::(UTC+12:00) Petropavlovsk-Kamchatsky – Old
Chatham Islands Standard Time:::(UTC+12:45) Chatham Islands
UTC+13:::(UTC+13:00) Coordinated Universal Time+13
Tonga Standard Time:::(UTC+13:00) Nuku’alofa
Samoa Standard Time:::(UTC+13:00) Samoa
Line Islands Standard Time:::(UTC+14:00) Kiritimati Island
Thank you, it is working now.
Nick, Ahmed and Eric, I had the same problem.
Did some search on the internet.
I changed ‘GMT Standard TIme” to ‘Central Europe Standard TIme’ but same problem because of the summertime ?
I changed it to “Azores Standard Time”And now it seems to be ok.
Not the most beautiful solution, but it works for me 🙂
(https://devkimchi.com/2018/02/05/converting-utc-to-local-time-via-azure-functions-and-logic-apps)
Thank you, Nick, for posting this solution, and thank you, onderduiker, for the time zone solution! I truly appreciate this info as I was stumped.
Hi,
I have tried convertToUtc(triggerOutputs()?[‘body/start’], ‘Eastern Standard Time’) and convertToUtc(triggerOutputs()?[‘body/start’], ‘ US Eastern Standard Time’), but each time it’s still syncs over 4 hours later on google. Am I doing something wrong with the time zone?
THanks,
Z
Hi,
I am trying this and running into an issue. I’ve tried all above and also tried using this, convertToUtc(triggerOutputs()?[‘body/start’], ‘Eastern Standard Time’)
The problem is all of the meetings are coming in 4 hours later on the Google calendar. Any suggestions?
Thanks,
Z
Here is the solution:
concat(triggerOutputs()?[‘body/Start’], ‘Z’)
concat(triggerOutputs()?[‘body/End’], ‘Z’)
Use these expressions, instead of convertToUtc will solve the issue of time difference. The downside of using “Azores Standard Time” is that when Daylight saving time ends in November, the created events on Google Calendar will be 1 hour ahead of that of Outlook.
Thanks Naiqi, I tried your solution and works perfectly and the most important thing, it doesn´t need any condition if we are on Daylight Saving or not. My only suggestion to the other users is to manually add the quotes as when I pasted it, it changed from straight to curly, so the expression was giving me errors.
@Naiqi, thank you for sharing!
Just to confirm (before I edit the main post), I am changing the two expressions:
convertToUtc(triggerOutputs()?['body/start'], 'GMT Standard Time')convertToUtc(triggerOutputs()?['body/End'], 'GMT Standard Time')
to:
concat(triggerOutputs()?[‘body/Start’], ‘Z’)concat(triggerOutputs()?[‘body/End’], ‘Z’)
And this then fixes the timezone issues for everyone?
It appears to work just fine with the exception that the commas in the new string are wrong and Flow wont accept them. Should be:
concat(triggerOutputs()?[‘body/Start’], ‘Z’)
concat(triggerOutputs()?[‘body/End’], ‘Z’)
This seems to work great for me and I appreciate your post about this Nick. It has helped me out significantly. I’m wondering though, is there a way to modify this or start a new flow that will update the events in GCal if they are modified in Office Calendar?
Hi,
This works for me, but I live in Brussels which is UTC +1. How can I change this?
(sorry if this is already in the comments…I can’t open them).
thanks a lot
Hi Nick, thanks for this great tutorial. This solved the problem of getting stuck at the error message but the resulting events are off by an hour. I’m guessing there is some information getting lost in translation. Did you notice the same thing at all? Also, there is a trigger event called “add/modify/delete outlook event”. Have you tried working with that trigger at all? I’m wondering if that would be a more useful trigger than this.
in mine office 365 calendar meeting is scheduled for 6:00 PM-8PM ,but when i use above code converttoUtc(triggerOutputs()?[‘body/start’], ‘Central Europe Standard Time’) , this meetings shows 4:00 PM -6 PM how come?
Helped me, thanks!
I tried to do this reverse and sync the google calendar with Outlook. However, Power Automate gives me an error every time I use this. Is there any expression that I can use to interpret the Google Calendar time&date format to outlook?