I wrote a slightly bloated script for a pivot table function for Numbers. Anybody please let me know what you think. I'd like some feedback / testing on this one.
Code:
tell application "Numbers"
tell document 1
if (count (name of every sheet)) > 1 then
set this_sheet to the first item of (choose from list (name of every sheet) as list with prompt "Please select a sheet:") as string
set this_sheet to sheet this_sheet
else
set this_sheet to sheet 1
end if
tell this_sheet
if (count (name of every table)) > 1 then
set this_table to the first item of (choose from list (name of every table) as list with prompt "Please select a table:") as string
set this_table to table this_table
else
set this_table to table 1
end if
tell this_table
set a to the first item of (choose from list (name of every column) as list with prompt "Select the first column:") as string
set b to the first item of (choose from list (name of every column) as list with prompt "Select the second column:") as string
set c to the first item of (choose from list (name of every column) as list with prompt "Select the sum values column:") as string
set a_values to my pivot((the value of cells 2 thru -1 in column a) as list)
set b_values to my pivot((the value of cells 2 thru -1 in column b) as list)
set a to name of this_sheet & " :: " & name of this_table & " :: $" & a
set b to name of this_sheet & " :: " & name of this_table & " :: $" & b
set c to name of this_sheet & " :: " & name of this_table & " :: $" & c
end tell
end tell
set this_sheet to make new sheet
tell this_sheet
tell table 1
set the row count to (count a_values) + 1
set the column count to (count b_values) + 1
repeat with t from 1 to (count a_values)
set the value of cell (t + 1) of column 1 to (item t of a_values) as text
end repeat
repeat with t from 1 to (count b_values)
set the value of cell (t + 1) of row 1 to (item t of b_values) as text
end repeat
repeat with x from 2 to row count
repeat with y from 2 to column count
set the value of cell y of row x to "=sumifs(" & c & "," & b & ",indirect(address(1,column(),2))," & a & ",indirect(address(row(),1,3)))"
end repeat
end repeat
end tell
end tell
end tell
end tell
on pivot(old_list)
set new_list to {}
repeat with this_item in old_list
if new_list does not contain (this_item as text) then copy (this_item as text) to the end of new_list
end repeat
return new_list as list
end pivot