Export Planner assigned users into Excel with Power Automate
“I’m stuck on exporting Planner tasks to Excel because some task are assigned to multiple users and Power Automate doesn’t store a nice list of names in a string!”
There’s already a blog post on how to display assigned users in a Planner task, but the post covers only two situations. It can be used when the tasks have 1 assignee, or when you create an html table with the tasks. But there’s another situation where you might need to translate the userId into user name. For example, when you export the tasks to an Excel file. If there’s more than 1 assignee, you can’t simply ‘Get user profile’. At the same time there’s no html table where you could later replace the userId. Since you’re creating the Excel rows one by one, you must get the user names before adding a new row.
This post will show you how to create a mapping table between the userId and user name, and how to use it to create the export.
Get all userIds
The first step is similar to the html report in previous post. You must get all the users who have assigned any Planner task. The xpath(…) expression will help you with that, it’ll give you all userId from the ‘List tasks’ output in an array. Use it in a ‘Compose’ action.
xpath(xml(json(concat('{"body":{"value":', body('List_tasks')?['value'] , '}}'))), '/body/value/_assignments/userId/text()')
Remove all the duplicates with the union(…) expression, keep each userId only once.
union(outputs('Compose'),outputs('Compose'))
And at this point the similarity with the previous solution ends. You’ve got the unique users userId, now it’s time to create the mapping table.
Create userId : user name mapping table
A mapping table will help you to convert the userId into a user name. Instead of calling the ‘Get user profile’ over and over again, you can call it only once per userId and store the value in the table. You can image the format of the table as below:
UserID1: Tom Riha
UserID2: Paul Murana
UserID3:...Each time you encounter one of the userId’s, you can take the corresponding name from the table. You don’t need to call ‘Get user profile’ again.
Initialize an object variable for the mapping table, for this post it’ll be called var_mappingTable. The object variable can’t be empty for this flow, therefore, add a default empty value.
{ "": ""}
Fill the mapping table
Add ‘Apply to each’ to loop through all the unique users (output from ‘Compose 2’) and ‘Get user profile’ for each of them. The currently processed id is referenced by the item() expression. This is the only place where you need the ‘Get user profile’ action.
item()
Now you’ve got two values. You’ve got the userId (the currently processed item), and you’ve got the user profile. These are the two values you need for the mapping table: userId and user name (or any other value from the user profile). Add them to the mapping table with the addProperty(…) expression in a ‘Compose’ action.
addProperty(variables('var_mappingTable'),item(),outputs('Get_user_profile_(V2)_-_get_user_by_userId')?['body/displayName'])
And store the new, updated mapping table in the original object variable. The ‘Compose’ is just a middle step in this situation because you can’t self-reference a variable. At the end of this loop you’ll have the complete mapping table for all relevant users.
With only 2 users the table would look like this:
Use the mapping table
The mapping table is ready, now it’s time to use it for the export. Since there might be multiple assigned users, you’ll need one more variable, this time an array variable, e.g. var_assignedUsers. Initialize the variable and add another ‘Apply to each’, this time to process the tasks.
For each of the Planner tasks, ‘Select’ only the userId from the ‘value assignments’. It’ll give you an array with only the userId in the same way you get e.g. emails from users.
item()?['userId']
And here comes the key part of the solution. You have an array with task assigned users userId’s. You have also a mapping table from userId to user name. Now it’s time to combine them together. Add another ‘Apply to each’ to loop through all the userId, and inside ‘Append to array variable’.
The ‘Append to array variable’ action is where you take a value from the mapping table based on the userId. The object variable contains all the userID’s and their corresponding names. But you want to take only 1 specific value (user name) from the variable. Only the value (user name) whose key is the currently processed userId. Utilising the JSON format of the variable, you can access the value by the key [‘userId]. And since userId is the currently processed value, it can be replaced by the item() expression.
This expression will look into the mapping table variable and search for user name by the userId. Store it into another variable (the array variable).
variables('var_mappingTable')?[item()]
At the end of the loop you’ll have all the user names in the array variable. Use the join(…) expression to convert them from an array into a string, and add it in the Excel row.
join(variables('var_assignedUsers'),', ')
The last step is to set the array variable back to null, to prepare it for a new list of users in the next loop (next task).
Full flow diagram
Summary
When you use Power Automate to export Planner tasks, and it doesn’t have to be only in Excel, there’re (like always) multiple solutions to export assigned users. The one described above is not the simplest one (that would start from the ‘Initialize variable 2’, and you’d use ‘Get user profile’ in the ‘Apply to each 3’ loop). But such solution could mean a massive amount of API requests. If you called ‘Get user profile’ once per user per task, even if you filter only some of the tasks, 1 user in 10 tasks would be 10 API requests.
The solution using a mapping table removes these duplicate API calls. Once you have the user in a mapping table, you can easily access it over and over again.