Last Friday, while working with one of our clients on an integration issue between Dynamics and the Microsoft Exchange global address book, he asked me if there was a way for them to have automated creation of events (calendar items) in their Microsoft Outlook calendar where the source of the data was an Excel spreadsheet in OneDrive. This is a short post, but might help someone out who needs to implement this.
My first thoughts were Server Side Sync of course, but there was no reason for these appointments to be in Dynamics. Well, seconds later my reaction was. This should be easy enough using the Excel and Outlook connectors. I started testing a few scenarios and within about 30 minutes, we had a solution that was ready to test. What better solution then creating a Power Automate Flow.
I created a manual flow first just to test this logic, but after that modified this to a scheduled flow.
The first step was to create an Excel spreadsheet with the data elements that we needed. I created a spreadsheet and a table within the spreadsheet and called it EventsTable.
This table can naturally grow or shrink based on the data that is needed.
Next was to create the first action in the flow (after the Manual Trigger, which we later replaced with schedule). We used the List rows present in a table action using the Excel Online (Business) connector.
Specified the Location, Document Library, Filename and the name of the table.
The next thing is looping through all the rows of the table, and creating Events from these. I added an Apply to each control and added an Outlook 365 Create event (V4) action to create the event record inside of Outlook 365.
That is basically it. I ran the flow to test it out, and within seconds I could see the three events (calendar appointments within my Outlook Web App).
The only thing left to do is change the manual trigger. I just went ahead and deleted the manual trigger and added the Recurrence (Schedule) trigger, and set this to run on a daily basis.
We can add additional conditions to check if the event is already there or update the Excel spreadsheet to make that an event has been created, but will leave that for future requirements.
Thank you Cristian for asking how to get this done…