Applescript and Iwork Numbers


I am trying to make a Iwork Numbers file where i have a sheet where i paste my bank statements
from my online bank into numbers, an then another sheet that contains all the store i usually shop in.
I then identify each store and divide the statements into categories like, Food, Clothes, House etc.

This is off course quite tedious work, so i want to automate this.

I want to make a applescript that takes each cell in the “Stores” sheet, and checks it against each
cell in the bankstatement sheet. And when it find anything, then marks a corresponding cell with the categori.
I have made a numbers file that does almost everything now, apart from the cell checking, which i now want
to do in numbers.

My REALLY big problem, is that i am a total newbie with applescripting, so i wanted to ask you if anyone
could help me with the checking script.

I have made a file, so you can see what i am trying to do.

I hope it makes sense, and that you will be able to help me.


You are the only one knowing the way to download dates from your bank.
I guess that if you enter my iDisk :,
mainly the folder :

For_iWork:iWork '09:for_Numbers09:

You will find useful infos in the numerous scripts available.
two of them may be really useful :
For_iWork:iWork '
For_iWork:iWork '

CAUTION: my scripts were designed for Numbers as a component of the iWork package.
I know that the one delivered thru the mac App Store isn’t installed the same way than the first one so, some handlers may require some changes.
At this time I don’t know the exact implantation of resources in this new release and for sure, I will not buy it just for see.

Yvan KOENIG (VALLAURIS, France) mercredi 25 mai 2011 16:21:00


And thank you for your reply. I think i might not have made myself totally clear.

I do not want to download dates and bankstatements from the web bank.

I have all the data in my sheet already, and i only want a script that can search for store names
in the “info” cells already in the sheet. I hope this makes it easier?


It seems you want the script to format the cells for you too. :confused:

There doesn’t appear to be any way in Numbers’s scripting dictionary to get bold text or to specify Danish currency. The script below uses System Events to perform a Command-“b” keystroke to embolden the entries in your “OK/Store not found” column and assumes that currency will default to Danish on your machine anyway. Hopefully it’ll get you started.

-- Test the layout of the "Bank statement" spreadsheet and do some cell formatting in it.
on setup()
	tell application "Numbers"
		tell sheet "Bank statement" of document 1
			tell table "Bank statements"
				-- Identify the headers row and th "Info", "Amount", and "OK/Store not found" columns.
				set {column:infoColumn, row:headerRow} to first cell whose value begins with "Info"
				set amountColumnNo to address of column of first cell whose value begins with "Amount"
				set okColumn to column of first cell whose value begins with "OK/"
				set {name:okColumnLetter, address:okColumnNo} to okColumn
				-- Identify the category columns and their headers.
				set {name:firstCatColumnLetter, address:firstCatColumnNo} to column (okColumnNo + 1)
				set {name:lastCatColumnLetter, address:lastCatColumnNo} to column of last cell of headerRow whose value is not 0.0
				set categoryHeaders to value of cells firstCatColumnNo thru lastCatColumnNo of headerRow
				-- Identify the beginning and end rows of the statement data.
				set startRow to (address of headerRow) + 1
				set endRow to address of row of last cell of infoColumn whose value is not 0.0
				-- Format the "OK/Store not found" column for black, centred text.
				set okRangeName to (okColumnLetter & startRow & ":" & okColumnLetter & endRow)
				set text color of range okRangeName to {0, 0, 0}
				set alignment of range okRangeName to center
				-- Format the category columns as currency.
				set format of range (firstCatColumnLetter & startRow & ":" & lastCatColumnLetter & endRow) to currency
			end tell
		end tell
	end tell
	-- Return useful data to the main handler.
	return {infoColumn:infoColumn, amountColumnNo:amountColumnNo, okColumnNo:okColumnNo, okRangeName:okRangeName, categoryHeaders:categoryHeaders, startRow:startRow, endRow:endRow}
end setup

on main()
	set {infoColumn:infoColumn, amountColumnNo:amountColumnNo, okColumnNo:okColumnNo, okRangeName:okRangeName, categoryHeaders:categoryHeaders, startRow:startRow, endRow:endRow} to setup()
	tell application "Numbers"
		tell sheet "Bank statement" of document 1
			-- Get the data from the "Stores" spreadsheet.
			tell table "Stores"
				-- This assumes that the two columns have matching entries in each row.
				set storeNames to value of every cell of column 3 whose value is not 0.0
				set categories to value of every cell of column 4 whose value is not 0.0
			end tell
			tell table "Bank statements"
				repeat with i from 1 to (count storeNames)
					-- For each store name and its matching category .
					set {thisName, thisCategory} to {item i of storeNames, item i of categories}
					-- . identify the number of the column matching the category .
					repeat with i from 1 to (count categoryHeaders)
						if (thisCategory is item i of categoryHeaders) then
							set catColumnNo to i + okColumnNo
							exit repeat
						end if
					end repeat
					-- . and the rows containing payments to the store.
					set matchingRows to (row of cells of infoColumn whose value contains thisName)
					-- Set the category and OK cells in those row as appropriate.
					repeat with thisRow in matchingRows
						tell thisRow
							set value of cell catColumnNo to value of cell amountColumnNo
							set value of cell okColumnNo to "OK"
						end tell
					end repeat
				end repeat
				-- When done, fill the unfilled cells in the "OK" column with "Store not found" in red.
				tell range okRangeName
					tell (cells whose value is not "OK")
						set text color to {65535, 10023, 4626}
						set value to "Store not found"
					end tell
				end tell
				-- There's no apparent way to set bold text in Numbers, so set the selection to the cells in the "OK" column to prepare for a Command-"b" keystroke.
				if (selection range exists) then
					set oldSelectionRange to name of selection range
					set oldSelectionRange to missing value
				end if
				set selection range to range okRangeName
			end tell
		end tell
	end tell
	-- Perform the Command-"b" keystroke to bolden the entries in the "OK" column and restore the Numbers selection to what it was (_if_ it was).
	tell application "System Events" to keystroke "b" using {command down}
	if (oldSelectionRange is not missing value) then tell application "Numbers" to tell table "Bank statements" of sheet "Bank statement" of document 1 to set selection range to range oldSelectionRange
end main


Sorry for my late reply.

Thank you very much this is awesome!!!