Attendance tracking in Numbers '09 via AppleScript

Feb 11, '09 07:30:00AM

Contributed by: mr. applescript

I've been playing around with the new AppleScript support in Numbers '09, and came up with a couple example scripts for teachers (or anyone who needs to track attendance over a month for a group of people). The first script creates a new table, in the currently open sheet, using the names from a chosen Address Book group.

The names of the people in the group are displayed alphabetically down the first column on the left, and the dates of the current month are displayed across the top row. The script will prompt for the Address Book group to use, as well as whether you want to omit or include weekends in the display.

-- CREATE MONTHLY ATTENDANCE TABLE

global include_weekends

display dialog "This script will create a new table for tracking attendance for the people in a chosen Address Book 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 include_weekends to true
else
  set include_weekends to false
end if

-- launch Address Book if not open
if application id "com.apple.AddressBook" is not running then
  set AB_was_open to false
  tell application id "com.apple.AddressBook" to launch
else
  set AB_was_open to true
end if

-- get the name of every group
tell application id "com.apple.AddressBook"
  set the group_names to the name of every group
end tell
if the group_names is {} then error number -128

-- prompt user to pick group
tell application "Numbers"
  activate
  set the chosen_group to (choose from list the group_names with prompt "Pick the group to be used in the table:")
  if the chosen_group is false then error number -128
  set the chosen_group to the chosen_group as string
end tell

-- extract the full names of the group's people
tell application id "com.apple.AddressBook"
  set the these_people to every person of group chosen_group
  set the full_names to {}
  repeat with i from 1 to the count of the these_people
    set this_person to item i of the these_people
    set the end of the full_names to (last name of this_person) & ", " & (first name of this_person)
  end repeat
  set the people_count to the count of the full_names
end tell

-- close Address Book and sort names
if AB_was_open is false then tell application id "com.apple.AddressBook" to quit
set the full_names to my simple_sort(full_names)

-- generate the column titles
set temp_date to the current date
set the column_titles to {}
set day of temp_date to 1
set this_month to the month of temp_date
repeat until month of temp_date is not this_month
  set this_day to day of temp_date
  set this_weekday to the (weekday of temp_date) as string
  if include_weekends is true or (include_weekends is false and this_weekday is not in {"Saturday", "Sunday"}) then
    set the day_title to (this_day & "-" & (text 1 thru 1 of this_weekday)) as string
    set the end of the column_titles to the day_title
  end if
  set temp_date to temp_date + (1 * days)
end repeat

set the day_count to the count of the column_titles

set the table_name to (text 1 thru 3 of (this_month as string) & "-" & year of (the current date)) as string

tell application "Numbers"
  activate
  
  if not (exists document 1) then
    display dialog "There is no document open." buttons {"Cancel"} default button 1
  end if
  
  -- make and populate the table
  tell document 1
    tell sheet 1
      
      set this_table to make new table with properties {name:table_name, column count:day_count + 1, row count:people_count + 1}
      tell this_table
        -- set any global cell properties
        set the height of every row to 24
        set the vertical alignment of every row to center
        set the alignment of every row to center
        -- insert data
        tell row 1
          -- set specific properties for the title row
          set the vertical alignment to center
          set the alignment to center
          -- insert column titles
          repeat with i from 2 to day_count + 1
            set value of cell i to item (i - 1) of the column_titles
          end repeat
        end tell
        tell column 1
          -- set specific properties for the title column
          set the vertical alignment to center
          set the alignment to left
          set width to 128
          -- insert full names
          repeat with i from 2 to people_count + 1
            set value of cell i to item (i - 1) of the full_names
          end repeat
        end tell
        tell columns 2 thru -1
          set width to 36
        end tell
        -- set format of cells
        set the range_start to the name of cell 2 of column 2
        set the range_end to the name of last cell of last column
        tell range (range_start & ":" & range_end)
          set format to checkbox
        end tell
      end tell
    end tell
  end tell
end tell

on simple_sort(my_list)
  set the index_list to {}
  set the sorted_list to {}
  repeat (the count of my_list) times
    set the low_item to ""
    repeat with i from 1 to (number of items in my_list)
      if i is not in the index_list then
        set this_item to item i of my_list as text
        if the low_item is "" then
          set the low_item to this_item
          set the low_item_index to i
        else if this_item comes before the low_item then
          set the low_item to this_item
          set the low_item_index to i
        end if
      end if
    end repeat
    set the end of sorted_list to the low_item
    set the end of the index_list to the low_item_index
  end repeat
  return the sorted_list
end simple_sort
The second script is meant to work with one of the attendance tables created by the first script. After completing the table (i.e. at the end of the month, where any missed days are reflected by an unchecked cell), select any cell in an attendance table and run the script to color the cells of missed days red.
-- COLOR MISSED DAYS

tell application "Numbers"
  activate
  tell document 1
    tell sheet 1
      try
        set this_table to the first table whose selection range is not missing value
      on error
        display dialog "Please select a cell in the table to process." buttons {"Cancel"} default button 1 with icon 2
      end try
      tell this_table
        set the range_start to the name of cell 2 of column 2
        set the range_end to the name of last cell of last column
        tell range (range_start & ":" & range_end)
          set background color of every cell whose value is true to {65535, 65535, 65535}
          set background color of every cell whose value is false to {65535, 0, 0}
        end tell
      end tell
    end tell
  end tell
end tell
Save both of the scripts as separate script files (.scpt) from Script Editor, and put them in Home » Library » Scripts » Applications » Numbers to have them appear in the Scripts Menu while Numbers is the frontmost application. Enjoy!

[robg adds: Both scripts worked as described.]

Comments (6)


Mac OS X Hints
http://hints.macworld.com/article.php?story=20090209112919268