Multiple Sorting criteria with Office Scripts

[PAuser_1-2-3]

@Paulie78 

@grantjenkins 

I found this great resource by Paul Murana on using Office scripts to perform sorting on arrays. When I applied multiple columns sorting, it seems to only sort by first column. 

How to sort an array of objects with Power Automate (tachytelic.net)

I also want to use the results to run another script to create a table and insert the sorted values then email it as an attachment, but the excel file is coming out blank. Here are my flow steps. I appended the sorted results to variable arrays because the create table script won't take string outputs. 

[grantjenkins]

Hi @PAuser_1-2-3 Was this the same ask as the other post below where you want to sort by multiple Calculated Columns in descending order?

https://powerusers.microsoft.com/t5/Building-Flows/Multi-Column-Sort-Help/m-p/2460558

[PAuser_1-2-3]

Correct instead of expression here I used the office script method but both still didn't work. 

[grantjenkins]

I would go about this differently. I'd concatenate the Calculated Columns in the order I want, then sort the combined values in descending order.

For this example, I've got the following SharePoint List where Amount is a Number column, and Age, Item_Status, and Auto_Status are Calculated columns.

See full flow below. I'll go into each of the actions.

Get items retrieves the items from the SharePoint list.

Select maps out the columns we want to use, and an additional column called Order that will combine our Calculated columns plus the Amount column. The expression used for Order is below.

concat(
    item()?['Item_Status'],
    '|',
    item()?['Auto_Status'],
    '|',
    item()?['Age'],
    '|',
    item()?['Amount']
)

Select Sorted uses the output from Select sorted in reverse order. The expressions used are below.

//From
reverse(sort(body('Select'), 'Order'))

//Map
item()?['Title']
item()?['Item_Status']
item()?['Auto_Status']
item()?['Age']
item()?['Amount']

After running the flow, Select Sorted would include all the values sorted in descending order.

[
  {
    "Title": "Item 0003",
    "Item_Status": "Young",
    "Auto_Status": "Low",
    "Age": "11.0000000000000",
    "Amount": 6
  },
  {
    "Title": "Item 0001",
    "Item_Status": "Old",
    "Auto_Status": "Medium",
    "Age": "49.0000000000000",
    "Amount": 51
  },
  {
    "Title": "Item 0004",
    "Item_Status": "Old",
    "Auto_Status": "High",
    "Age": "50.0000000000000",
    "Amount": 110
  },
  {
    "Title": "Item 0002",
    "Item_Status": "Middle",
    "Auto_Status": "Medium",
    "Age": "24.0000000000000",
    "Amount": 25
  }
]

[PAuser_1-2-3]

I tried this sorting method in conjunction with the XML expressions that you posted here: Solved: Re: Export to Excel or CSV - SharePoint choice col... - Power Platform Community (microsoft..... Only Item_Status and Auto_Status columns worked. Age and Amount columns are still off in some items. 

Here is the Order expression in Select Items action. From SharePoint: Item_Status, Auto_Status, Age are calculated fields and Amount is Currency.

concat(xpath(item(), 'string(//Item_Status/text())'),'|',xpath(item(), 'string(//Auto_Status/text())'),'|',if(equals(xpath(item(), 'string(number(//Age/text()))'), 'NaN'), 0, xpath(item(), 'number(//Age/text())')),'|',if(equals(xpath(item(), 'string(number(//Amount/text()))'), 'NaN'), 0, xpath(item(), 'number(//Amount/text())')))

Then here is the sorting expression in Select Sorted action:

reverse(sort(body('Select_Items'),'Order'))

[Chriddle]

"|" might not be the optimal divider" since

sort(
	json('[''a|a'', ''aaa|bbb'']')
)

returns following sort order:

[
  "aaa|bbb",
  "a|a"
]

If it matters, you should use a line feed. Or something else higher up in the ASCII table than letters.

[Paulie78]

Hey @PAuser_1-2-3 ,

Glad you found the original script useful. That script never supported sorting on multiple properties. So instead use this updated script which allows you to specify mutliple sort properties:

function main(workbook: ExcelScript.Workbook, strArray: string, properties: string[], sortOrder: 'ASC' | 'DESC'): string {
    try {
        let array = JSON.parse(strArray);

        array.sort((a, b) => {
            for (let property of properties) {
                if (a[property] > b[property]) {
                    return sortOrder === 'ASC' ? 1 : -1;
                }
                if (a[property] < b[property]) {
                    return sortOrder === 'ASC' ? -1 : 1;
                }
            }
            return 0; // If all properties are equal
        });

        return JSON.stringify(array); // Convert array back to string if needed
    } catch (error) {
        // Handle parsing error
        console.log('Invalid JSON string:', error);
        return ''; // Or handle the error appropriately
    }
}

This gives you an interface like this:

Where the sort criteria is passed to the script as an array. Let me know if that helps you, if it does I will update the blog post.

The original post is redundant now since the sort() expression was added to Power Automate, but even that does not support sorting by multiple properties.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[PAuser_1-2-3]

I tried this new script but my spreadsheet is coming out blank. Here is my setup, not sure if I missed anything.

The outputs from multiple sorting script is a String. 

When I append them back into Array variable, the outputs still look like a String but the Create Table script accepted it as an Array......strange!