Microsoft Teams stores the attendance in a report directly after the meeting. In this blog, I will show you how you can retrieve that information and share it in an e-mail via a Power Automate flow.
Inspiration
This question from boredom:
Not looking for the list of invitees but those that actually attended the meeting (a subset of those invited).
Power Users Community thread: Capture Meeting Attendees
List attendance Records method
In one of my earlier blogs, I showed you how you could retrieve the transcript after the meeting. This setup uses a similar approach.
When navigating through the Graph API documents I noticed there was also a List meeting attendance reports method. The List meeting attendance reports method can be used to retrieve a list of attendance reports for an online meeting.
But the more surprising one was the List attendance records method. This method can be used to retrieve the attendance records of an online meeting. It even shows things like seconds joined in a meeting.
QuickChart.io
To make the flow a bit more challenging I also wanted to present that information in a visually appealing way. I decided to use show the information in a doughnut chart.
Paul Murana has written a great blog about this using the QuickChart.io approach. I highly recommend looking at his blog about this: How to generate a chart with Power Automate.
Flow setup
Checklist before you start:
– This example uses HTTP connector actions which are part of a premium feature, make sure you have a license that covers this.
– The HTTP actions also use an app (with the correct Graph API permissions) in Azure Active Directory for authentication.
1. Add a When an upcoming event is starting soon (V3) action.
a. Select your Calendar Id, in this case Calendar
2. Add a Get my profile (v2) action.
To retrieve my own email to send the message to.
3. Add a Initialize variable action.
a. Provide a Name, in this example onlineMeetingId
b. Select a Type, String
c. Leave Value empty
4. Add a second Initialize variable action.
a. Provide a Name, in this example AttendanceReportId
b. Select a Type, String
c. Leave Value empty
5. Add a Condition action.
In this condition the body field within the body is checked for the meetup-join hyperlink. A indexOf function is used. If this is found it will output 1. If it is not found (equal to -1), it will output 0.
a. Use the expression below for the where statement
| if(equals(indexOf(triggerOutputs()?[‘body/body’], ‘https://teams.microsoft.com/l/meetup-join/’), -1), 0, 1) |
view rawCheckifitisaTeamsMeeting hosted with by GitHub
6. Add a HTTP action (in the If Yes section).
A Get onlineMeeting method from the Graph API is used. In this case specifically with a $filter query parameter and the JoinWebUrl. This is information we can retrieve from the body/body of the event message.
a. Select the GET method
b. Use the URI from the code snippet below
| https://graph.microsoft.com/v1.0/users/@{outputs(‘Get_my_profile_(V2)’)?[‘body/id’]}/onlineMeetings?$filter=JoinWebUrl eq ‘@{if(equals(indexOf(triggerOutputs()?[‘body/body’], ‘https://teams.microsoft.com/l/meetup-join/’), -1), ‘No Teams Meeting’, concat(‘https://teams.microsoft.com/l/meetup-join/’, slice(split(split(triggerOutputs()?[‘body/body’], ‘https://teams.microsoft.com/l/meetup-join/’)[1], ‘class’)[0], 0, -2)))}’ |
view rawUriGetOnlineMeeting hosted with by GitHub
c. Select Active Directory OAuth for the Authentication, provide the details of your Azure AD App.
7. Add a Set Variable action (in the If Yes section).
a. In the Name field select the onlineMeetingId variable
b. In the Value field use the expression from the code snippet below
| body(‘HTTP’)[‘value’][0][‘id’] |
view rawSetVaronlineMeetingId hosted with by GitHub
8. Add a Delay until action (in the If Yes section).
a. In the Timestamp field use the expression from the code snippet below
| addMinutes(triggerOutputs()?[‘body/end’], 30, ‘yyyy-MM-ddTHH:mm:ssZ’) |
view rawDelayUntil30minutesafterscheduledendofmeeting hosted with by GitHub
9. Add a second HTTP action (in the If Yes section).
A List meetingAttendanceReports method is used to retrieve a list of attendance reports for the specific online meeting.
a. Select the GET method
b. Use the URI from the code snippet below
| https://graph.microsoft.com/v1.0/users/@{outputs(‘Get_my_profile_(V2)’)?[‘body/id’]}/onlineMeetings/@{variables(‘onlineMeetingId’)}/attendanceReports |
view rawUriListmeetingAttendanceReports hosted with by GitHub
c. Select Active Directory OAuth for the Authentication, provide the details of your Azure AD App.
10. Add a second Condition action (in the If Yes section).
In this condition a length function is used to check how many items are returned by the previous HTTP action. If this is greater than 0 we continue with the rest of the steps.
a. Use the expression below for the where statement
| length(body(‘HTTP_-_List_AttendanceReports’)[‘value’]) |
view rawCheckIfListAttendanceReportsReturnsResults hosted with by GitHub
11. Add a second Set Variable action (in the If Yes section of the second condition).
a. In the Name field select the AttendanceReportId variable
b. In the Value field use the expression from the code snippet below
| body(‘HTTP_-_List_AttendanceReports’)[‘value’][0][‘id’] |
view rawSetVarAttendanceReportId hosted with by GitHub
12. Add a third HTTP action (in the If Yes section of the second condition).
A List attendanceRecords method is used to retrieve the attendance records of the specific online meeting.
a. Select the GET method
b. Use the URI from the code snippet below
| https://graph.microsoft.com/v1.0/users/@{outputs(‘Get_my_profile_(V2)’)?[‘body/id’]}/onlineMeetings/@{variables(‘onlineMeetingId’)}/attendanceReports/@{variables(‘AttendanceReportId’)}/attendanceRecords |
view rawUriGetAttendanceRecords hosted with by GitHub
c. Select Active Directory OAuth for the Authentication, provide the details of your Azure AD App.
13. Add a Select action (in the If Yes section of the second condition).
a. Use the expression from the code snippet below in the From field
| body(‘HTTP_-_attendenceRecords’)[‘value’] |
view rawSelectFromAttendanceRecords hosted with by GitHub
b. Use the json from the code snippet below in the Map field
| { | |
| “User”: “@{item()[‘identity’][‘displayName’]}”, | |
| “Email”: “@{item()[’emailAddress’]}”, | |
| “Seconds in Meeting”: “@{item()?[‘totalAttendanceInSeconds’]}” | |
| } |
view rawSelectAttendanceRecords hosted with by GitHub
14. Add a second Select action (in the If Yes section of the second condition).
a. Use the Output from the first Select in the From field
b. Switch to text mode and use the expression below from the code snippet in the Map field
| item()?[‘User’] |
view rawSelectUser hosted with by GitHub
15. Add a third Select action (in the If Yes section of the second condition).
a. Use the Output from the first Select in the From field
b. Switch to text mode and use the expression below from the code snippet in the Map field
| item()?[‘Seconds in Meeting’] |
view rawSelectSecondsInMeeting hosted with by GitHub
16. Add a fourth HTTP action (in the If Yes section of the second condition).
A HTTP request is used to generate a QuickChart.io chart.
a. Select the POST method
b. Use https://quickchart.io/chart for the URI field
c. Use the body from the code snippet below
| { | |
| “backgroundColor”: “white”, | |
| “width”: 500, | |
| “height”: 250, | |
| “format”: “png”, | |
| “chart”: { | |
| “type”: “doughnut”, | |
| “data”: { | |
| “datasets”: [ | |
| { | |
| “data”: @{body(‘Select_-_Seconds_in_Meeting’)} | |
| } | |
| ], | |
| “labels”: @{body(‘Select_-_User’)} | |
| }, | |
| “options”: { | |
| “title”: { | |
| “display”: true, | |
| “text”: “Second in Meeting per Attendee” | |
| } | |
| } | |
| } | |
| } |
view rawCreateDoughnutQuickChart hosted with by GitHub
17. Add a Compose action (in the If Yes section of the second condition).
The image will be embedded into the body of e-mail message. This action is the create an IMG tag with a base64 version of the image.
a. Use the value from the code snippet below in the Inputs
| <img src=”data:@{body(‘HTTP_-_Quickchart’)[‘$content-type’]};base64,@{body(‘HTTP_-_Quickchart’)[‘$content’]} ” /> |
view rawBase64ImageTag hosted with by GitHub
18. Add a Send an email (v2) action (in the If Yes section of the second condition).
In this example I am sharing it with myself. Obviously, you could change this setup to share it with others, like the organisers or the attendees, etc.
a. In the To use the Mail field of the Get User Profile (v2)
b. In the Body use the Outputs of the Compose action
That is it for the setup of this example.
SharePoint and Power PlatForm 