Omnifocus weekly report script....

[fmantek]

I use Omnifocus a lot, and the thing that bugged me about it (or i am just to dumb to figure out how to do it) is that it does not help me a lot answering snippet emails..... I could not find a good way to create a report that showed: this is what i did last week...

If there is such a way, please tell me :)

so i wrote the applescript below that walks over the omnifocus database and creates a bbedit text file with dates/titles/shortnotes looking like this:

"30/4/08 .NET Library work

30/4/08 Add solution file to VS 2005 setup

30/4/08 Add deleted detection code on the contact object..

30/4/08 Add email constructor for primary and rel

30/4/08 Verify that the Contacts template is part of the setup

30/4/08 64 bit patch
Re: GData.NET for Windows x64

30/4/08 Released 1.1.4.1 Maintainance release
Added PrimaryEmail, PrimaryPostalAddress, PrimaryPhonenumber and PrimaryIMAddres
"

Note this is the first applescript i ever wrote, so if you think it can use improvements, let me know :) always eager to learn - i found getting into applescript not that easy...

If you want to use this without BBEdit, all you have to do is change the method BBEditOutput to use another application.


global theDate
global targetDate

set theDate to current date
set targetDate to theDate - 7 * days

tell application "OmniFocus" to activate

tell front document of application "OmniFocus"
set alertReply to display alert "Ready to generate outline" message "This process could take several minutes, during which OmniFocus and BBEdit will be unavailable for other tasks." as warning buttons {"OK", "Cancel"} default button "Cancel"
if (button returned of alertReply is "Cancel") then return
set topLevelProjectsAndFolders to every section
end tell

tell application "BBEdit"
activate
make new document
end tell

createReport(topLevelProjectsAndFolders)

on createReport(theProjects)
if (theProjects is {}) then return
createLine(item 1 of theProjects)
createReport(rest of theProjects)
end createReport

on createLine(anItem)
BBEditOutput(anItem)
createChildren(anItem)
end createLine

on BBEditOutput(anItem)
local rowData, nextLine, n
set rowData to getRowData from anItem
if (rowData is not {}) then
tell front window of application "BBEdit"
set sd to short date string of (rowDate of rowData)
set nextLine to sd & " " & (rowTopic of rowData) & return as Unicode text
select insertion point after last character
set selection to nextLine
try
set maxLength to 80
set n to (rowNote of rowData) as Unicode text
if (length of n > maxLength) then
set n to characters 1 thru maxLength of n
end if
if (length of n > 1) then
set n to " " & n & return
select insertion point after last character
set selection to n
end if
-- insert a blank
select insertion point after last character
set selection to return


on error
-- ignore error, there might not be a note, or it's to short
end try
end tell
end if
end BBEditOutput



on getRowData from anItem
using terms from application "OmniFocus"
set theModification to modification date of anItem
set theTopic to name of anItem
set theNote to (note of anItem)
end using terms from

if (theModification > targetDate) then
return {rowTopic:theTopic, rowNote:theNote, rowDate:theModification}
else
return {}

end if
end getRowData


-- anItem is an OF folder, project, or action
-- aRow is an OmniOutliner row
on createChildren(anItem)
using terms from application "OmniFocus"
try
-- First see if anItem is a folder
set itemChildren to every section of anItem
on error
-- Error says anItem isn't a folder, so see if it is a project
try
set anItem to root task of anItem
on error
-- Error says anItem isn't a project, so it must be a task
end try
set itemChildren to every task of anItem
end try
end using terms from
createChildrenHelper(itemChildren)
end createChildren

-- itemChildren is a list of OF folders, projects, and actions
-- aRow is an OmniOutliner row
on createChildrenHelper(itemChildren)
if (itemChildren is {}) then return
set childItem to item 1 of itemChildren
BBEditOutput(childItem)
createChildren(childItem)
createChildrenHelper(rest of itemChildren)
end createChildrenHelper

[Lizard]

I took this script and adapted it to be more of a weekly status email to your boss. ("Last week I did X. Next week I will do Y.")

I'm working on getting it posted somewhere shareable. If I don't add a link soon, feel free to email our ninjas and ask for it.

[wtmonroe]

I took this script and adapted it to be more of a weekly status email to your boss. ("Last week I did X. Next week I will do Y.")

I'm working on getting it posted somewhere shareable. If I don't add a link soon, feel free to email our ninjas and ask for it.

Lizard, I would really like to see this script as I use OF to generate something akin to a weekly report now. My lack of Applescript knowledge has made it hard for me to generate the formatting I would like though.

[Lizard]

The script is available over here (http://dl.dropbox.com/u/1747618/weekly_focus_summary.applescript).

[RobTrew]

The script is available over here (http://dl.dropbox.com/u/1747618/weekly_focus_summary.applescript).

A few quick thoughts about this very useful script:


It seems to fetch the note data, but doesn't put it into the report. Was that the intention ? (It would certainly speed it up a bit to elide the fetching of note data, which can be quite slow).
Since OF ver 1.8, the introduction of the flattened tasks property has made it possible to write these things rather more easily and briefly (and with noticeably faster execution - see the illustrative sketch below).
The list comes out unsorted. This could be fixed by querying the cache rather than the applescript library, and using an ORDER BY statement in the SQL.


Note also that I have added a clause to exclude completed items from the Next Week list. (More generally, I am a little unclear about the intended role of the modification date conditions in the Last Week list - intuitively I might have expected this to have been defined in terms of the completion date).

property pstrSubject : "TPS Report"
property pstrGreeting : "Boss Name
"
property pstrThisWeek : "Here's what I did this week:
"
property pstrNextWeek : "Here's what I plan on doing next week:
"

property dteNow : current date
property dteHistory : dteNow - (7 * days)
property dteFuture : dteNow + (7 * days)

tell application "OmniFocus"
activate
tell front document
set refHistory to a reference to (flattened tasks where (in inbox = false and its modification date > dteHistory))
set refFuture to a reference to (flattened tasks where ¬
(in inbox = false) and (completion date is missing value) and (modification date < dteHistory) and (due date > dteHistory) and (due date < dteFuture))
end tell

set {lstHDate, lstName} to {modification date, name} of refHistory
repeat with i from 1 to length of lstHDate
set item i of lstHDate to short date string of item i of lstHDate & space & item i of lstName
end repeat

set {lstFDate, lstName} to {modification date, name} of refFuture
repeat with i from 1 to length of lstFDate
set item i of lstFDate to short date string of item i of lstFDate & space & item i of lstName
end repeat
end tell

set text item delimiters to return
set strMsg to (pstrGreeting & "
" & pstrThisWeek & "
" & lstHDate as string) & "

" & pstrNextWeek & "
" & lstFDate as string
set text item delimiters to space

tell application "Mail"
tell (make new outgoing message with properties {visible:true, subject:pstrSubject, content:strMsg})
make new to recipient at end of to recipients with properties {name:"Boss", address:"the_boss@megacorp.com"}
end tell
activate
end tell

[RobTrew]

The list comes out unsorted. This could be fixed by querying the cache rather than the applescript library, and using an ORDER BY statement in the SQL.

This version may well need to be modified for future builds of OF (if Omni change the schema of the cache), but it should be very fast (assuming Mail is already open) and the lists are sorted (by date modified for last week, and by due date for next week).

(In this version too I have added a clause to exclude completed items from the Next Week listing. Again, I am not sure that I have fully understood why the original script is using modification dates rather than completion dates).

property pstrSubject : "TPS Report"
property pstrGreeting : "Boss Name,"
property pstrThisWeek : "Here's what I did this week:"
property pstrNextWeek : "Here's what I plan on doing next week (with due dates):"
property pstrRecpName : "Boss"
property pstrRecpAddr : "the_boss@megacorp.com"

property pDateFormat : "%m/%d/%Y"

-- OF SQL TIME BEGINS AT MIDNIGHT AT THE START OF 2001-01-01
property pDateZero : "strftime('%s','2001-01-01')"
property pLastWeek : "(strftime('%s','now','-7 days') - " & pDateZero & ")"
property pNextWeek : "(strftime('%s','now','+7 days') - " & pDateZero & ")"

on OFDate(strFieldName)
"strftime('" & pDateFormat & "', " & strFieldName & " + " & pDateZero & ", 'unixepoch')"
end OFDate

set text item delimiters to "|"
set lstFields to text items of (do shell script "sqlite3 ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
SELECT \"" & pstrGreeting & "\"; select null;

SELECT \"" & pstrThisWeek & "\"; select null;
SELECT " & OFDate("dateModified") & ", name
FROM task WHERE (inInbox=0) and (dateModified > " & pLastWeek & ")
ORDER BY dateModified; select null;

SELECT \"" & pstrNextWeek & "\"; select null;
SELECT " & OFDate("dateDue") & ", name
FROM task WHERE (inInbox=0) and (dateCompleted is null) and (dateModified < " & pLastWeek & ")
and (dateDue > " & pLastWeek & ") and (dateDue < " & pNextWeek & ")
ORDER BY dateDue;"))

set text item delimiters to space
tell application id "com.apple.mail"
tell (make new outgoing message with properties {visible:true, subject:pstrSubject, content:lstFields as string})
make new to recipient at end of to recipients with properties {name:pstrRecpName, address:pstrRecpAddr}
end tell
activate
end tell

[RobTrew]

The list comes out unsorted. This could be fixed by querying the cache rather than the applescript library, and using an ORDER BY statement in the SQL.

To get a sorted list without resorting to SQL and the cache, you could, of course, simply generate an additional column containing a sortable (numeric) form of the date, and then get the shell to do the sorting and remove the temporary column with something like:

echo reportlines | sort -k 1,3 | cut -f 2,3"
(sort by columns 1 and 3, then return only columns 2 and 3)

e.g.

property pstrSubject : "TPS Report"
property pstrGreeting : "Boss Name"
property pstrThisWeekHdr : "Here's what I did this week:"
property pstrNextWeekHdr : "Here's what I plan on doing next week:"
property pstrRecpName : "Boss"
property pstrRecpAddr : "the_boss@megacorp.com"

property dteNow : current date
property dteHistory : dteNow - (7 * days)
property dteFuture : dteNow + (7 * days)
property pdteBase : missing value

on run
-- MAKE SURE THAT WE HAVE A BASE DATE FOR EXPRESSING PARTICULAR DATES AS SORTABLE INTEGERS
if pdteBase is missing value then
set pdteBase to dteNow
tell pdteBase
set {its year, its month, its day, its time} to {2001, 1, 1, 0}
end tell
end if

tell application "OmniFocus"
tell front document
-- DEFINE THE TWO SETS OF TASKS,
set refHistory to a reference to (flattened tasks where (in inbox = false and its modification date > dteHistory))
set refFuture to a reference to (flattened tasks where ¬
(in inbox = false) and (completion date is missing value) and (modification date < dteHistory) ¬
and (due date > dteHistory) and (due date < dteFuture))

-- COLLECT THEIR NAMES AND THE DATES REQUIRED,
set {lstHDate, lstName} to {modification date, name} of refHistory
repeat with i from 1 to length of lstHDate
set dte to item i of lstHDate
set item i of lstHDate to ((dte - pdteBase) as string) & tab & short date string of dte & tab & item i of lstName
end repeat

set {lstFDate, lstName} to {due date, name} of refFuture
repeat with i from 1 to length of lstFDate
set dte to item i of lstFDate
set item i of lstFDate to ((dte - pdteBase) as string) & tab & short date string of dte & tab & item i of lstName
end repeat
end tell
end tell

-- AND SORT THE LISTS.
-- (setting line delimiter to \n in preparation for sorting lines in the shell)
set text item delimiters to "
"
set strThisWeek to do shell script "echo " & quoted form of (lstHDate as string) & " | sort -k 1,3 | cut -f 2,3"
set strNextWeek to do shell script "echo " & quoted form of (lstFDate as string) & " | sort -k 1,3 | cut -f 2,3"
set my text item delimiters to space

set strMsg to (pstrGreeting & "

" & pstrThisWeekHdr & "

" & strThisWeek) & "

" & pstrNextWeekHdr & "

" & strNextWeek

tell application "Mail"
tell (make new outgoing message with properties {visible:true, subject:pstrSubject, content:strMsg})
make new to recipient at end of to recipients with properties {name:pstrRecpName, address:pstrRecpAddr}
end tell
activate
end tell
end run


--

[Lizard]

I wasn't intentionally accessing the note data and then dropping it on the floor. Honestly, it was a pretty quick hack-n-slash merging of fmantek's original script with another script I'd written to do a similar weekly summary for OmniPlan.

[RobTrew]

I wasn't intentionally accessing the note data and then dropping it on the floor. Honestly, it was a pretty quick hack-n-slash merging of fmantek's original script with another script I'd written to do a similar weekly summary for OmniPlan.

Of course - and it provides a very helpful point of departure. Thank you for sharing it.

Is the idea of using the modification date for the 'next week' list that one can identify a subset of the available tasks by "touching" them (toggling their flag or check-box state, for example) ? That seems an interesting idea - (one could assign a keystroke to a simple Touch script, perhaps) - and if that is the intention, then my drafts probably need to be changed ... (they are rather unimaginatively using due dates to assemble the second list).

--

[skillet]

(In this version too I have added a clause to exclude completed items from the Next Week listing. Again, I am not sure that I have fully understood why the original script is using modification dates rather than completion dates).

Neat script. Is there a way to have it just show actions that have been marked as complete in the past week? Currently it shows items that don't have start and due dates, as well as incomplete items.

[RobTrew]

You would need, I think, to replace modification date with completion date in the following line:

set refHistory to a reference to (flattened tasks where (in inbox = false and its modification date > dteHistory))

[masterninja01]

Hey guys, I'm trying to figure out a way to create a report for my boss but need to exclude my personal projects from the progress report. Is that possible?

By the way, nice script!

[RobTrew]

You could temporarily ctrl-click and 'drop' the private projects whenever you generate a report, and start the two flattened tasks where queries with an additional clause:

(flattened tasks where status of its containing project is not dropped and (in inbox = false and

[RobTrew]

Alternatively, if all private projects are contained by a particular folder, you could temporarily Ctrl-click and drop that folder, and start the where clauses with:

(flattened tasks where effectively hidden of folder of its containing project is false

Restoring the folder to active after the report is generated.

If you want to learn a little scripting you could also write a where clause which always excluded the contents of a specific folder, regardless of its state.