Get a list of calendar events and organize them by day

[MRKennedy]

I have a requirement to get a list of events from an outlook calendar for the current month, and organize the events by day.  I have viewed many tutorials and was able to easily find a way to query the calendar using the "Get calendar view of events (V3)" for the month and easily print the monthly calendar in the following format.

the flow to create this output is below:

The challenge I am facing is that I would like the output to be by day similar to the following format: 

Sunday, January 1st, 2023

Start Time   End Time   Subject

9:00AM       10:00AM     Subject Title 1

10:00AM       11:00AM     Subject Title 2

....   

...

Monday, January 2nd, 2023

Start Time   End Time   Subject

10:00AM       11:00AM     Subject Title 1

....

        I realize I can query "Get calendar view of events (V3)" for each day of the month, but that function has a lot of overhead and will be running this flow for multiple calendars, I would like to query the "Get calendar view of events (V3)" once for the month, and then format/parse the data later in the flow to achieve the desired format.  Is there an elegant way to do this?

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for.

For this example, I've got three events in the current month, two of which are on the same day.

See full flow below. I'll go into each of the actions.

Get calendar view of events retrieves events created this month. Below are the expressions used, which includes converting the current UTC time to my time zone. I've also added an Order By using start/DateTime so the events come back in the correct order.

//Start Time
startOfMonth(convertTimeZone(utcNow(), 'utc', 'E. Australia Standard Time'))

//End Time
startOfMonth(addToTime(convertTimeZone(utcNow(), 'utc', 'E. Australia Standard Time'), 1, 'month'))

Select Events builds up my objects, again converting the UTC dates to my time zone.

//Start time
convertTimeZone(item()?['start'], 'utc', 'E. Australia Standard Time')

//End time
convertTimeZone(item()?['end'], 'utc', 'E. Australia Standard Time')

//Date - will be used to filter the events per day
convertTimeZone(item()?['start'], 'utc', 'E. Australia Standard Time', 'yyyy-MM-dd')

Select Dates uses the output from Select Events and extracts out just the Date for grouping the events. The expression used is:

item()?['Date']

Initialize variable HTML creates a new string variable called html that will eventually hold all the tables, etc.

Apply to each iterates through each of the dates. Because we can have multiple events on the same date, we need to remove duplicates using the union expression.

union(body('Select_Dates'), body('Select_Dates'))

Filter array uses the output from Select Events and the following expression. This will give us all the events for the current date we are iterating over.

item()?['Date']

//Current Item is:
items('Apply_to_each')

Append to string variable start adds the current date as a Heading 4 element, then the start of the table. The date is also formatted to look like what you had in your example.

<h4>@{formatDateTime(items('Apply_to_each'), 'dddd, MMMM d, yyyy')}</h4>
<table>
    <thead>
        <tr>
            <th>Start Time</th>
            <th>End Time</th>
            <th>Subject</th>
        </tr>
    </thead>
    <tbody>

Apply to each event then iterates over each of the events in the Filter array (events from the current date).

Append to string variable row builds up each of the table rows with the event data. Below are the expressions used:

formatDateTime(items('Apply_to_each_event')?['Start time'], 'HH:mm tt')
formatDateTime(items('Apply_to_each_event')?['End time'], 'HH:mm tt')
items('Apply_to_each_event')?['Subject']

The full input is:

<tr>
<td>@{formatDateTime(items('Apply_to_each_event')?['Start time'], 'HH:mm tt')}</td>
<td>@{formatDateTime(items('Apply_to_each_event')?['End time'], 'HH:mm tt')}</td>
<td>@{items('Apply_to_each_event')?['Subject']}</td>
</tr>

After we've built up each of the rows, we use Append to string variable end to add the remaining part of the table.

</tbody>
</table>

The html variable will now contain all the html that we need (headers, tables, etc.).

Compose Style is some CSS to make the tables look nicer in the email. I've just used some CSS that I use for a lot of my tables - you might have your own.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Send an email uses the output from Compose Style and the html variable content.

The email output in this example is:

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[MRKennedy]

Your output is exactly what I am looking for, and you are provided exceptional instructions.  However, I am getting the error that is likely "user error" but over the last hour, I can't resolve it: 

"Unable to process template language expressions in action 'Append_to_string_variable' inputs at line '0' and column '0': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'."

At the following step: 

This is my code in the expression: "formatDateTime(items('Apply_to_each'), 'dddd, MMMM d, yyyy')"

Any idea why it's returning an object versus a string?

[grantjenkins]

@MRKennedy For your Select Dates, are you setting the Map to Text mode? See arrow on screenshot.

[MRKennedy]

Yes, I have tried using both options.   It appears my "Filter array" is producing zero output.

[grantjenkins]

It looks like you're adding your expressions as plain text. See screenshot below on how to add the expressions.

[MRKennedy]

First, thank you for all of your help!  This morning, I started from scratch and recreated the flow using the directions you initially provided to ensure I did not "fat finger" something and I used the dialog as shown above.  However, I still receive the same error in the same location as above.  are there any screen capture, error logs I can post to assist with the troubleshooting?  Clearly it works, as you were able to generate  my desired output.  But I am not sure what I am doing wrong.

Thank you again in advance!

[MRKennedy]

@grantjenkins Any other suggestions? Or things that I may be able to try to get past this error?

[grantjenkins]

Are you able to provide screenshots of your entire flow similar to how I've added mine so I can see where you may have gone wrong.

[MRKennedy]

I was able to get it working!  Your instructions were perfect - thank you!