Script: listing tasks by their creation date
 

I needed to check the creation date of some tasks this morning.

You can do this with individual tasks through the OF menu - [I]Inspectors > Action > Added[/I].

What I wanted, however, was to select a range of tasks, and then see a list of them sorted in the order of their creation.

This script puts a tab-delimited list into the Clipboard. It list the selected tasks, sorted by the date on which they were added to the OmniFocus database.

Two date fields and a text field: [I]Created[/I], [I]Modified[/I], [I]Name[/I].

It can be pasted into something like Excel.

[CODE]property pDateFormat : "%m/%d/%Y %H:%M"
property pDelim : "~|~"

-- OF SQL TIME BEGINS AT MIDNIGHT AT THE START OF 2001-01-01
property pDateZero : "strftime('%s','2001-01-01')"

on run
tell application id "OFOC"
tell content of front document window of front document
set lstID to id of selected trees where its class is not item and its class is not folder
set lngID to count of lstID
if lngID < 1 then return
set strCondn to "persistentIdentifier = \"" & first item of lstID & "\""
if lngID > 1 then
repeat with i from 2 to lngID
set strCondn to strCondn & " or persistentIdentifier = \"" & item i of lstID & "\""
end repeat
end if
end tell
end tell

set strCmd to "sqlite3 -separator " & quoted form of pDelim & " ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
SELECT " & OFDate("dateAdded") & ", " & OFDate("dateModified") & " , name
FROM task where " & strCondn & "
ORDER by dateAdded")

set lstTasks to paragraphs of (do shell script strCmd)
set {dlm, my text item delimiters} to {my text item delimiters, pDelim}
set beginning of lstTasks to {"Created", "Modified", "Action"}

set lngLines to lngID + 1

repeat with i from 1 to lngLines
set item i of lstTasks to text items of item i of lstTasks
end repeat

set text item delimiters to tab
repeat with i from 1 to lngLines
set item i of lstTasks to (item i of lstTasks) as text
end repeat

set my text item delimiters to return
set strReport to lstTasks as string
set my text item delimiters to dlm

display dialog ("Tab-delimited list of " & lngID as string) & " selected action(s),
sorted by date added to the OmniFocus database,
has been placed in Clipboard.

" & strReport buttons {"OK"} with title "Selected actions sorted by date added"
set the clipboard to strReport
end run


on OFDate(strFieldName)
"strftime('" & pDateFormat & "', " & strFieldName & " + " & pDateZero & ", 'unixepoch')"
end OFDate
[/CODE]