Trying to create a weekly report
 

Every week I need to produce a weekly report of active projects and completed work. Currently this is quite a messy process of triggering a particular perspective, reviewing what's in there, load a 'print-friendly' theme, saving to a file and then repeating for the completed actions. I'd like to be able to make this scriptable so that running a script creates a text/ markdown file as per the template below.

* Active Projects
[INDENT]* Get a list of active folders (excluding those that aren't relevant such as Personal & Someday/ Maybe)
* For each folder get a list of active projects (excluding SALs)
* If a sub folder is found, list the active projects within it[/INDENT]* Completed
[INDENT]* Query for any completed projects within the relevant folders. If any are found list them.
* Query for completed actions and list ordered by completed date[/INDENT]* Collect all produced above and create an .md file

Does this sound doable and, in addition to Rob Trew's great OF-AQL script, is there anything obvious that may be worth me looking at to get this kind of functionality.

---

##ACTIVE PROJECTS

###Folder 1
Project 1
Project 2
Project 3

###Folder 2
Project 4
Project 5

###Folder 3
Project 6
Project 7

####Sub Folder 3.1
Project 8
Project 9

####Sub Folder 3.2
Project 10

###Folder 4
Project 11


##COMPLETED FROM 7 DAYS

###Projects

####Folder 1
Project 12

####Folder 2
Project 13

###Actions
task name 1, project 8, completed date
task name 2, project 7, completed date
task name 3, project 7, completed date

By hacking together a Rob Trew script together with [URL="http://veritrope.com/code/omnifocus-write-active-project-list-to-text-file"]this Veritrope one[/URL] I've cobbled something together which works nicely for my purposes. It's uncommented but probably self explanatory all the same. If it proves useful for someone, then that'd be super!


[CODE]

tell application "OmniFocus"
set list_Projects to {}
set list_Folders to {}
set exclude_areas to {"Personal", "Management", "Someday/ Maybe"}
set oDoc to default document
set ExportList to "Current List of Active Projects" & return & "---" & return & (current date) & return & return as Unicode text


set folderObjs to (folders of oDoc)
-- SORT THE LIST

set show_section to false
repeat with folder_obj in folderObjs
if name of folder_obj is not in exclude_areas then
set thisSection to return & "##" & name of folder_obj & return & return
set projectObjs to (projects of folder_obj where hidden of its folder is false and its status is active)
repeat with project_obj in projectObjs
if project_obj is not singleton action holder then
set thisSection to thisSection & name of project_obj & " " & return
set show_section to true
end if
end repeat
set subFolderObjs to (folders of folder_obj)
repeat with subfolder_obj in subFolderObjs
set thisSection to thisSection & return & "###" & name of subfolder_obj & return & return
set projectObjs to (projects of subfolder_obj where hidden of its folder is false and its status is active and (its start date is missing value or start date < (current date)))
repeat with project_obj in projectObjs
if project_obj is not singleton action holder then
set thisSection to thisSection & name of project_obj & " " & return
set show_section to true
end if
end repeat
end repeat
if show_section then
set ExportList to ExportList & thisSection
set show_section to false
end if
end if
end repeat

set ExportList to ExportList & return & return & "Completed Tasks" & return & "---" & return & return

set week_ago to (current date) - 7 * days

tell oDoc
set refDoneInLastWeek to a reference to (flattened tasks where (completion date ≥ week_ago))
set {lstName, lstContext, lstProject, lstFolder, lstDate} to {name, name of its context, name of its containing project, name of folder of its containing project, completion date} of refDoneInLastWeek
set strText to ""
repeat with iTask from 1 to count of lstName
if lstFolder is not in exclude_areas then
set {strName, varContext, varProject, varDate} to {item iTask of lstName, item iTask of lstContext, item iTask of lstProject, item iTask of lstDate}
if varDate is not missing value then set strText to strText & short date string of varDate & " - "
if varProject is not missing value then set strText to strText & " [" & varProject & "] - "
set strText to strText & strName
if varContext is not missing value then set strText to strText & " @" & varContext
set strText to strText & " " & return
end if
end repeat
end tell

set ExportList to ExportList & strText as Unicode text



set fn to choose file name with prompt "Name this file" default name "Weekly Development Report" & ¬
".md" default location (path to desktop folder)
tell application "System Events"
set fid to open for access fn with write permission
write ExportList to fid
close access fid
end tell

end tell



[/CODE]

Very cool, thanks!

Looks very useful :-)

(I notice that it only reaches down to one level of folder indentation, and that may well be all that you are using. If, however, you do want it to find folders at an arbitrary depth of nesting (and correspondingly extend the MD header ### sequences) you could make some edits broadly along the following lines)

[CODE]on run
tell application "OmniFocus"
set dteNow to (current date)
set ExportList to "Current List of Active Projects" & return & "---" & return & dteNow & return & return as Unicode text
tell default document
repeat with oFolder in (flattened folders where hidden is false and name is not "Personal" and name is not "Management" and name is not "Someday/Maybe") as list
set ExportList to ExportList & my IndentAndProjects(oFolder, dteNow) & return
end repeat

set ExportList to ExportList & return & return & "Completed Tasks" & return & "---" & return & return
set week_ago to dteNow - 7 * days

set refDoneInLastWeek to a reference to (flattened tasks where (completion date ≥ week_ago) and name of folder of its containing project is not "Personal" and name of folder of its containing project is not "Management" and name of folder of its containing project is not "Someday/Maybe")
set {lstName, lstContext, lstProject, lstDate} to {name, name of its context, name of its containing project, completion date} of refDoneInLastWeek
set strText to ""
repeat with iTask from 1 to length of lstName
set {strName, varContext, varProject, varDate} to {item iTask of lstName, item iTask of lstContext, item iTask of lstProject, item iTask of lstDate}
if varDate is not missing value then set strText to strText & short date string of varDate & " - "
if varProject is not missing value then set strText to strText & " [" & varProject & "] - "
set strText to strText & strName
if varContext is not missing value then set strText to strText & " @" & varContext
set strText to strText & " " & return
end repeat
end tell

set ExportList to ExportList & strText as Unicode text

set fn to choose file name with prompt "Name this file" default name "Weekly Development Report" & ".md" default location (path to desktop folder)
tell application "System Events"
set fid to open for access fn with write permission
write ExportList to fid
close access fid
end tell
end tell
end run


on IndentAndProjects(oFolder, dteNow)
tell application id "OFOC"
set strIndent to "##"
set oParent to container of oFolder
repeat while class of oParent is folder
set strIndent to strIndent & "#"
set oParent to container of oParent
end repeat

set {dlm, my text item delimiters} to {my text item delimiters, return & return}
set strActive to (name of (projects of oFolder where it is not singleton action holder and its status is active and its start date is missing value or start date < dteNow)) as string
set my text item delimiters to dlm

return strIndent & name of oFolder & return & strActive & return
end tell
end IndentAndProjects
[/CODE]

Thanks for the tweaks, Rob (makes it much more useful for others) and thanks for the original contribution.

I don't really get involved with Applescript outside of 'Frankenstein' code whereby I hack and butcher other's solid efforts into my own mish-mash to get them to fit my own use.

This is great, thanks! I think it will make my boss very happy. :-)

I did find a bug though. In order to make the list of completed tasks respect exclude_areas, I had to change line 50 to:

[CODE]if [I]item iTask of[/I] lstFolder is not in exclude_areas then[/CODE]

A Couple of questions.

1. When I put the code in a text editor with line numbers, it looked like line 50 was a blank line towards the end. Do you think you could clarify exactly where that snippet goes for those of us who aren't very script savvy, but do like using them?

2. When I ran the script as written (Rob's iteration), it didn't seem to respect the sidebar filter of the projects. So there were completed projects mixed in with the active ones in the report.

Is anyone else seeing that behavior?

KS

Figured out the answer to my first questions. I was sing Rob Trew's script and not the original one.

Still curious about the second question, particularly as it relates to Rob Trew's script. Do I need to revise it, so it only captures active projects?

KS

[QUOTE=kingsinger;109786]Still curious about the second question, particularly as it relates to Rob Trew's script. Do I need to revise it, so it only captures active projects?[/QUOTE]

My snippet is just an illustration of how to traverse the folder tree.
You can adjust its actual output by editing the [I]where[/I] clauses.