Possible bug or in-built error: Recurrence Time Zone doesn't work and Convert Time Zone can't fix it

[IserLuick]

Hi everyone!

I'm a beginner trying to make a simple flow which does the following:

1.- It triggers everyday at 00:00.
2.- It gets a list of the events programmed on Google Calendar for that day.
3.- It creates a task for every event in a Google Tasks list.

The problem I'm struggling with is that Flow is stuck in UTC time zone, and my time zone is UTC-6. I've been trying to fix this all day but I can't get it to work properly.

Let me explain this problem in detail with images and some code, as well as the solutions that didn't work:

For step 1, I set a Recurrence trigger:

For step 2, I set a "List the events on a calendar" action to get the events for today from Google Calendar:

For step 3, I set a "Create a task in a task list" applied to each event in the list provided from step 2:

This flow seems pretty straight forward and should work; however, it doesn't. When I run the flow, the events list that is generated from Google Calendar has the following code:

[
  {
    "kind": "calendar#event",
    "etag": "\"3099287415345000\"",
    "id": "7oujmmbp151ir55r7hpqa1sfcf",
    "status": "confirmed",
    "htmlLink": "https://www.google.com/calendar/event?eid=N291am1tYnAxNTFpcjU1cjdocHFhMXNmY2YgbHVpc2VyaWNreGNveUBt",
    "created": "2019-02-06T20:18:17Z",
    "updated": "2019-02-09T19:23:47.064Z",
    "summary": "Cita Endocrinólogo 18:00",
    "description": "Cita con el endocrinólogo.",
    "colorId": "11",
    "creator": "luiserickxcoy@gmail.com",
    "organizer": "luiserickxcoy@gmail.com",
    "start": "2019-02-09T00:00:00+00:00",
    "end": "2019-02-09T01:00:00+00:00",
    "iCalUID": "7oujmmbp151ir55r7hpqa1sfcf@google.com",
    "sequence": 0,
    "extendedProperties": {
      "private": {
        "everyoneDeclinedDismissed": "-1"
      }
    },
    "reminders": {
      "useDefault": false,
      "overrides": [
        {
          "method": "popup",
          "minutes": 10
        },
        {
          "method": "popup",
          "minutes": 15
        },
        {
          "method": "popup",
          "minutes": 5
        }
      ]
    },
    "location": "",
    "attendees": "",
    "endTimeUnspecified": false
  },
  {
    "kind": "calendar#event",
    "etag": "\"3099480479780000\"",
    "id": "533n8iml7vtmk4591699rtadlt_20190209T030000Z",
    "status": "confirmed",
    "htmlLink": "https://www.google.com/calendar/event?eid=NTMzbjhpbWw3dnRtazQ1OTE2OTlydGFkbHRfMjAxOTAyMDlUMDMwMDAwWiBsdWlzZXJpY2t4Y295QG0",
    "created": "2019-02-07T14:42:58Z",
    "updated": "2019-02-09T19:23:59.89Z",
    "summary": "Mantenimiento dermatológico tarde",
    "description": "\nLavarse la cara con el jabón especial\nPoner 6 disparos de Capilarium",
    "colorId": "11",
    "creator": "luiserickxcoy@gmail.com",
    "organizer": "luiserickxcoy@gmail.com",
    "start": "2019-02-09T03:00:00+00:00",
    "end": "2019-02-09T03:30:00+00:00",
    "recurringEventId": "533n8iml7vtmk4591699rtadlt_R20190209T030000",
    "originalStartTime": {
      "dateTime": "2019-02-09T03:00:00+00:00",
      "timeZone": "America/Mexico_City"
    },
    "iCalUID": "533n8iml7vtmk4591699rtadlt_R20190209T030000@google.com",
    "sequence": 1,
    "extendedProperties": {
      "private": {
        "everyoneDeclinedDismissed": "-1"
      }
    },
    "reminders": {
      "useDefault": true
    },
    "location": "",
    "attendees": "",
    "endTimeUnspecified": false
  },
  {
    "kind": "calendar#event",
    "etag": "\"3099100883760000\"",
    "id": "4ndb18msjpcatol241klr0o2dl_20190209T140000Z",
    "status": "confirmed",
    "htmlLink": "https://www.google.com/calendar/event?eid=NG5kYjE4bXNqcGNhdG9sMjQxa2xyMG8yZGxfMjAxOTAyMDlUMTQwMDAwWiBsdWlzZXJpY2t4Y295QG0",
    "created": "2019-02-07T14:15:52Z",
    "updated": "2019-02-07T14:40:41.88Z",
    "summary": "Aseo personal",
    "description": "\nBañarse\nPonerse desodorantes\nVestirse",
    "colorId": "11",
    "creator": "luiserickxcoy@gmail.com",
    "organizer": "luiserickxcoy@gmail.com",
    "start": "2019-02-09T14:00:00+00:00",
    "end": "2019-02-09T15:00:00+00:00",
    "recurringEventId": "4ndb18msjpcatol241klr0o2dl",
    "originalStartTime": {
      "dateTime": "2019-02-09T14:00:00+00:00",
      "timeZone": "America/Mexico_City"
    },
    "iCalUID": "4ndb18msjpcatol241klr0o2dl@google.com",
    "sequence": 1,
    "extendedProperties": {
      "private": {
        "everyoneDeclinedDismissed": "-1"
      }
    },
    "reminders": {
      "useDefault": true
    },
    "location": "",
    "attendees": "",
    "endTimeUnspecified": false
  },
  {
    "kind": "calendar#event",
    "etag": "\"3099276930168000\"",
    "id": "3rvvhnh6onm5m8798r7cm0hrnl_20190209T150000Z",
    "status": "confirmed",
    "htmlLink": "https://www.google.com/calendar/event?eid=M3J2dmhuaDZvbm01bTg3OThyN2NtMGhybmxfMjAxOTAyMDlUMTUwMDAwWiBsdWlzZXJpY2t4Y295QG0",
    "created": "2019-02-07T14:41:10Z",
    "updated": "2019-02-08T15:07:45.084Z",
    "summary": "Mantenimiento dermatológico mañana",
    "description": "Lavar la cara con el jabón especial\nPoner los 6 disparos de Capilarium\nPoner protector solar",
    "colorId": "11",
    "creator": "luiserickxcoy@gmail.com",
    "organizer": "luiserickxcoy@gmail.com",
    "start": "2019-02-09T15:00:00+00:00",
    "end": "2019-02-09T15:30:00+00:00",
    "recurringEventId": "3rvvhnh6onm5m8798r7cm0hrnl",
    "originalStartTime": {
      "dateTime": "2019-02-09T15:00:00+00:00",
      "timeZone": "America/Mexico_City"
    },
    "iCalUID": "3rvvhnh6onm5m8798r7cm0hrnl@google.com",
    "sequence": 3,
    "extendedProperties": {
      "private": {
        "everyoneDeclinedDismissed": "-1"
      }
    },
    "reminders": {
      "useDefault": true
    },
    "location": "",
    "attendees": "",
    "endTimeUnspecified": false
  }
]

As you can see, all the events are shifted by 6 hours; for example, the event named "Cita con el endocrinólogo 18:00" happened yesterday, at 18:00 in UTC-6 time, on February 8; however, the time was shifted 6 hours forward to 00:00 on February 9 and it showed as an event for today; similarly, events that happened at 18:00 today weren't listed because of the same problem, probably shifted to 00:00 on February 10.

I checked my Time Zone settings on Google Calendar and I made sure that it is UTC-6 in the general settings, in the particular calendar and even in each individual event. All the dates and times on Google Calendar are correct and my notifications arrive in a timely manner, so I'm pretty sure this is not the source of the problem.

I tried converting the time zone:

And it doesn't work. Also, I tried using some code to achieve the same result:

And it doesn't work either. I tried changing my language and region settings:

And making this change didn't solve the problem.

I think I have exhausted my resources, as well as the options that I have seen recommended in other discussions. It seems that Flow is stuck in UTC time and can't shift to other time zone no matter what I try.

I'm inclined to think that this is a bug that only the developers can fix; however, if there is something that I'm missing that can fix this Flow, I'd love to try it.

Thank you very much for reading this post. If I didn't explain my problem understandably or you need more information to find a solution, I'm more than willing to provide everything I can.

Have a nice day!

PS: I tried to create this post under Building Flows, but it says "Marked as spam. If you believe this is an error, submit an abuse report."

[IserLuick]

 I finally found how to make this to work!

The error lies in the way that the event list is retrieved. Google Calendar stores events in UTC time. Quoting from https://webapps.stackexchange.com/questions/67777/google-calendar-event-in-utc-time-zone:

How Calender Treats time zones

Whenever you create an event, Calendar converts it from your time zone to UTC time, using currently known conversion rules. By using one universal time for all events, Calendar can keep all of your guests’ calendars consistent regardless of which time zones they're in. When we display the event on your calendar, it is converted from UTC to appear in your own time zone.

It seems that Microsoft Flow reads the events from Google Calendar directly from the stored data, which is in UTC time, that's why any Time Zone conversion in Flow won't work; however, I found a way around this.

1.- Recurrence Time Zone works fine, so it will trigger at 22:00 in UTC -6 time every day

2.- For the "List the events on a calendar" action I put the following expression for the min time:

addHours(utcNow(),+2)

And for the max time, I put the following expression:

addHours(utcNow(),+26)

utcNow() gives the current time in UTC time; in other words, If the Flow triggers at 22:00 in UTC-6, it will be 04:00 in UTC time. By adding the hours I make Flow tell Google Calendar to list the events programmed between 06:00 of today and 06:00 of tomorrow which, in UTC-6 time, would translate to the events listed between 00:00 of today and 00:00 of tomorrow.

3.- For the "Create a task in a task list" applied to each event in the list, I put the following expression for the Due time:

addHours(items('Apply_to_each')?['start'],-6)

This expression substracts 6 hours to the UTC time, converting it to UTC-6 so the Tasks can have the correct Due time in my time zone.

I've tested this and it now works as intended. I hope this can be helpful for more people struggling with similar problems with their Flows.

Have a nice day!

[IserLuick · Accepted solution]

I finally found how to make this to work!

The error lies in the way that the event list is retrieved. Google calendar stores the events in UTC time. Quoting from https://webapps.stackexchange.com/questions/67777/google-calendar-event-in-utc-time-zone:

How Calender Treats time zones

Whenever you create an event, Calendar converts it from your time zone to UTC time, using currently known conversion rules. By using one universal time for all events, Calendar can keep all of your guests’ calendars consistent regardless of which time zones they're in. When we display the event on your calendar, it is converted from UTC to appear in your own time zone.

It seems that Microsoft Flow reads the events from Google Calendar directly from the stored data, which is in UTC time, that's why any Time Zone conversion in Flow won't work; however, I found a way around this.

1.- Recurrence Time Zone works fine, so it will trigger at 22:00 in UTC -6 time every day

2.- For the "List the events on a calendar" action I put the following expression for the min time:

addHours(utcNow(),+2)

And for the max time, I put the following expression:

addHours(utcNow(),+26)

utcNow() gives the current time in UTC time; in other words, If the Flow triggers at 22:00 in UTC-6, it will be 04:00 in UTC time. By adding the hours I make Flow tell Google Calendar to list the events programmed between 06:00 of today and 06:00 of tomorrow which, in UTC-6 would translate to the events listed between 00:00 of today and 00:00 of tomorrow.

3.- For the "Create a task in a task list" applied to each event in the list, I put the following expression for the Due time:

addHours(items('Apply_to_each')?['start'],-6)

This expression substracts 6 hours to the UTC time, converting it to UTC-6 so the Tasks can have the correct Due time in my time zone.

I've tested this and it now works as intended. I hope this can be helpful for more people struggling with similar problems with their Flows.

Have a nice day!

[IserLuick]

I finally found how to make this to work!

The error lies in the way that the event list is retrieved. Google calendar stores the events in UTC time. Quoting from https://webapps.stackexchange.com/questions/67777/google-calendar-event-in-utc-time-zone:

How Calender Treats time zones

Whenever you create an event, Calendar converts it from your time zone to UTC time, using currently known conversion rules. By using one universal time for all events, Calendar can keep all of your guests’ calendars consistent regardless of which time zones they're in. When we display the event on your calendar, it is converted from UTC to appear in your own time zone.

It seems that Microsoft Flow reads the events from Google Calendar directly from the stored data, which is in UTC time, that's why any Time Zone conversion in Flow won't work; however, I found a way around this.

1.- Recurrence Time Zone works fine, so it will trigger at 22:00 in UTC -6 time every day

2.- For the "List the events on a calendar" action I put the following expression for the min time:

addHours(utcNow(),+2)

And for the max time, I put the following expression:

addHours(utcNow(),+26)

utcNow() gives the current time in UTC time; in other words, If the Flow triggers at 22:00 in UTC-6, it will be 04:00 in UTC time. By adding the hours I make Flow tell Google Calendar to list the events programmed between 06:00 of today and 06:00 of tomorrow which, in UTC-6 would translate to the events listed between 00:00 of today and 00:00 of tomorrow.

3.- For the "Create a task in a task list" applied to each event in the list, I put the following expression for the Due time:

addHours(items('Apply_to_each')?['start'],-6)

This expression substracts 6 hours to the UTC time, converting it to UTC-6 so the Tasks can have the correct Due time in my time zone.

I've tested this and it now works as intended. I hope this can be helpful for more people struggling with similar problems with their Flows.

Have a nice day!