October 16, 2021 at 11:11 AM by Dr. Drang
In my last post, I mentioned an AppleScript I wrote some time ago that turns a table in Numbers into a MultiMarkdown1 table. A couple of days later, jacobio asked a question in the Mac Power Users forum, that led me to posting the script there. After looking it over—and seeing jacobio’s independently developed script—I made a few small changes to the script and decided to talk about it here.
The premise for the script is that I’m writing a blog post in BBEdit, and I want to include a table. Writing a Markdown table isn’t especially hard, but doing so involves lots of typing of formatting characters, the pipes and colons that tell the Markdown processor how to make the table but aren’t part of the table’s content. It’s much easier to write a table in a spreadsheet or some other dedicated table-writing tool and then invoke a script that converts it to Markdown. And if the table I want to insert is already in a spreadsheet, using a conversion script is even faster.
(Let me pause here and mention TableFlip, which Rosemary Orchard talked about in that same thread. I haven’t used it, but it sounds great, especially if most of the tables you deal with have to be written from scratch. It’s killer feature is how it ties the Markdown table in your document to the spreadsheet-like form in TableFlip itself. Changes you make to the table in TableFlip are conveyed automatically to the Markdown document. The downside to using TableFlip is that you don’t have a version of the table in a spreadsheet, which is often very useful.)
I’ve created a BBEdit package of scripts and text filters that help me write blog posts. The script is in that package and has a few lines that are package-specific. But most of it could be used as a standalone conversion script. Here’s the code:
applescript: 1: -- Get the path to the Normalize Table text filter in the package. 2: tell application "Finder" to set cPath to container of container of container of (path to me) as text 3: set normalize to quoted form of (POSIX path of cPath & "Text Filters/06)Blogging/02)Normalize Table.py") 4: 5: -- Construct a MultiMarkdown table from the top Numbers table 6: tell application "Numbers" 7: tell table 1 of sheet 1 of document 1 8: set tbl to "" 9: set h to header row count 10: set c to column count 11: set r to row count 12: 13: -- header lines 14: repeat with i from 1 to h 15: set tbl to tbl & "|" 16: repeat with j from 1 to c 17: set val to formatted value of cell j of row i 18: if val = missing value then 19: set val to "" 20: end if 21: set tbl to tbl & " " & val & " |" 22: end repeat 23: set tbl to tbl & linefeed 24: end repeat 25: 26: -- formatting line 27: set tbl to tbl & "|" 28: repeat with j from 1 to c 29: set a to alignment of cell j of row h 30: if a = right then 31: set tbl to tbl & "---:|" 32: else if a = center then 33: set tbl to tbl & ":--:|" 34: else 35: set tbl to tbl & ":---|" 36: end if 37: end repeat 38: set tbl to tbl & linefeed 39: 40: -- body lines 41: repeat with i from h + 1 to r 42: set tbl to tbl & "|" 43: repeat with j from 1 to c 44: set val to formatted value of cell j of row i 45: if val = missing value then 46: set val to "" 47: end if 48: set tbl to tbl & " " & val & " |" 49: end repeat 50: set tbl to tbl & linefeed 51: end repeat 52: 53: end tell -- table 54: end tell -- Numbers 55: 56: -- Normalize the table 57: set the clipboard to tbl 58: set tbl to do shell script ("pbpaste | " & normalize) 59: 60: tell application "BBEdit" to set selection to tbl
Lines 1–3 are some of the package-specific stuff I talked about before. Let’s skip over that for now and get to the meat of the script, which starts on Line 6 with a long
tell block that controls Numbers.
The script assumes that the table of interest is the first table of the first sheet of the frontmost Numbers document. Line 7 starts a
tell block that speaks to that table. Line 8 initializes the
tbl variable, which is where we’re going to put all the text of the Markdown table. Lines 9–11 get the sizes of the header and the table as a whole.
A word about headers is in order. Some Markdown implementations allow for only one header line, but others—including MultiMarkdown—allow for any number of header lines. This script accommodates the more general case. In Numbers, the header lines (rows) are set in the Table section of the Format side panel. There’s a pop-up button that lets you set the number of header rows, and this is typically reflected in the number of shaded rows at the top of the table.
Lines 14–24 use nested loops to create the header lines of the Markdown table. The outer loop, which starts on Line 14, goes through the header rows of the table. The inner loop, which starts on Line 16, goes through the columns of each row. As is often the case, AppleScript’s idiosyncrasies are more difficult to deal with than the logic of the process. To make our Markdown table show what the Numbers table shows, we need to ask for the
formatted value of each cell. Also, instead of asking for
column j of row i in Line 17, we have to ask for
cell j of row i. And finally, if a cell is empty, AppleScript will set
val to the literal text “missing value” in Line 17, so we need Lines 18–20 to correct that to an empty string before appending
val and the appropriate space and pipe (
|) characters to
tbl in Line 21.
Lines 27–38 use the alignment of the cells in the last header row (
row h) to create the formatting line in the Markdown table. I think the logic here is pretty easy to understand. If the alignment of the cell is
right, we add a short string of hyphens with a colon at the right end; if the alignment is
left, we add a short string of hyphens with a colon at each end; for any other alignment, we treat the column as left-aligned and add a short string of hyphens with a colon at the left end. “Any other alignment” includes
auto align; this is the default and is presented as left-aligned for text, which is what header cells usually contain.
Lines 41–51 loop through the body of the table. Apart from the limits on the
repeat command in Line 41, this is the same code as in Lines 14–24. I probably should factor this out into a function.
When the code exits the
tell block on Line 54,
tbl contains a valid but ugly Markdown table. Something like this:
| | County | Yes | No | Margin | CMargin | |:---|:---|---:|---:|---:|---:| | 1 | Kern | 126,999 | 78,477 | 48,522 | 48,522 | | 2 | Placer | 114,643 | 85,302 | 29,341 | 77,863 | | 3 | Shasta | 49,141 | 21,655 | 27,486 | 105,349 | | 4 | Tulare | 64,372 | 41,009 | 23,363 | 128,712 | | 5 | El Dorado | 58,393 | 39,907 | 18,486 | 147,198 | | 6 | Stanislaus | 82,911 | 69,247 | 13,664 | 160,862 | | 7 | Tehama | 15,958 | 6,186 | 9,772 | 170,634 | | 8 | Madera | 25,638 | 16,233 | 9,405 | 180,039 | | 9 | Sutter | 20,458 | 11,593 | 8,865 | 188,904 | | 10 | Kings | 19,710 | 11,242 | 8,468 | 197,372 |
This can be made nicer looking by running it through my “Normalize Table” text filter, which is also part of my Blogging package and which I’ve written about before. Lines 2–3 figure out the path to the text filter, and Lines 57–58 apply it to the contents of
tbl, putting the nicely-formatted result, e.g.,
| | County | Yes | No | Margin | CMargin | |---:|:-----------|--------:|-------:|-------:|--------:| | 1 | Kern | 126,999 | 78,477 | 48,522 | 48,522 | | 2 | Placer | 114,643 | 85,302 | 29,341 | 77,863 | | 3 | Shasta | 49,141 | 21,655 | 27,486 | 105,349 | | 4 | Tulare | 64,372 | 41,009 | 23,363 | 128,712 | | 5 | El Dorado | 58,393 | 39,907 | 18,486 | 147,198 | | 6 | Stanislaus | 82,911 | 69,247 | 13,664 | 160,862 | | 7 | Tehama | 15,958 | 6,186 | 9,772 | 170,634 | | 8 | Madera | 25,638 | 16,233 | 9,405 | 180,039 | | 9 | Sutter | 20,458 | 11,593 | 8,865 | 188,904 | | 10 | Kings | 19,710 | 11,242 | 8,468 | 197,372 |
tbl. Note that the code uses the clipboard and
pbpaste to send the text through the “Normalize Table” filter. I could avoid the clipboard by setting up a shell command that uses a here-document, but that code is messy and easy to screw up (I speak from experience). Because I use Keyboard Maestro’s clipboard history manager, I can always get back to what was on the clipboard before I ran this script.
Finally, Line 60 puts the text of
tbl into my current BBEdit document. If there’s a selection, it replaces the selection; if not, it inserts the text at the cursor.
If you want to adapt this script for use with any text editor, delete Lines 60 and 1–3 and change Line 58 to
set tbl to do shell script "pbpaste | /path/to/normalize | pbcopy"
For this last part to work, you’ll need to have a normalization script and replace
/path/to/normalize with the path to it. When you get it working, you’ll have a script that will put the formatted Markdown table on your clipboard, ready to be pasted anywhere.
This script is no speed demon. AppleScript is very deliberate as it walks through the table, so there’s always a pause between choosing thecommand and seeing the table appear in BBEdit. If you adapt it to put the table on your clipboard, you might want to add a command at the end to play a sound when the clipboard is ready for pasting.
From now on, I’m just going to call these Markdown tables. Gruber’s Markdown doesn’t include tables, but pretty much every Markdown implementation has them—either built in or as an option. I bet most Markdown users don’t even know that tables are an extension. ↩