Hello darkness, my old friend; I’ve come to talk with you again…
Been a while since I dabbled with Power Automate, and now that I am this song is going around in my head. :)
It’s a nice weekend, I should be out and about, instead I have this thing I want to try with Power Automate and so I am dabbling with that instead. With the album “Sounds of Silence” playing in the background, of course…
To begin with I want to get events from my Outlook Calendar. The “Get Events (V4)” connector seems to be the one for this.
If I simply do a Get events I get a lot of events. So filtering seems to be the way to go.
If I run the Flow it gives me a link where I can view the output. In Firefox it looks like this.
One would think this is the “real” output and I can maybe filter along the properties shown there. But that’s misleading (learnt from experience).
You see, this connector uses Graph API behind the scenes and to see the real output you must check the example in the Graph API documentation for list events (or run a query via Graph Explorer etc. and see for yourself).
While some things like Subject are as seen in the Power Automate output, others are not so:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "@odata.context":"https://graph.microsoft.com/v1.0/$metadata#users('cd209b0b-3f83-4c35-82d2-d88a61820480')/events(subject,body,bodyPreview,organizer,attendees,start,end,location)", "value":[ { "@odata.etag":"W/\"ZlnW4RIAV06KYYwlrfNZvQAAKGWwbw==\"", "id":"AAMkAGIAAAoZDOFAAA=", "subject":"Orientation ", "bodyPreview":"Dana, this is the time you selected for our orientation. Please bring the notes I sent you.", "body":{ "contentType":"html", "content":"<html><head></head><body><p>Dana, this is the time you selected for our orientation. Please bring the notes I sent you.</p></body></html>" }, "start":{ "dateTime":"2017-04-21T10:00:00.0000000", "timeZone":"Pacific Standard Time" }, "end":{ "dateTime":"2017-04-21T12:00:00.0000000", "timeZone":"Pacific Standard Time" }, "location": { "displayName": "Assembly Hall", "locationType": "default", "uniqueId": "Assembly Hall", "uniqueIdType": "private" } ... } |
Thus, for instance, to query the start time I must filter it not in a way you’d expect, but like this:
start/dateTime lt '2022-10-29T14:30:00.0000000' and start/dateTime gt '2022-10-29T12:30:00.0000000'
This gives me all the events starting during that time range specified.
Rather than hard coding here’s how I can get the current time in the correct format (see this page for codes): formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ss.0000000') for the start time; and formatDateTime(addMinutes(utcNow(),15),'yyyy-MM-ddTHH:mm:ss.0000000') for the end time (which is 15 mins away, but I can change that as needed).
Taking it one step further to filter by subject too.
|
1 |
start/dateTime ge '@{variables('startTime')}' and start/dateTime le '@{variables('endTime')}' and contains(Subject,'Focus') |
Turns out there’s another way of getting this info, via the “Get calendar view of events (V3)” connector (corresponds to this Graph query I suppose):
I am going to ignore this latter one for now, just wanted to mention it here since I came across it.
What next? I want to see if there are any results or not. Remember the output looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
{ "value": [ { "originalStartTimeZone": "originalStartTimeZone-value", "originalEndTimeZone": "originalEndTimeZone-value", "responseStatus": { "response": "", "time": "datetime-value" }, "iCalUId": "iCalUId-value", "reminderMinutesBeforeStart": 99, "isReminderOn": true } ] } |
The value property is an array. So see if that’s empty would be a good start? If there is no output here’s what I get:
So I can put it into a condition? Thus: empty(outputs('Get_events_(V4)')?['body/value'])
Ok, so that works.
Next I want to figure out the correct search query to use. Say I run this Flow every 15 mins. I want to capture the following sort of events:
Events that are starting in this 15 min window, and ending within or out of it. So that’s events with a start time that’s greater than the current time and less than 15 mins + the current time; and end time is less than or greater than 15 mins + the current time. Actually… come to think of it, the end time doesn’t matter, all I care about is that the event starts in this 15 min window.
I can capture these thus: (start/dateTime ge '@{variables('startTime')}' and start/dateTime le '@{variables('endTime')}')
Next, events that start before this 15 min window and end during this 15 min window or after the 15 min window. So that’s events with a start time that’s less than the current time; and end time is (a) greater than than the current time and less than the current time + 15 mins, or (b) greater than the end time.
Like this? (start/dateTime le '@{variables('startTime')}' and ((end/dateTime ge '@{variables('startTime')}' and end/dateTime le '@{variables('endTime')}') or (end/dateTime ge '@{variables('endTime')}')))
I think that covers everything? 🤞Plus, I also want to limit to events that contain a particular word in the subject – “Focus” – and if there are more than 1 results I want to order them by end time in descending order (so the farthest one is returned one).
My final search query is this, for now: ((start/dateTime ge '@{variables('startTime')}' and start/dateTime le '@{variables('endTime')}') or (start/dateTime le '@{variables('startTime')}' and ((end/dateTime ge '@{variables('startTime')}' and end/dateTime le '@{variables('endTime')}') or (end/dateTime ge '@{variables('endTime')}')))) and contains(Subject,'Focus')
Here’s what it looks like, with the order set:
That works! With three matching events I get them correctly:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[ { "subject": "Focus 3", "start": "2022-10-29T20:30:00.0000000", "end": "2022-10-29T22:00:00.0000000", "startWithTimeZone": "2022-10-29T20:30:00+00:00", "endWithTimeZone": "2022-10-29T22:00:00+00:00", "body": "", ... }, { "subject": "Focus", "start": "2022-10-29T20:00:00.0000000", "end": "2022-10-29T21:30:00.0000000", "startWithTimeZone": "2022-10-29T20:00:00+00:00", "endWithTimeZone": "2022-10-29T21:30:00+00:00", "body": "", ... }, { "subject": "Focus", "start": "2022-10-29T20:00:00.0000000", "end": "2022-10-29T21:00:00.0000000", "startWithTimeZone": "2022-10-29T20:00:00+00:00", "endWithTimeZone": "2022-10-29T21:00:00+00:00", "body": "", ... } ] |
I want to find the end time of the first event. I can do that like this: first(outputs('Get_events_(V4)')?['body/value'])?['end']
Eventually I want to get how long this event is from now, to do this I must convert that end time into ticks: ticks(variables('eventEndTime'))
I can get the time difference in minutes from now, thus: div(sub(variables('eventEndTimeInTicks'),ticks(utcNow())),600000000)
Thanks to this blog post for pointing the ticks method.