Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

How to process Teams attendance report with Power Automate

Posted on August 25, 2021August 26, 2021 by Tom

“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
Power Automate Teams attendance report

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
Power Automate Teams attendance report

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.

Power Automate Teams attendance report

Full flow diagram

Power Automate Teams attendance report

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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

9 thoughts on “How to process Teams attendance report with Power Automate”

  1. Matt says:
    February 27, 2022 at 8:16 pm

    Hi,
    Thanks for this, but I keep getting an error when I test it.
    The error is:
    Unable to process template language expressions in action ‘Compose_2’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.

    I have no idea what this means 🙂
    Any advice would be appreciated, thanks.

    Reply
    1. Tom says:
      March 5, 2022 at 7:13 pm

      Hello Matt,
      there might be a problem if the attendance is shorter than 1 hour, you might need to use an extended expression in the ‘Compose 2’ action that’ll check if it should calculate with hours or only minutes:
      if(contains(split(item(),decodeUriComponent('%09'))[3], 'h'),
      add(
      mul(int(split(split(item(),decodeUriComponent('%09'))[3],'h ')[0]),60),
      int(replace(split(split(item(),decodeUriComponent('%09'))[3],'h ')[1],'m',''))
      ),
      if(contains(split(item(),decodeUriComponent('%09'))[3], 'm'),
      add(
      int(split(split(item(),decodeUriComponent('%09'))[3],'m ')[0]),
      1
      ),
      1
      )
      )

      also I later found that the solution works only if you’re using Teams in English, the report changes the time format depending on local settings.

      Reply
  2. Nicy Scaria says:
    March 9, 2022 at 12:51 pm

    The input skip(split(triggerOutputs()?[‘body’],decodeUriComponent(‘%0A’)),8) returns the same code as output and it is being considered as a string in the future processes and not as an array.

    Reply
    1. Tom says:
      March 16, 2022 at 7:03 pm

      Hello Nicy,
      are you using the right dynamic content? Does the triggerOutputs()?[‘body’] contain any values? You can also try to split it by the other new line character ‘%0D%0A’ as described here: https://tomriha.com/how-to-replace-new-line-n-in-a-power-automate-expression/

      Reply
  3. David Roger says:
    April 29, 2022 at 11:56 pm

    Hi Tom,

    Thank you so much for this tutorial, it is super helpful. I have two questions. The first is that I am getting the same error message as Matt. In my case, I have an attendance report that had three people attend. It pulls the information for the 3 people just fine, but it hangs up when it gets to the fourth row and there is no information to split. I’m not sure how to get it to stop after the rows containing only actual information.

    Secondly, I would like to extract only the date from the Join column, as opposed to the date and time. I used the ‘%09’ as you recommended, which brought me to the value below:

    “Date”: “4/26/2022, 10:57:36 AM”

    From here, I would like to drop everything after the comma , but I am not sure how to do that. I think it requires a second split command, but I’m not sure what the command should look like.

    Thanks again for any help you can provide!

    Reply
    1. Tom says:
      May 3, 2022 at 8:14 pm

      Hello David,
      I always add a ‘Condition’ to check if the current row is not empty to get rid of the empty lines.
      Once you get the date you can use split() expression and take only the first part using index, as explained e.g. here: https://tomriha.com/how-to-split-file-name-for-further-processing-in-power-automate/

      To get rid of everything after the comma you could use:
      split(,’,’)[0]

      Reply
      1. David Roger says:
        May 3, 2022 at 10:29 pm

        Thank you so very much, Tom! I really appreciate it!

        Reply
  4. Prathima says:
    June 15, 2022 at 1:01 pm

    Hi,

    How to get the Meeting Name and Meeting ID from the report.

    Thanks.

    Reply
    1. Tom says:
      June 22, 2022 at 8:47 pm

      Hello Prathima,
      the Meeting Name is on the third row, and the Meeting Id is maybe the number on 6th row. Don’t use the skip(…), stop with the split() by the new line and select it using the index.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme