Under the table
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 |
back into 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 the command 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. ↩
