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


Click here to return to the 'Working with Multiple Sheets' 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 | # ]