[Finlay Boo]

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

* 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

* Completed

* 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

* 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

[Finlay Boo]

By hacking together a Rob Trew script together with this Veritrope one 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!

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

[eronel]

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)

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

[Finlay Boo]

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.

[khagler]

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:

if item iTask of lstFolder is not in exclude_areas then

[kingsinger]

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

[kingsinger]

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

Originally Posted by kingsinger

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?

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