An example of using automation with the built-in collaboration features of the iWork Numbers application.

In this example, instructors use the following AppleScript script to automate the sign-up process for a conference. In turn, each instructor selects a table cell whose row and column headers indicate the class they would like to teach.

When the script is run, their name is inserted into the selected table cell and its background color is changed from red to green. In addition, a new Calendar even for the class is added to their default calendar.

Items to note:

• The script uses the name of the current user for the instructor’s name
• The script assumes the default calendar is the one in which to add new events
• The top header row of the table has a value representing the day of the session, that is a date object.
• The bottom header of the table has a value, representing the class start time and end time, that is a string. (13:45-17:15)
• The times expressed in the bottom header of the table has a value are in “military time format” (13:45-17:15)

DOWNLOAD the Numbers document and AppleScript script files. Information about how to activate the Script Menu and install scripts to it, can be found here.

Collaborated Sign-Up Sheet
Open in Script Editor
01		tell application "Numbers"
02		activate
03		try
04		if not (exists document 1) then error number 1000
05		tell document 1
06		try
07		tell active sheet
08		set the selectedTable to ¬
09		(the first table whose class of selection range is range)
10		end tell
11		on error
12		error number 1001
13		end try
14		tell selectedTable
15		if the (count of cells of selection range) is not 1 then ¬
16		error number 1002
17
18		set cellID to the name of item 1 of selection range
19
20		if address of row of cell cellID is less than or equal to 2 or ¬
21		address of column of cell cellID is 1 then error number 1003
22
23		set taskName to the formatted value of cell 1 of row of cell cellID
24
25		set sessionDate to the value of cell 1 of column of cell cellID
26		set sessionTimeString to ¬
27		the formatted value of cell 2 of column of cell cellID
28		--> "08:45-13:00"
29
30		set startHour to (word -4 of sessionTimeString) as integer
31		set startMinute to (word -3 of sessionTimeString) as integer
32		set endHour to (word -2 of sessionTimeString) as integer
33		set endMinute to (word -1 of sessionTimeString) as integer
34
35		copy sessionDate to startTime
36		set hours of startTime to startHour
37		set minutes of startTime to startMinute
38		set seconds of startTime to 0
39
40		copy sessionDate to endTime
41		set hours of endTime to endHour
42		set minutes of endTime to endMinute
43		set seconds of endTime to 0
44
45		tell current application
46		set userName to long user name of (get system info)
47		set firstName to word 1 of userName
48		end tell
49		set value of cell cellID to firstName
50		set the background color of cell cellID to {38390, 64038, 22477}
51		end tell
52		end tell
53
54		tell current application
55		set calendarID to ¬
56		(do shell script "defaults read com.apple.iCal CalDefaultCalendar")
57		end tell
58
59		tell application "Calendar"
60		activate
61		if calendarID is "UseLastSelectedAsDefaultCalendar" then
62		set defaultCalendar to calendar 1
63		else
64		set defaultCalendar to first calendar whose uid is calendarID
65		end if
66		tell defaultCalendar
67		set newEvent to make new event with properties ¬
68		{summary:taskName, start date:startTime, end date:endTime}
69		end tell
70		show newEvent
71		end tell
72
73		on error errorMessage number errorNumber
74		if errorNumber is 1000 then
75		set alertString to (errorNumber as string) & space & "MISSING RESOURCE"
76		set errorMessage to ¬
77		"Please create or open a document before running this script."
78		else if errorNumber is 1001 then
79		set alertString to "SELECTION ERROR"
80		set errorMessage to ¬
81		"Please select a single cell in a table before running this script."
82		else if errorNumber is 1002 then
83		set alertString to "SELECTION ERROR"
84		set errorMessage to ¬
85		"Please select a single cell before running this script."
86		else if errorNumber is 1003 then
87		set alertString to "SELECTION ERROR"
88		set errorMessage to ¬
89		"Please select a single cell that is not in a row or column header."
90		else
91		set alertString to "EXECUTION ERROR"
92		end if
93		display alert alertString message errorMessage buttons {"Cancel"}
94		error number -128
95		end try
96		end tell