HowTo: Update Work Area Properties From SharePoint

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.

I am getting more and more requests for assistance with getting data from a SharePoint site and using that data to update Work Area / Project Properties within ProjectWise. So, in this post we will look at one method of extracting data from a SharePoint site. Then we will step through the process of updating the Work Area / Project Properties within ProjectWise.

Most of the cmdlets and / or functions used within this post are delivered with the PWPS_DAB module. I am using version 1.11.2.0 of the PWPS_DAB module at the time of this post. Thanks to Zachary Kerr, we have a few PowerShell functions for working with SharePoint within PowerShell. These functions are delivered in the  HelperFunctions.psm1 file delivered with the PWPS_DAB module. However, it will require the installation of the SharePointPnPPowerShell2013 module available from the PSGallery.

<# Module Name: SharePointPnPPowerShell2013
   Version:     3.9.1905.2
   Repository:  PSGallery #>
# Use the following to locate the module.
Find-Module 'SharePointPnPPowerShell2013'
# Use the following to install the module from the PSGallery.
Install-Module -Name 'SharePointPnPPowerShell2013' -Force

We will need to import the HelperFunctions.psm1 module to take advantage of the SharePoint functions delivered.

<# Import the HelperFunctions.psm1 script module into PowerShell.
    Use the Get-Module cmdlet to get the folder path to the version
    of the PWPS_DAB module currently in use. #>
$PWPS_DAB_Info = Get-Module -Name PWPS_DAB
$Path = $PWPS_DAB_Info.Path.Substring(0, $PWPS_DAB_Info.Path.LastIndexOf('\'))

# Import HelperFunctions.psm1
Import-Module -Name "$Path\HelperFunctions.psm1" -Force
<# List the available SharePoint functions within the 
   HelperFunctions script module. #>
Get-Command -Name '*sharepoint*' -Module 'helperfunctions'

---

Now that we have the helperfunctions.psm1 and the SharePointPnPPowerShell2013 modules imported, we can take a look at the ProjectWise and SharePoint cmdlets and functions we will be using. I recommend taking a look at the help information for each of the functions to become familiar with the available parameters, examples, etc.

# Included with the PWPS_DAB module
Get-Help -Name Update-PWRichProjectProperties -Full
Get-Help -Name Copy-FileToSharePoint -Full
Get-Help -Name Copy-PWDocumentToSharePoint -Full
Get-Help -Name Get-SharePointListItem -Full
Get-Help -Name New-SharePointConnection -Full
# Included with the SharePointPnPPowerShell2013 module
Get-Help -Name Connect-PnPOnline -Full 
Get-Help -Name Disconnect-PnPOnline -Full
Get-Help -Name Get-PnPListItem -Full
Get-Help -Name Set-PnPListItem -Full

---

SharePoint

The following shows the SharePoint Site we will be working with. There are four items listed. The third item does not have a corresponding Work Area / Project within ProjectWise. In this post, we are simply going to set the ProjectInProjectWise value to No. We could however, use this as a flag to create a new Work Area / Project in ProjectWise.

The following image shows the third item. Notice the ProjectInProjectWise value is set to Yes, even though there is not a corresponding project within ProjectWise. We will update this setting to be No.

The first thing we need to do is create a PowerShell Credentials object and open a connection to the specified SharePoint site.

# SharePoint Site URL
$Url = "https://company.sharepoint.com/sites/SharePointDemo"

# SharePoint User and Secure Password
$SharePointUser = 'username@email.com'
# Prompts for the user password.
$SecurePassword = Read-Host -Prompt 'Enter Password:' -AsSecureString
# Splat containing New-Object cmdlet parameter names and values.
$Splat_Credentials = @{
    TypeName = 'PSCredential'
    ArgumentList = ($SharePointUser, $SecurePassword)
}
$Credentials = New-Object @Splat_Credentials

# Create connection with the specified SharePoint Site.
# Splat containing Connect-PnPOnline cmdlet parameter names and values.
$Splat_SharePoint = @{
    Url = $Url
    Credentials = $Credentials
}
$SharePointConnection = Connect-PnPOnline @Splat_SharePoint -ReturnConnection -Verbose

NOTE: SharePoint is Case-Sensative!!

# Get the desired data. Four SharePoint items should be returned.
$SPItems = Get-PnpListitem -List 'ProjectWise_Work_Area_Properties'

The following shows the results of the Get-PnpListitem. Four items were returned.

Next, we will create a Work Area Properties class containing six properties which will allow us to store the SharePoint data in, in a well formatted fashion. You will see there is a corresponding property in the class for each item property from SharePoint.

Create WorkAreaProperties Class And Populate ArrayList

class WorkAreaProperties
{
    [ValidateNotNullOrEmpty()][string]$Number
    [ValidateNotNullOrEmpty()][string]$Name
    [ValidateNotNullOrEmpty()][string]$Description
    [ValidateNotNullOrEmpty()][string]$Location
    [ValidateNotNullOrEmpty()][string]$InProjectWise
    [ValidateNotNullOrEmpty()][int] $SharePointIndex
}

Next, we will loop through each of the SharePoint items, create a new instance of the WorkAreaProperties class ($wk) and populate our array list with the data.

# ArrayList to store data in
$arrayWorkAreaProperties = New-Object System.Collections.ArrayList
<# Loop through all items returned from SharePoint and populate
    the arrayWorkAreaProperties arraylist with the desired data. #>
foreach ($SPItem in $SPItems) {
    <# Populate the wk variable with values from the current SharePoint item.
        Test each to determine if empty or null. #>
    foreach ($item in $SPItem.FieldValues) { 
        # Create new instance of the WorkAreaProperties class.
        $wk = [WorkAreaProperties]@{}

        if( -not ([string]::IsNullOrEmpty($item.ProjectNo))) {
            $wk.Number = $item.ProjectNo
        }
        if( -not ([string]::IsNullOrEmpty($item.Title))) { 
            $wk.Name = $item.Title
        }
        if( -not ([string]::IsNullOrEmpty($item.ProjectDescription))) { 
            $wk.Description = $item.ProjectDescription
        }
        if( -not ([string]::IsNullOrEmpty($item.ProjectInProjectWise))) { 
            $wk.Location = $item.ProjectLocation
        }
        if( -not ([string]::IsNullOrEmpty($item.ProjectInProjectWise))) { 
            $wk.InProjectWise = $item.ProjectInProjectWise
        }
        $wk.SharePointIndex = $index
        <# Add WorkArea properties to the ArrayList. 
            Using Out-Null to suppress output from ArrayList.Add method. #>
        $arrayWorkAreaProperties.Add($wk) | Out-Null
    } # end foreach ($item... (Inner Loop)
} # end foreach ($SPItem... (Outer Loop)

The followings shows the contents of the $arrayWorkAreaProperties.

---

ProjectWise

The following image shows the Work Area / Project types within the ProjectWise Administrator client that I will be using. There are four properties we will be updating which correspond to the data obtained from SharePoint.

We will loop through the entries in the array list. Determine if the specified project exists within ProjectWise. If it does exist, update the Work Area / Project properties. If the project does not exist, continue to next item. We will need to ensure the InProjectWise value in the SharePoint site is set correctly for each item.

The following shows the list of Work Areas / Projects in the ProjectWise datasource.

<# Loop through each item in the array list and update the 
    Work Area / Project properties if needed. Ensure the InProjectWise
    item is set correctly within the SharePoint site for each item. #>
foreach ($item in $arrayWorkAreaProperties) {
    Write-Verbose -Message "Processing $($item.Name)" -Verbose
    <# Determine if a project exists within ProjectWise using the name 
        property. In my datasource, all projects are contained in the 
        Projects folder. #>
    # Splat containing Get-PWRichProjects cmdlet parameter names and values. 
    $Splat_Project = @{
        FolderPath = "Projects"
        FolderName = $item.Number
    }

    try { 
        $pwProject = Get-PWRichProjects @Splat_Project -JustOne -ErrorAction Stop
    } catch {
        Write-Warning -Message "Folder Name: $($item.Number); $($Error[0].Exception.Message); Continuing to next entry."
        <# If the Work Area / Project does not exist, ensure the 
            SharePoint InProjectWise value is set to No. #>
        if($item.InProjectWise -ne "No") {
            # Set SharePoint ProjectInProjectWise value to false. 
            try {
                # Splat containing Set-PnPListItem cmdlet parameter names and values.
                $Splat_SetSPItem = @{
                    List = $SPListName
                    Identity = $SPItems[$item.SharePointIndex]
                    Values = @{ProjectInProjectWise = "$false"}
                }
                Set-PnPListItem @Splat_SetSPItem -ErrorAction Stop
            } catch {
                Write-Warning -Message "Error occurred while attempting to set the ProjectInProjectWise value in SharePoint. $($Error[0].Exception.Message)"
            }
        } # end if($item.InProjectWise...
        continue
    } # end try / catch...
    
    # Populate hash table with Work Area / Project Property names and values.
    $Properties = @{
        PROJECT_WorkAreaNumber = $item.Number
        PROJECT_WorkAreaName = $item.Name
        PROJECT_WorkAreaDescription = $item.Description
        PROJECT_WorkAreaLocation = $item.Location
    }
    
    # Update the Work Area / Project Properties within ProjectWise.
    try {
        # Splat containing Update-PWRichProjectProperties cmdlet parameter names and values.
        $Splat_UpdateProperties = @{
            InputFolder = $pwProject
            ProjectProperties = $Properties
        }
        $Result = Update-PWRichProjectProperties @Splat_UpdateProperties -ErrorAction Stop
    } catch {
        Write-Warning -Message "Error occurred while attempting to update the Project Properties. $($Error[0].Exception.Message)"
    }
} # end foreach ($item...

The following images show that the Work Area / Project Properties were successfully updated with the data from SharePoint for each of the projects.

For SharePoint item three, there was not a corresponding project within ProjectWise. So, we updated the ProjectInProjectWise value within SharePoint to No ($false) using the Set-PnPListItem cmdlet.

The following shows the updated value within SharePoint.

The last thing we need to do, is close the connection to the SharePoint site.

# Disconnect from the SharePoint site. There is no output for this cmdlet.
Disconnect-PnPOnline -Connection $SharePointConnection

---

Experiment with it and have fun.

Here is a link to the complete script file.

HowTo Update Work Area Properties From SharePoint

Hopefully, you find this useful. Please let me know if you have any questions or comments. If you like this post, please click the Like button at the bottom of the page.