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

Export Planner assigned users into Excel with Power Automate

Posted on August 4, 2021September 8, 2021 by Tom

“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.

{ "": ""}
Planner Excel assigned users Power Automate

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'])
Planner Excel assigned users Power Automate

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.

Planner Excel assigned users Power Automate

With only 2 users the table would look like this:

Planner Excel assigned users Power Automate

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()]
Planner Excel assigned users Power Automate

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

Planner Excel assigned users Power Automate

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.


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.

15 thoughts on “Export Planner assigned users into Excel with Power Automate”

  1. Jim C says:
    October 28, 2021 at 11:25 pm

    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

    Reply
    1. Tom says:
      October 31, 2021 at 6:13 pm

      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.

      Reply
    2. Jerome says:
      June 18, 2022 at 2:19 am

      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.

      Reply
  2. Marlon Kaltenborn says:
    November 4, 2021 at 5:12 am

    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!

    Reply
    1. Tom says:
      November 7, 2021 at 4:51 pm

      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.

      Reply
      1. Kevin Bruner says:
        May 24, 2022 at 1:37 pm

        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

        Reply
        1. Kevin Bruner says:
          May 24, 2022 at 6:17 pm

          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

          Reply
          1. Tom says:
            June 1, 2022 at 5:27 pm

            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. 🙂

    2. Jerome says:
      June 18, 2022 at 2:25 am

      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’])

      Reply
  3. Lukas says:
    May 4, 2022 at 11:58 pm

    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!

    Reply
    1. Tom says:
      May 11, 2022 at 6:06 pm

      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/

      Reply
  4. Kevin Bruner says:
    May 24, 2022 at 6:17 pm

    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

    Reply
  5. Sravani says:
    June 23, 2022 at 5:50 pm

    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?

    Reply
  6. Sravani says:
    June 24, 2022 at 12:04 am

    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.

    Reply
  7. Sandy says:
    June 25, 2022 at 8:14 am

    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

    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