Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
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.

28 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
    1. Tom says:
      July 3, 2022 at 11:44 am

      Hello Sravani,
      you probably didn’t use the right brackets when setting/updating the variable.

      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
    1. Tom says:
      July 3, 2022 at 11:46 am

      Hello Sravani,
      you probably didn’t switch the ‘Select’ action to the text only mode.

      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
    1. Tom says:
      July 6, 2022 at 6:40 pm

      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.

      Reply
  8. Joe says:
    July 31, 2022 at 6:00 am

    Hi Tom,

    This is amazing and solved a problem I have had for ages! Thank you for your work on this.

    Reply
  9. Andre Carvalho says:
    September 27, 2022 at 2:48 am

    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!

    Reply
    1. Andre Carvalho says:
      September 27, 2022 at 3:04 am

      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'”

      Reply
      1. Tom says:
        October 5, 2022 at 2:34 pm

        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/

        Reply
  10. Said says:
    October 5, 2022 at 10:26 pm

    This is just brilliant !! Thanks Tom !

    Reply
  11. Sandy says:
    December 2, 2022 at 7:46 pm

    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

    Reply
    1. Tom says:
      December 15, 2022 at 1:35 pm

      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.

      Reply
  12. Jane says:
    February 9, 2023 at 11:43 am

    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.

    Reply
    1. Tom says:
      February 22, 2023 at 7:12 pm

      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/

      Reply
  13. Rachita Rachita says:
    March 7, 2023 at 4:05 pm

    I’ve executed this. However for all the tasks data is getting populated in a single cell for all tasks. Could anyone help?

    Reply

Leave a Reply Cancel reply

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

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

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.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes