How to process Teams attendance report with Power Automate
“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.
Meeting Summary
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 xxx@tomriha.com Organizer
Pepa Zdepa 25. 6. 2021 15:05:15 25. 6. 2021 16:40:07 1h 34m pz@tomriha.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(triggerOutputs()?['body'],decodeUriComponent('%0A'))Once you have the rows you can skip the first 8 of them.
skip(split(triggerOutputs()?['body'],decodeUriComponent('%0A')),8)
What you get as the output is an array where each item is one of the rows, one user information.
Extract the user information
Since it’s an array, you’ll have to loop through it in ‘Apply to each’ to process the items one by one.
In that loop you’ll extract all the pieces of information about each user. As you could see above, the pieces of information are separated by the tab (shown as \t) character.
Don’t try to split(…) the rows by the ‘\t’, it won’t work. You must encode the tab character in the same way you encode the new line character. The uri value for tab is %09.
decodeUriComponent('%09')Using an index to access the specific part of such split(…) you can directly access all the pieces of information and use it. The ‘Compose’ example below could be as well ‘Create item’, ‘Condition’, or any other flow action processing the attendees.
split(item(),decodeUriComponent('%09'))[0] ...Name
split(item(),decodeUriComponent('%09'))[1] ...Join Time
split(item(),decodeUriComponent('%09'))[2] ...Leave Time
split(item(),decodeUriComponent('%09'))[3] ...Duration
split(item(),decodeUriComponent('%09'))[4] ...userPrincipalName (email)
split(item(),decodeUriComponent('%09'))[5] ...Role
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’.
split(item(),decodeUriComponent('%09'))[3]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.
split(split(item(),decodeUriComponent('%09'))[3],'h ')[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.
mul(int(split(split(item(),decodeUriComponent('%09'))[3],'h ')[0]),60)Process the minutes
Now it’s time to process also the minutes. Start from the same split(…) as when processing hours, but this time take the minutes on index 1.
split(split(item(),decodeUriComponent('%09'))[3],'h ')[1]The result will be ‘–m’. Remove the ‘m’ with the replace(…) expression to keep only the number of minutes.
replace(split(split(item(),decodeUriComponent('%09'))[3],'h ')[1],'m','')Convert it to integer with the int(…) expression to get the number of minutes in format for calculations.
int(replace(split(split(item(),decodeUriComponent('%09'))[3],'h ')[1],'m',''))Calculate the whole duration in minutes
The last step is to use the add(…) expression to sum the hours in minutes and minutes together.
add(
mul(int(split(split(item(),decodeUriComponent('%09'))[3],'h ')[0]),60),
int(replace(split(split(item(),decodeUriComponent('%09'))[3],'h ')[1],'m',''))
)
The result will be a number of minutes the user spent on the meeting.
Full flow diagram
Summary
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.