Swapping Axes in Numbers

Swapping Axes in Numbers

I was working on a spreadsheet in Numbers and it occurred to me that the data could be presented better—when I convert it to HTML—if I were to swap the X and Y axes. This won’t be a chart, so the oft-suggested solution of using TRANSPOSE() won’t work.

(“Axes”, by the way, is the plural of “axis.” I say that not because you don’t know that, but because I want “axis” to be picked up by the search engines.)

Finding this AppleScript was a chore, because the dozens of references to it nearly all linked to an extinct iDisk account, so I’m posting it here where it might live a few more years until the Internet implodes.

--\[SCRIPT transpose]{code}
(*
Thanks to Scott Lindsey & Ed.Stockly from applescript-users@lists.apple.com

Copy a group of cells in the clipboard.
Put the cursor where you want.
Run the script.
It will insert the transposed set of values.
Of course, it will be a 'frozen' block.
Changes in the source one will not be reflected.

Yvan KOENIG (Vallauris, FRANCE)
15 janvier 2009
3 juin 2009
*)
property theApp : "Numbers"

on run
  try
    set avant to the clipboard as text
  on error
    error "No copied values in the clipboard !"
  end try
  set avant to paragraphs of avant

  repeat with i from 1 to count of avant
    set item i of avant to my decoupe(item i of avant, tab)
  end repeat

  set {rname, tName, sName, dName} to my getSelection()
  if rname is missing value then error "No selected cells"

  set twoNames to my decoupe(rname, ":")
  set {rowNum1, colNum1} to my decipher(item 1 of twoNames, dName, sName, tName)

  (* Here we know the starting point of the destination area. *)

  tell application "Numbers"
    activate
    tell document dName to tell sheet sName to tell table tName

      set rowsCible to (get row count)
      set rowsNeeded to rowNum1 - 1 + (count of item 1 of avant)
      if rowsNeeded > rowsCible then
        repeat (rowsNeeded - rowsCible) times
          add row below row rowsCible
        end repeat
      end if -- rowsNeeded

      set columnsCible to (get column count)
      set columnsNeeded to colNum1 - 1 + (count of avant)
      if columnsNeeded > columnsCible then
        repeat (columnsNeeded - columnsCible) times
          add column after column columnsCible
        end repeat
      end if -- columnsNeeded

      repeat with i from 1 to count of avant
        repeat with j from 1 to count of item 1 of avant
          set value of cell (colNum1 - 1 + i) of row (rowNum1 - 1 + j) to item j of item i of avant
        end repeat
      end repeat
    end tell
  end tell
end run

--=====

on getSelection()
  local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection
  tell application "Numbers"
    activate
    tell document 1
      set mySelectedRanges to selection range of every table of every sheet
      repeat with sheetRanges in mySelectedRanges
        repeat with thisRange in sheetRanges
          if contents of thisRange is not missing value then
            try
              thisRange as text
            on error errMsg number errNum
              set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errMsg, quote)
              return {myRange, myTable, mySheet, myDoc}
            end try
          end if -- contents…
        end repeat -- thisRange
      end repeat -- sheetRanges
    end tell -- document 1
  end tell -- application

  return {missing value, missing value, missing value, missing value}
end getSelection

--=====

on decipher(n, d, s, t)
  tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
end decipher

--=====

on decoupe(t, d)
  local l
  set AppleScript's text item delimiters to d
  set l to text items of t
  set AppleScript's text item delimiters to ""
  return l
end decoupe

--=====
--[/SCRIPT]{code}