Automating Project Server 2007 timesheets with Powershell

I needed to import some ‘support’ time that was being tracked from outside of project server. We had reports that combined the two but that seemed messy. I don’t really program though so I needed to work it out in powershell. I believe I have all the major functionality done. Still to be determined are all the issues around data overwrites, timing etc but here is a short walkthough of connecting to Project Server 2007 PSI with powershell, querying users, querying timesheets, updating a line on a timesheet and queuing an update.

Lots of help from the SDK

http://msdn.microsoft.com/en-us/library/websvctimesheet.timesheet.queueupdatetimesheet.aspx

I also referenced some of the chrisfie, code from codeplex. It was in c# but it gave some good direction.

Still very rough, maybe I will post more as I polish it out. As I spent a good part of my day today on this, hopefully it will help somebody else.

On to the powershell:

#set up some env variables. this is largely to find wsdl and csc, maybe other libraries, i stole it from the web.

$env:VSINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio 9.0"

$env:VCINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio $obj\VC"

$env:DevEnvDir="$env:VSINSTALLDIR\Common7\IDE"

$env:FrameworkSDKDir="$env:VSINSTALLDIR\SDK\v2.0"

$FrameworkPath=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())

$env:FrameworkDir=$(split-path $FrameworkPath -Parent)

$env:FrameworkVersion=$(split-path $FrameworkPath -Leaf)

$env:PATH="$env:VSINSTALLDIR\Common7\IDE;$env:VCINSTALLDIR\BIN;$env:VSINSTALLDIR\Common7\Tools;$env:VSINSTALLDIR\Common7\Tools\bin;$env:VCINSTALLDIR\PlatformSDK\bin;$env:FrameworkSDKDir\bin;$env:FrameworkDir\$env:FrameworkVersion;$env:VCINSTALLDIR\VCPackages;C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin;$env:PATH"

$env:INCLUDE="$env:VCINSTALLDIR\ATLMFC\INCLUDE;$env:VCINSTALLDIR\INCLUDE;$env:VCINSTALLDIR\PlatformSDK\include;$env:FrameworkSDKDir\include;$env:INCLUDE"

$env:LIB="$env:VCINSTALLDIR\ATLMFC\LIB;$env:VCINSTALLDIR\LIB;$env:VCINSTALLDIR\PlatformSDK\lib;$env:FrameworkSDKDir\lib;$env:LIB"

$env:LIBPATH="$FrameworkPath;$env:VCINSTALLDIR\ATLMFC\LIB"

#I tried to start w/ the connect-webservice from the orielly cookbook but it was giving me errors and this just worked.

# connect to the Proj Serv Interface (PSI) and create a timesheet object to manipulate timesheets

wsdl.exe http://<SNIP>/pwa/_vti_bin/psi/timesheet.asmx?WSDL

csc /t:library TimeSheet.cs

[Reflection.Assembly]::LoadFrom("$pwd\timesheet.dll")

$objTS = New-Object TimeSheet

$objTS.UseDefaultCredentials = $true

# connect to the Proj Serv Interface (PSI) and create a resource object to manipulate resources

wsdl.exe http://<SNIP>/pwa/_vti_bin/psi/resource.asmx?WSDL

csc /t:library Resource.cs

[Reflection.Assembly]::LoadFrom("$pwd\Resource.dll")

$objRes = New-Object Resource

$objRes.UseDefaultCredentials = $true

#we will need a date mathable datetime for calc in a bit so setup a date

$today = date

#get a list of all active users

$lstRes = $objRes.ReadUserList("Active")

#you could loop through the resource list but I will set it to just me for troubleshooting.

# the full list of users can obtained with: $lstRes = $objRes.ReadUserList("Active")

#method def:

#public TimesheetListDataSet ReadTimesheetList (Guid resUID,DateTime startDate,DateTime finishDate,int select))

# the “int select” is from this table, values are added together if you need multiples.

# Acceptable Value=4. Select timesheets with Acceptable status.

# AllExisting Value=31. Select all timesheets.

# AllPeriods Value=32. Select all timesheets plus an empty record for each period with no timesheet.

# Approved Value=8. Select timesheets with Approved status.

# CreatedByMe Value=64. Select timesheets you created.

# InProgress Value=1. Select timesheets with InProgress status.

# Rejected Value=16. Select timesheets with Rejected status.

# Submitted Value=2. Select timesheets with Submitted status.

$lstTS = $objTS.ReadTimesheetList($objRes.GetCurrentUserUid(), $today.addDays(-50), $today, 31)

#we now have all the timesheets in our date range.

$lstTS.TimeSheets

#you can get a specific timesheet

$ts = $objTS.ReadTimeSheet($lstTS.Timesheets.Item(2).TS_UID)

$ts

#there are two main items here. Lines are the rows you see in your timesheet view. actuals are the items in the columns

#check your lines with

$ts.Lines

#get a specific line. NOTE: Do Not use $line variable here. That is a PS variable and it will be reset every time you hit tab.

$tsLine = $ts.Lines.Item(2)

#you will use this in a bit to get your line UID. this is what you attach your actual to.

#now check your actuals

$ts.actuals

#create a new actual to attach

$myActual = $ts.Actuals.NewActualsRow()

#note I am working on GMT and my server is on EST so it is likely the reason why the times are 5 hours off, i didn't bother to check into it too much

$myActual.TS_ACT_START_DATE = [datetime]"4/7/2009 5:00:00 AM"

$myActual.TS_ACT_FINISH_DATE = [datetime]"4/8/2009 4:59:59 AM"

$myActual.TS_ACT_VALUE = 90000 # this looks like a lot of time but for some reason PS stores data as 1000 units/min so 90,000 = 1.5 hours.

$myActual.TS_LINE_UID = $tsLine.TS_LINE_UID

#add my actual to the actuals list

$ts.Actuals.AddActualsRow($myActual)

#add my ts to the update queue

$objTS.QueueUpdateTimesheet([Guid]::NewGuid(), $ts.Headers.Item(0).TS_UID, $ts)

#check your timesheet, man, it's updated.