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:
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.
Prabhal Garg says
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.
Paulie says
In which way is it not working, please provide some more detail and I will try to help you.
Prabhal Garg says
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.
Ingo Dettmar says
Works great! Thanks a lot, this is much less complex than the other workarounds.
Paul says
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!!
Paulie says
Yes. When you get your array back you can use the “take” expression to get only the first 10 rows of data.
Cory says
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
Cam says
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
Badri says
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
leandro galvis says
Hello, I have an array but it contains: file name and file container. How do I order that array? Thanks.
Jhon Q says
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.
David says
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?
Rachel Albrecht says
Saved! This has been giving me a headache and now it works seamlessly!
Liz says
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 🙂
Paulie says
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?