The Omni Group Forums - View Single Post - Script: listing tasks by their creation date

RobTrew

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

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

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: Created, Modified, Name.

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