AppleScript Library

iWork Numbers Library

   Some handlers to facilitate scripting iWork Numbers. AppleScript execution in Numbers is not very fast, but for some tasks it is certainly usefull.

All handlers displayed on this page were freely available on the internet or written by myself. Please refer to the copyright section at the end of this page. Feedback appreciated.


AppleScript Editor open download

Properties
myName
Cell Access And Manipulation
valueOfCell
setValueOfCell
setCheckbox
nextEmptyCellInRow
nextEmptyCellInColumn
clearRange
Table Manipulation
rowCountOfTable
setRowCountOfTable
columnCountOfTable
setColumnCountOfTable
deleteRowsOfTable
deleteColumnsOfTable
Cell Address Conversion
integersToAddress
addressToIntegers

Properties

property myName

Name that should be used when loading this library.

show source AppleScript-Editor open insert append toc
property myName : "_numbers"

Cell Access And Manipulation

valueOfCell(sheetName, tableName, rowNumber, colNumber)

Get the value of a cell in Numbers' front document.

sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
rowNumberintegerrow number of the target cell
colNumberintegercolumn number of the target cell
anythingtext, real, date, boolean

Written by:ljr

Example:

valueOfCell(1, 1, 2, 2) 
--> "AAA", true or whatever...
show source AppleScript Editor open insert append toc
on valueOfCell(sheetName, tableName, rowNumber, colNumber)
	local sheetName, tableName, rowNumber, colNumber, res
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						tell cell colNumber of row rowNumber
							set res to value
						end tell
					end tell
				end tell
			end tell
		end tell
		return res
	on error eMsg number eNum
		error "Can't valueOfCell: " & eMsg number eNum
	end try
end valueOfCell

setValueOfCell(cellValue, sheetName, tableName, rowNumber, colNumber)

Set the value of a cell in Numbers' front document.

cellValueanythingcell value, using text is strongly recommended
sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
rowNumberintegerrow number of the target cell
colNumberintegercolumn number of the target cell
nothingerror if problems

Written by:ljr

Example:

setValueOfCell("AAA", 1, 1, 2, 2) 
--> cell in front document set to "AAA"
show source AppleScript Editor open insert append toc
on setValueOfCell(cellValue, sheetName, tableName, rowNumber, colNumber)
	local sheetName, tableName, rowNumber, colNumber, cellValue
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						tell cell colNumber of row rowNumber
							set value to cellValue
						end tell
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't setValueOfCell: " & eMsg number eNum
	end try
end setValueOfCell

setCheckbox(cbValue, sheetName, tableName, rowNumber, colNumber)

Check/uncheck a checkbox in Numbers' front document. Checkboxes can't be set directly but a little workaround will do the trick.

cbValueboolean or integertrue/1 = check, false/0 = uncheck
sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
rowNumberintegerrow number of the target cell
colNumberintegercolumn number of the target cell
nothingerror if problems

Written by:ljr

Example:

setCheckbox(true, 1, 1, 1, 1) 
--> checkbox will be checked
show source AppleScript Editor open insert append toc
on setCheckbox(cbValue, sheetName, tableName, rowNumber, colNumber)
	local sheetName, tableName, rowNumber, colNumber, cbValue, cbValInt
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set format of cell colNumber of row rowNumber to automatic
						set cbValInt to 0
						if cbValue then set cbValInt to 1
						set value of cell colNumber of row rowNumber to cbValInt
						set format of cell colNumber of row rowNumber to checkbox
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't setCheckboxInNumbers: " & eMsg number eNum
	end try
end setCheckbox

nextEmptyCellInRow(sheetName, tableName, rowNumber, compValue)

Find the next empty cell in a row of a table in Numbers' front document (starting from the left).

sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
rowNumberintegerrow number to inspect
compValueinteger or textcompare value indicating that a cell is empty (usually 0, sometimes "")
integercolumn number of next empty cell

Written by:ljr

Example:

nextEmptyCellInRow(1, 1, 1, 0) 
--> column number of next empty cell in row 1
show source AppleScript Editor open insert append toc
on nextEmptyCellInRow(sheetName, tableName, rowNumber, compValue)
	local sheetName, tableName, rowNumber, res, i, len
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set len to column count
						set res to 0
						repeat with i from 1 to len
							if (value of cell i of row rowNumber) = compValue then
								set res to i
								exit repeat
							end if
						end repeat
						if res = 0 then
							error "No empty column in table " & tableName & "!" number 111
						end if
					end tell
				end tell
			end tell
		end tell
		return res
	on error eMsg number eNum
		error "Can't nextEmptyCellInColumn: " & eMsg number eNum
	end try
end nextEmptyCellInRow

nextEmptyCellInColumn(sheetName, tableName, colNumber, compValue)

Find the next empty cell in a column of a table in Numbers' front document (starting from the top).

sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
colNumberintegercolumn number to inspect
compValueinteger or textcompare value indicating that a cell is empty (usually 0, sometimes "")
integerrow number of next empty cell

Written by:ljr

Example:

nextEmptyCellInColumn(1, 1, 1, 0) 
--> row number of next empty cell in column 1
show source AppleScript Editor open insert append toc
on nextEmptyCellInColumn(sheetName, tableName, colNumber, compValue)
	local sheetName, tableName, colNumber, res, i, len
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set len to row count
						set res to 0
						repeat with i from 1 to len
							if (value of cell colNumber of row i) = compValue then
								set res to i
								exit repeat
							end if
						end repeat
						if res = 0 then
							error "No empty row in table " & tableName & "!" number 111
						end if
					end tell
				end tell
			end tell
		end tell
		return res
	on error eMsg number eNum
		error "Can't nextEmptyCellInColumn: " & eMsg number eNum
	end try
end nextEmptyCellInColumn

clearRange(sheetName, tableName, startRow, startCol, endRow, endCol)

Clear a given range of a table in Numbers' front document maintaining. Slower than the built in command, but it maintains formatting and style.

sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
startRowintegerfirst row to clear
startColintegerfirst column to clear
endRowintegerlas row to clear
endColintegerlast column to clear
nothinggiven range is cleared

Written by:ljr

Example:

clearRange(1, 1, 1, 1, 10, 10) 
--> range A1:J10 cleared
show source AppleScript Editor open insert append toc
on clearRange(sheetName, tableName, startRow, startCol, endRow, endCol)
	local sheetName, tableName, startRow, startCol
	local endRow, endColNumber
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						repeat with i from startRow to endRow
							repeat with j from startCol to endCol
								set value of cell j of row i to ""
							end repeat
						end repeat
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't clearRange: " & eMsg number eNum
	end try
end clearRange

Table Manipulation

rowCountOfTable(sheetName, tableName)

Get the row count of a table in Numbers' front document.

sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
integernumber of rows

Written by:ljr

Example:

rowCountOfTable(1, 1) 
--> 45
show source AppleScript Editor open insert append toc
on rowCountOfTable(sheetName, tableName)
	local sheetName, tableName, res
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set res to row count
					end tell
				end tell
			end tell
		end tell
		return res
	on error eMsg number eNum
		error "Can't rowCountOfTable: " & eMsg number eNum
	end try
end rowCountOfTable

setRowCountOfTable(rowCount, sheetName, tableName)

Set the row count of a table in Numbers' front document.

rowCountintegernumber of rows
sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
nothingerror if problems

Written by:ljr

Example:

setRowCountOfTable(10, 1, 1) 
--> row count of table changed
show source AppleScript Editor open insert append toc
on setRowCountOfTable(rowCount, sheetName, tableName)
	local sheetName, tableName, rowCount
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set row count to rowCount
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't setRowCountOfTable: " & eMsg number eNum
	end try
end setRowCountOfTable

columnCountOfTable(sheetName, tableName)

Get the column count of a table in Numbers' front document.

sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
integernumber of columns

Written by:ljr

Example:

columnCountOfTable(1, 1) 
--> 11
show source AppleScript Editor open insert append toc
on columnCountOfTable(sheetName, tableName)
	local sheetName, tableName, res
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set res to column count
					end tell
				end tell
			end tell
		end tell
		return res
	on error eMsg number eNum
		error "Can't columnCountOfTable: " & eMsg number eNum
	end try
end columnCountOfTable

setColumnCountOfTable(columnCount, sheetName, tableName)

Set the column count of a table in Numbers' front document.

columnCountintegernumber of column
sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
nothingerror if problems

Written by:ljr

Example:

setColumnCountOfTable(5, 1, 1) 
--> column count of table changed
show source AppleScript Editor open insert append toc
on setColumnCountOfTable(columnCount, sheetName, tableName)
	local sheetName, tableName, columnCount
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						set column count to columnCount
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't setColumnCountOfTable: " & eMsg number eNum
	end try
end setColumnCountOfTable

deleteRowsOfTable(startRow, endRow, sheetName, tableName)

Delete some rows of a table in Numbers' front document.

startRowintegerfirst row to delete
endRowintegerlast row to delete
sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
nothingerror if problems

Written by:ljr

Example:

deleteRowsOfTable(5, 10, 1, 1) 
--> rows 5 to 10 deleted
show source AppleScript Editor open insert append toc
on deleteRowsOfTable(startRow, endRow, sheetName, tableName)
	local startRow, endRow, sheetName, tableName
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						delete (rows startRow thru endRow)
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't deleteRowsOfTable: " & eMsg number eNum
	end try
end deleteRowsOfTable

deleteColumnsOfTable(startCol, endCol, sheetName, tableName)

Delete some columns of a table in Numbers' front document.

startColintegerfirst column to delete
endColintegerlast column to delete
sheetNamestring or integername or index of the target sheet
tableNamestring or integername or index of the target table
nothingerror if problems

Written by:ljr

Example:

deleteColumnsOfTable(5, 10, 1, 1) 
--> columns 5 to 10 deleted
show source AppleScript Editor open insert append toc
on deleteColumnsOfTable(startCol, endCol, sheetName, tableName)
	local startCol, endCol, sheetName, tableName
	try
		tell application "Numbers"
			tell document 1
				tell sheet sheetName
					tell table tableName
						delete (columns startCol thru endCol)
					end tell
				end tell
			end tell
		end tell
	on error eMsg number eNum
		error "Can't deleteRowsOfTable: " & eMsg number eNum
	end try
end deleteColumnsOfTable

Cell Address Conversion

integersToAddress(rowNumber, colNumber)

Get the address of a cell of a table in Numbers' front document by its row and column number.

rowNumberintegerrow number of the target cell
colNumberintegercolumn number of the target cell
stringaddress of cell

Written by:ljr

Example:

integersToAddress(10, 56) 
--> "BD10"
show source AppleScript Editor open insert append toc
on integersToAddress(rowNumber, colNumber)
	local rowNumber, colNumber, x, res, alphabet
	try
		set rowNumber to rowNumber as integer
		set colNumber to colNumber as integer
		if rowNumber > 65535 or colNumber > 256 then
			error "Max. number of rows or columns exceeded!" number 111
		end if
		set alphabet to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
		set res to ""
		set x to colNumber div 26
		if x > 0 then set res to alphabet's text x
		set res to res & alphabet's text (colNumber mod 26)
		set res to res & rowNumber
		return res
	on error eMsg number eNum
		error "Can't integersToAddress: " & eMsg number eNum
	end try
end integersToAddress

addressToIntegers(str)

Get the row and column number of a cell of a table in Numbers' front document by its address.

strstringaddress of the target cell
listtwo integers (row number, column number)

Written by:ljr

Example:

addressToIntegers("BD10") 
--> {10, 56}
show source AppleScript Editor open insert append toc
on addressToIntegers(str)
	local str, ASTID, alphabet, rowNumber, colNumber
	local charOne, charTwo, posOne, posTwo
	set ASTID to AppleScript's text item delimiters
	try
		set alphabet to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
		set charOne to str's text 1
		if charOne is not in alphabet then
			error "Invalid address!" number 111
		end if
		set charTwo to str's text 2
		if charTwo is not in alphabet then
			set charTwo to charOne
			set charOne to ""
			set rowNumber to str's text 2 thru -1
		else
			set rowNumber to str's text 3 thru -1
		end if
		if charOne ≠ "" then
			set AppleScript's text item delimiters to charOne
			set posOne to (alphabet's first text item's length) + 1
		else
			set posOne to 0
		end if
		set AppleScript's text item delimiters to charTwo
		set posTwo to (alphabet's first text item's length) + 1
		set colNumber to posOne * 26 + posTwo
		set rowNumber to rowNumber as integer
		set AppleScript's text item delimiters to ASTID
		return {rowNumber, colNumber}
	on error eMsg number eNum
		error "Can't addressToIntegers: " & eMsg number eNum
	end try
end addressToIntegers

Copyright

© 2011 ljr (http://applescript.bratis-lover.net)
all handlers

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON INFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.