Excel – Sorted With AppleScript

They say necessity is the mother of invention. But so, I’d say, are impatience and frustration.

I find the sorting dialog in Excel on Mac a source of both of these. So I wrote some AppleScript to remove some of the friction.

I’m sharing this because, yet again, I found nothing on the Web to help me. And some of the pieces might be what other people need for other automations.

The Trouble With Excel Sort

It’s easy to invoke the sort dialog from the keyboard – which I’d’ve fixed if I had to. (It’s Cmd+Shift+R.) But after that it gets a bit tedious:

1. I have to explicitly click to get a drop down of columns to sort on.
2. The default is to sort ascending and I almost always want to sort descending. So I have to click in a drop down to change that.
3. It’s very hit and miss whether the “I have headers” is selected – and I always have headers.
4. There is no tabbing or character navigation; It’s a badly designed dialog.

To be fair the dialog allows sorting in multiple columns – but it’s rare I’d want to do that.

You’ll note the things I’m objecting to require taking my hands off the keyboard, which is another joy / productivity killer.

Tiny violins, perhaps. 😀

The Code

All the above problems are tolerable. But it turns out they’re fixable – with a little AppleScript. So why not?

As I said above, searching the web proved futile. So the following was hard fought – which is why I think it worth sharing with those of you who’ve made it this far.

When you run this code it works on the range (rows and columns) you’ve selected, treating the first row as a headings row.

From the user point of view

1. You are prompted for which column to sort on.
2. You are prompted for whether the sort is ascending or descending.
3. The sort is performed.

First character navigation works.

Here is the complete code.

tell application "Microsoft Excel"
    activate

    set r to selection

    -- Populate list of header cell values
    set headerList to {}

    set headerRow to row 0 of r
    set columnCount to count of columns of r
    set c to cells of headerRow
    set ix to 1
    repeat with theHeaderCell in c
        set headerText to (ix as string) & " " & string value of theHeaderCell
        set ix to ix + 1
        set end of headerList to headerText
    end repeat


    -- Choose which column to sort on
    set selectedHeader to {choose from list headerList}
    set chosenHeaderValue to item 1 of item 1 of selectedHeader

    -- Calculate the column number of the sorting column
    repeat with i from 1 to columnCount
        set columnHeader to item i of headerList
        if columnHeader = chosenHeaderValue then exit repeat
    end repeat
    set sortingColumn to i + (first column index of r) - 1

    set direction to item 1 of item 1 of {choose from list {"Ascending", "Descending"}}

    set sortingCell to cell (first row index of r) of column sortingColumn

    if direction is "Ascending" then
        sort r key1 sortingCell order1 sort ascending header header yes
    else

        sort r key1 sortingCell order1 sort descending header header yes
    end if

end tell

Now let’s look at some of the key elements of the code.

Getting The Currently Selected Cells

You get them as a range object with

set r to selection

A lot of things you wouldn’t think of as ranges in fact are. This is a more obvious case, though.

Populating A List With Header Cell Values

set headerList to {}

set headerRow to row 0 of r
set columnCount to count of columns of r
set c to cells of headerRow
set ix to 1
repeat with theHeaderCell in c
    set headerText to (ix as string) & " " & string value of theHeaderCell
    set ix to ix + 1
    set end of headerList to headerText
end repeat

In the above the code

1. Extracts the first row of the range into headerRow.
2. Creates a count of the number of cells in that first row – for later use – columnCount.
3. Extracts the cells of that first row as a list – c.
4. Iterates over the list of cells. For each cell it extracts the value and prepends an index to it – with a space in between. The number is needed to create a uniquely identifying string. This string is added to the list headerList. ix is a counter, of course.

The net result is a list of strings with values such as “1 System/Subsystem”.

Choosing Which Column To Sort On

AppleScript allows you to select (potentially multiple) values from a displayed list.

The code to get the text value of such selected values is

set selectedHeader to {choose from list headerList}
set chosenHeaderValue to item 1 of item 1 of selectedHeader

The first line pops up a dialog with the list in (in this case headerList, previously created).

The second line selects the first selected item from the list and sets chosenHeaderValue to it.

You could, of course, pop up any list and handle it this way, including if you allowed multiple values to be selected on.

Calculating The Column Number Of The Sorting Column

Unfortunately choose from list doesn’t return the index(es) of the chosen item(s). So you have to search through the list to find a match. Because there might be duplicate items in the list I had to prepend a unique identifier. Hence the use of the counter ix above.

Here’s the code to search for the selected item

repeat with i from 1 to columnCount
    set columnHeader to item i of headerList
    if columnHeader = chosenHeaderValue then exit repeat
end repeat

It compares the selected item’s text to each item in the list – until it finds a match.

The value of i has to be added to the first column number of the range (and then one taken off) to get the number of the column you want to sort on:

set sortingColumn to i + (first column index of r) - 1

Choosing The Direction To Sort In

Another dialog is used to choose the sorting direction

    set direction to item 1 of item 1 of {choose from list {"Ascending", "Descending"}}

Performing The Sort

First we need to nominate a cell to sort based on. In fact it suffices to choose the first cell of the sorting column:

set sortingCell to cell (first row index of r) of column sortingColumn

The sort itself is quite straightforward. Depending on the value of direction you can sort ascending or descending:

if direction is "Ascending" then
    sort r key1 sortingCell order1 sort ascending header header yes
else
    sort r key1 sortingCell order1 sort descending header header yes
end if

If you had more than one column to sort on you might use key2, key3 etc in the above, nominating a different cell for each.

Conclusion

Again, the struggle is real 😀 when it comes to using AppleScript to automate Excel externally. To some extent VBA can be used internally but it can’t readily be injected and controlled from outside.

And I have a couple more challenges in mind:

• Transposing rows and columns in a range. Or, equivalently valuably to me, telling graphing which direction defines series. Perhaps both.
• Folding a range – so that it is wider. So, for example, 2 columns wide and 8 rows tall becomes 4 columns wide and 4 columns tall. This kind of folding is useful to me – when combined with exporting to CSV. (CSV can be input to mdpre / md2pptx to make tables in PowerPoint slides.)

I know transposition can be done – but maybe not without explicitly iterating over cells. We’ll see.