HowTo: Create New Work Area Using SharePoint Data

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.

In this post, I’m going to expand on what was done in the previous post, HowTo: Update Work Area Properties From SharePoint. In the previous post we gathered data from SharePoint, and based on the ProjectInProjectWise field value (Yes/No), we would update the Work Area properties for the corresponding Work Area. In this post, if the value in the ProjectInProjectWise field is No, we are going to create a new Work Area, update the Work Area properties, and update the description.

Again, 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. 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. Refer to the previous post for instructions on importing the HelperFunctions and installing the SharePointPnPPowerShell2013 module.

The following are the cmdlets / 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 Get-PWRichProjectProperties -Full 
Get-Help -Name New-PWRichProject -Full 
Get-Help -Name Update-PWFolderNameProps -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 will only be dealing with the third item. We will be creating a new Work Area / Project within ProjectWise and updating  the corresponding Work Area / Project properties.

The following image shows the third item. Notice the ProjectInProjectWise value is set to No. Once we create the new Work Area we we will update this setting to be Yes indicating a Work Area / Project exists within ProjectWise.

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!!

The following shows we have successfully created a connection with the SharePoint site.

# The SharePoint List we want to return data for.
$SPListName = 'ProjectWise_Work_Area_Properties'
# Get the desired data.
$SPItems = Get-PnpListitem -List $SPListName

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

As we did in the previous post, we are going to create a Work Area Properties class containing six properties which we will store the SharePoint data in. 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, we will continue on to the next entry. If the project does not exist, we will create a new Work Area / Project and update the properties accordingly.  We will also need to update the Work Area / Project description. Once, complete, we will set the ProjectInProjectWise value within SharePoint to Yes, indicating there is now a corresponding Work Area / Project.

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

<# Loop through each item in the array list and determine if the 
    specified project exists within ProjectWise. If it does, 
    continue to the next item. If it does not exist, create a 
    new Work Area / Project and update the corresponding
    Work Area / Project properties and desciption. 
    Set the ProjectInProjectWise item to Yes once a project has been created. #>
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 a Projects folder. #>
    $Splat_Project = @{
        FolderPath = "Projects"
        FolderName = $item.Number
    }

    <# Attempt to get the project within ProjectWise. If an object is
        returned, the project exists. Continue to the next item. #>
    try { 
        $pwProject = Get-PWRichProjects @Splat_Project -JustOne -ErrorAction Stop
        continue
    } catch {
        Write-Warning -Message "Project '$($item.Number)'; Need to create."
    } # 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
    }

    #region Create new Work Area / Project

    <# We will create our new Work Area / Project in the Projects folder. 
        Include Work Area / Project properties.#>
    $Splat_NewWorkArea = @{
        NewFolderPath = "Projects\$($item.Number)"
        ProjectType = 'MyWorkArea'
        StorageArea = 'Storage'
        ProjectProperties = $Properties
    }
    try { 
        $pwNewProject = New-PWRichProject @Splat_NewWorkArea -ErrorAction Stop
    } catch {
        Write-Warning -Message "Error occurred while attempting to create new Work Area / Project. $($Error[0].Exception.Message)"
    }

    #endregion Create new Work Area / Project

    #region Update the new project description within ProjectWise.
    ... (See below for code.)
    #endregion Update the new project description within ProjectWise.

    #region Set SharePoint ProjectInProjectWise value to Yes (True). 
    ... (See below for code.)
    #endregion Set SharePoint ProjectInProjectWise value to Yes (True).

} # end foreach ($item...

Now that we’ve created our new Work Area / Project, you can see that the folder description has not been populated.

However, the Work Area / Project properties have been.

Update the folder description.

#region Update the new project description within ProjectWise.

try {
    $Splat_UpdateDescription = @{
        FolderID = $pwNewProject.ProjectID
        NewDescription = $item.Name
    }
    $Result = Update-PWFolderNameProps @Splat_UpdateDescription -ErrorAction Stop
} catch {
    Write-Warning -Message "Error occurred while attempting to update the Project description. $($Error[0].Exception.Message)"
}

#endregion Update the new project description within ProjectWise.

Update SharePoint

Now that we have the new Work Area / Project created, we can update the SharePoint field value for ProjectInProjectWise to Yes.

#region Set SharePoint ProjectInProjectWise value to Yes (True). 

try {
    $Splat_SetSPItem = @{
        List = $SPListName
        Identity = $SPItems[$item.SharePointIndex]
        Values = @{ProjectInProjectWise = "$true"}
    }
    Set-PnPListItem @Splat_SetSPItem -ErrorAction Stop
} catch {
    Write-Warning -Message "Error occurred while attempting to set the InProjectWise value within SharePoint. $($Error[0].Exception.Message)"
}

#endregion Set SharePoint ProjectInProjectWise value to Yes (True).

The following shows the output within PowerShell.

The following shows that the field value was updated 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 Create New Work Area Using SharePoint Data

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.