# SG, 20150812 rev 201706

# user settings -- change as needed
set firstColumnsWidth to 65
set otherColumnsWidth to 65
set includeTotals to true --set to false if don't want Totals row/column in crosstab table
set totalsBackgroundColor to {62258, 62256, 62257} --RGB for a light gray

global delayAfterPaste
set delayAfterPaste to 0.3 -- use higher value if headers, formulas paste incorrectly
property colLetters : "ABCDEFGHIJKLMOPQRST"

# prompt for operation
set operation to choose from list {"SUM", "COUNT"} with prompt "Do you want to Sum or Count?"
if operation is not false then set operation to operation as string

# get information from clicked source table
tell application "Numbers" to tell document 1 to tell active sheet
    try
        set sourceTable to its table 1 whose selection range's class is range
    on error
        display dialog "Click in a table with data to 'crosstabulate'?" buttons "Cancel"
    end try
    tell sourceTable
        set srcTableName to name
        set columnLabelsRow to header row count -- assumes labels are in last Header Row
        set srcColumnLabels to row columnLabelsRow's cells's value
        set lastBodyRow to row count - footer row count
    end tell
end tell

# get user choice of source columns for xtab's row, column and value
set xRowsCol to chooseColumn from srcColumnLabels on "'DOWN THE LEFT' show labels from ..."
 an AppleScript record,e.g.:  {colLabel:"Fruit", colLetter:"A"}
set xColumnsCol to chooseColumn from srcColumnLabels on "'ACROSS THE TOP' show labels from ..."

if operation = "SUM" then
    set xValueCol to chooseColumn from srcColumnLabels on "'SUM THE VALUES' from ..."
    set xValues to getValuesInColumn from srcTableName against (xValueCol's colLetter) below columnLabelsRow thru lastBodyRow
    set xValues to doSort on (getDistinctVals from xValues)
    
    # check if the value column contains numbers that can be summed
    set isNumber to checkIfNumber against xValues's item -1 --any non-numbers already sorted to end
    if not isNumber then
        display dialog "There are non-numbers in the column you chose to SUM. 
Do you want to COUNT instead?" buttons {"Yes, Count", "Cancel"}
        set operation to "COUNT"
    end if
end if

# extract distinct, sorted values from the column chosen for crosstab rows
set xRowHeads to getValuesInColumn from srcTableName against (xRowsCol's colLetter) below columnLabelsRow thru lastBodyRow
set xRowHeads to doSort on (getDistinctVals from xRowHeads)
set xRowHeadCount to count xRowHeads

# extract distinct, sorted values from the column chosen for crosstab columns
set xColHeads to getValuesInColumn from srcTableName against (xColumnsCol's colLetter) below columnLabelsRow thru lastBodyRow
set xColHeads to doSort on (getDistinctVals from xColHeads)
set xColHeadCount to count xColHeads

# construct the formula
if operation = "SUM" then
    
    # assemble description for cross table's cell "A1"
    set xTabDescription to ¬
        "Sum of '" & xValueCol's colLabel & "' by '" & xRowsCol's colLabel & ¬
        "'  and  '" & xColumnsCol's colLabel & "'"
    
    set theFormula to "=SUMIFS(" & ¬
        srcTableName & "::" & xValueCol's colLabel ¬
        & "," & ¬
        srcTableName & "::" & xRowsCol's colLetter ¬
        & "," & ¬
        "A" & "," & ¬
        srcTableName & "::" & xColumnsCol's colLetter ¬
        & "," & ¬
        "2:2" & ")"
     e.g. "=SUMIFS(Table 1::Cost,Table 1::A,A,Table 1::C,2:2)"
else
    set xTabDescription to ¬
        "Count of '" & xRowsCol's colLabel & ¬
        "'  by  '" & xColumnsCol's colLabel & "'"
    
    set theFormula to "=COUNTIFS(" & ¬
        srcTableName & "::" & xRowsCol's colLetter ¬
        & "," & ¬
        "A" & "," & ¬
        srcTableName & "::" & xColumnsCol's colLetter ¬
        & "," & ¬
        "2:2" & ")"
     e.g. "=COUNTIFS(Table 1#A,A,Table 1::C,2:2)"
end if

# set up and populate a new crosstab table
tell application "Numbers" to tell document 1 to tell active sheet
    set newTable to make new table with properties ¬
        {row count:xRowHeadCount + 2, column count:xColHeadCount + 1, header row count:2}
    
    tell newTable
        
        # style the new crosstab table
        set column 1's width to firstColumnsWidth
        set (columns 2 thru -1)'s width to otherColumnsWidth
        tell row 1 to set {background color, font name} to {"white", "Helvetica"}
        tell row 2
            set alignment to right
            set cell 1's background color to "white"
        end tell
        
        # insert description in cell A1, with no text wrap
        tell cell "A1" to set {text wrap, value} to {false, xTabDescription}
        
        # Paste crosstab row header labels
        set rowHeaderBlock to getString of me from xRowHeads by return
        set the selection range to range "A3"
        my pasteBlock(rowHeaderBlock)
        
        # Paste crosstab column header labels
        set colHeaderBlock to getString of me from xColHeads by tab
        set the selection range to range "B2"
        my pasteBlock(colHeaderBlock)
        
        # Paste formula into body cells
        set formulaBlock to ""
        repeat with r from 1 to xRowHeadCount
            repeat with c from 1 to xColHeadCount
                set formulaBlock to formulaBlock & theFormula & tab
            end repeat
            set formulaBlock to formulaBlock's text 1 thru -2 & return
        end repeat
        
        set selection range to range "B3"
        my pasteBlock(formulaBlock)
        
        
        # add row and column totals
        if includeTotals then
            try
                set rowSumRngEnd to ¬
                    colLetters's text (last column's address)
            on error
                display dialog "Too many columns! Switch orientation." buttons "Cancel"
            end try
            
            set totalRow to add row below last row
            set footer row count to 1
            
            set totalCol to add column after last column
            tell totalCol to set (cells 3 thru -1)'s background color to totalsBackgroundColor
            
            set rowTotBlock to "TOTAL" & return
            repeat with i from 3 to (row count - 1)
                set rowTotBlock to rowTotBlock & "=SUM(B" & i & ":" & rowSumRngEnd & i & ")" & return
            end repeat
            set selection range to range (totalCol's second cell's name)
            my pasteBlock(rowTotBlock)
            
            tell totalRow to set (cells 2 thru -1)'s background color to totalsBackgroundColor
            
            set colTotBlock to "TOTAL" & tab
            repeat with i from 2 to column count
                set colTotBlock to colTotBlock & "=SUM(" & column i's name & ")" & tab
            end repeat
            set colTotBlock to colTotBlock's text 1 thru -2
            set selection range to range (totalRow's first cell's name)
            my pasteBlock(colTotBlock)
            
        end if
    end tell
end tell

--------------------- handlers ---------------------------------


to pasteBlock(theTextBlock) -- pasting text block is MUCH faster than setting value cell by cell
    set the clipboard to theTextBlock
    activate application "Numbers"
    tell application "System Events" to keystroke "v" using {option down, shift down, command down}
    delay delayAfterPaste
end pasteBlock


to chooseColumn from aList on thePrompt
    set theChoice to choose from list aList with prompt thePrompt
    if theChoice is false then error number -128 --halt script if 'Cancel'
    set colNum to getOffset for theChoice against aList
    set colLtr to colLetters's text colNum
    {colLabel:theChoice as string, colLetter:colLtr} -- an AppleScript record
end chooseColumn

to getOffset for thisItem against aList
    repeat with i from 1 to count aList
        if aList's item i is thisItem as text then return i
    end repeat
    return 0 -- if not in list return 0
end getOffset

to getValuesInColumn from tableName against colLetter below labelsRow thru lastBodyRow
    tell application "Numbers" to tell document 1 to tell active sheet
        tell table tableName to set valuesInColumn to ¬
            column colLetter's (cells (labelsRow + 1) thru lastBodyRow)'s value
    end tell
    valuesInColumn
end getValuesInColumn

to getDistinctVals from aList
    set newList to {}
    repeat with i from 1 to count aList
        try
            tell aList's item i to ¬
                if it is not missing value and ¬
                    it is not in newList then copy it to newList's end
        on error
            display dialog "Can't get distinct values?" buttons "Cancel"
        end try
    end repeat
    newList
end getDistinctVals

to doSort on aList
    # http://macscripter.net/viewtopic.php?id=24746
    script S
        property IL : {} -- index list
        property SL : {} -- sorted list
        property ML : aList
    end script
    repeat (the number of items in S's ML) times
        set the lowItem to ""
        repeat with i from 1 to (number of items in S's ML)
            if i is not in the S's IL then
                set thisItem to item i of S's ML as text
                if the lowItem is "" then
                    set the lowItem to thisItem
                    set the lowItemIndex to i
                else if thisItem comes before the lowItem then
                    set the lowItem to thisItem
                    set the lowItemIndex to i
                end if
            end if
        end repeat
        set the end of S's SL to the lowItem
        set the end of the S's IL to the lowItemIndex
    end repeat
    return S's SL
end doSort

to checkIfNumber against aValue
    try
        if aValue is not missing value then set aValue to aValue as number
        return true
    on error
        return false
    end try
end checkIfNumber

to getString from aList by delim
    set text item delimiters to delim
    return aList as text
end getString