Omnifocus Script for Stats

I do not rememebr where I got this. It was on the Omnifocus forums at some point. Anyway I use it to get lots of useful stats about my system.


**property** pVersion : "2.01"

**property** pstrDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus2/Data/Library/Caches/com.omnigroup.OmniFocus2/OmniFocusDatabase2"

**property** pblnToClipboard : *true*

**property** pblnSubTreeCounts : *true*

**property** pToClipboard : "Copy list to clipboard"

-- Ver 0.8 adds clipboard option to dialogue

-- Ver 0.9 gives an error message if the cache schema has changed, leading to an SQL error in the script

-- Ver 1.0 slight simplification of the code

-- Ver 1.1 added count of Pending projects

-- Ver 1.2 added a count of available actions

-- Ver 1.3 added a break-down of unavailable actions

-- Ver 1.4 added count of Current projects to complement Pending projects

-- ver 1.5 replaced Applescript time function with SQL time expression

-- Ver 1.7 Reorganizes menu, and attempts to enable access for .macappstore installations of OF

--Ver 1.8 Adjusts handling of variant bundle identifiers generally

-- Ver 2.00 Redraft of task breakdown, with progressive narrowing of criteria ...

-- Ver 2.01 Change for OF2. pstrDBPath reset

**tell** *application* "Finder"

**if** pstrDBPath ≠ "" **then**

**set** strCmd **to** "sqlite3 -separator ': ' \"" & pstrDBPath & "\" " & quoted form **of** ("

select \"INBOX GROUPS & ACTIONS\", count(*) from task where (inInbox=1);

select \" Inbox action groups\", count(*) from task where (inInbox=1) and (childrenCount>0);

select \" Inbox actions\", count(*) from task where (inInbox=1) and (childrenCount=0);

select null;

select \"FOLDERS\" , count(*) from folder;

select \" Active folders\", count(*) from folder where effectiveActive=1;

select \" Dropped folders\", count(*) from folder where effectiveActive=0;

select null;

select \"PROJECTS\", count(*) from projectInfo where containsSingletonActions=0;

select \" Active projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"active\");

select \" Current projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart is null or t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));

select \" Pending projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart > (strftime('%s','now') - strftime('%s','2001-01-01')));

select \" On-hold projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"inactive\");

select \" Completed projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"done\");

select \" Dropped projects\", count(*) from projectInfo where (containsSingletonActions=0) and (( status=\"dropped\") or (folderEffectiveActive=0));

select null;

select \"SINGLE ACTION LISTS\", count(*) from projectInfo where containsSingletonActions=1;

select \" Active single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"active\");

select \" On-hold single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"inactive\");

select \" Completed single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"done\");

select \" Dropped single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (( status=\"dropped\") or (folderEffectiveActive=0));

select null;

select \"CONTEXTS\", count(*) from context;

select \" Active contexts\", count(*) from context where (effectiveActive=1) and (allowsNextAction=1);

select \" On-hold contexts\", count(*) from context where (effectiveActive=1) and allowsNextAction=0;

select \" Dropped contexts\", count(*) from context where effectiveActive=0;

select null;

select \"ACTION GROUPS\", count(*) from task where (projectinfo is null) and (childrenCount>0);

select \" Remaining action groups\", count(*) from task where (projectinfo is null) and (dateCompleted is null) and (childrenCount>0);

select \" Completed action groups\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount>0);

select null;

select \"ACTIONS\", count(*) from task where (projectinfo is null) and (childrenCount=0);

select \" Completed actions\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount=0);

select \" Dropped project actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp where (projectinfo is null) and (childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is not null and (tp.status=\"dropped\" or tp.folderEffectiveActive=0));

select \" Dropped context actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and c.effectiveActive= 0;

select \" Remaining actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1);

select null;

select \" Actions in Projects on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1)

and (tp.containingProjectInfo is not null and tp.status=\"inactive\");

select \" Actions in Contexts on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1)

and (tp.containingProjectInfo is null or tp.status!=\"inactive\")

and (tp.context is not null and c.allowsNextAction=0);

select null;

select \" Blocked actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1)

and (tp.containingProjectInfo is null or tp.status!=\"inactive\")

and (tp.context is null or c.allowsNextAction=1)

and tp.blocked=1;

select \" Sequentially blocked\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1)

and (tp.containingProjectInfo is null or tp.status!=\"inactive\")

and (tp.context is null or c.allowsNextAction=1)

and tp.blocked=1

and tp.blockedByFutureStartDate=0;

select \" Awaiting start date\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1)

and (tp.containingProjectInfo is null or tp.status!=\"inactive\")

and (tp.context is null or c.allowsNextAction=1)

and tp.blocked=1

and tp.blockedByFutureStartDate=1;

select null;

select \" Available actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0) and (dateCompleted is null)

and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))

and (tp.context is null or c.effectiveActive= 1)

and (tp.containingProjectInfo is null or tp.status!=\"inactive\")

and (tp.context is null or c.allowsNextAction=1)

and tp.blocked=0;

")

-- try

**set** strList **to** **do shell script** strCmd

-- on error

-- display dialog "The SQL schema for the OmniFocus cache may have changed in a recent update of OF." & return & return & ¬

-- "Look on the OmniFocus user forums for an updated version of this script." buttons {"OK"} with title pTitle & "Ver. " & pVersion

-- return

-- end try

**activate**

**if** button returned **of** ( **display dialog** strList buttons {pToClipboard, "OK"} default button "OK" with title pTitle & " Ver. " & pVersion) ¬

**is** pToClipboard **then** **tell** *application* *id* "com.apple.finder" **to** **set the clipboard to** strList

**else**

**display dialog** "OmniFocus cache not found ..." buttons {"OK"} default button 1 with title pTitle & " Ver. " & pVersion

**end** **if**

**end** **tell**

**on** FileExists(strPath)

( **do shell script** "test -e " & strPath & " ; echo $?") = "0"

**end** FileExists

**on** GetCachePath()

**try**

**tell** *application* "Finder" **to** **tell** ( *application file* *id* "OFOC") **to** "$HOME/Library/Caches/" & **its** id & "/OmniFocusDatabase2"

**on** **error**

**error** "OmniFocus not installed ..."

**end** **try**

**end** GetCachePath
1 Like

Could you use triple backticks above and below your script to format it as code please? A backtick looks like this ` :slight_smile:

Is that better? Not sure i got it done right and have to run out right now. If not ok can fix after sheep work this am

1 Like

Much better, thanks!

Does it work with OF 3?

No clue, I don’t have that installed yet, have to schedule time to upgrade to High Sierra first. (And no, I won’t go to Mojave yet, too much to get running again at each OS upgrade to warrant that until I need it.

FWIW the OF 3 upgrade is probably the nly thing driving me to High Sierra now.

What does the script output to? Does it display within Omnifocus or somewhere else?

A pop up window with a button to save to clipboard. Looks like this.

34%20AM

1 Like

Super stupid question as I don’t run many scripts, where/how do I run this?

Put the script in the script folder for OF. You can find out where it is from the help menu Open scripts folder.

I added it to my toolbar
Right click on the tool bar of an OF window and select customize toolbar. The scripts show up there. Drag it into the toolbar. Click on Done to get back out then click on the script icon in the toolbar.

There is a way to run it once without putting it into the toolbar but I can’t remember how to do it, sorry

1 Like

This is kind of cool. Do oh just use it to get an idea of how much you have?

And so I can see progress. I actually keep a copy of the data usually once a month or so.

I wonder if there is a way to do actual analysis with this data other than looking at it? Does it export to a spreadsheet at all?

Could you scrape the data out via OCR or modify script to save it in some other format?

Sadly this no longer works in OF 3 Actually it runs but won’t update the data.

The script is from Rob Trew and came from here http://forums.omnigroup.com/showthread.php?t=18405

I suspect that it needs to have “context” replaced with “tag” to run with OF3.


JJW