Create a Multi-Markdown Table from a Table in Numbers

I’ve been doing a lot of work with Multi-Markdown tables for my day job. And I’ve been somewhat frustrated with how much formatting (and reformatting) is required to keep them looking nice in a plain text Markdown file.

So I tool a page out of @drdrang’s playbook and created an AppleScript that converts the first table on the selected Apple Numbers sheet to a Multi-Markdown table:

(*
	Converts the first table on the active Number's
	sheet to a Multi-Markdown table.
*)

tell application "Numbers"
	set _md to ""
	
	-- If there's an open document, process it
	if (count of documents) > 0 then
		set _doc to the first item in documents
		set _sheet to the _doc's active sheet
		set _table to the first item in _sheet's tables
		
		tell _table
			-- Format the table's header
			set _isFirstRow to true
			set _rows to rows
			repeat with _row in _rows
				-- Generate a Markdown table row for the row's cells
				repeat with _cell in _row's cells
					set _val to _cell's value
					if _val ≠ missing value then
						set _md to (_md & "| " & _val as Unicode text) & " "
					else
						set _md to _md & "|  "
					end if
				end repeat
				set _md to _md & "|" & linefeed
				
				-- Set the justification based on how the first row is styled.
				if _isFirstRow then
					repeat with _cell in _row's cells
						if _cell's alignment = auto align or _cell's alignment = justify or _cell's alignment = left then
							set _md to _md & "| :---- "
						else if _cell's alignment = center then
							set _md to _md & "| :---: "
						else if _cell's alignment = right then
							set _md to _md & "| ----: "
						end if
					end repeat
					set _md to _md & "|" & linefeed
					set _isFirstRow to false
				end if
			end repeat
		end tell
	end if
	
	return _md as Unicode text
end tell

This script can be executed by a Keyboard Maestro action that types or pastes its output.

I then used @drdrang’s Python script to “tidy” the Multi-Markdown table returned by the AppleScript.

Note that the script is somewhat slow as I think AppleScript doesn’t perform string concatenations very quickly. On my Mac—which is admittedly quite ancient—it takes about 8 seconds to output a Multi-Markdown table from a Numbers table that has 175 rows.

8 Likes

I have a script that’s meant to be run from BBEdit, but apart from the last few lines, it’s very much like yours.

on addr(r, c)
  set alphabet to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  return (character c of alphabet) & r
end addr

tell application "Numbers"
  set thisTable to table 1 of sheet 1 of document 1
  tell thisTable
    set out to ""
    set h to header row count
    set c to column count
    set r to row count - h
    
    -- header lines
    repeat with i from 1 to h
      set out to out & "|"
      repeat with j from 1 to c
        set theCell to my addr(i, j)
        set val to formatted value of cell theCell
        if val = missing value then
          set val to ""
        end if
        set out to out & " " & val & " |"
      end repeat
      set out to out & linefeed
    end repeat
    
    -- formatting line
    set out to out & "|"
    repeat with j from 1 to c
      set theCell to my addr(1, j)
      set a to alignment of cell theCell
      if a = right then
        set out to out & "---:|"
      else if a = center then
        set out to out & ":--:|"
      else
        set out to out & ":---|"
      end if
    end repeat
    set out to out & linefeed
    
    -- body lines
    repeat with i from 1 to r
      set out to out & "|"
      repeat with j from 1 to c
        set theCell to my addr(i + h, j)
        set val to formatted value of cell theCell
        if val = missing value then
          set val to ""
        end if
        set out to out & " " & val & " |"
      end repeat
      set out to out & linefeed
    end repeat
  end tell
  get out
end tell

tell application "BBEdit"
  set selection to out
end tell

(I like your idea of doing repeat with cell in _row's cells instead of my repeat with j from 1 to c for looping over the columns. It avoids the need to convert from column number to column letter.)

I have always found my script to run slower than expected. I just tested it on a table with 175 rows and 6 columns, and it took about 4 seconds on my M1 MacBook Air. AppleScript just isn’t the quickest language in the world.

I’ve often thought about rewrting it to process the body of the table in another language via do shell script, but I doubt the time invested in the rewrite would ever be regained.

1 Like

I love the AppleScript solutions! Personally for the last few years I’ve been using TableFlip, but it is another tool you need to download!

2 Likes

+1 for TableFlip. Great for going from Google Sheets, Numbers or Excel to a Markdown table — and vice versa.

Personally, I would likely use the Python library PrettyTable for this. You can either build up the data from within your script or import a CSV file, and then output an ASCII formatted table. There are various options for controlling which data gets displayed and how it is arranged (sorting, justification, etc). By default it uses the style commonly used in database shells, but it also has predefined styles for Markdown, Org Mode, and HTML, among others.

For even more output format options, there is also pytablewriter, although I have no personal experience with it.