How to process Teams attendance report with Power Automate
Posted On August 25, 2021
“I’d like to track a history of participants on regular Teams meeting, can I somehow get information from the attendance report with Power Automate?”
MS Teams has a nice feature that’ll allow the meeting organiser to download an attendance report. You’ll get a .csv file with information about the meeting and the participants. There’s a full name, join and leave time, duration, email address, and role for each of them. The .csv report is easy to read if you open it in Excel as Excel will format it in an easy to read way. But without Excel it’s not so pretty. And if you want to process it automatically, you’ll need to process the original file in its format.
This post will describe how to parse the .csv file so you can process the participants data, e.g. to track the attendance in a SharePoint list.
Store the file, trigger the flow
Power Automate doesn’t have an action that would download the report automatically, the first step will be always manual. Download the file and upload it to a specific location, e.g. SharePoint document library. You’ll have to manually download and upload the file after each meeting.
Use that folder in a trigger ‘When a file is created in folder’.
When you do a test run using only the trigger and some dummy action, you’ll see the actual format of the .csv file. There’s some general information about the meeting at the top, and below it the participant information, e.g.
Total Number of Participants 2
Meeting Title Parse Teams .csv
Meeting Start Time 25. 6. 2021 15:00:28
Meeting End Time 25. 6. 2021 16:40:07
Debug Id 05866461-4479-4492-bf29-24704e9c4caf
Full Name Join Time Leave Time Duration userPrincipalName Role
Tomas Riha 25. 6. 2021 15:00:28 25. 6. 2021 16:40:06 1h 39m firstname.lastname@example.org Organizer
Pepa Zdepa 25. 6. 2021 15:05:15 25. 6. 2021 16:40:07 1h 34m email@example.com Presenter
That’s the data to extract.
Skip the general information
The first step is to skip all the meeting information at the top as we’re interested only in the participants. The easiest way is to skip the corresponding number of rows. In this situation it’s 8 lines: 6 rows + 1 empty row + 1 row with the attendees table header.
But to skip rows, you must first tell the flow what the rows are. Tell the flow to split(…) the file content by the new line character.
split(item(),decodeUriComponent('%09')) ...Join Time
split(item(),decodeUriComponent('%09')) ...Leave Time
split(item(),decodeUriComponent('%09')) ...userPrincipalName (email)
Get the duration in minutes
The format of the Join Time and Leave Time depends on your regional settings, so I’ll ignore those columns. The column that seems to be the same is the Duration. And that’s also probably the more important one. But the default format of duration is also hard to work with, calculate with and hard to format. It’d be much easier to convert it into a single number of minutes.
Note: this section is optional, if you’re fine with the Duration format you can keep it as it is.
Process the hours
Again, start from directly accessing the Duration value, that’ll give you ‘-h –m’.
To get the actual number of minutes, you must split(…) it once more. Split it by ‘h ‘ and take only the 1st part, the number of hours on index 0.
The number of hours should be multiplied by 60 to get number of minutes with the mul(…) expression. But to do that, you must also convert the number of hours into integer with the int(…) expression. The result will be the number of hours in minutes.
The result will be a number of minutes the user spent on the meeting.
Full flow diagram
The Teams attendance report is a nice feature, although I wouldn’t call it ‘Power Automate friendly’. But with a few basic expressions, mostly the split(…) expression and indexing in the result array, you can extract the data for further processing. You can store the attendance data for each meeting separately, or aggregate it for each user in a SharePoint list for some long term summary. Or you can build further logic depending on the time spent on the meeting. Once you have the data you can process it in any way you like. All you need to do is to manually download the attendance report from Teams and upload it to a SharePoint library.