Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.
Got a quick one for you today. Had a request to get a list of all participants assigned to a Rich Project / Work Area and export the results out to an Excel spreadsheet.
We will be using the following cmdlets to accomplish this task. All of the cmdlets are available using the PWPS_DAB module. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.
- Get-PWFolderSecurity
- Show-PWFolderBrowserDialog
- ConvertTo-DataTable
- New-XLSXWorkbook
First thing we need to do is determine which Rich Project / Work Area to report on. We are going to use the Show-PWFolderBrowserDialog to quickly select our project. The selected project’s ProjectWise Folder object will be returned and we will use this to populate the $PWProject variable.
# Select Project to report on. $PWProject = Show-PWFolderBrowserDialog
We selected the ‘BSI200 – Peterborough Geospatial Investigation’ project.
The following shows the returned folder object.
Next we will obtain any access control applied to the Project / Work Area using the Get-PWFolderSecurity cmdlet. We will need to filter the results to exclude any inherited access control objects.
# Get all folder security applied to the selected Project / Work Area. # The following returns all access control including any inherited. $PWAccessControl = Get-PWFolderSecurity -InputFolder $PWProject -Verbose
The following shows a couple of the entries returned. You can see one of them is inherited from a workflow at the datasource level.
We can exclude these entries by applying a Where-Object as shown in the following.
# We can add a Where-Object to only return the access control # explicitly applied to the Project / Work Area. $PWAccessControl = Get-PWFolderSecurity -InputFolder $PWProject | Where-Object InheritingFrom -Like 'none*'
You can see the inherited object is no longer returned.
We will now convert the returned list of access control objects to a datatable using the ConvertTo-DataTable cmdlet. This is a requirement for the New-XLSXWorkbook cmdlet.
# Convert the access control list to a datatable to be exported. $dt = ConvertTo-DataTable -InputObject $PWAccessControl
The following shows some of the datarows within the datatable after the conversion.
Finally, we need to export the datatable to an Excel spreadsheet using the New-XLSXWorkbook cmdlet.
# Export the datatable to the specified output file. Include the -Open switch parameter # to immediately open the spreadsheet after the export process is complete. New-XLSXWorkbook -InputTables $dt -OutputFileName 'd:\temp\export\accesscontrol.xlsx' -Open -Verbose
The resulting spreadsheet should look something like the following.
Now that we’ve gone through all of the individual steps, I am going to show you how to accomplish this is one line of PowerShell code by using piping.
<# Select the Project / Work Area to report on. Apply the resulting ProjectWise Folder object to the Get-PWFolderSecurity cmdlet. Pipe the resulting objects to the Where-Object to filter on only access control objects that are explicitly applied to the Project / Work Area. Next pipe the resulting Access Control objects to the ConvertTo-DataTable cmdlet to create a datatable that will then be piped to the New-XLSXWorkbook cmdlet for exporting to the specified path and file name. #> Get-PWFolderSecurity -InputFolder (Show-PWFolderBrowserDialog) -Verbose | Where-Object InheritingFrom -Like 'none*' | ConvertTo-DataTable | New-XLSXWorkbook -OutputFileName 'd:\temp\export\accesscontrol.xlsx' -Open -Verbose
Experiment with it and have fun.
Hopefully, you find this useful. Please let me know if you have any questions or comments.
powerwisescripting.blog 