Script: custom OmniFocus action lists on the desktop, using Geektool
 

[IMG]http://farm6.static.flickr.com/5050/5277441501_9a079b0e0e_o.jpg[/IMG]

OVERVIEW[INDENT]It can be useful to glance at a key action list on the desktop, without having to fire up OmniFocus and choose a perspective. (A [I]due now[/I] list perhaps, or [I]due soon[/I], or a context list, or simply the inbox, as a spur to getting it clear).

This post splits off from [URL="http://forums.omnigroup.com/showthread.php?t=13507"]another thread[/URL] which began by discussing ways of displaying a desktop banner of the current action, and drifted off topic towards Geektool.

I posted a couple of rather rudimentary scripts to that thread (for getting particular - unformatted and unwrapped - OF lists into a Geektool window), but here is a much more general script which enables you to attach a range of customized list specifiers to one or more Geektool 'windows' (shell geeklets) at the same time.
[/INDENT]
FUNCTION
[INDENT]Displays one or more custom action lists (or project lists) on your desktop, even when OmniFocus isn't running, using [URL="http://projects.tynsoe.org/en/geektool/"]GeekTool[/URL].

The lists can be wrapped to fit the geeklet window, shifted to upper case, or generally modified by piping them through shell commands.[/INDENT]
LIST DEFINITIONS[INDENT]Some simple queries are pre-defined by the script,[INDENT]in inbox
due soon
due now
next action
in context <Context Name>[INDENT]in context "Phone"
in context "Library"[/INDENT]selected actions

[[I]selected actions[/I] does, of course, require OmniFocus to be running - it lists the actions currently selected in the OF GUI][/INDENT]and these can all be modified, for example to adjust sort order,
[INDENT]in inbox order by dateAdded
in inbox order by dateAdded DESC
in inbox order by dateDue, dateAdded
due now order by effectivedateDue
next action order by effectiveDateDue
in context "Library" order by task.name[/INDENT][/INDENT][INDENT]
and/or further restrict contents.
[INDENT]next action and flagged=1
next action and flagged=0 order by effectiveDateDue[/INDENT][/INDENT][INDENT]Their format can be adjusted by piping them through further shell commands, like fold and tr.[INDENT]Wrapping with 'fold'
[INDENT]inbox order by dateDue, dateAdded | fold -s -w 50
in context "Phone" | fold -s -w 30[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Shifting to upper case with 'tr'
[INDENT]due now order by effectiveDateDue | tr "[:lower:]" "[:upper:]" | fold -s -w 40
active selections order by name | tr "[:lower:]" "[:upper:]" | fold -s -w 40[/INDENT]
(or anything else which can be achieved by piping to another shell command).[/INDENT][/INDENT][INDENT]More sophisticated queries can be devised in Sqlite SQL.[INDENT]Project / Projects is a pre-defined alias,
and a set of relative date expressions can be used in the SQL clauses.[INDENT]yesterday, today, now, soon
<jan> ... <dec> (Interpreted as references to midnight at the start of the first day of the next (or current) month of that name).

today +1w, now +2d etc.[/INDENT][/INDENT][INDENT][/INDENT][/INDENT]
PRECEDING THE LIST WITH A HEADER[INDENT]The default list header is [I]$Q ($N) & return[/I] where $Q is a variable representing the query, and $N is the number of matches.
In addition, a custom header can now be specified for a List/Geeklet by following the query with the switch [B]--header=[/B] followed by an unquoted string, optionally including $Q or $N
e.g. [LIST][*][I]Due Soon --header=$Q [$N matches]: | fold -s -w 40[/I][*][I]task where flagged=1 order by projectinfo, dateDue --header=Flagged ($N)[B]\n[/B] | fold -s -w 40[/I][*]or simply (to display no header at all) [I]Active Selections --header= | fold -s -w 40[/I][/LIST]
Note that additional blank lines in (or at the end of) the header can be specified as [B]\n[/B]
[/INDENT]INSTALLATION[LIST=1][*]Choose a home for this script (if you put it in the OmniFocus script directory it will appear in the OF script menu, and can be installed on the OF toolbar). (~/Library/Scripts/Applications/Omnifocus )[*]Install Geektool [url]http://projects.tynsoe.org/en/geektool/[/url][*]Drag one or more shell windows from the GeekTool preference pane onto your desktop, select each of your shell windows in turn, and use the properties dialog to assign it: [LIST][*]A name (at the top of the properties dialog - it says "optional" but this script needs each of your Geeklets to have a name).[*]The colours and font that you want (not urgent, these can be changed later),[*]A time out period of 5 seconds, and refresh interval of 10+ seconds. (lists that won't change rapidly can obviously be refreshed less frequently).

(Note that there is no need to assign a command - that will be done automatically by this script.)[/LIST][*]Launch this script.[/LIST]
USAGE[LIST=1][*]Run this script,[*]choose a shell geeklet from the list that appears,[*]and respond the prompt by typing the name of a simple or modified query.[/LIST][INDENT]It may be helpful to begin your experimentation by simply accepting the default query which is offered.
If an error message appears in your geeklet, run this script again and carefully check the spelling and syntax of your list description.[/INDENT]

I have updated the code above to ver 0.3 which simplifies the customization or suppression of custom headers for each list/geeklet.

(See the new section (above) under the heading "PRECEDING THE LIST WITH A HEADER")

I have also assembled some sample geeklets which can be installed directly by double-clicking them (as long as GeekTool as been installed on your system, and you have copied the theGeekOFSQL.scpt to your OmniFocus script folder).

(See the geeklets zip attached below - note that these require installation of the script in Post 1 above)

OVERVIEW OF THE GEEKLETS

These are pre-fabricated samples of the kind of Geek Tools shell geeklet that can be be run with the GeekOFSQL.scpt applescript.

They are not guaranteed to be useful, and are intended simply to provide a quick way of trying out the script.

Each of them displays a different list from your OmniFocus data. With the exception of the Active Selections geeklet, they do not need Omnifocus to be running.

The colors and font sizes etc may need to be adjusted for your desktop.


INSTALLATION

1. Install GeekTool from [url]http://projects.tynsoe.org/en/geektool/[/url]

2. Copy the script GeekOFSQL.scpt to the OmniFocus scripts folder, so that its path is:

~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt

(where ~ represents your home folder).

[Note that this is an important step, as these geeklets depend on finding the script in that location]

3. Install any or all of the geeklet (.glet) files by double-clicking them.


REFERENCE

For reference, these Geeklets run the following commands. (The simplest way to attach or modify Geeklet commands for OmniFocus lists is, however, to run the GeekOFSQL.scpt in the Applescript script editor, and then choose the name of a Geeklet from the drop-down list).

The script can be found in the first post in this thread.

Active Selections:
osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'Active Selections --hdr=\n' | fold -s -w 60

Flagged:
osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'task where flagged=1 order by projectinfo, dateDue --header=Flagged ($N)\n' | fold -s -w 40

Next Action:
osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'Next Action' | fold -s -w 40

Due Soon:
osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'Due Soon order by effectiveDateDue' | fold -s -w 45

Due Now:
osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'Due Now order by effectiveDateDue' | fold -s -w 45

Inbox:
osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'In Inbox order by dateAdded --hdr=Inbox ($N)\n' | fold -s -w 40


[COLOR="White"]--[/COLOR]

Updated the script and the sample geeklets to format longer lines as hanging indents.

(Simply by piping them through a few replacements, e.g.
[INDENT][I]osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'Next Action' | tr '\r' '\n' | fold -s -w 40 | perl -pe 's/(^[^•]+)/\t\1/g' | perl -pe 's/•/-/g' [/I][/INDENT]
I'm sure there is a more elegant way :-)

[COLOR="White"]--[/COLOR]

Rob, where could I find the OmniFocus SQlite specification? Is there a complete reference available?

Thank you.

No formal documentation that I know of, but you can put a list of the fields and their types into the clipboard with code like:

[CODE]property pstrDBPath : "~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2"
property plstResults : {}

on run
set text item delimiters to return
set str to ""
set lstTables to ({"Task", "ProjectInfo", "Context", "Folder"})
repeat with oTable in lstTables
set strSQL to "PRAGMA TABLE_INFO(" & oTable & ");"
RunSQL(strSQL)
set str to str & oTable & return & return & (plstResults as string) & return & return & return
end repeat
set text item delimiters to space

set the clipboard to str
end run

on RunSQL(strSQL)
set strCmd to "sqlite3 " & pstrDBPath & space & quoted form of strSQL

set plstResults to (paragraphs of (do shell script strCmd))
end RunSQL
[/CODE]

(Probably important to bear in mind that the schema can change, unannounced, between builds, and that reading the cache has been described on this forum as "at the deep end of not officially supported").

Warm thanks, Rob. In the meantime, and considering my lack of your SQL chops, I resorted to the open-source "SQLite Database Browser 2.0," which also facilitates inductive reasoning about the function of each key.

Matching Due Soon
 

Hi Rob,

I love your Geektool scripts.

I have a question. How can I modify the script so that the "due soon" in Geektool matches the "due soon" in Omnifocus? I have "due soon" set to 2 days in Omnifocus but it seems that the "geektool" due soon is only picking up tomorrow's tasks and missing the tomorrow's tomorrow's tasks?

Thanks,

Steve

Good question, and I have now updated the script.

[URL="http://forums.omnigroup.com/showpost.php?p=90549&postcount=1"]Ver .09[/URL] (post 1 in this thread, above) now automatically gets the current Due Soon interval setting, as in
[INDENT][B]OF > Preferences > Data > "Due Soon" is in the next N days[/B][/INDENT]
(OF does not need to be running).

[COLOR="White"]--[/COLOR]

Hi Rob,

I updated the Script. This is the behavior that I am seeing with Due Soon marked to 2 days in OF Preferences:

1) [U]Your Due Now[/U] - Only lists items that were due yesterday or earlier, which in Omnifocus is marked as Red.

2) [U]Your Due Soon[/U] - Lists both today's items, which in Omnifocus is marked as Red, and tomorrow's items, which in Omnifocus is market as Orange. However, it misses listing the items due the day after tomorrow, which in OF are also marked in Orange.

I think there may a difference in the logic between your script and OF badges. OF considers today and earlier to be red and X number of days as per OF preference starting with tomorrow to be orange.

Am I doing something wrong?

[QUOTE=sterlizzi;90929]with Due Soon marked to 2 days in OF Preferences ...

2) [U]Your Due Soon[/U] - Lists both today's items, which in Omnifocus is marked as Red, and tomorrow's items, which in Omnifocus is market as Orange. However, it misses listing the items due the day after tomorrow, which in OF are also marked in Orange.
[/QUOTE]

The difference may be in our OF settings - with:
[INDENT][I]Preferences > Data > "Due Soon" is in the next 2 days[/I][/INDENT]and [INDENT][I]Prefs > Data > Default Time for Due dates[/I] at 17:00[/INDENT]
Today and Tomorrow are (this morning) orange on my system, and day after tomorrow is unmarked (black).
(I would expect the colouring to change at 17:00 this evening).

(Script ver 1.0, OF 1.8.2)

[IMG]http://farm6.static.flickr.com/5282/5299118085_7a407b2574_o.jpg[/IMG]

(Where Dec 30 is day after tomorrow)

My understanding of Orange and Red in OF is:

[Orange]-> ('Due Soon' in the script)
[INDENT][I]tasks where (completed is false) and [B](due date > now) and (due date < soon)[/B] and (((status of its containing project is active) and (effectively hidden of folder of its containing project is false or folder of its containing project is missing value)) or (in inbox is true))[/I][/INDENT]
[Red]-> ("Due Now" in the script)
[INDENT][I]tasks where (completed is false) and [B](due date < now)[/B] and (((status of its containing project is active) and (effectively hidden of folder of its containing project is false or folder of its containing project is missing value)) or (in inbox is true))[/I][/INDENT]
(The Sqlite SQL clauses in the script aim to match this).

There may be some subtlety here that is eluding me. Do you have a different Default Time setting, for example ?
At what time of day does there seem to be a mismatch ?

[COLOR="White"]--[/COLOR]

My OF Preferences
 

Rob,

My current preferences settings are:
[INDENT]"Due Soon" is in the next 2 days

Default Time For Due Dates: 5:00PM[/INDENT]
I will check over the next day and see what happens.

Anyhow, I love the Geeklets. Awesome work.


Steve

:-) I'm glad you find them useful.

Given that our settings look the same, if you did find that you were getting a mismatch, attention might focus next on the data. (Are non-default due times involved, for example).

(With my data the geeklet lists do seem to be reliably matching the red and orange badging in OF).

Bravo!

[QUOTE=RobTrew;90786]No formal documentation that I know of, but you can put a list of the fields and their types into the clipboard with code like:

[CODE]property pstrDBPath : "~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2"
property plstResults : {}

on run
set text item delimiters to return
set str to ""
set lstTables to ({"Task", "ProjectInfo", "Context", "Folder"})
repeat with oTable in lstTables
set strSQL to "PRAGMA TABLE_INFO(" & oTable & ");"
RunSQL(strSQL)
set str to str & oTable & return & return & (plstResults as string) & return & return & return
end repeat
set text item delimiters to space

set the clipboard to str
end run

on RunSQL(strSQL)
set strCmd to "sqlite3 " & pstrDBPath & space & quoted form of strSQL

set plstResults to (paragraphs of (do shell script strCmd))
end RunSQL
[/CODE]

(Probably important to bear in mind that the schema can change, unannounced, between builds, and that reading the cache has been described on this forum as "at the deep end of not officially supported").[/QUOTE]

The other thing which may be useful to understand is the relationship between Applescript dates and the numeric datestamp strings in the cache. (Representing dates as a count of seconds since midnight at the start of 2001).

Here is a simple conversion function:

[CODE]property pdteBase : missing value

on SQL2ASDate(strDateStamp)
try
set intDate to (strDateStamp as integer)
on error
return missing value
end try
if pdteBase is missing value then
set pdteBase to current date
tell pdteBase
set {its year, its month, its day, its time} to {2001, 1, 1, 0}
end tell
end if
(pdteBase + intDate) as string
end SQL2ASDate

on AS2SQLDate(strDate)
try
set varDate to date strDate
on error
return "null"
end try

if pdteBase is missing value then
set pdteBase to current date
tell pdteBase
set {its year, its month, its day, its time} to {2001, 1, 1, 0}
end tell
end if
(varDate - pdteBase) as string
end AS2SQLDate[/CODE]

and if you want to to interpret these OmniFocus dates (dateStamp strings giving a number of seconds since midnight at the start of 1 Jan 2001) without resorting to Applescript date functions, you can keep to SQL with expressions like:

[CODE]select (strftime('%s','now') - strftime('%s','2001-01-01'));[/CODE]

for Now or Current Date,
or:

[CODE]select name, datetime(dateDue + strftime('%s','2001-01-01'), 'unixepoch') from task where dateDue is not null;[/CODE]

to convert a date field like dateDue to a human-readable string.

This could be really useful for other things except it appears to be saved as "Run Only". Was that intentional? It looks like people were able to view and edit earlier versions.

You should be able to download the script, open up Applescript Editor (or Script Debugger, if you have it), and have it open the script (or select the script and right-click to bring up the contextual menu, then choose Open with->Applescript Editor). I just did it with version 1.2 of the script, linked from the very first post in this thread.

It's conceivable that earlier versions of OS X might have difficulty reading a .scpt file created under 10.6

I have now supplemented the existing .scpt with a plain text (.applescript) version.

(See the post at the start of this thread).

Interesting. Both fail for me:

AppleScript Editor 2.3 (118) "Open Unable to read the file because the script is not editable (it was saved as run-only)."

Script Debugger 4.5: "The document "GeekOFSQL.scpt" could not be opened.

Other scripts downloaded from the forums work well for me (including a random one I tried just now from the 'Copy last sent message to Clippings' thread), and I consider myself fairly proficient with AppleScript.

What is your OS version ?

(The recently added version is plain text, so you should have no difficulty with that).

[QUOTE=RobTrew;94991]It's conceivable that earlier versions of OS X might have difficulty reading a .scpt file created under 10.6

I have now supplemented the existing .scpt with a plain text (.applescript) version.

(See the post at the start of this thread).[/QUOTE]

The plain text version is working perfectly. I'm also using 10.6 and am not sure if it's just me, but at this point I'm not going to worry about it.

Thanks a lot for your help!

FWIW, I just downloaded the zip file from the first post and I can't open the script either. AppleScript Editor says it was saved as run only which it cleary isn't as QuickLook shows the source, but Script Debugger says:

The operation couldn't be completed. (OSStatus error -1728)

Running "macerror -1728" gives me:

Mac OS error -1728 (errAENoSuchObject): e.g.,: specifier asked for the 3rd, but there are only 2. Basically, this indicates a run-time resolution error.

Then it occured to me perhaps the problem wasn't the script itself but that I was missing some component needed by the script. I downloaded the text, loaded it into Script Debugger, and hit compile. Sure enough, error -1728 "can't get application id org.tynsoe.geektool3". I don't have geektool installed and the script won't even load into the editor without it.

[QUOTE=GrumpyDave;95016]FWIW, I just downloaded the zip file from the first post and I can't open the script either.
...
Then it occured to me perhaps the problem wasn't the script itself but that I was missing some component needed by the script.[/QUOTE]

Confirmed. After installing GeekTool, the original script opens up without a problem. Good call, Dave.

Though with the plain text version I can still open it without GeekTool, remove the GT-specifics, and edit it to meet my needs.

Well spotted :-)

(The instructions in the first post do spell out that Geektools needs to be installed before the script is run, but there is probably too much text there - easy to miss ...)

I think your instructions are perfectly fine for anyone that wants to use it as is.

It's just odd that it can't open the script without GT. I can understand and expected a compile- or run-time error until I stripped out the GT specific stuff, but an issue with opening up the script itself and a horribly unhelpful/just plain wrong error message was not what I had anticipated.

Thanks again for all your help. Your script got me 95% of the way towards the HTML dumps I wanted to make while an OF window wasn't active and saved me a lot of hours.

[QUOTE=RobTrew;90549]and these can all be modified, for example to adjust sort order,
in inbox order by dateAdded
in inbox order by dateAdded DESC
in inbox order by dateDue, dateAdded
due now order by effectivedateDue
next action order by effectiveDateDue
in context "Library" order by task.name[/QUOTE]

Is it possible to sort by start date? I use start date as the date they are due and Due Date for things that are really Due and can't be done after that "Due Date".

This is awesome thanks Rob for putting this together!

Also I can't seem to sort anything by context.
i.e.
in context "Phone" by dateAdded

I would ideally like to sort by
Flagged=1,
startDate
and that the action is unchecked

I can't seem to figure out how this is sorting things, it doesn't seem to mater if the project is dropped, or checked off or if the actions in the project or complete it still shows them in the list for me in OmniFocus 1.9

[CODE]osascript ~/Library/Scripts/Applications/Omnifocus/GeekOFSQL.scpt 'task where flagged=1 order by projectinfo, dateDue --header=Flagged ($N)\n' | fold -s -w 40[/CODE]

[QUOTE=skillet;99312]
Flagged=1,
startDate
and that the action is unchecked
[/QUOTE]
[INDENT]WHERE ... and dateCompleted is null ORDER BY flagged DESC, dateToStart[/INDENT]
or perhaps
[INDENT]ORDER BY flagged DESC, dateToStart, dateCompleted[/INDENT]
[COLOR="White"]--[/COLOR]

[QUOTE=skillet;99312]I can't seem to sort anything by context.
i.e.
in context "Phone" by dateAdded
[/QUOTE]

Don't have it to hand, but if memory of the underlying query serves, you should be able to sort by [I]c.name[/I] for context titles.

[QUOTE=RobTrew;99320][INDENT]WHERE ... and dateCompleted is null ORDER BY flagged DESC, dateToStart[/INDENT]
or perhaps
[INDENT]ORDER BY flagged DESC, dateToStart, dateCompleted[/INDENT]
[COLOR="White"]--[/COLOR][/QUOTE]

Wonderful that did the trick! I am not sure what "flagged DESC" does in this case as well as including "dateCompleted". Removing both of those gives me the same results.

Anyway thank you that is exactly what I was looking for. The cool thing with this it shows actions with start dates now but are part of Projects that have start date in the future. I had a few of those and I didn't realize because OmniFocus doesn't show those actions with Project start dates in the future, which is good that OF behaves this way.
[CODE]osascript ~/Library/Scripts/Applications/OmniFocus/GeekOFSQL.scpt 'task where flagged=1 and dateCompleted is null ORDER BY flagged DESC,dateToStart,dateCompleted --header=Flagged ($N)\n' | fold -s -w 40[/CODE]

[QUOTE=RobTrew;99321]Don't have it to hand, but if memory of the underlying query serves, you should be able to sort by [I]c.name[/I] for context titles.[/QUOTE]

This didn't work for me
[CODE]in context "Phone" by c.name[/CODE]

But this does which is the only addition to a context filter I have been able to get to work. This is the best addition though, so I am glad I can filter out tasks that are completed. I didn't realize that was happening in the prebaked templates you made.
[CODE]in context "Phone" and dateCompleted is null[/CODE]

Thanks again for all your work on this, it is brilliant!

[QUOTE=skillet;99331]This didn't work for me
[CODE]in context "Phone" by c.name[/CODE][/QUOTE]

I think it would probably need to be:

[CODE]in context "Phone" [B]order by[/B] c.name[/CODE]

More output fields?
 

Hi, Great script, but I am not that familiar (yet) with apple script.

At the moment the only thing the script outputs is the task name is there a way to get it to output the Project that the action/task is part of?

R

It may be worth looking at some of the code in:

[url]http://forums.omnigroup.com/showpost.php?p=99012&postcount=7[/url]

and the rest of that thread.

[COLOR="White"]--[/COLOR]

For some reason order by dateAdded like post two shows is not working for me.

I would love to see more examples in post 2 because those were EXTREMELY helpful.

i.e. things like
-show in context by date added
-show available flagged actions (not all flagged actions like the example shows things that have been completed)
-Show available actions that start today (if that is even possible)

[CODE]osascript ~/Dropbox/Reference\ Files/*My\ Scripts/Aplication\ Scripts/OmniFocus\ Scripts/GeekOFSQL.scpt 'in context "Pomodoros" order by dateAdded'[/CODE]

This is very cool and helpful and keeps me on track without having to keep going back to OmniFocus.

My guess would be that more than one of the underlying tables has a [I]dateAdded[/I] field, making the query ambiguous.

Probably worth trying:

[CODE]'in context "Pomodoros" order by [B]task.[/B]dateAdded'[/CODE]

As in the example in Post 1:

[CODE]in context "Library" order by task.name[/CODE]

[QUOTE=RobTrew;102454]My guess would be that more than one of the underlying tables has a [I]dateAdded[/I] field, making the query ambiguous.

Probably worth trying:

[CODE]'in context "Pomodoros" order by [B]task.[/B]dateAdded'[/CODE][/QUOTE]

Sadly that isn't working for me either :( Does that work on your machine? I am on Snow Leopard 10.6.8 and OmniFocus/1.9.3/GM-v77.75.11

Yes, working here with the same OS and OF as yours.

Thanks Rob, ID10T error on my system.

I finally figured out that it was sorting in the correct order it just looked like it was sorting by name because my first actions were all numbers. On top of that I forgot to add DESC so I wasn't seeing the things I just added (which I was thinking I should see).

I'm sure I would have thrown in the towel if I didn't know it was working on your computer. Sometimes it is good to just take a break and come back to stuff like this another day or in my case another month ;).

Have tried this several times; when I run the .applescript file, I get a pop up window saying this:

Syntax Error
Can’t get application id
"com.omnigroup.OmniFocus".

Running OSX 10.7.2, OmniFocus version 1.9.4 (v77.86) on a 13" MacBook Pro.

Any help would be greatly appreciated.

Andrew, are you running the Mac App Store version of OmniFocus, by any chance?

I've made an edit to the .applescript file in the .zip, which should, I think, enable the App Store variant of OmniFocus to run it.

You will need to save the applescript file as a compiled (.scpt) script.

[COLOR="White"]--[/COLOR]

Tweaks help, please?
 

I love the script, but being a complete amateur scripter without any real working knowledge of SQL, I haven't been able to adjust the script to do the following things:

First and most important: I use the DueNow and DueSoon scripts, and I'd love to see the number of days till the action is due. Is there a way to have the 'StrList' return something that looks like this: 'Action: tomorrow' or day after tomorrow, 3 days from now, etc. Basically the same date format that '%RD' returns with iCalBuddy tasks.

Second and not very important: Is there an easy way to reduce the amount of space between the bullet point and the returned task? I don't understand the added perl functions posted earlier well enough to figure it out - but my guess is it's somewhere in there.

Thanks for your time. It's a great script, I just need those dates or it isn't as useful to me as iCalBuddy at the moment. And I don't trust iCal enough to switch everything over there.

iCalBuddy is a really solid and well-crafted piece of work - this is just a light sketch made from horse-back. (abbrevn: [I]hack[/I] :-)

You could probably, subject to the MIT licensing, which I haven't checked, incorporate the iCB language code into this Geektool script, but doing that is unlikely to rise to the top of my own agenda :-)

On the bullet gap - you can experiment with editing the eighth property near the start of the source code:
[INDENT][I]property pPrefix : (ASCII character 165) & tab[/I][/INDENT]
(replacing [I]tab[/I] with " " for example)

[COLOR="White"]--[/COLOR]

That's okay. I hoped perhaps it was a quick bit of code that could be copied and pasted in, but I know enough coding to know there's not many easy fixes.

It's a great script by the way and I love using it. Thanks for the editing tip as well. :-)

Thanks RobTrew, this is a fantastic script! Much better than the clunky one I was trying to use.

I have one question though, (as I am an applescript newbie): can anyone tell me please how I might update the script to add in a 'show available actions' list rather than simply 'next actions' which doesn't include actions in my miscellaneous box etc.

[QUOTE=Lusule;107565]... how I might update the script to add in a 'show available actions' list rather than simply 'next actions' which doesn't include actions in my miscellaneous box etc.[/QUOTE]

I'll have a look at that. 'Availability' has quite a complex definition in OmniFocus, so it may get messy :-)

(In English terms it's something like:
[INDENT][I]Tasks which are neither waiting their turn in a sequential project, nor already completed, nor deferred by a future start date (either of their own or inherited from a project or Single Action list), nor out of action because their project or context is on hold or dropped.[/I][/INDENT]
and in Applescript, roughly:[INDENT][I](blocked is false) and (completed is false) and ((start date is missing value) or (start date < now)) and ((its containing project is missing value) or (status of its containing project is active) and ((start date of its containing project < now) or (start date of its containing project is missing value))) and ((its context is missing value) or ((allows next action of its context is true) and (hidden of its context is false)))[/I][/INDENT]
So the trick would be to draft and debug something equivalent in SQL ... )

[COLOR="White"]--[/COLOR]

The Omnifocus Applescript Dictionary has an available task element and an available task class - neither of those are appropriate to make it easier?

[QUOTE=Lusule;107607]The Omnifocus Applescript Dictionary has an available task element and an available task class - neither of those are appropriate to make it easier?[/QUOTE]

[I]Available tasks[/I] are properties of Contexts, so useful if you are listing tasks under context headings in Applescript.

The Geektool script, however, doesn't use Applescript to filter tasks, as that route would be slow, require OmniFocus to be running, and interfere with the GUI responsiveness of the app itself.

Instead, it queries the OF cache, using SQL. The SQL data model doesn't as it happens, offer us a short cut to fully available tasks.

A useful simplification might be to query for tasks where [I]blocked=0 and dateCompleted is null and effectiveContainingProjectInfoActive=1[/I]

Ah that's a shame, and there was I hoping it would be something simple :(

These scripts are really helpful, but I'm having a problem with the Inbox and Context versions—they both show completed/checked items. I can't figure out how to alter the script show it only shows active or uncompleted items.

I'm a really new OF user, so I don't know if it's a setting that I'm missing. Thank you for any help.

[QUOTE=RobertK;111439]I can't figure out how to alter the script show it only shows active or uncompleted items.[/QUOTE]

You can add the condition [I]and dateCompleted is null[/I] inside a [I]where[/I] clause.

Sth like:[INDENT][I]task where inInbox = 1 and dateCompleted is null[/I][/INDENT]or[INDENT][I]task join context c on task.context = c.persistentIdentifier where c.name=\"Library\" and dateCompleted is null[/I][/INDENT]
[COLOR="White"]--[/COLOR]

Thank you so much!

Single-Action list
 

How do I get it to show tasks that are in a certain Single-Action list, project or folder?

I haven't built any shortcuts for those – at the moment I'm afraid I'll just have to point you towards [URL="http://forums.omnigroup.com/showpost.php?p=90786&postcount=5"]post 5 in this thread[/URL], and to the documentation for Sqlite3 …

But perhaps others have already shaped up queries for the listings you want ...

Here is a template for a shell script (for direct Applescript-free use with Geektool) which uses [URL="http://developer.apple.com/library/mac/#documentation/Darwin/Reference/ManPages/man1/awk.1.html"]awk[/URL] for formatting, and would enable you to list tasks by [B]project[/B].

This version uses the % wildcard to list any projects with names containing the word "write", together with any uncompleted tasks in those projects.

[CODE]#!/bin/sh
#OFOC=$(osascript -e 'tell application "Finder" to get id of application file id "OFOC"')
OFOC="com.omnigroup.OmniFocus" # For Appstore-purchased variant use (slower) line above instead
echo "SAMPLE LISTING:"
sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2 '
SELECT p.name, c.name, tt.name
FROM ((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt
left join task p on tt.task=p.persistentIdentifier)
left join context c on tt.context = c.persistentIdentifier
WHERE tt.dateCompleted is null and p.name like "%write%"
ORDER BY p.name, c.name' | awk '
BEGIN {FS="\|"; prj=0; ctx=0;}
{
# Whenever the value of col. 1 changes,
# write it out as a PROJECT header,
if (prj!=$1) {prj=$1; if (length(prj) < 1) {print "\n(INBOX)"}
else {print "\n" toupper(prj)}};

# and whenever the value of col. 2 changes,
# write it out as a [CONTEXT] sub-header.
if (ctx!=$2) {ctx=$2; if (length(ctx) > 0) {print "[" ctx "]"}};

# Whenever col 3. contains sth other than the project name,
# write it out as a • TASK.
if (length($3) > 0 && $3!=$1) {print "• " $3}
}'[/CODE]

[COLOR="White"]--[/COLOR]

[QUOTE=drongo;112517]How do I get it to show tasks that are in a certain Single-Action list, project or [B]folder[/B]?[/QUOTE]

A shell-script example of filtering by folder. (In this case for projects and tasks in folders with names containing the string "[I]writ[/I]")

[CODE]#!/bin/sh
#OFOC=$(osascript -e 'tell application "Finder" to get id of application file id "OFOC"')
OFOC="com.omnigroup.OmniFocus" # For Appstore-purchased variant use (slower) line above instead
echo "SAMPLE LISTING:"
sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2 '
SELECT f.name, p.name,c.name, tt.name
FROM (((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt
left join task p on tt.task=p.persistentIdentifier)
left join context c on tt.context = c.persistentIdentifier)
left join folder f on tt.folder=f.persistentIdentifier
WHERE tt.dateCompleted is null and f.name like "%writ%"
ORDER BY f.name, p.name, c.name'| awk '
BEGIN {FS="\|"; fldr=0; prj=0; ctx=0;}
{
# Whenever the value of col. 1 changes,
# write it out as a FOLDER header,
if (fldr!=$1){fldr=$1; if (length(fldr) < 1) {print "\n(No folder)"}
else {print "\n[Folder: " toupper(fldr) "]"}};

# and whenever the value of col. 2 changes,
# write it out as a PROJECT header.
if (prj!=$2){prj=$2; if (length(prj) < 1) {print "\n\t(INBOX)"}
else {print "\n\t" toupper(prj)}};

# Whenever the value of col. 3 changes,
# write it out as a [CONTEXT] sub-header.
if (ctx!=$3){ctx=$3; if (length(ctx) > 0) {print "\t[" ctx "]"}};

# and whenever col 4. contains sth other than the project name,
# write it out as a • TASK.
if (length($4) > 0 && $4!=$2) {print "\t• " $4}
}'[/CODE]

[QUOTE=drongo;112517]How do I get it to show tasks that are in a certain [B]Single-Action list[/B], project or folder?[/QUOTE]

Single-Action holders with names containing the string "misc"

[CODE]#!/bin/sh
#OFOC=$(osascript -e 'tell application "Finder" to get id of application file id "OFOC"')
OFOC="com.omnigroup.OmniFocus" # For Appstore-purchased variant use (slower) line above instead
echo "SAMPLE LISTING:"
sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2 '
SELECT f.name, p.name, c.name, tt.name
FROM (((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt
left join task p on tt.task=p.persistentIdentifier)
left join context c on tt.context = c.persistentIdentifier)
left join folder f on tt.folder=f.persistentIdentifier
WHERE tt.dateCompleted is null and tt.containsSingletonActions=1 and p.name like "%misc%"
ORDER BY p.name, c.name' | awk '
BEGIN {FS="\|"; fld=0; prj=0; ctx=0;}
{
# Whenever the value of col. 1 changes,
# write it out as a FOLDER header,
if ( fld!=$1) { fld=$1; if (length( fld) < 1) {print "\n(No folder)"}
else {print "\n[Folder: " toupper( fld) "]"}};

# and whenever the value of col. 2 changes,
# write it out as a PROJECT header.
if (prj!=$2) {prj=$2; if (length(prj) < 1) {print "\n\t(INBOX)"}
else {print "\n\t" toupper(prj)}};

# Whenever the value of col. 3 changes,
# write it out as a [CONTEXT] sub-header.
if (ctx!=$3) {ctx=$3; if (length(ctx) > 0 && length($4) > 0) {print "\t[" ctx "]"}};

# and whenever col 4. contains sth other than the project name,
# write it out as a • TASK.
if (length($4) > 0 && $4!=$2) {print "\t• " $4}
}'[/CODE]

A shell script version of Due Soon, illustrating an approach to listing imminently due tasks with their due date (+time) and number of remaining days, in NerdTool GeekTool etc

[CODE]#!/bin/sh
#OFOC=$(osascript -e 'tell application "Finder" to get id of application file id "OFOC"')
OFOC="com.omnigroup.OmniFocus" # For Appstore-purchased variant use (slower) line above instead
echo "DUE SOON:"
OFQUERY="sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2"
YEARZERO=$($OFQUERY "SELECT strftime('%s','2001-01-01')")
NOW=$($OFQUERY "SELECT (strftime('%s','now', 'localtime'))")
DUE="($YEARZERO + tt.effectiveDateDue)"
DAY=86400
DAYS_LEFT="round((($DUE-$NOW)*1.0)/$DAY,1)"
DAYS_OVERDUE="($NOW-$DUE)/$DAY"

$OFQUERY "
SELECT f.name, p.name, c.name, tt.name, $DAYS_LEFT, strftime('%m-%d %H:%M',$DUE, 'unixepoch')
FROM (((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt
left join task p on tt.task=p.persistentIdentifier)
left join context c on tt.context = c.persistentIdentifier)
left join folder f on tt.folder=f.persistentIdentifier
WHERE tt.isDueSoon=1 and $DAYS_OVERDUE < 14
" | awk '
BEGIN {FS="\|"; fld=0; prj=0; ctx=0;}
{
# Whenever the value of col. 1 changes, write it out as a FOLDER header,
if ( fld!=$1) { fld=$1; if (length( fld) < 1) {print "\n(No folder)"}
else {print "\n[Folder: " toupper( fld) "]"}};

# and whenever the value of col. 2 changes, write it out as a PROJECT header.
if (prj!=$2) {prj=$2; if (length(prj) < 1) {print "\n\t(INBOX)"}
else {print "\n\t" toupper(prj)}};

# Whenever the value of col. 3 changes, write it out as a [CONTEXT] sub-header.
if (ctx!=$3) {ctx=$3; if (length(ctx) > 0) {print "\t[" ctx "]"}};

# and whenever col 4. contains sth other than the project name, write it out as a • TASK.
if (length($4) > 0 && $4!=$2) {print "\t• " $4, "("$5" days to "$6")"}
}'[/CODE]

[QUOTE=codefreespirit;106771]I'd love to see the number of days till the action is due.[/QUOTE]

You could use, or refine, a custom relativedays() function in a bash (awk) script:

[CODE]#!/bin/sh
#Ver 0.04
# Time-zone handling adjusted
# Works with Vanilla and AppStore OF
# Exclusion of completed tasks corrected
OFOC="com.omnigroup.OmniFocus"
if [ ! -d "$HOME/Library/Caches/$OFOC" ]; then OFOC=$OFOC.MacAppStore; fi
OFQUERY="sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2"

NOW=$(date +%s) # the date command automatically allows for daylight savings like BST in the UK
#TODAY=$(date -v0H -v0M -v0S +%s) #Midnight at the start of today: set the time component to 00:00

ZONERESET=$(date +%z | awk '
{if (substr($1,1,1)!="+") {printf "+"} else {printf "-"} print substr($1,2,4)}')
YEARZERO=$(date -j -f "%Y-%m-%d %H:%M:%S %z" "2001-01-01 0:0:0 $ZONERESET" "+%s")
DUE="($YEARZERO + t.effectiveDateDue)";
DAY=86400
DAYS_LEFT="round((($DUE-$NOW)*1.0)/$DAY,1)"
DAYS_OVERDUE="($NOW-$DUE)/$DAY"

JOIN="(((task tt left join projectinfo pi on tt.containingprojectinfo=pi.pk) t
left join task p on t.task=p.persistentIdentifier)
left join context c on t.context = c.persistentIdentifier)
left join folder f on t.folder=f.persistentIdentifier"

MATCHES="status='active' and t.dateCompleted is null and t.isDueSoon=1 and $DAYS_OVERDUE < 14"
TOTAL=$($OFQUERY "SELECT count(*) FROM $JOIN WHERE $MATCHES")

echo "DUE SOON OR IN LAST FORTNIGHT ($TOTAL tasks)"
$OFQUERY "
SELECT f.name, p.name, c.name, t.name, $DAYS_LEFT, strftime('%m-%d %H:%M',$DUE, 'unixepoch')
FROM $JOIN
WHERE $MATCHES
ORDER BY f.name, p.name, c.name, t.effectiveDateDue
" | awk '
function relativedays(days) {
if (days<0) {return "("(-days) " days ago)"}
else {return "("days " days left)"}
}
BEGIN {FS="\|"; fld=0; prj=0; ctx=0;}
{
# Whenever the value of col. 1 changes, write it out as a FOLDER header,
if ( fld!=$1) { fld=$1; if (length( fld) < 1) {print "\n(No folder)"}
else {print "\n(Folder: " toupper( fld) ")"}};

# and whenever the value of col. 2 changes, write it out as a PROJECT header.
if (prj!=$2) {prj=$2; if (length(prj) < 1) {print "\nInbox:"}
else {print "\n" prj ":"}};

# Whenever the value of col. 3 changes, write it out as a [CONTEXT] sub-header.
if (ctx!=$3) {ctx=$3; if (length(ctx) > 0) {print "[" ctx "]"}};

# and whenever col 4. contains sth other than the project name, write it out as a • TASK.
if (length($4) > 0 && $4!=$2) {print "• " $4, relativedays($5), $6}
}'[/CODE]

Excellent!
 

Just wow, Rob! Thank you so much. You outdid yourself.

I'm running into one problem though. It shows all tasks even if they are completed.

I tried a couple of things (guessing that MATCHES= is supposed to set the matching criteria for the tasks displayed).

MATCHES="(t.isDueSoon=1 + t.DateCompleted=0) and $DAYS_OVERDUE < 14"

Also tried:

MATCHES="t.isDueSoon=1 and $DAYS_OVERDUE < 14 and t.DateCompleted=0"

Both caused all output to vanish.

Add: I tried t.DateCompleted=null, but that didn't work either.

Thanks !

You should be able to test on ([I]t.dateCompleted is null[/I]) vs ([I]t.dateCompleted is not null[/I])

I'll take a look tomorrow and correct it.

(PS glancing at it now it looks as if there's also scope for a daylight savings adjustment, and for making it more easily accessible to Appstore-purchased versions of OF)

[COLOR="White"]--[/COLOR]

[QUOTE=RobTrew;113923]I'll take a look tomorrow and correct it.

(PS glancing at it now it looks as if there's also scope for a daylight savings adjustment, and for making it more easily accessible to Appstore-purchased versions of OF)[/QUOTE]

Version 0.02 now in [URL="http://forums.omnigroup.com/showpost.php?p=113374&postcount=61"]post 61[/URL] above.

[CODE]#Ver 0.02
# Works with Vanilla and AppStore OF
# Adjusts for any daylight savings
# Exclusion of completed tasks corrected[/CODE]

You're amazing, Rob. Thank you so much for all your help and hard scripting work. It's appreciated.

Rob, I love the fact that you posted these shell scripts. This is one of my favorite threads. So, thanks a ton, they are useful to me on a daily basis.

I always had trouble with AppleScript geeklets not showing up when using them in conjunction with OmniFocus. The shell scripts fix that.

The only thing I'm missing is some way to display remaining flagged tasks. I tried to make the folders shell script to work with perspectives, but I failed miserably since I've been into shell scripting for only a month.

[QUOTE=pattulus;114431]The only thing I'm missing is some way to display remaining flagged tasks[/QUOTE]


Should be something like:
[CODE]select [I][field list][/I] from task where [B]effectiveFlagged=1 and dateCompleted is null[/B][/CODE]

[QUOTE=RobTrew;114436]Should be something like:
[CODE]select [I][field list][/I] from task where [B]effectiveFlagged=1 and dateCompleted is null[/B][/CODE][/QUOTE]

Thanks. That wasn't the (shell) script I referred to, but it works splendid with your pack from the 1st post.

I hesitated to try it out at first because my other Geeklets for OmniFocus which also use AppleScript never showed up the information after l log into my Mac. At least unless OmniFocus was loaded on startup or via the OS X resume feature. I always had to open Geektool and click on the empty shell containers and the text popped up.

[B]However[/B], with your script it does work without all that hassle.

One tiny question to the audience here:

[CODE]osascript ~/GeekOFSQL.applescript 'task where flagged=1 and dateCompleted is null order by projectinfo, dateDue --header=Flagged ($N)\n' | fold -s -w 120 | perl -pe 's/(^[^•]+)/\t\1/' | perl -pe 's/•/-/g' [/CODE]

Is it possible to get an output like "==== Flagged (4) ===="?

I have equal signs around all my other lists and I've googled some Linux boards and people suggest that I put the equal signs in quotes. I've tried all kind of quotes, inserting Hex or decimal values for the '=' it just won't work.

[QUOTE=pattulus;114471]
Is it possible to get an output like "==== Flagged (4) ===="?
[/QUOTE]

I've come up with a very simple workaround: I use the ≡ sign which just looks like the equal sign with the font-size I use for my geeklets. I'm happy.

[QUOTE=RobTrew;113933]Version 0.02 now in [URL="http://forums.omnigroup.com/showpost.php?p=113374&postcount=61"]post 61[/URL] above.

[CODE]#Ver 0.02
# Works with Vanilla and AppStore OF
# Adjusts for any daylight savings
# Exclusion of completed tasks corrected[/CODE][/QUOTE]

I'm happily using Ver 0.03 of the script. Completed tasks are now excluded, but only if they are not in the specified timeframe (last fortnight): [CODE]$DAYS_OVERDUE < 14[/CODE].

So at the moment every completed item of the last 14 days still shows up. I've tried some (amateurish) things, but couldn't get it to also exclude the last 14 days completed items.

Are you still seeing this with ver [URL="http://forums.omnigroup.com/showpost.php?p=113374&postcount=61"]0.04[/URL] ?

[QUOTE=RobTrew;115818]Are you still seeing this with ver [URL="http://forums.omnigroup.com/showpost.php?p=113374&postcount=61"]0.04[/URL] ?[/QUOTE]

Hi Rob, Version 0.04 only shows me the headline: "Due Soon or in last fornight (3 tasks)" but not the actual list of tasks. The good thing is it doesn't shows the completed projects with a due date anymore.

I'll continue using v0.03 for now (since it displays the taks).

Thanks for your support.

Tasks starting today
 

I've read all through this thread and I must be missing something simple. I can't get:

[CODE]tasks where (dateToStart is Today) order by dateAdded --header=Tasks Starting Today ($N)\n | fold -s -w 40[/CODE]

to display tasks that are starting today. Based on:

[QUOTE]yesterday, today, now, soon
<jan> ... <dec> (Interpreted as references to midnight at the start of the first day of the next (or current) month of that name).

today +1w, now +2d etc.[/QUOTE]

from Post 1, it appeared I could use a relative date but obviously I am still doing something wrong.

Any thoughts?

The date fields are all timestamps (total number of seconds since the start of the epoch 2001-01-01 00:00)

So you need to get the moments at which today started and will end, and filter for cases where (dateToStart >= MidnightLastNight) and (dateToStart < MidnightToNight)

Or if 'today' and 'tomorrow' are predefined with midnight values, where (dateToStart >= today, and dateToStart <= tomorrow)

Thanks that helped with the dates. The script was working perfectly for a few days and now is returning no results. Any thoughts on what may be wrong with:

[CODE]osascript ~/Library/Scripts/Applications/OmniFocus/GeekOFSQL.applescript 'tasks where (dateToStart >= today) and (dateToStart <= tomorrow) order by dateAdded DESC --header=Tasks Starting Today ($N)\n' | fold -s -w 80[/CODE]

[QUOTE=Tukanuk;118440]Thanks that helped with the dates. The script was working perfectly for a few days and now is returning no results. Any thoughts on what may be wrong with:

[CODE]osascript ~/Library/Scripts/Applications/OmniFocus/GeekOFSQL.applescript 'tasks where (dateToStart >= today) and (dateToStart <= tomorrow) order by dateAdded DESC --header=Tasks Starting Today ($N)\n' | fold -s -w 80[/CODE][/QUOTE]

Seems to be working here (though I would probably advise you to save the .applescript file as a compiled (.scpt) file from Applescript Editor):

[CODE]~/Library/Scripts/Applications/OmniFocus/GeekOFSQL.scpt[/CODE]

Is there a way to use this script to filter things so that only items in particular OF perspective were placed on the desktop with geektool?

I have a perspective that I call current focus, which includes the 8-10 projects I'm focusing on right now.

I then typically switch that perspective to context view and look at things ungrouped, sorted by due date and filtered for next actions. This gets me down 5-10 actions that I should be focusing on right now.

Would there be any way to put that info on the desktop with this script?

JL

[QUOTE=kingsinger;123833]

Would there be any way to put that info on the desktop with this script?

JL[/QUOTE]

I would be inclined to use psidnell's [URL="https://github.com/psidnell/ofexport/blob/master/DOCUMENTATION.md"]ofexport [/URL]command line script to put custom reports on the desktop with GeekTool.

[QUOTE=RobTrew;123837]I would be inclined to use psidnell's [URL="https://github.com/psidnell/ofexport/blob/master/DOCUMENTATION.md"]ofexport [/URL]command line script to put custom reports on the desktop with GeekTool.[/QUOTE]

Okay, I'll give that a look.

Filtering by Started date
 

I'd love to put a list on my desktop of tasks that have a start date (i.e. not null, not in the future), ordered by the start date. Is this possible?