Using iWork Numbers.app AppleScript to Sum Columns for All Tables on a Sheet

| Comments

Overall, I’m pretty happy with Numbers.app (part of Apple’s iWork suite) as a replacement for Excel. It’s considerably cheaper and has lots of user interface tweaks to make it more pleasant to work with.

One of these changes is that each sheet can actually have multiple tables on it, and these can be arranged independently on the same page. This prevents the problem that happens in excel where you have multiple sets of data you want to see at the same time, but the cell/row sizing for one set of data affects the data in the other set that happens to be on the same row.

I’ve been leveraging this for a one-off personal project and I had a need to sum up all of the data in the 2nd column on all of the tables within a particular sheet.

This brought me to AppleScript, Apple’s scripting language that’s used to drive applications.

You can write scripts in the AppleScript editor (found in /Applications/Utilities). In there, you can look up the commands and objects that a particular program makes available by going to File->Open Dictionary and then finding the application that you want to script.

I’ve done a little work in AppleScript in the past, but always recoil from it when I’m done. It’s so damn wordy in what appears to be an attempt to make it more “friendly” when it really just makes it harder to remember the right syntax to get something done. It probably just has “too many notes”.

Regardless, here’s the script that I came up with to sum up all of the values in the 2nd column (Column B) for all of the tables on the currently active sheet and then insert the result onto the 2nd column of a table named “Totals” (which must previously exist).

tell application "Numbers"
    tell document 1
        set total to 0
        set totalTable to 0
        tell sheet 1
            repeat with j from 1 to (count of tables)
                try
                    tell table j
                        if (name is not equal to "Totals") then
                            repeat with k from 1 to count of rows
                                set total to total + (value of cell 2 of row k)
                            end repeat
                        else
                            set totalTable to j
                        end if
                    end tell
                end try
            end repeat
            tell table totalTable
                display dialog "total = " & total & " total table = " & totalTable
                set value of cell 2 of row 1 to total
            end tell
        end tell
    end tell
end tell

I needed this ability as the number of tables on this sheet will change quite frequently depending on what I’m doing with it, and the values in the 2nd column of each of these tables will also get frequently modified and I need to easily know the sum across all of the tables.

To actually execute the script within Numbers.app, there are a couple of ways to do it. It doesn’t appear that Numbers has it’s own script directory (normally, it would be under ~/Library/Application Support/iWork/Numbers/Scripts), but you can add it to your own User Scripts directory at ~/Library/Scripts.

If you want to access it through your menus, you can enable the global script icon up there by going into the AppleScript Editor preferences. On the General tab, check the box that says “Show Script menu in menu bar”.

If you’re using LaunchBar or Quicksilver, you can also add this directory to their catalog as a place to search. Then it’s as easy as cmd-space and the name of the script to execute it (both apps also allow you to set up keyboard shortcuts too).

In all, it feels hacky, and I’m sure it could be improved if I actually knew what the hell I was doing in AppleScript, but as I said, it’s a one-off that I need for a fairly particular problem.

I should probably take a little more time to learn more about AppleScript (or look more closely at a bridge to a language that I find a bit more palatable). Being able to script the applications that I use can be very useful.

Comments