• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

How to sort an array of objects with Power Automate

April 21, 2021 by Paulie 17 Comments

I recently wrote a blog post that showed how to sort an array in Power Automate using Office scripts. That script works well for simple arrays, but it is unable to sort an array of objects.

Using the following array as an example:

[
  {
    "Ranking": 1,
    "Player": "Novak Djokovic",
    "Age": 33,
    "Points": 11873
  },
  {
    "Ranking": 2,
    "Player": "Daniil Medvedev",
    "Age": 25,
    "Points": 9850
  },
  {
    "Ranking": 3,
    "Player": "Rafael Nadal",
    "Age": 34,
    "Points": 9490
  },
  {
    "Ranking": 4,
    "Player": "Dominic Thiem",
    "Age": 27,
    "Points": 8615
  },
  {
    "Ranking": 5,
    "Player": "Stefanos Tsitsipas",
    "Age": 22,
    "Points": 7860
  }
]

There are no in-built actions in Power Automate to sort simple arrays, or arrays of objects. So I wrote a small Office script that does this. It accepts three parameters:

  • The array to be sorted (Passed as a string).
  • The property you want to sort on. (Age, Points, Player etc).
  • The direction to sort in (Either ASC or DSC).

And the output is a nicely sorted array. Screenshot of the flow:

Image of a Power Automate Flow that is sorting an array of objects.

Note that the array, which was defined in the first step, has to be converted to a string before being passed to the script. I did this with the code:

string(outputs('playerRankings'))

After the script has executed you can easily retrieve the results of the sort from the result property of the Excel action.

Here is the code for the Office Script which you can copy and paste into your environment:

function main(workbook: ExcelScript.Workbook, strArray: string, property: string, sortOrder: string): string {
  let array = JSON.parse(strArray);
  if (sortOrder === 'ASC') {
    return array.sort((n1, n2) => {
      if (n1[property] > n2[property]) {
        return 1;
      }

      if (n1[property] < n2[property]) {
        return -1;
      }

      return 0;
    });
  } else {
    return array.sort((n1, n2) => {
      if (n1[property] > n2[property]) {
        return -1;
      }

      if (n1[property] < n2[property]) {
        return 1;
      }

      return 0;
    });
  }
}

Alternatively, you can download the Office Script from here and place it into your Documents\Office Scripts folder in OneDrive.

Conclusion

Although it would be great if this functionality was a native action in Power Automate – this post demonstrates how you can sort an array of objects in Power Automate using Office Scripts.

Very short post, but hopefully adds useful, much needed functionality to your flows. Let me know how you get on in the comments. Office Scripts have all kinds of uses, check out my other posts to see what I have used them for:

  • How to use Regex in Power Automate
  • Power Automate – How to sort an array
  • How to Sum an Array of Numbers in Power Automate

Office scripts used in this way are a simple and low cost method of getting access to a JavaScript engine, to fill in the gaps in standard Power Automate Functionality. Just be mindful that there is a limit of 200 executions per day on Office Scripts.

If you haven’t used Office Scripts before, check out the Microsoft getting started guide.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. Prabhal Garg says

    May 18, 2021 at 11:44 am

    Sir, That’s a wonderful solution.
    But we are facing a problem while running the script.
    Its not Sorting my array in the proper way I want.
    Can you please guide me how can I solve this error.

  2. Paulie says

    May 18, 2021 at 11:45 am

    In which way is it not working, please provide some more detail and I will try to help you.

  3. Prabhal Garg says

    May 18, 2021 at 12:56 pm

    I am trying to sort my array of objects by the total column but when i apply the script in ascending or descending order it don’t give the required output.

  4. Ingo Dettmar says

    September 15, 2021 at 12:24 pm

    Works great! Thanks a lot, this is much less complex than the other workarounds.

  5. Paul says

    October 6, 2021 at 5:53 pm

    This is excellent and I’ve made it work for a use case I’m working on. Now… I’m getting a sorted array based on the criteria I want (number of likes each post in yammer gets). Any tips on how I might then reduce the number of rows down to let’s say the top ten eg. the final step of the ability to say “these are the top 10 posts in our yammer community base on number of likes!”. Thanks!!

  6. Paulie says

    October 6, 2021 at 6:22 pm

    Yes. When you get your array back you can use the “take” expression to get only the first 10 rows of data.

  7. Cory says

    November 20, 2021 at 3:08 am

    Great article on how to sort an Power Automate array. I’ve followed your instructions (first time Office Script) and downloaded the OSTS file via the link to make sure there weren’t any typos or bad characters but getting the error below when calling the “Run Script” action in my Power Automate job. Any ideas?

    We were unable to run the script. Please try again.
    Runtime error: array.sort is not a function

  8. Cam says

    January 20, 2022 at 3:47 am

    does this mean, that this Array Sorting approach no longer works?

    https://docs.microsoft.com/en-us/office/dev/scripts/develop/typescript-restrictions#incompatible-typescript-functions

  9. Badri says

    January 24, 2022 at 7:52 am

    Its a neat solution and will be very usefuly, if I am able to sort out a small issue with the script itself.
    It throws an error in the xl code edditor which goes like this : Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.
    I think its to do with the line : let array = JSON.parse(strArray);
    I don’t have any prior knowlwdge of typescript and hence any suggestions or help will be appreciated

  10. leandro galvis says

    March 15, 2022 at 6:43 pm

    Hello, I have an array but it contains: file name and file container. How do I order that array? Thanks.

  11. Jhon Q says

    March 17, 2022 at 11:17 pm

    Hi everyone, I ran with the same issues with the script, the issue in my side was that I was trying to get the individual values of the result from the script to populate a word template, and apparently that cannot be done, after three days trying to find out what was the issue, I tried to set a variable with the script results and them get the individual results form there, and it works!!!..hoping this work for you guys.

  12. David says

    March 22, 2022 at 9:01 pm

    This sorting script worked great for me on my last project, thank you very much!

    But now i am working on something a bit more involved, and i find my self needing to sort by 3 different columns. First by two string columns, and third by date. This script doesnt seem to be able to do that. I did try passing multiple “property’s” with a comma separator, but no luck.

    Would it be possible to expand this to accept 3 property inputs and sort in the order they are provided?

  13. Rachel Albrecht says

    March 25, 2022 at 10:58 am

    Saved! This has been giving me a headache and now it works seamlessly!

  14. Liz says

    April 13, 2022 at 9:06 pm

    This was so helpful! Has anyone had any luck sorting by 2 columns? Ex. I need to sort by names in alphabetical order and then within each name, sort by a numerical value.

    I’ve tried to add it into the code above but I’m very limited in my knowledge on TypeScript 🙂

  15. Paulie says

    April 13, 2022 at 9:07 pm

    It’s an interesting question – might take a look. Can you send me a sample of what you want to sort through the contact page?

Trackbacks

  1. It Ain't Boring: Exploring Dynamics 365 says:
    September 16, 2021 at 8:03 pm

    C# code in Power Automate: let’s sort a string array?

    In the previous post, I wrote about how we can use C# code in Power Automate connectors now. The example given there (converting a string to uppercase) was not that useful, of course, since we could s…

  2. It Ain't Boring: Exploring Dynamics 365 says:
    September 17, 2021 at 4:38 pm

    C# code in Power Automate: let’s sort a string array?

    In the previous post , I wrote about how we can use C# code in Power Automate connectors now. The example

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 245 other subscribers.

Go to mobile version