“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.
Hi Tom,
I am working through gathering all assigned users for open tasks in Planner.
I discovered your information above, and I’ve created what I believe is a copy of it for educational purposes. I start with the list tasks action and follow along above until I get to the “Use the mapping table” section. I stopped there just to save and verify it would run, at which time I discovered my current error.
In the (first) “for each” action I get the following error:
The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Compose_2′)’ is of type ‘String’. The result must be a valid array.
For the compose 2 action it is just the union of the first compose action:
union(outputs(‘Compose’),outputs(‘Compose’))
as per your instructions.
Compose action (1) is copied right from your instructions for the xpath command.
xpath(xml(json(concat(‘{“body”:{“value”:’, body(‘List_tasks’)?[‘value’] , ‘}}’))), ‘/body/value/_assignments/userId/text()’)
I’ve checked and verified my entries and wondered if you can provide any insight. Did MS change something that breaks this in a newly entered flow based on your instructions above? (MS has been known to break things without saying anything.)
Any feedback would be appreciated.
Thanks,
Jimmy
Hello Jim,
I just tried to run the flow and it worked fine. I’d try to remove the input of ‘Apply to each’ and add it again – select the ‘Outputs’ of ‘Compose 2’ from the available dynamic content.
Hi Jim,
I had the same problem and the reply of Tom to your question is the correct answer because you have to select the ‘Outputs’ of ‘Compose 2’ and not the ‘Select’ one as I did.
Oh ! I found out that the you have to insert the provided text from this article in the expression field in order to solve the error message :
‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Compose_2′)’ is of type ‘String’. The result must be a valid array.
Hello Tom,
First off, thanks for putting this together. Your attention to detail is really appreciated.
I’ve followed all your steps exactly and checked them over several times, and when I get to this stage for the “Compose – fill mapping table” step:
addProperty(variables(‘var_mappingTable’),item(),outputs(‘Get_user_profile_(V2)_-_get_user_by_userId’)?[‘body/displayName’])
The flow checker gives this error:
Correct to include a valid reference to ‘Get_user_profile_(V2)_-get_user_by_userId’ for the input parameter(s) of action ‘Compose-_fill_mapping_table’.
I can’t figure out what the error is being caused by.. it seems like I’ve done everything as you’ve explained.
Thank you!
Hello Marlon,
the error tells you that there’s no action with name ‘Get user profile (V2) -get user by userId’ which you’re trying to use in the expression. If you’re following the post step by step then the action is missing a space after the – in the name, it should be ‘Get_user_profile_(V2)_-get_user_by_userId’ in the expression to include also the space.
Hi Tom,
Thank you for the detailed explanation of this function. I can’t wait to try it out. However, I am having the same issue as Marlon. I have tried adding a space in several ways to the Expression, but the Flow checker continues to reject it. Here is my current expression:
‘Get_user_profile_(V2)_- get_user_by_userId’
I have added a space after the dash, before “get”. I appreciate any help you can give!
Kevin
Never mind. 🙂 I had named the step differently. Now that that has been corrected, I am running a test, and it is working fabulously!
Thank you for your hard work with this!
Kevin
Hello Kevin,
when you reference any action it can’t have any spaces in the name, all the spaces will be replaced by an underscore, so for your action it would be ‘Get_user_profile_(V2)_-_get_user_by_userId’.
But I’m glad that you solved it. 🙂
Hi Marlon,
That’s funny how syntax is so important. In the expression field i wrote the entire expression from this article and found a little difference. There is no _ (underscore) after the V2, so the proper expression would become :
addProperty(variables(‘var_mappingTable’),item(),outputs(‘Get_user_profile_(V2)’)?[‘body/displayName’])
Hi Tom, thank you for your work! Which trigger would you recommend for this flow? I need something that will start the flow each time the task will be modified. Thank you in advance!
Hello Lukas,
it’s a shame but Power Automate doesn’t have a trigger when a Planner task is modified. The only solution I can currently think of would be to have a SharePoint list where you’d store the taskId and assigned user, and you’d check with a recurrent flow. I described the solution here: https://tomriha.com/how-to-get-notified-when-planner-task-was-reassigned-power-automate/
Never mind. 🙂 I had named the step differently. Now that that has been corrected, I am running a test, and it is working fabulously!
Thank you for your hard work with this!
Kevin
Hi Tom,
I tried to replicate all the above steps, I am getting below error
The variable ‘var_mappingTable’ of type ‘Object’ cannot be initialized or updated with value of type ‘Array’. The variable ‘var_mappingTable’ only supports values of types ‘Object’.
any ideas how to resolve this?
Hello Sravani,
you probably didn’t use the right brackets when setting/updating the variable.
I am getting below error in append to array step, can you please let me know what i am doing wrong here?
Unable to process template language expressions in action ‘Append_to_array_variable’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘variables(‘var_mappingTable’)?[item()]’ cannot be evaluated because property ‘{ “1234”: “” }’ cannot be selected.
Hello Sravani,
you probably didn’t switch the ‘Select’ action to the text only mode.
Tom,
How can I write the final output to sql stored procedure? I have used the Join expression, but it is pulling all the users instead of assigned users
Hello Sandy,
I don’t know how to write it into sql stored procedure, I never did that.
If it’s pulling all the users then I’d check if you empty the variable after each loop, it should return only the assigned ones.
Hi Tom,
This is amazing and solved a problem I have had for ages! Thank you for your work on this.
Hello Tom!
First of all, thanks for the great step-by-step!
I need to select only the user IDs for the assigned users for the recently completed task (usually its 2), so I can automatically email both whenever a task is completed. Is there a way I can simplify the above flow?
I thought of going “When a task is completed”> “Get task” (to get the only task needed).
How would you go from there?
Thanks!
Just an update:
The output for “Get a task” includes a section called “assignments”, with the following code:
[
{
“userId”: “XXXX”,
“value”: {
“@odata.type”: “#microsoft.graph.plannerAssignment”,
“assignedDateTime”: “DATE/TIME”,
“orderHint”: “ZZZZ”,
“assignedBy”: {
“user”: {
“id”: “FFFF”
}
}
}
},
{
“userId”: “YYYY”,
“value”: {
“@odata.type”: “#microsoft.graph.plannerAssignment”,
“assignedDateTime”: “DATE/TIME”,
“orderHint”: “SSSS”,
“assignedBy”: {
“user”: {
“id”: “FFFF”
}
}
}
}
]
I tried the “Get user profile (V2)” action with the function
triggerOutputs()?[‘assignments’]?[‘userId’]
but I get an error message saying ” The ‘inputs.parameters’ of workflow operation ‘Get_user_profile_(V2)’ of type ‘OpenApiConnection’ is not valid. Error details: The resolved string values for the following parameters are invalid, they may not be null or empty: ‘id'”
Hello Andre,
the assignments contain an array that you must handle somehow in the expression, e.g. loop through triggerOutputs()?[‘assignments’] and for each of them ‘Get user profile’ using the item()?[‘userId’]. It has [ and ] brackets = array, and you must deal with it somehow before you can access the values. Take a look on this JSON parsing article to learn more on navigating a json: https://tomriha.com/how-to-get-a-specific-value-from-a-json-object-in-power-automate/
This is just brilliant !! Thanks Tom !
Hi Tom,
If one of the user left the company, and flow is still pulling his Id and in the Get user profile action it is failing with error User not found. How can we handle this situation. Thanks in advance
Hello Sandy,
I’d use the ‘Configure run after’ setting on the following action (one of the points here: https://tomriha.com/3-ways-to-disable-an-action-or-a-section-of-a-flow-in-power-automate/) to let the flow continue even if the ‘Get user profile’ action failed.
How could I use this to get the task name and user id for only those tasks that are not completed? I have tried using filter array but can’t get it quite to work.
Hello Jane,
you can if you prefilter only the tasks where ‘percentComplete’ is not equal to 100: https://tomriha.com/export-planner-task-status-as-text-instead-of-percentage-power-automate/
I’ve executed this. However for all the tasks data is getting populated in a single cell for all tasks. Could anyone help?
Hi Tom,
I am getting the error already mentioned but with a different nuance:
Error: Correct to include a valid reference to ‘Get_user_profile_(V2)_-_get_user_by_userId’ for the input parameter(s) of action ‘Compose_4’.
I wonder if it is because some of the users assigned to those tasks are not in the system anymore (old tasks from users who left the organization).
If it is because of that, any way around it?
Hi Tom,
I think I passed the issue, but got stuck later with the error:
InvalidTemplate. Unable to process template language expressions in action ‘Append_to_array_variable’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘variables(‘var_mappingTable’)?[item()]’ cannot be evaluated because property ‘{ “xxxxxxxxxxxxxxx”: “” }’ cannot be selected. Please see https://aka.ms/logicexpressions for usage details.’.
Hello Marcelo,
it’s probably because you didn’t switch the ‘Select’ action to the ‘Value only’ mode using the small icon on the right. If you don’t switch it it’ll keep creating objects as you can recognise by the { and } brackets.
Thanks for this. I used this along with another flow to export all tasks to excel.
Getting the same issue as Sravani:
The variable ‘var_mappingTable’ of type ‘Object’ cannot be initialized or updated with value of type ‘String’. The variable ‘var_mappingTable’ only supports values of types ‘Object’.
Brackets used:
{
“”: “”
}
Hello AQ,
I’d double check the double quotes, whether you’re using the right characters as in the comment they seem wrong (although I can’t tell whether it isn’t the comments box that changed them)
Hey Tom. Are you still checking this? I get an error when running. The Get User Profile using “Item()” throws Bad Request. See output below. Any ideas??? Thanks
I pasted the output but your website gave me a security error and didn’t let me go forward. SO, I’m trying again without the actual error message.