Hi all,
Wondered if anyone had rustled up anything along these lines - generally using Markdown as my editing workflow, being able to point at a sheet, then run a script to re-export the document as a (Multi)markdown table would be a real boon.
I started looking at the applescript for this and found it a little over my head. Has anyone got any useful pointers, excess energy and cognitive time, or suggestions about this?
Thanks!
The only rather obscure idea coming to my mind would be to convert the sheet to HTML, to upload the HTML file to a public website and to capture it from there as Markdown. But I don’t know whether this would actually return the desired result.
DEVONthink’s scripting dictionary has several commands to work with sheets and their content. E.g.,
set myCells to get cells of [whatever]
So you could grab the data from a source (making sure it is a sheet, first) and then reformat it to table form.
Or, instead of writing the reformatting yourself, incorporate open source code such as this into your script. (Although, this particular flavor is rather complex.)
Just for fun…
Select the sheet and run this script and it will generate a skeleton Markdown document with the data converted to a Markdown table. It also does a default sort, which is on the first field, or the first name in this case.
This is a VERY simple affair (no error checking involved, say to see if you have a Sheet selected even) and there is nothing but the default Markdown formatting. That’s for you to play about with…
tell application id "DNtp"
set md to {}
set tableLeader to {"---"}
repeat with thisRecord in (selection as list)
set {fName, plainText} to {name, plain text} of thisRecord
set headerCount to do shell script "echo " & (quoted form of (plainText as string)) & " | awk 'NR==1{print NF}'"
repeat with a from 1 to (headerCount)
copy "|---" to end of tableLeader
end repeat
copy return to end of tableLeader
set convertedText to do shell script "echo " & (quoted form of plainText) & " | sort | sed 's_ _\\|_g' | sed '1 a\\
" & (tableLeader as string) & "'"
end repeat
create record with {name:(fName & ".md"), type:markdown, content:convertedText} in current group
end tell
This is an amazing start, thank you! All I can see it needs is a pipe adding to the start and end of each line, and tabs to be converted to pipes (hoping there are no bare tab characters in the table cells!)
I was expecting a lot of unfamiliar applescript looping, so this cuts out a chunk of work!
I did a little butchery to substitute the |s in where needed, but I need a little more time to get the header in. I think basically making the output the header, then the header on a new line with non-pipe characters exchanged for dashes, then the main substituted content would export exactly the right thing. I confirmed this with a little manual editing after feeding through this adjusted script pasted below.
tell application id "DNtp"
set md to {}
set tableLeader to {"---"}
repeat with thisRecord in (selection as list)
set {fName, plainText} to {name, plain text} of thisRecord
-- plain text needs to be hacked together: tabs->|,add | at start of line, add | at end of line
set plainText to do shell script "echo " & (quoted form of (plainText as string)) & " | awk '{print \"|\"$0\"|\"}' | tr \"\t\" \"|\" "
set headerCount to do shell script "echo " & (quoted form of (plainText as string)) & " | awk 'NR==1{print NF}'"
repeat with a from 1 to (headerCount)
copy "|---" to end of tableLeader
end repeat
copy return to end of tableLeader
set convertedText to do shell script "echo " & (quoted form of plainText) & " | sort | sed 's_ _\\|_g' | sed '1 a\\
" & (tableLeader as string) & "'"
end repeat
create record with {name:(fName & ".md"), type:markdown, content:convertedText} in current group
end tell
Hopefully have a little more time next week to play around with it. Thanks again, Jim!
Starting and ending pipes aren’t a requirement of MultiMarkdown, so I didn’t include them. The converted sheet works as expected in DEVONthink. And you’re welcome.
Here FWIW, is a version composed from generic functions in JavaScript for Automation.
(This version is in ES6 JS, so compatible only with Sierra onwards. You can, however, get a (Yosemite onwards) ES5 version by pasting the source into the Babel JS Repl at babeljs.io/repl/ )
(() => {
'use strict';
// (++) :: [a] -> [a] -> [a]
const append = (xs, ys) => xs.concat(ys);
// comparing :: (a -> b) -> (a -> a -> Ordering)
const comparing = f =>
(x, y) => {
const
a = f(x),
b = f(y);
return a < b ? -1 : (a > b ? 1 : 0);
};
// concat :: [[a]] -> [a] | [String] -> String
const concat = xs =>
xs.length > 0 ? (() => {
const unit = typeof xs[0] === 'string' ? '' : [];
return unit.concat.apply(unit, xs);
})() : [];
// curry :: Function -> Function
const curry = (f, ...args) => {
const go = xs => xs.length >= f.length ? (f.apply(null, xs)) :
function () {
return go(xs.concat(Array.from(arguments)));
};
return go([].slice.call(args, 1));
};
// drop :: Int -> [a] -> [a]
// drop :: Int -> String -> String
const drop = (n, xs) => xs.slice(n);
// filter :: (a -> Bool) -> [a] -> [a]
const filter = (f, xs) => xs.filter(f);
// findIndex :: (a -> Bool) -> [a] -> Maybe Int
const findIndex = (p, xs) =>
xs.reduce((a, x, i) =>
a.nothing ? (
p(x) ? {
just: i,
nothing: false
} : a
) : a, {
nothing: true
});
// intercalate :: String -> [a] -> String
const intercalate = curry((s, xs) => xs.join(s));
// length :: [a] -> Int
const length = xs => xs.length;
// lines :: String -> [String]
const lines = s => s.split(/[\r\n]/);
// map :: (a -> b) -> [a] -> [b]
const map = (f, xs) => xs.map(f);
// maximumBy :: (a -> a -> Ordering) -> [a] -> a
const maximumBy = (f, xs) =>
xs.reduce((a, x) => a === undefined ? x : (
f(x, a) > 0 ? x : a
), undefined);
// replicate :: Int -> a -> [a]
const replicate = (n, x) =>
Array.from({
length: n
}, () => x);
// show :: Int -> a -> Indented String
// show :: a -> String
const show = (...x) =>
JSON.stringify.apply(
null, x.length > 1 ? [x[1], null, x[0]] : x
);
// splitOn :: a -> [a] -> [[a]]
// splitOn :: String -> String -> [String]
const splitOn = curry((needle, haystack) =>
typeof haystack === 'string' ? (
haystack.split(needle)
) : (function sp_(ndl, hay) {
const mbi = findIndex(x => ndl === x, hay);
return mbi.nothing ? (
[hay]
) : append(
[take(mbi.just, hay)],
sp_(ndl, drop(mbi.just + 1, hay))
);
})(needle, haystack));
// unconsMay :: [a] -> Maybe (a, [a])
const unconsMay = xs => xs.length > 0 ? {
just: [xs[0], xs.slice(1)],
nothing: false
} : {
nothing: true
};
// unlines :: [String] -> String
const unlines = xs => xs.join('\n');
// MMD TABLE -------------------------------------------------------------
// mmdTableFromTabbed :: String -> String
const mmdTableFromTabbed = s => {
const mbHeadTail = unconsMay(
map(splitOn('\t'), filter(x => length(x) > 0, lines(s)))
);
return mbHeadTail.nothing ? s : (() => {
const
ht = mbHeadTail.just,
t = ht[1];
return unlines(map(x => '|' + intercalate('\t|', x) + '\t|',
append(
[
ht[0],
replicate(
length(maximumBy(comparing(length), t)),
':--:'
)
],
t
)
));
})();
};
// CONVERSION OF A SELECTED DT SHEET TO MD -------------------------------
const
dt = Application('DEVONthink Pro'),
seln = dt.selection(),
mbRec = seln.length > 0 ? {
just: seln[0]
} : {
nothing: true
},
mbMD = mbRec.nothing ? (
mbRec
) : mbRec.just.type() === 'sheet' ? {
just: mmdTableFromTabbed(mbRec.just.richText.text())
} : mbRec;
return mbMD.nothing ? mbMD : (
dt.createRecordWith({
name: mbRec.just.name() + '.md',
type: 'markdown',
content: mbMD.just
}, { in: dt.currentGroup()
}),
mbMD.just
);
})();
Or for a pretty-printing version (spaces rather than tabs):
Dear heck, that’s astounding, houthakker!
A lot to learn in there. I suspect JS is the route to take these days for desktop automation, as it is with everything else!
I would disagree with this. Part of the beauty of AppleScript is its human-readability and ease of entry for new scripters. AppleScript on its own is far more capable than many people give it credit for.
You use the tools (1) you feel comfortable with and (2) get the job done. We can all expand our toolboxes, if we’re so inclined, but AppleScript presents a more immediate reward for effort for those without CS degrees. 
PS: This is not a competition about AS vs JS. houthakker’s does what it does and does it well. Mine was scratched together in 15 minutes as a proof-of-concept and to share an approach to the problem: instead of looking at it as “a file”, look at the underlying text the file is made up of.
I am personally quite lazy about these things in the sense that I throw them together like a child’s Lego structures from very familiar and general prefab units (generic functions based on the Haskell prelude) which I just paste in.
These days I probably do tend to reach more for the JavaScript than the AppleScript versions of these throw-away building blocks, but I still maintain the same set of generic functions for AppleScript. (A Quiver pasting macro lets me quickly choose and paste the same generic functions for either language).
So while the JS staples (saving me from having to write loops all the time) might be:
// filter :: (a -> Bool) -> [a] -> [a]
const filter = (f, xs) => xs.filter(f);
// foldl :: (b -> a -> b) -> b -> [a] -> b
const foldl = (f, a, xs) => xs.reduce(f, a);
// foldr (a -> b -> b) -> b -> [a] -> b
const foldr = (f, a, xs) => xs.reduceRight(flip(f), a);
// map :: (a -> b) -> [a] -> [b]
const map = (f, xs) => xs.map(f);
// flip :: (a -> b -> c) -> b -> a -> c
const flip = f => (a, b) => f.apply(null, [b, a]);
I am equally happy to reach for the same things in AppleScript:
-- filter :: (a -> Bool) -> [a] -> [a]
on filter(f, xs)
tell mReturn(f)
set lst to {}
set lng to length of xs
repeat with i from 1 to lng
set v to item i of xs
if |λ|(v, i, xs) then set end of lst to v
end repeat
return lst
end tell
end filter
-- foldl :: (a -> b -> a) -> a -> [b] -> a
on foldl(f, startValue, xs)
tell mReturn(f)
set v to startValue
set lng to length of xs
repeat with i from 1 to lng
set v to |λ|(v, item i of xs, i, xs)
end repeat
return v
end tell
end foldl
-- foldr :: (b -> a -> a) -> a -> [b] -> a
on foldr(f, startValue, xs)
tell mReturn(f)
set v to startValue
set lng to length of xs
repeat with i from lng to 1 by -1
set v to |λ|(item i of xs, v, i, xs)
end repeat
return v
end tell
end foldr
-- map :: (a -> b) -> [a] -> [b]
on map(f, xs)
tell mReturn(f)
set lng to length of xs
set lst to {}
repeat with i from 1 to lng
set end of lst to |λ|(item i of xs, i, xs)
end repeat
return lst
end tell
end map
-- Lift 2nd class handler function into 1st class script wrapper
-- mReturn :: Handler -> Script
on mReturn(f)
if class of f is script then
f
else
script
property |λ| : f
end script
end if
end mReturn
The only area in which the shelf-life of AppleScript is getting a little bit past expiry is in the growth of iOS, where apps like 1Writer and Drafts, and now omniGraffle 3, already let us script in JavaScript, but seem unlikely to ever let us write solutions in AppleScript.
It would be nice if AS was native to iOS too. It’s such an easy and comfortable language to learn (and teach).
I agree you have powerful functions but I dare say functional programming concepts aren’t for the average non-CS person. Indeed they’re confusing for some of them too! Cool stuff but require a LOT of prerequisite knowledge to understand, hence the barrier to entry is VERY high, IMHO.
Probably a discussion for another forum, but perhaps we should be wary of confusing familiarity with simplicity ?
A name space full of mutating variables quickly creates more complexity and puzzlement than most people can really juggle successfully, and it’s far from clear to me that that a loop with incrementing variables and an exit condition (often mutating state elsewhere) really provides as simple a mental model as a map.
I would guess that the real issue may be more historical and cultural than cognitive – many of us grew up on looping branching and sequencing, so they can often seem more familiar, at least to our generation, even if they do lead more quickly to complexity and tangle.
But the toolbox is big – plenty of room for everyone to make their own choices and experiments.
PS Some bits of AppleScript are really quite hard and uncomfortable, of course – records in particular – very hard to find out what fields a record has got in AppleScript - much easier in other languages …
For some basic things like sorting and regex one can easily end up trying to make sense of the bridge to Objective C …
Here is the basic sort I use in AppleScript, for example:
use framework "Foundation" -- for basic NSArray sort
-- sort :: [a] -> [a]
on sort(xs)
((current application's NSArray's arrayWithArray:xs)'s ¬
sortedArrayUsingSelector:"compare:") as list
end sort
and for changing case:
use Framework "Foundation"
-- toLower :: String -> String
on toLower(str)
set ca to current application
((ca's NSString's stringWithString:(str))'s ¬
lowercaseStringWithLocale:(ca's NSLocale's currentLocale())) as text
end toLower
-- toTitle :: String -> String
on toTitle(str)
set ca to current application
((ca's NSString's stringWithString:(str))'s ¬
capitalizedStringWithLocale:(ca's NSLocale's currentLocale())) as text
end toTitle
-- toUpper :: String -> String
on toUpper(str)
set ca to current application
((ca's NSString's stringWithString:(str))'s ¬
uppercaseStringWithLocale:(ca's NSLocale's currentLocale())) as text
end toUpper
By the time we get to sorting records:
-- List of {strKey, blnAscending} pairs -> list of records -> sorted list of records
-- sortByComparing :: [(String, Bool)] -> [Records] -> [Records]
on sortByComparing(keyDirections, xs)
set ca to current application
script recDict
on |λ|(x)
ca's NSDictionary's dictionaryWithDictionary:x
end |λ|
end script
set dcts to map(recDict, xs)
script asDescriptor
on |λ|(kd)
set {k, d} to kd
ca's NSSortDescriptor's sortDescriptorWithKey:k ascending:d selector:dcts
end |λ|
end script
((ca's NSArray's arrayWithArray:dcts)'s ¬
sortedArrayUsingDescriptors:map(asDescriptor, keyDirections)) as list
end sortByComparing
Probably fair to say that most languages are easier than this …
Here’s another script, this one doesn’t require shell scripts:
tell application id "DNtp"
repeat with thisRecord in (selection as list)
if type of thisRecord is sheet then
set {theRows, theCols} to {cells of thisRecord, columns of thisRecord}
set {theHeader1, theHeader2} to {"", ""}
repeat with theCol in theCols
set theHeader1 to theHeader1 & "|" & theCol & " "
set theHeader2 to theHeader2 & "|:--: "
end repeat
set theMD to theHeader1 & "|" & return & theHeader2 & "|" & return
repeat with theRow in theRows
set theLine to ""
repeat with theCell in theRow
set theLine to theLine & "|" & theCell & " "
end repeat
set theMD to theMD & theLine & "|" & return
end repeat
create record with {name:(name of thisRecord & ".md"), type:markdown, content:theMD} in (parent 1 of thisRecord)
end if
end repeat
end tell
Or in JavaScript for Automation (ES6 – Sierra onwards)
(if we bypass pretty-printing)
(() => {
const dt = Application("DEVONthink Pro");
dt.selection()
.forEach(rec => {
if (rec.type() === 'sheet') {
const
[rows, cols] = [rec.cells(), rec.columns()],
rules = cols.map(() => ':--:');
dt.createRecordWith({
name: rec.name() + '.md',
type: 'markdown',
content: [cols].concat([rules])
.concat(rows)
.map(x => '|' + x.join(' | ') + '|')
.join('\n')
}, { in: dt.currentGroup()
})
}
});
})();
Which reminds me, on the choice of which language to reach for when a task arises, that this morning I wanted a function that would give a true/false value in exchange for a Regex pattern and a string:
regexTest :: RegexPattern -> String -> Bool
My choices were either AppleScript:
use framework "Foundation" -- "OS X" Yosemite onwards, for NSRegularExpression
-- regexTest :: RegexPattern -> String -> Bool
on regexTest(strRegex, str)
set ca to current application
set oString to ca's NSString's stringWithString:str
((ca's NSRegularExpression's regularExpressionWithPattern:strRegex ¬
options:((ca's NSRegularExpressionAnchorsMatchLines as integer)) ¬
|error|:(missing value))'s firstMatchInString:oString options:0 ¬
range:{location:0, |length|:oString's |length|()}) is not missing value
end regexTest
regexTest("\\s", tab)
-- result: true
or JavaScript for Automation:
// regexTest :: RegexPattern -> String -> Bool
function regexTest (rgx, s) {
return rgx.test(s);
}
regexTest(/\s/, '\t');
// result: true
AppleScript seemed a bit hard for a person of my advancing years …
That’s not vanilla AppleScript and not accessible (or understandable) by many people who use AppleScript.
Here’s my first reaction, written in the shell but adapted as a simple handler
on test(re, str)
do shell script "[[ " & (quoted form of str) & " =~ " & re & " ]] && echo 'true' || echo 'false'"
end test
test("\\(*[0-9]{3}\\)*[\\ -.]*[0-9.-]{8}", "(555) 555.1212")
--> true
Again, not necessarily accessible for some Scripters, but responding to the choices we make for tools to use.
Well, quite 
A beginner might be less confused by the js
(/\s/).test('\t')
There’s clearly no good reason for those who have invested years in AppleScript to suddenly stop using it, but equally, we should probably pause before recommending it now to beginners.
It made more sense a quarter of a century ago, in 1993, when iOS was not imagined, and url-encoding, sorting, regex patterns and easily handled records were perhaps less taken for granted, but I’m not sure it would be all that sensible a choice now, particularly as people will often need to learn a little JS anyway. I actually remember finding the vaunted ‘English-like’ syntax of AS obstructively opaque when I first encountered it, c. 2001.
But as I said, the toolkit is large, and the task is more important than the particular instrument that we happen to reach for.
My 2 cents:
AppleScript is absolutely perfect for workflows (e.g. sending data from one application to another, capturing or filing data etc.) or everything based on the application’s script suite. And that’s what most AppleScripters probably do, especially as the scripts are human readable.
But it’s definitely very poor for writing your own algorithms (e.g. sorting but even finding & replacing strings ) as it’s not only cumbersome but also very slow.