Ted Naleid

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.