TL;DR
No rocket science. Just an Azure Logic App which retrieves Outlook 365 calendar items, runs those through an Azure Function logic to determine, which of my days are already to packed and need to be blocked or which blocks can be lifted again. The Logic App processes the response and executes a series of Create events
or Delete events
with the Outlook 365 connector. Fast forward...
Motivation
A while back - oh it's already 2 years ago - I posted on creating a simple daily sliding auto-blocker in Outlook 365 with Power Automate Flows. This at least helps me keeping some sanity in short term. I vary the blocking range between 1 and 3 days depending on the balance of consumed meeting time compared to the project (actual) work load I have to muster in times. To some degree people respect when I am blocked for the current day or the day after or at least pick up the phone or reach out in a chat to check for short term availability: fine.
Still my calendar is flooding with meeting requests in the 1 to 3 weeks time ranges and - at least with the amount of emails and such requests coming in - I am not always able to adaptively block spots in the future for some focus time I need to actually work on items committed in those meetings. I know there is Microsoft Viva Insights app in Microsoft Teams out there, but it seems that it is not yet deployed in our organization ... and ... as a programmer by heart, why not solve such a problem with some code.
I refer to myself as a programmer. After years of architecture and project management work I lack the amount of practice and patterns to live up to the standards of software developers or software engineers I have around me each day.
Basic idea
Microsoft Graph SDKs (to access Microsoft Outlook 365 calendar entries) are availabe for various languages and environments which could do the trick but I did not want to spend too much time coding - basically looking for some low code approach. I was already fiddling around bringing some kind of decission logic (in the magnitude I require to solve my problem) into Power Automate Flows, but this is really not what it's made for and the flows get messy quite fast. So I was thinking of putting the core decission logic in Azure Functions and then call if from a an outside flow, so that I do not have any Graph implementation complexity within the function. Azure Functions can be called from Power Automate Flows - check out a nice sample here from Rajkishore - but I wanted to try Logic Apps which also allowed me to have everything in one subscription - without the need to pass authentication information from the Office 365 to the Azure space.
Implementation
The resulting solution has this flow:
- Retrieve calendar items for a certain time into the future - by using
Get calendar view of events
; this flow element already projects recurring events in the response, which is not given byGet events
- Decide on which days to put blockers or where to remove blockers - by calling an Azure Function
- Delete blockers where required - by using the
Delete event
- Create blockers where required - by using the
Create event
Function App
As the Function App is referenced in the Logic App later, it is created first. I created a Function App with these specs:
- Publish: Code
- Runtime stack: Node.js
- Version: 14 LTS
- Hosting: Windows (which allow direct function edit/test), use Linux if you deploy from a repository (like I did later)
- Plan type: Consumption
Although I am not very versed in Javascript I decided for Node.js, as this allows processing of the request and response natively, without the serialization hustles of C# - where I have more mileage.
In the Function App I created a function checkCalendar
:
// expects a list of events in the body coming from Microsoft Graph me/calendar/{calendarId}/events
module.exports = function(context, req) {
const threshold = 300; // 5 hours
const markerSubject = 'AUTO-BLOCKER';
// explode events spanning multiple days
let events = explodeMultipleDays(req);
// go through each event and total duration
let totals = calculateTotals(events, markerSubject);
// determine when to create a blocker and when to remove it
let creates = [];
let deletes = [];
totals.forEach(e => {
if (e.isBlocked) {
if (e.total < threshold || e.totalBlocks > 1) { // delete block when below threshold or when redundant blocks
if (e.id) {
deletes.push({ day: e.day, id: e.id });
}
}
} else {
if (e.total > threshold) { // create block when above threshold
creates.push({ day: e.day, event: markerSubject });
}
}
});
context.res = {
body: {
creates: creates,
deletes: deletes,
totals: totals
}
};
context.done();
};
Date.prototype.addDays = function(days) {
const date = new Date(this.valueOf());
date.setDate(date.getDate() + days);
return date;
};
function explodeMultipleDays(req) {
let events = [];
req.body.forEach(e => {
if (e.isAllDay && e.start.substring(0, 10) < e.end.substring(0, 10)) {
var start = new Date(e.start);
var end = new Date(e.end);
var day = start;
while (day < end) {
var eClone = Object.assign({}, e);;
eClone.start = day.toISOString();
eClone.end = day.toISOString();
events.push(eClone);
day = day.addDays(1);
}
} else {
events.push(e);
}
});
return events;
}
function calculateTotals(events, markerSubject) {
let totals = [];
events.forEach(e => {
var start = new Date(e.start);
var end = new Date(e.end);
if (start.getDay() > 0 && start.getDay() < 6) { // only count weekdays
// create aggregation entry
var day = start.toISOString().substring(0, 10);
var entry = totals.find(e => e.day === day);
if (!entry) {
totals.push({
day: day,
total: 0,
totalBlocks: 0,
isBlocked: false
});
entry = totals.find(e => e.day === day);
}
// capture already blocked days
if (e.isAllDay && e.subject === markerSubject) {
if (entry) {
entry.isBlocked = true;
entry.totalBlocks++;
entry.id = e.id;
}
} else if (e.isAllDay && e.showAs !== 'free') { // count complete blocks
if (entry) {
entry.isBlocked = true;
entry.totalBlocks++;
}
} else if (e.showAs !== 'free' && !e.isAllDay) { // only count busy days
var duration = (end.getTime() - start.getTime()) / 60000; // minutes
if (entry) {
entry.total += duration;
}
}
}
});
return totals;
}
I moved the function to this GitHub repository and used the debugging experience of Visual Studio Code and Codespaces to run and tune with several sample inputs. This GitHub repo I then connected to the Azure Function using the Deployment center. Really a nice experience for this kind of not-so-mission-critical scenario.
This function has to be able to process a request in this format (roughly):
[
{
"subject": "Meeting one",
"start": "2021-12-03T06:30:00.0000000",
"end": "2021-12-03T07:30:00.0000000",
"isAllDay": false,
"showAs": "busy",
...
}, {
"subject": "Multi day event",
"start": "2021-12-04T00:00:00.0000000",
"end": "2021-12-07T00:00:00.0000000",
"isAllDay": true,
"showAs": "busy",
...
}
]
and returns 3 arrays:
{
"creates": [
{
"day": "2021-12-15",
"event": "AUTO-BLOCKER"
}
],
"deletes": [
{
"day": "2021-12-14",
"id": "AAMkADdjMzcxNzA0LTMxZTEtNDk0ZS1iMDk1LTU0YjI4N2MyN2RjNABGAAAAAABi-...."
}
],
"totals": [
{
"day": "2021-12-03",
"total": 60,
"totalBlocks": 0,
"isBlocked": false,
"id": ""
},...
]
}
-
creates
has all the days for which a new blocker needs to be created -
deletes
has all event ids for which an existing blocker can be removed again -
totals
just for debugging / information how the single days are evaluated-
total
: number of total minutes on a day (overlapping meetings not yet considered correctly) -
totalBlocks
: number of already existing blockers found for a day - in case of duplicates due to incorrect logic, these duplicate are removed over several cycles -
isBlocked
: day already has an existing blocker -
id
: event id for the last existing blocker found
-
I am aware that the logic above is very rudimentary and does yet not even consider overlapping meetings correctly, resulting in much higher "total meeting time in a day". I will fix this over time and update the above mentioned repository. Also the logic currently operates on UTC - which is very close to my own time zone.
Logic App
I created the Logic App with a Recurrence trigger. To query the relevant range of calendar events, I initialize 2 variables:
-
startDate
, a string with expressionsubstring(string(utcNow()),0,10)
returning only the current date in YYYY-MM-DD format -
endDate
, a string with expressionsubstring(string(addDays(utcNow(),90)),0,10)
returning a date in 90 days also in YYYY-MM-DD format
I use Get calendar view of events
and pass in the above variables as a filter and then sort by start/dateTime
to get events in the correct order for processing in the function. When creating this flow element, you are asked to sign in to Outlook 365 for the outlook365 API connection that is created.
Now the function from above can be selected - for simplicity I have it in the same subscription and resource group. As Request Body I pass in @body('Get_calendar_view_of_events_(V3)')?['value']
.
In parallel actions I delete and create the blocker events.
On the left I loop over the to be deleted events json(body('checkCalendar'))?['deletes']
. The id to be deleted is referenced by item()?['id']
.
On the right I loop over the to be created events json(body('checkCalendar'))?['creates']
End and Start time are referenced by item()?['day']
and Subject by item()?['event']
.
For good measure I added a 3 and 7 second delay left and right as I experienced some retries when running with a big series of operations.
As always with Logic Apps, the tricky part - at least for me - is to figure out these expressions to address the various elements correctly.
Conclusion
As stated above the logic is far from perfect. With this base setup I am able to observe, evolve and fine tune what I am trying to achieve.
The big question remains: Will this have a positive effect on my meeting vs. project/actual work balance? I think that tools just can support. In the end just my own discipline can keep me in a healthy balance.
Top comments (1)
I had to fix the code to explode multiple days - I did not clone the events object correctly / at all and was working on the same instance.