Project Online: Setting Permissions in sites beyond PWA


Thanks goes to Paul Mather for most of the technical content of this particular post – as I’m taking a couple of his ideas and bringing them together.  I'm using PowerShell to set permissions in project sites and using Flow and Azure Functions to automate running that PowerShell.  I’ve also included a video that walks through this.  The original recording was for an internal presentation – so I’ve edited it down a bit.

First I’ll introduce the scenario.  With the recent announcement that Project Online can now support 30,000 projects this was in part achieved by now allowing sites for your projects to be created in site collections outside of the ‘PWA’ site collection.  So in my example I have /sites/pwa as my main Project Web App site collection – but I can create sites in /sites/pwasites.  The recommended limit for subsites in a site collection is 2000, and going beyond this does impact the performance of Project Online – and if you also have the SharePoint publishing features activated it can seriously impact every single page load in PWA.  Expect some guidance from the product group shortly that suggests having no subsites at all in your PWA site!  Limitations when you move sites to another site collection are that you no longer can synchronize permissions to the site based on the project stakeholders; you also cannot sync tasks to these sites – and you also must be in Project Permissions mode.  This article looks to address the first of those.

Here is the video – but I’ll also talk this through in words further down and have the sample code so you can try this for yourselves.

I broad terms I’m triggering a Flow on publish from Project Online, then writing some data to a Planner plan (unnecessary step – but shows how you might also write status during the process) and then hitting the Azure Function Url and passing in the PROJ_UID for the published project.- so the flow looks like this:

image

So the first part is monitoring my pwa for publishes, then if the Project Type is 0 (a normal project) it writes ProjectName, ProjectType and ProjectId to my PublishedProjects plan in my To do bucket and then makes the HTTP call:

image

This is making a POST to my Azure Function and passing in the body some json that contains the ProjectId.

First the PowerShell code in my Function reads this ProjectId from the incoming request, references the module it needs (which are also loaded into the Azure Function environment) and sets up the environment – including forming the OData Url to pull the ProjectName and ProjectWorkspaceInternalUrl for the specific project.

# POST method: $req
$requestBody = Get-Content $req -Raw | ConvertFrom-Json
$projID = $requestBody.projID


# GET method: each querystring parameter is its own variable
if ($req_query_name)
{
$projID = $req_query_name
}


#add SharePoint Online DLL - update the location if required
Import-Module "D:\Home\site\wwwroot\HttpTriggerPowerShell1\Microsoft.SharePoint.Client.dll"
Import-Module "D:\Home\site\wwwroot\HttpTriggerPowerShell1\Microsoft.SharePoint.Client.Runtime.dll"


#set the environment details
$PWAInstanceURL = "
https://brismithpjo.sharepoint.com/sites/pwa"

$username = "brismith@brismithpjo.onmicrosoft.com"
$password = "<password>"
$securePass = ConvertTo-SecureString $password -AsPlainText -Force


#set the Odata URL to get the Workspace Url
$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectId eq GUID'$projID'&`$Select=ProjectName, ProjectWorkspaceInternalUrl"

Once we have this set then the next stage is to call the Odata Url and get that data.  I’m using a ForEach even though this will only return a single row – which makes the code re-usable in other scenarios too where you might be pulling more data.  I get my name and site Url as variables and also use these to construct my group name that I will be adding my users to – which will fit the default SahrePoint group of <Projectname> Members.  This assume that the project name is the same as the site name – be careful here as that may not be a safe assumption depending on character set and any special characters used.

#get all of the data from the OData URL
while ($url){
[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass);
$webrequest = [System.Net.WebRequest]::Create($url)
$webrequest.Credentials = $spocreds
$webrequest.Accept = "application/json;odata=verbose"
$webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $webrequest.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()
$results = ConvertFrom-Json -InputObject $data
$results1 += $results.d.results
if ($results.d.__next){
$url=$results.d.__next.ToString()
}
else {
$url=$null
}
}


#for each project, create the list item - update the newitem with the correct list columns and project data
foreach ($projectrow in $results1)
{
$projectSiteURL = $projectrow.ProjectWorkspaceInternalUrl
$groupName = $projectrow.ProjectName + " Members"
}

Next I’m checking the site Url to see if this is in my pwasites site collection – as these are the only ones I’m interested in.  One thought I had was trying to make this differentiation in Flow and not even calling the function if I don’t need it – you can take that as homework.  For those projects whose site is in pwasites I then make a REST call to the ProjectServer endpoint to get the Ids of the resources in the project.  For each team member I then get their account and name from Odata – making sure I’m only getting ones that actually have a ResourceNTAccount – so taking my @team array and filtering down to a @teamusers array of ones that have accounts so I can add them to the SharePoint site.  The final step is making a SharePoint CSOM call adding my users to the group.

#Filter only for projects that are external - if $projectSiteURL contains pwasites
if ($projectSiteURL -like '*pwasites*'){


     #get all of the team members  - will include resources that are not users too.
$team = @()


        #set the REST URL project
$url = $PWAInstanceURL + "/_api/ProjectServer/Projects('$projID')/ProjectResources()?`$Select=Id"


        #get all of the data from the REST URL for the Project Team
while ($url){
[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass);
$webrequest = [System.Net.WebRequest]::Create($url)
$webrequest.Credentials = $spocreds
$webrequest.Accept = "application/json;odata=verbose"
$webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $webrequest.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()
$results = ConvertFrom-Json -InputObject $data
$team += $results.d.results
if ($results.d.__next){
$url=$results.d.__next.ToString()
}
else {
$url=$null
}
}


    #get all of the team members login accounts and remove resources that are not users in PWA.
$teamusers = @()


        Foreach ($teammember in $team)
{
#set the resource ID
$teammemberID = $teammember.Id


                #set up the Odata URL
#$url = $PWAInstanceURL + "/_api/ProjectServer/EnterpriseResources('$teammemberID')/User?`$Select=LoginName,Id" #alternative to OData
$url = $PWAInstanceURL + "/_api/ProjectData/Resources()?`$Select=ResourceNTAccount,ResourceName&`$Filter=ResourceId eq guid'$teammemberID' and ResourceNTAccount ne null"


                #get all of the data from the OData URL for the project team members that are users in PWA
[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass);
$webrequest = [System.Net.WebRequest]::Create($url)
$webrequest.Credentials = $spocreds
$webrequest.Accept = "application/json;odata=verbose"
$webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $webrequest.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()
$results = ConvertFrom-Json -InputObject $data
$teamusers += $results.d.results
}


    #add the user to the project site
Foreach ($teamuser in $teamusers)
{
$teamuserLogin = $teamuser.ResourceNTAccount
$teamusername = $teamuser.ResourceName

#get SP site client context
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($projectSiteURL)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass)
$ctx.Credentials = $credentials


                #get all the site groups on the Project Site
$projSiteGroups = $ctx.Web.SiteGroups
$ctx.Load.($projSiteGroups)


                #get the correct group to add the user into
$projSiteGroup = $projSiteGroups.GetByName($groupName)
$ctx.Load($projSiteGroup)

#add the user to the group on the Project Site
$projSiteUser = $ctx.Web.EnsureUser($teamuserLogin)
$ctx.Load($projSiteUser)
$teamMemberToAdd = $projSiteGroup.Users.AddUser($projSiteUser)
$ctx.Load($teamMemberToAdd)
$ctx.ExecuteQuery()
}
}

To test your function you can manually give it the project GUID of a known project with a site in your 'pwasites' site - simply using the json format:

{
  "projID": "77c4992f-562f-e711-80d3-00155de84000"
}

And once tested and working then use the Url for your function in the Flow HTTP step.  Get function Url is a link at top right of your Azure function editing page.

You could probably use Azure Automation for this too as I’m using PowerShell – and run this on a timer rather than triggered by Flow.  Really depends on your requirements.  I’ve also simplistically added all users to members, but you could potentially do a more granular setting of permissions and add to other group.  Subsequent publishes will add new users – but I don’t handle removing users who no longer have access.

Thanks again to Paul Mather – and these are his blog posts that inspired this post and video.

https://pwmather.wordpress.com/2017/07/07/projectonline-project-user-sync-to-project-sites-ppm-o365-powershell-sharepoint/

https://pwmather.wordpress.com/2017/07/28/running-projectonline-powershell-in-azure-using-azurefunctions-ppm-cloud-flow-logicapp-part1/

https://pwmather.wordpress.com/2017/08/01/running-projectonline-powershell-in-azure-using-azurefunctions-ppm-cloud-flow-logicapp-part2/

Comments (0)

Skip to main content