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:

1. 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 by Get events
2. Decide on which days to put blockers or where to remove blockers - by calling an Azure Function
3. Delete blockers where required - by using the Delete event
4. 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": ""
    },...
  ]
}

1. creates has all the days for which a new blocker needs to be created
2. deletes has all event ids for which an existing blocker can be removed again
3. 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:

1. startDate, a string with expression substring(string(utcNow()),0,10) returning only the current date in YYYY-MM-DD format
2. endDate, a string with expression substring(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.