Office Script - getSort() for a custom list

[Mike1402]

Hi I've been trying to put together a script which will sort a named range in the active worksheet. I chose script over VBA as the script can easily be used in many workbooks which have the same layout and named range, and are used by multiple people, but I'm happy to take advise if there is a better way ...

I have the following code:

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getActiveWorksheet();

    //Define the range
    const priceRange = selectedSheet.getRange("MainTable");

    //Define custom lists to sort on for each custom column
    const customSpeed = ["H", "V", "W", "Y", "(Y)", "Z"];


    priceRange.getSort().apply([
        {
            key: 1,
            ascending: true,
            sortOn: ExcelScript.SortOn.value,
            customPattern
        }
    ], false);
}

I've had a few different iterations of the last part of the above code, none of it works as it doesn't appear to be part of the 'SortField'. I've tried looking through the Microsoft API reference, tried searching ... even tried using ChatGPT! There doesn't appear to be much help with this that I could find. Any ideas? Should I just do it differently? I wanted to avoid making the workbook a macro enabled book.

I can successfully sort using the above code without the sortOn custom list attempt, just regular ascend or descend.

Thanks.