The Omni Group
These forums are now read-only. Please visit our new forums to participate in discussion. A new account will be required to post in the new forums. For more info on the switch, see this post. Thank you!

Go Back   The Omni Group Forums > OmniFocus > OmniFocus Extras
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Omnifocus weekly report script.... Thread Tools Search this Thread Display Modes
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
 
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.
 
Quote:
Originally Posted by Lizard View Post
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.
 
The script is available over here.

Last edited by Brian; 2011-02-04 at 02:30 PM.. Reason: reformat link presentation
 
Quote:
Originally Posted by Lizard View Post
The script is available over here.
A few quick thoughts about this very useful script:
  1. 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).
  2. 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).
  3. 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).

Code:
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

Last edited by RobTrew; 2011-02-07 at 02:22 AM..
 
Quote:
Originally Posted by RobTrew View Post
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).

Code:
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

Last edited by RobTrew; 2011-02-05 at 08:40 AM.. Reason: facilitates editing of date format
 
Quote:
Originally Posted by RobTrew View Post
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:

Code:
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.

Code:
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
--

Last edited by RobTrew; 2011-02-07 at 03:46 AM.. Reason: Allowed for use of due date in future list
 
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.
 
Quote:
Originally Posted by Lizard View Post
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).

--

Last edited by RobTrew; 2011-02-07 at 09:53 PM..
 
Quote:
Originally Posted by RobTrew View Post
(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.
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
OmniFocus 2.0 Progress Report Nigrini OmniFocus 1 for Mac 58 2013-01-06 11:03 AM
Take Export from OmniSync for Omnifocus and create report? JonSingh OmniFocus Extras 0 2012-08-06 02:02 PM
Trying to create a weekly report Finlay Boo OmniFocus Extras 8 2012-04-26 12:50 AM
Extracting/Exporting for SIMPLE Weekly Report egrFocused Applying OmniFocus 0 2010-11-01 07:24 AM
Omnifocus report template for iWork Numbers '09? signal15 OmniFocus 1 for Mac 1 2009-04-21 12:59 PM


All times are GMT -8. The time now is 05:45 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.