In Excel – Sorted With AppleScript I mentioned I might have a go at transposing rows and columns in an Excel spreadsheet with AppleScript.

I said there were two things I could try – each with a different use case:

• Transposing Rows And Columns In A Spreadsheet
• Transposing Rows And Columns In A Graph

I – after quite a bit of effort – achieved both. Again, in the spirit of saving others time, I’m posting them here.

Transposing Rows And Columns In A Spreadsheet

The following code transposes the selected cells into cell A1 of a new work sheet. I’m using a new worksheet as it saves worrying about whether the transposed range will overwrite cells you don’t want it to.¹ You can always cut and paste from the new worksheet to where you actually want it to go.²

tell application "Microsoft Excel"
    activate
    set r to selection
    copy range r
    tell active workbook
        set tempSheet to (make new worksheet at end)
    end tell
    paste special range "A1" with transpose
end tell

Having ensured Excel is activated the code:

1. Creates a range r from the selected cells.
2. Copies the cells to the clipboard.
3. Creates a new worksheet at the end of the active workbook.
4. Pastes the copied cells into cell A1 of this new workbook.

One thing I found didn’t work was attempting to paste special with transpose into the original selection; It simply didn’t transpose cells and columns.

Transposing Rows And Columns In A Graph

About half the time Excel decides that each row in the data is a series when I want each column to be a series (and vice versa).

There is a dialog ( “Select Data…”) you can pop up – but it requires taking your hands off the keyboard and clicking a button “Switch Row/Column”. This takes time and gets old really fast when you’re working with graphs all day long.

So I discovered a faster way – which could be automated with a hot key (perhaps with Keyboard Maestro or BetterTouchTool. Here’s the code:

tell application "Microsoft Excel"
    activate
    -- Get the last chart created
    set s to active sheet
    set chartCount to count of charts of chart objects of s
    set c to chart of (last chart object of s)

    -- Get the "plot by" direction for that chart and
    set p to (plot by of c)

    -- Set "plot by" to the opposite orientation
    if p is by columns then
        set plot by of c to by rows
    else
        set plot by of c to by columns
    end if

end tell

Here’s how it works.

1. Under the Get the last chart created comment are three lines of code which yield the last chart created – in variable c.
2. Then variable p is set to the value of the plot by attribute of the chart.
3. Based on whether its value is “by columns” or “by rows” the same attribute is set to the converse value.

The detection of the last chart created is generally what I want: My use case is to create a chart, decide I don’t like it, and then immediately transpose it.³

Conclusion

Once again problems can usefully solved – but with much experimentation. And through extensive use of the excellent Script Debugger. In particular, if you assign an object to a variable you can see its properties.

I hope this has proven useful for somebody; It certainly has for me.

And, having mentioned BetterTouchTool and Keyboard Maestro above, I’ve automated both of these using a Stream Deck. In detail I use a keystroke action that kicks off automation.

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.

In Automating Microsoft Excel I wrote about some basic manipulation of graphs in Excel for Mac OS, using AppleScript.

I’m writing about it again because of the paucity of examples on the web.

Here is an example that shows how to do a number of things in that vein.

tell application "Microsoft Excel"
    set c to active chart
    tell c

        set xAxis to (get axis c axis type category axis)
        tell xAxis
            set has title to true
        end tell
        set tx to axis title of xAxis
        set axis title text of tx to "Drawer / DCM / Chip"
        set font size of (font object of tx) to 14

        set yAxis to (get axis c axis type value axis)
        tell yAxis
            set has title to true
        end tell
        set ty to axis title of yAxis
        set axis title text of ty to "Core Count"
        set font size of (font object of ty) to 14

        set fore color of fill format of chart format of series "VH GCPs" to {0, 0, 255}


        set fore color of fill format of chart format of series "VM GCPs" to {51, 153, 255}

        set fore color of fill format of chart format of series "VL GCPs" to {185, 236, 255}
        set fore color of fill format of chart format of series "VL Unparked GCPs" to {185, 236, 255}
        set fore color of fill format of chart format of series "VL Parked GCPs" to {185, 236, 255}

        chart patterned series "VL Parked GCPs" pattern dark horizontal pattern

        tell series "Offline GCPs"
            set foreground scheme color of chart fill format object to 2
            set line style of its border to continuous
            set weight of its border to border weight medium
            set color of its border to {0, 0, 255}

        end tell

        set fore color of fill format of chart format of series "VH zIIPs" to {0, 255, 0}
        set fore color of fill format of chart format of series "VM zIIPs" to {96, 255, 180}
        set fore color of fill format of chart format of series "VL zIIPs" to {185, 255, 236}
        set fore color of fill format of chart format of series "VL Unparked zIIPs" to {185, 255, 236}
        set fore color of fill format of chart format of series "VL Parked zIIPs" to {185, 255, 236}

        chart patterned series "VL Parked zIIPs" pattern dark vertical pattern


        tell series "Offline zIIPs"
            set foreground scheme color of chart fill format object to 2
            set line style of its border to continuous
            set weight of its border to border weight medium
            set color of its border to {0, 255, 0}
        end tell

    end tell
end tell

The above is the code I use to colour my drawer-level graphs.

Let me extract pieces that you might to use. (My assumption is that anybody reading this far got here because they came for AppleScript / Excel tips.)

Addressing The Active Chart

Everything in this post assumes you have selected a chart (graph) and want to manipulate it. The other snippets will need to be wrapped in this.

tell application "Microsoft Excel"
    set c to active chart
    tell c
        ...
    end tell
end tell

The point is to tell the active chart object what to do.

Manipulating Chart Axis Titles

First let’s manipulate the title of the x axis. Excel calls this the category axis.

The following

1. Sets xAxis to the category axis of our chart.
2. Turns on the title for the axis.
3. Sets tx to the axis title object.
4. Sets its text to “Drawer / DCM / Chip”
5. Sets its font size to 14 points

set xAxis to (get axis c axis type category axis)
tell xAxis
    set has title to true
end tell
set tx to axis title of xAxis
set axis title text of tx to "Drawer / DCM / Chip"
set font size of (font object of tx) to 14

Now the y axis title:

set yAxis to (get axis c axis type value axis)
tell yAxis
    set has title to true
end tell
set ty to axis title of yAxis
set axis title text of ty to "Core Count"
set font size of (font object of ty) to 14

The only real difference is we’re setting yAxis to what Excel calls the value axis.

Setting The Foreground Fill Colour For A Series

This one turned out to be quite difficult to figure out.

1. You address a series in a chart by its legend value: series "VH GCPs.
2. You get the chart format of the series: chart format.
3. You get its fill format: fill format.
4. You set its foreground colour (for color a) using RGB (Red/Green/Blue) values. In this case full-on blue:`

The brackets in that last denote a list of values.

Put it all together and you get this:

set fore color of fill format of chart format of series "VH GCPs" to {0, 0, 255}

The main snippet has several of these.

Setting A Series Pattern

This one took a while to figure out.

chart patterned series "VL Parked GCPs" pattern dark horizontal pattern

It’s actually a command. Again the series is referred to as series "VL Parked GCPs".

I wanted a horizontally striped pattern so I chose pattern dark horizontal pattern. For another series I chose pattern dark vertical pattern.

Setting The Box Surround For A Series

I wanted some empty-looking series. Before I discovered the chart patterned series command I wrote the following.

tell series "Offline zIIPs"
    set foreground scheme color of chart fill format object to 2
    set line style of its border to continuous
    set weight of its border to border weight medium
    set color of its border to {0, 255, 0}
end tell

(The set foreground scheme color incantation uses the current Excel file’s scheme colour 2 – which happened to be white. I discovered this before discovered set for color of fill format.... I wasn’t happy with the colour control scheme colours give, so I persisted with being able to specify RGB values.)

The elements I want to draw your attention to here are around setting the border:

• You can set the border line style with set line style of its border to ....
• You can set the weight of its border with set weight of its border to .... I found the standard border width a bit weedy.
• You can set the colour of its border with set color of its border to and here I’ve specified an RGB value.

Conclusion

It took a lot of experimenting to gain the above techniques – which is why I wanted to share them. I will say Script Debugger (which is a purchasable app) helped a lot, especially with seeing the attributes of objects such as the axis object. It does a nicer job of formatting Excel’s AppleScript dictionary than the built in Script Editor.

No doubt I’ll find more techniques – as I stretch what my code can do. If I do I’ll share them.

And now I’m happy knowing I’ve automated much of the drudgery of making charts in Excel.

Making Of

This is the second blog post I wrote today in a plane coming back from Istanbul. Some of the code was worked on during down time between customer meetings.

I can recommend writing under such circumstances, even if they’re a bit cramped. The one downside is my reluctance to pay for in-flight wifi. But I contend blog posts benefit from “sleep on it” so there’s no urgency to posting.