AppleScript and Numbers: Attendance Table for Contacts Group

Attendance Table for Contacts Group

This script is designed for teachers, or others, who require the keeping of an attendance record for a group of people, listed in the user’s Contacts application.

It provides examples of these techniques:

Construction of a new table (95-96)
The import of data from another application (19-59)
The use of assigned data formats for cells (132)
The use of contextual formulas (144)
The use of the sort command (150)

Here’s the script:

Create an Attendance Table for a Chosen Contact Group

try

tell application id "com.apple.iWork.Numbers"

activate

if not (exists document 1) then error number 1000

display dialog "This script will create a new table for tracking " & ¬

"the attendance of the people in a chosen Contacts group." & ¬

return & return & ¬

"Should the table include Saturdays and Sundays?" buttons ¬

{"Cancel", "Omit Weekends", "Include Weekends"} default button 3

if the button returned of the result is "Include Weekends" then

set includeWeekends to true

else

set includeWeekends to false

end if

end tell

-- launch Contacts if not already open

if application id "com.apple.AddressBook" is not running then

set AddressBookWasOpen to false

tell application id "com.apple.AddressBook" to launch

else

set AddressBookWasOpen to true

end if

-- get the name of every Contacts group

tell application id "com.apple.AddressBook"

set the AddressBookGroupNames to the name of every group

end tell

if the AddressBookGroupNames is {} then error number 1001

-- prompt user to pick group

tell application id "com.apple.iWork.Numbers"

activate

set the chosenGroup to ¬

(choose from list the AddressBookGroupNames ¬

with prompt "Pick the Contacts group to be used for the table:")

if the chosenGroup is false then error number -128

set the chosenGroup to the chosenGroup as string

end tell

-- extract the full names of the group’s people

tell application id "com.apple.AddressBook"

set thesePeople to every person of group chosenGroup

if thesePeople is {} then error number 1002

set the fullNames to {}

repeat with i from 1 to the count of thesePeople

set thisPerson to item i of thesePeople

set the end of the fullNames to ¬

(last name of thisPerson) & ", " & (first name of thisPerson)

end repeat

set the peopleCount to the count of the fullNames

end tell

-- close Contacts if it wasn’t previously open

if AddressBookWasOpen is false then

tell application id "com.apple.AddressBook" to quit

end if

-- generate the column titles

set tempDate to the current date

set the columnTitles to {}

set day of tempDate to 1

set thisMonth to the month of tempDate

repeat until month of tempDate is not thisMonth

set thisDay to day of tempDate

set thisWeekday to the (weekday of tempDate) as string

if includeWeekends is true or (includeWeekends is false and ¬

thisWeekday is not in {"Saturday", "Sunday"}) then

set the dayTitle to ¬

(thisDay & "-" & (text 1 thru 1 of thisWeekday)) as string

set the end of the columnTitles to the dayTitle

end if

set tempDate to tempDate + (1 * days)

end repeat

set the dayCount to the count of the columnTitles

set the tableName to chosenGroup & space & ¬

(text 1 thru 3 of (thisMonth as string) & ¬

"-" & year of (the current date)) as string

set tableName to my incrementtableNameIfNeeded(tableName, "-")

tell application id "com.apple.iWork.Numbers"

activate

tell document 1

tell the active sheet

-- make and populate the table

-- include extra column for total of missed days

set thisTable to make new table with properties ¬

{column count:dayCount + 2, row count:peopleCount + 1, name:tableName}

tell thisTable

-- set any global cell properties

100

set the height of every row to 24

101

set the vertical alignment of every row to center

102

set the alignment of every row to center

103

-- insert data

104

tell row 1

105

-- set specific properties for the title row

106

set the vertical alignment to center

107

set the alignment to center

108

-- insert column titles

109

repeat with i from 2 to dayCount + 1

110

set value of cell i to item (i - 1) of the columnTitles

111

end repeat

112

end tell

113

tell column 1

114

-- set specific properties for the title column

115

set the vertical alignment to center

116

set the alignment to left

117

set the width to 128

118

-- insert full names

119

repeat with i from 2 to peopleCount + 1

120

set value of cell i to item (i - 1) of the fullNames

121

end repeat

122

end tell

123

tell columns 2 thru -2

124

set width to 36

125

end tell

126

127

-- get the range of the cells to set

128

set the rangeStart to the name of cell 2 of column 2

129

set the rangeEnd to the name of last cell of column -2

130

-- set format of cells

131

tell range (rangeStart & ":" & rangeEnd)

132

set format to checkbox

133

end tell

134

135

-- Set the style and formula of the total missed days column

136

tell last column

137

set the width to 60

138

set the value of cell 1 to "MISSED"

139

set alignment to center

140

end tell

141

repeat with i from 2 to the count of rows

142

tell row i

143

set the rangeString to (name of cell 2 & ":" & name of cell -2)

144

set the formulaString to "=COUNTIF(" & rangeString & ", FALSE)"

145

set the value of the last cell to formulaString

146

end tell

147

end repeat

148

149

-- sort the table based on student names

150

sort by column 1 direction ascending

151

152

end tell

153

end tell

154

end tell

155

return thisTable

156

end tell

157

on error errorMessage number errorNumber

158

if errorNumber is 1000 then

159

set alertString to "MISSING NUMBERS RESOURCE"

160

set errorMessage to "Please create or open a document before running this script."

161

else if errorNumber is 1001 then

162

set alertString to "MISSING CONTACTS RESOURCE"

163

set errorMessage to "There are no groups in your Contacts."

164

else if errorNumber is 1002 then

165

set alertString to "MISSING CONTACTS RESOURCE"

166

set errorMessage to "There are no people in the chosen Contacts group."

167

else

168

set alertString to "EXECUTION ERROR"

169

end if

170

if the errorNumber is not -128 then

171

tell application id "com.apple.iWork.Numbers"

172

activate

173

display alert alertString message errorMessage buttons {"Cancel"}

174

error number -128

175

end tell

176

end if

177

end try

178

179

on incrementtableNameIfNeeded(thistableName, thisDivider)

180

-- a routine for deriving a unique table name based on a provided table name

181

tell application "Numbers"

182

tell the front document

183

tell the active sheet

184

-- get a list of the names of the tables on the active sheet

185

set the thesetableNames to the name of every table

186

set the testtableName to the thistableName

187

set incrementor to 0

188

-- increment the passed name, if needed

189

repeat until testtableName is not in thesetableNames

190

set incrementor to incrementor + 1

191

set the testtableName to ¬

192

thistableName & thisDivider & (incrementor as string)

193

end repeat

194

-- return the non-conflicting table name

195

return the testtableName

196

end tell

197

end tell

198

end tell

199

end incrementtableNameIfNeeded

TOP | CONTINUE

DISCLAIMER

THIS WEBSITE IS NOT HOSTED BY APPLE INC.

Mention of third-party websites and products is for informational purposes only and constitutes neither an endorsement nor a recommendation. MACOSXAUTOMATION.COM assumes no responsibility with regard to the selection, performance or use of information or products found at third-party websites. MACOSXAUTOMATION.COM provides this only as a convenience to our users. MACOSXAUTOMATION.COM has not tested the information found on these sites and makes no representations regarding its accuracy or reliability. There are risks inherent in the use of any information or products found on the Internet, and MACOSXAUTOMATION.COM assumes no responsibility in this regard. Please understand that a third-party site is independent from MACOSXAUTOMATION.COM and that MACOSXAUTOMATION.COM has no control over the content on that website. Please contact the vendor for additional information.