Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Attendance tracking in Numbers '09 via AppleScript Apps
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. 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.]
    •    
  • Currently 2.67 / 5
  You rated: 5 / 5 (9 votes cast)
 
[15,891 views]  

Attendance tracking in Numbers '09 via AppleScript | 6 comments | Create New Account
Click here to return to the 'Attendance tracking in Numbers '09 via AppleScript' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Working with Multiple Sheets
Authored by: mr. applescript on Feb 11, '09 09:11:05AM
The scripting dictionary in the current version of Numbers does not have a "current sheet" property to provide a means for determining which sheet is the active one. To make the scripts work when a document contains multiple sheets, I adapted the scripts:

-- COLOR MISSED DAYS
tell application "Numbers"
	try
		tell document 1
			-- only one table at a time can have selected cells
			set the table_references to (the first table of every sheet whose selection range is not missing value)
			set the selected_table to ""
			repeat with i from 1 to the count of the table_references
				set this_reference to item i of the table_references
				if the class of this_reference is table then
					set the selected_table to this_reference
					set the parent_sheet_index to i
					exit repeat
				end if
			end repeat
			if the selected_table is "" then error "Please select a cell in the table to process."
		end tell
		tell sheet parent_sheet_index
			tell the selected_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 missing value -- transparent
					set background color of every cell whose value is false to {65535, 0, 0}
				end tell
			end tell
		end tell
	on error error_message
		display dialog error_message buttons {"Cancel"} default button 1 with icon 2
	end try
end tell
-- CREATE ATTENDANCE TABLE WITH ADDRESS BOOK GROUP
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
		if the (count of sheets) is 1 then
			set the sheet_name to the name of sheet 1
		else
			set the sheet_names to the name of every sheet
			set the chosen_sheet to choose from list sheet_names with prompt "Which sheet is currently selected?"
			if chosen_sheet is false then error number -128
			set the sheet_name to chosen_sheet as string
		end if
		
		tell sheet sheet_name
			set the table_names to the name of every table
			set the test_name to the table_name
			set incrementor to 0
			repeat until test_name is not in the table_names
				set incrementor to incrementor + 1
				set the test_name to the table_name & "-" & (incrementor as string)
			end repeat
			set the table_name to the test_name
			
			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


[ Reply to This | # ]
Working with Multiple Sheets
Authored by: Yvan KOENIG on May 30, '09 06:50:49AM

I apologize but the second script fails under 10.4.11

is running is not a valid command

make a new table with properties fails too.

telleing to application id fails too.

I didn't see any comment telling that the script is dedicated to a single operating system.

Yvan KOENIG



[ Reply to This | # ]
Working with Multiple Sheets
Authored by: Yvan KOENIG on May 30, '09 07:48:48AM

Hello

The "COLOR MISSED DAYS" scripts contains what appears to be an oddity.

A cell containing the value zero (0 or 0.00) will be colored as well as really empty cells.

The only way to get rid of that is to use an auxiliary table in which you would create the formula:
=ISBLANK(theSheet :: theTable :: theCell)

grabbing the value of this cell (true or false) will give you the true status of the pointed cell.

Yvan KOENIG (from FRANCE samedi 30 mai 2009 16:48:16)



[ Reply to This | # ]
Attendance tracking in Numbers '09 via AppleScript
Authored by: jaxjason on Feb 11, '09 10:05:23AM

Can't the second script basically be done with a conditional format on the range? Why, other than an example of scripting, would it be different? (Just a question, if just an exmaple, its a very good one showing how to get selected ranges, individual elements and set properties for cells).

Thanks,
Jason



[ Reply to This | # ]
Attendance tracking in Numbers '09 via AppleScript
Authored by: mr. applescript on Feb 11, '09 10:50:19AM

Two reasons: 1) If you address all cells, then the top left cell will change color too since it is empty and will return a default value of 0 which is read as false. 2) You're right, they're examples of how to select a range and set cell properties ;-)



[ Reply to This | # ]
Attendance tracking in Numbers '09 via AppleScript
Authored by: arsov on Feb 12, '09 06:09:34AM

Hi!
I'm new to scripts in general. I looked trough the first script. What I would like to do is make the script follow track attendance only on Sundays. What do I need to change?
Thanks.



[ Reply to This | # ]