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.