#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 2

January 3, 2018 at 11:10 pm | Posted in Configuration, Customisation, Functionality, Information, Reporting | 8 Comments
Tags: , , , , , , ,

Following on from my first post discussing including HTML formatting for Project Online Power BI Reports, in this post we will look at a summary of options to get the correct data into Power BI then walkthrough one of those options. In part 3, the final part, we will look at one of the other options to get the data.

For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

As per the first post, it is very simple to have the data rendered in Power BI to include the HTML formatting, the slightly more tricky part is to get the Project Online data into Power BI with the HTML included.

First, a bit of background on where in Project Online you can access the enterprise project multiline custom fields with the HTML included. As per the first post, you need to access the REST API ({PWAURL}/_api/ProjectServer) to get the data with the HTML included as the OData Reporting API ({PWAURL}/_api/ProjectData) has had the HTML removed. Using the REST API we can view the endpoints at the root:

SNAGHTML59e8e263

This is the REST API to programmatically interact with the Project Online data, you can create, read, update and delete data using this API depending on your access. For this reporting post we only need to read data, carryout the steps with an account that has access to all projects in the PWA instance like an Admin account.

The endpoint we need is /Projects:

SNAGHTML59ef8e46

This will detail all of the projects the logged on user has access to – it is recommended to carry out these steps with an account that has access to all projects in the PWA instance otherwise you might / will see errors in later steps. For each project detailed you will see a few key project level properties including things like Name, Description, Created Date, ID to name a few. It is also possible to navigate from there using the Project ID to get more details for that project. For example you can get the project tasks using the following URL: ProjectServer/Projects(‘{ProjectGUID}’)/Tasks or get the project team using this URL: ProjectServer/Projects(‘{ProjectGUID}’)/ProjectResources. To get the enterprise project level multiline custom fields we need to use the following URL: ProjectServer/Projects(‘{ProjectGUID}’)/IncludeCustomFields. Accessing this URL for the specified Project GUID (replace the placeholder with an actual project GUID) you will see more properties for that project including the multiline custom fields we need:

SNAGHTML59fcff6f

Notice the HTML in the custom field outlined in red in the image above. You would need to do this call for all projects but using the correct Project GUIDs. Also worth pointing out, in this API the custom fields are referenced using the internal names, for example Custom_x005f_4d0daaaba6ade21193f900155d153dd4 rather than the display names. You can use the custom fields endpoint to map the internal names to the display names: /ProjectServer/CustomFields.

So that covers the background on how you access the multiline custom field data that includes the HTML using the REST API, next we look at how to do this from Power BI. What makes it slightly more tricky than just using the normal OData Reporting API is that you have to make a call dynamically for each project GUID if you are using the REST API directly. In this series of posts we will look at calling this API dynamically straight from Power BI (covered later on in this post) but that has a limitation and also another method to get this data from one call / endpoint but that requires a bit of custom code / a 3rd party tool but does remove the limitation / issue. I will cover off the latter option in the 3rd blog post including a code sample / snippet.

Moving on to Power BI and getting this data dynamically and explaining the limitation. This process with follow the same approach I documented a while back to report on project site data using the the SharePoint list REST API: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/ As per the post above, this will require a custom function and a custom column to call the function. The limitation of this approach is that it works fine in the Power BI Desktop client but the data will not currently refresh in the Power BI App service. There might be workarounds to this limitation but that is beyond the scope of this blog post.

Firstly get a REST URL for one project that includes custom fields, for example I have used this: https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘ad641588-f34b-e511-89e3-00059a3c7a00‘)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4 – replace the parts highlighted in yellow with details from your PWA instance. In this example I have included just one of my multiline custom fields but include as many multiline fields as required, just separate them using a comma. As mentioned before, use the /CustomFields endpoint to identify the correct custom fields to include in the select statement. You can see below, the example multiline field I have used is called “Status Summary”

SNAGHTML5cb3b4d5

Now add this URL as a data source in Power BI using the Get Data > OData feed option. That will open the Query Editor and show the record:

image

Update the Query Name to something like projectHTMLCFsFunction as this query will be turned into a function. In the Query Editor, on the View tab access the Advanced Editor and you will see your query:

SNAGHTML5cc54192

The full query will be similar to this:

let
    Source = OData.Feed("https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘ad641588-f34b-e511-89e3-00059a3c7a00’)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4")
in
    Source

This needs to be modified to turn this into a parameterised function like below, parts highlighted in yellow are added / edited:

let loadHTMLCFs = (GUID as text) =>
    let
        Source = OData.Feed("https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘"&GUID&"‘)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4")
    in
        Source
in  loadHTMLCFs

A screen shot below to show the completed query in the Query Editor as the formatting is clearer, bits added / edited are outlined in red:

SNAGHTML5cc4e299

Click Done in the Query Editor and you will see the following:

image

No need to do anything with the parameter or buttons. Now we need to add another data source in for the other data feeds required in the report, for the purpose of this blog I will just add in the minimum required and that is the default OData Reporting API /Projects endpoint to get the other project fields into the report. In the Query Editor on the Home tab click New Source > OData feed and add in the OData Reporting API URL: https://tenant.sharepoint.com/sites/pwa/_api/ProjectData then select the tables required. For this blog post I have just selected Projects. Using the Query Editor, remove unwanted columns, rename columns etc. You will need to keep at least ProjectId and ProjectType, they are required. For the purpose of the blog post I have just selected ProjectId, ProjectType, ProjectName and ProjectOwnerName. Using ProjectType, filter out ProjectType 7 as this is the Timesheet Project record. Keeping this in the dataset will cause errors later on.

Once you have edited the query as required a new custom column needs to be added to invoke the function created earlier. Click the Add Column tab then click Custom Column. Give the column a name such as GetProjectHTMLCFs then enter the following: projectHTMLCFsFunction([ProjectId]) as seen below:

image

projectHTMLCFsFunction is the name of the function we created earlier and we are passing in the ProjectId. When clicking OK, this might take a while depending on how many projects you have as this will invoke the function for each project and call the REST API, passing in the ProjectId for that row and bring back the records. Once completed you will see the records as below in the new custom column:

SNAGHTML5cdd9337

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the multiline custom fields, in this example I just have one:

image

Click OK and the data will refresh / load then display the data for the multiline columns:

SNAGHTML5ce22300

Notice we have the HTML in the data! Rename the columns for the correct display names then when completed, click Close & Apply. The changes will now be applied to the Power BI Report and load the data. Add in the HTML Viewer custom visual as detailed in blog post 1 then add the data on the the report canvas as you would normally. Ensure that the multiline custom fields use the HTML Viewer custom visual:

image

An example with a normal table visual and the HTML Viewer visual:

image

That’s it, design your Project Status reports to now include the HTML formatting your users have added. Just remember though, this will only refresh in the Power BI Desktop client. It can be published to the Power BI App service but the data will be static and will not update, you would need to open the report in the Power BI Desktop client, refresh it then publish it back into the Power BI service.

Next up in part 3 we will look at a slightly different approach to get the data in Power BI that does enable the report / data to refresh in the Power BI App service.

Advertisements

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 1

January 1, 2018 at 9:40 am | Posted in Configuration, Customisation, Functionality, Information, Reporting | 3 Comments
Tags: , , , , , , ,

My first post for 2018, Happy New Year to all! This post is the first of 2 or 3 posts covering HTML formatting in your Power BI reports from Project Online multiline project level custom fields as seen below – screenshot from mock up / demo data:

image

For those of you that are familiar with the Project Online Reporting API, Microsoft made a change back in May 2016 to remove the HTML from the OData API ({PWAURL}/_api/ProjectData): https://pwmather.wordpress.com/2016/05/30/projectonline-odata-reporting-api-updated-to-remove-html-tags-office365-bi-excel-powerbi/. This was due to requests from customers so that Excel / Power BI reports could contain cleansed data without having to remove the HTML from the strings yourself. As mentioned in the blog post above, the HTML strings for multiline project custom fields are still available from the REST API ({PWAURL}/_api/ProjectServer).

Back in November 2017 a new custom Power BI visual was released to render HTML: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/#HTMLViewer, this now means that you can include the nicely formatted text from Project Online multiline project level custom fields in your Power BI reports. A couple of screen shots below show what your project custom field multiline data probably looks like today in your reports and what it could look like. Ignore the very basic dull looking report, this is purely just to demo the HTML rendering.

Without the HTML formatting from the OData API – it is just a block of text:

image

With the HTML formatting – it is nicely formatted and readable:

image

This matches the text on the Project Detail Page (PDP) in the Project Web App for that example demo project:

image

To be able to include the HTML formatting there are two parts:

  • Get the data that includes the HTML
  • Add the HTML Viewer custom visual to your Power BI Desktop client

The latter being very simple from the Power BI Desktop client by either clicking the ellipsis in the Visualizations pane:

image

Or using the button on the Home ribbon:

image

Then search for the HTML viewer and add it:

image

In the next 1 or 2 posts I will cover some different options for getting access to the data that includes the HTML.

#ProjectServer and #SharePoint 2013 / 2016 November 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

November 16, 2017 at 9:20 am | Posted in Administration, CU, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 November 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 November 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4051890

Project 2016 November 2017 update:
https://support.microsoft.com/en-us/kb/4011227

SharePoint Server 2016 / Project Server 2016 November 2017 update: 
https://support.microsoft.com/en-us/kb/4011243 & https://support.microsoft.com/en-us/kb/4011244

The Office 2013 November 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4051890

Project Server 2013 November 2017 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4011249

Project Server 2013 November 2017 update:
https://support.microsoft.com/en-us/kb/4011257

Project 2013 November 2017 update:
https://support.microsoft.com/en-us/kb/4011235

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the November 2017 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

#Agile now available in #MSProject #Kanban #Scrum #Sprints #Tasks

October 25, 2017 at 7:57 am | Posted in Administration, Configuration, Customisation, Functionality, Information | Comments Off on #Agile now available in #MSProject #Kanban #Scrum #Sprints #Tasks
Tags: , , , ,

Agile is now available in Microsoft Project! This is available on the following build version or later: 8625.2055 Click-to-Run. This build version is available on the Insider – formerly Insider Fast or Monthly Channel (Targeted) – formerly Insider Slow. This post is an intro to the new features. On the New menu you will see three new projects as highlighted below:

image

The three new project types are:

  • Scrum Project
  • Kanban Project
  • Waterfall Project

Firstly, “Waterfall Project” is the traditional type of project that is the same as using “Blank Project”. When creating either of these you will notice some new views in Project:

image

These views are available regardless of the project type selected. I can insert the new Board Status and Sprint fields in to the default Gantt Chart view:

image

Switching views, I can then see these test tasks on the associated board views:

image

image

Both of these provide the ability to add new tasks as well as drag and drop capabilities to move tasks to different columns / statuses. On the board view you can add / update the columns / buckets, once updated here the Board Status column in the table views will have the updated values. Regardless of the view, you can use the Format tab option on the Task Boards Tools ribbon to change the columns:

image

More on these in when we look at the new project types.

Creating a Scrum Project or a Kanban Project gives you some other functionality. Starting with a Scrum Project (purely because it is first in the list) we can see the new project below:

image

The Scrum Project defaults to open with the Sprint Planning Board as the active view. On the ribbon you will also notice there is an Agile Tools Scrum tab as well as the previously seen Task Board Tools Format tab.

image

The Planning button enables you to switch between the two Sprint Planning views:

image

The Sprint button enables you to switch between the two Current Sprint views:

image

The All button enables you to switch to the Agile Tasks view which is a filtered tabular view that only shows tasks that are tagged as Agile = Yes.

Adjust allows you to manage the sprints. This creates three automatically for you:

image

By default the sprints are generated using the project finish date but you can change to manage this via a custom date if needed:

image

The Agile button enables you to change your project to a different Agile methodology:

image

Switching between Scrum / Kanban / None just changes the options you have on the ribbon. Changing to None will remove all the Agile options ribbon but you can use the Project tab to change your project back to an Agile methodology:

image

Tasks can be moved to different sprints or status using the board view with drag and drop or just the familiar drop down menus on the table views.

Switching to Kanban, the options change to enable you to switch backlog views:

image

When creating a new Kanban project, the default view is the Backlog Board.

The All button enables you to switch to the Agile Tasks view which is a filtered tabular view that only shows tasks that are tagged as Agile = Yes. Then you have the same Agile button too.

There are also new default Agile reports:

image

Here is the Agile – Task Status report as an example:

image

This new feature is a client side feature only, this capability is not available in PWA. All of the new fields in Project such as Agile, Sprint and Board Status are in Project only.

If you are looking to use an Agile methodology to manage your projects in Microsoft Project, take a look at this new feature as it allows you to manage a single project using a combination of methodologies if you wish.

#ProjectServer and #SharePoint 2013 / 2016 October 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

October 19, 2017 at 1:32 pm | Posted in Administration, CU, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 October 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 October 2017 updates and cumulative updates are now available – post a bit later than normal, please see the links below:

https://support.microsoft.com/en-us/kb/4043461

Project 2016 October 2017 update:
https://support.microsoft.com/en-us/kb/4011141

SharePoint Server 2016 / Project Server 2016 October 2017 update: 
https://support.microsoft.com/en-us/kb/4011217 & https://support.microsoft.com/en-us/kb/4011161

The Office 2013 October 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4043461

Project Server 2013 October 2017 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4011175

Project Server 2013 October 2017 update:
https://support.microsoft.com/en-us/kb/4011182

Project 2013 October 2017 update:
https://support.microsoft.com/en-us/kb/4011156

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the October 2017 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

#ProjectServer and #SharePoint 2013 / 2016 September 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

September 13, 2017 at 9:24 pm | Posted in Administration, CU, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 September 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 September 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4040279

Project 2016 September 2017 update:
https://support.microsoft.com/en-us/kb/4011101

SharePoint Server 2016 / Project Server 2016 September 2017 update: 
https://support.microsoft.com/en-us/kb/4011127 & https://support.microsoft.com/en-us/kb/4011112

The Office 2013 September 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4040279

Project Server 2013 September 2017 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4011115

Project Server 2013 September 2017 update:
https://support.microsoft.com/en-us/kb/4011118

Project 2013 September 2017 update:
https://support.microsoft.com/en-us/kb/4011109

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the September 2017 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

Link #ProjectOnline tasks to #Planner #Microsoft365 #PPM #PMOT #MSProject

August 29, 2017 at 4:04 pm | Posted in Administration, Functionality, Information | Comments Off on Link #ProjectOnline tasks to #Planner #Microsoft365 #PPM #PMOT #MSProject
Tags: , , , , ,

In the latest release of Project Online Desktop Client, depending on the release channel that you are on, you might have noticed the new Planner button in the Task ribbon:

image

My Office version is on the Office Insider Fast channel so that I get the latest changes first but this will typically be controlled by your IT admins so you might need to wait a while until this feature reaches the Office release channel that you are on.

Hovering over the new button provides the details for this feature:

image

This feature allows you to create a hyperlink to the linked Planner Plan from the Planner icon in the indicators column. It could be that you have a bucket type task (sprint etc.) in the Project Online project then the detail tasks / activities might be in the linked Planner plan.

Select a task in the project then click the Planner button and a side pane will launch:

image

Click the “Link to existing Planner plan..” link then you will be able to type the name of the Office 365 Group that contains the Planner plan:

image

Start typing name of the group then select the correct group, in this example I have one called Pauls Test Plan:

image

Notice how it also states that it will add the resources assigned to the task into the group. Click the Create Link button:

image

It then shows that this task is now linked to that Planner plan:

image

You then get the hyperlink directly to the linked Planner Plan using the Planner icon in the indicators column or using the link in the Link to plan pane.

You can only link one task from the project plan to one Planner plan, if you try and link another task to the same Planner plan you will see this alert:

image

The resources added to the linked task didn’t get added to the group as suggested but as this feature is only in the Office Insider builds that might come when this is released in the other release channels. ***Update – this feature does work providing the PWA Resource email address matches the O365 user principle name. I believe other options are being explored***

For release details, see the article here: https://support.office.com/en-us/article/What-s-new-in-Project-2016-111bcaf9-bc27-4c15-80e6-85e726307520?ui=en-US&rs=en-US&ad=US#Audience=Office_Insiders

#ProjectServer and #SharePoint 2013 / 2016 August 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

August 10, 2017 at 7:46 am | Posted in Administration, CU, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 August 2017 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 August 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4036121

Project 2016 August 2017 update:
https://support.microsoft.com/en-us/kb/4011034

SharePoint Server 2016 / Project Server 2016 August 2017 update: 
https://support.microsoft.com/en-us/kb/4011049 & https://support.microsoft.com/en-us/kb/4011053

The Office 2013 August 2017 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4036121

Project Server 2013 August 2017 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4011074

Project Server 2013 August 2017 update:
https://support.microsoft.com/en-us/kb/4011082

Project 2013 August 2017 update:
https://support.microsoft.com/en-us/kb/4011084

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the August 2017 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

Running #ProjectOnline #PowerShell in #Azure using #AzureFunctions #PPM #Cloud #Flow #LogicApp Part2

August 1, 2017 at 4:32 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell, Workflow | Comments Off on Running #ProjectOnline #PowerShell in #Azure using #AzureFunctions #PPM #Cloud #Flow #LogicApp Part2
Tags: , , , , , , ,

Following on from part 1 where I introduced the idea of automating certain Microsoft 365 PPM Project Online customisations using PowerShell, Microsoft Flow / Azure Logic Apps and Azure Functions, in part 2 I will set up an example automation for creating a Project Online event driven snapshot application on project published without having to set up any server or write any complied code! If you missed part 1 where this concept was introduced, see the link below:

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

Firstly I created an Azure Function app in my Azure subscription then created a new function based on the HttpTrigger – PowerShell template:

image

Give the function a name and set the Authorisation level – set the authorisation level to the correct setting for your function. Click Create. For details on Azure Functions, start here: https://docs.microsoft.com/en-us/azure/azure-functions/

You will then be presented with the function and sample code:

image

We will now create the PowerShell script to create the snapshot. This is based on a script I published a while back: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/

The script has been updated to work in an Azure Function but also modified to use a parameter so that it only captures data for the published project, the PowerShell script can be seen further on in the post.

Firstly upload the SharePoint CSOM DLLs using the upload button:

image

I used the SharePoint CSOM DLLs from the SharePoint Online Management Shell:

image

Then enter the PowerShell code – screen shots below and code pasted below the images:

image

image

image

Code sample used in function:

# 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\ProjectSiteUserSyncHTTPTrigger\Microsoft.SharePoint.Client.dll"
Import-Module "D:\home\site\wwwroot\ProjectSiteUserSyncHTTPTrigger\Microsoft.SharePoint.Client.Runtime.dll"

#set the environment details
$PWAInstanceURL = "https://mod497254.sharepoint.com/sites/PWA2"
$username = "admin@MOD497254.onmicrosoft.com" 
$password = "password"
$securePass = ConvertTo-SecureString $password -AsPlainText -Force
#create the SharePoint list on the PWA site and add the correct columns based on the data required
$listname = "ProjectSnapShots"
$results1 = @()

#set the Odata URL with the correct project fields needed
$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectId eq GUID'$projID'&`$Select=ProjectId,ProjectName,ProjectPercentCompleted"

#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
    }
}

#add data to snapshot list
#get PWA site client context
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($PWAInstanceURL) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass) 
$ctx.Credentials = $credentials 
$ctx.ExecuteQuery()  
 
#get the target list 
$List = $ctx.Web.Lists.GetByTitle($listname) 
$ctx.Load($List) 
$ctx.ExecuteQuery() 

#for each project, create the list item - update the newitem with the correct list columns and project data
foreach ($projectrow in $results1) 
{ 
   $itemcreationInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation 
   $newitem = $List.AddItem($itemcreationInfo) 
   $newitem["Title"] = $projectrow.ProjectName
   $newitem["ProjectId"] = $projectrow.ProjectId
   $newitem["PercentCompleted"] = $projectrow.ProjectPercentCompleted
   $newitem.Update() 
   $ctx.ExecuteQuery() 
} 

The PowerShell code would need to be updated with your environment details: (PWAInstanceUrl, username, password and listname variables). Also the OData URL will need to be updated to include the project level fields that you want to snapshot.The target SharePoint list will also need to be set up in the PWA site collection for the project fields the script uses. This is the list I set up for this example:

image

SnapshotDate is set to Todays date so we don’t need to set that in the code.

The code is simple to follow but in summary the first part will get the projID from request body – we will pass in the ProjectID for the published project from the Flow / Logic App trigger. Then the SharePoint Online CSOM DLLs are imported in. Then the specific PWA environment details are set for the variables. The OData URL is then added to the url variable. Here notice we are filtering for the ProjectID and passing in the $projID variable we get from the request body. The Select part of the query will need to be updated for your project level fields. Next the code gets the data from the OData feed using the web request and adds the data into the results array. Once we have the data, we connect to the SharePoint list, in the example it is the ProjectSnapShots as set in the $listname variable. Lastly the new item is created in the list using the data from the results array.

Now the Azure Function is ready to be used. It can be tested using the Test option in the right hand panel, update the Request body:

image

Update it for a valid project ID. Then click Run above the function code:

image

The Logs window below will help you debug any errors etc.:

image

Then check the SharePoint list in the PWA site and the new item should have been created:

image

We now know the Azure Function is working as expected, now we need to call the Azure function when a project is published. All we need from the Azure Function is the URL to use, use the </>Get function URL button:

image

Select the correct Key, in this example I used the default function key. Copy the URL as it will be needed later.

To call the Azure Function when a project is published, the choice here for a no code option would be Microsoft Flow or Azure Logic App. For this I will use Microsoft Flow but the same steps (triggers , actions etc.) would be used in the Azure Logic App. Create a new Flow and search for Project Online:

image

Then select the Project Online – When a project is published trigger.

Enter the PWA URL:

image

Then click the ellipsis and set the connection for the PWA URL or create a new connection if needed:

image

Click + New step then Add an Action and search Http:

image

Select HTTP – HTTP:

image

Complete the HTTP action:

Method is POST, the Uri is the URL for the function that we copied earlier, Headers are not required. The Body is where we pass in the project ID from the published project trigger:

image

The Flow is now completed:

SNAGHTML55ecdca7

Now click Save flow.

In PWA, Publish a project or projects and see the snapshot data created on the configured snapshot list once the Flow has run:

Flow run:

image

Data added to the list for the project I published – in this example it was the Office 2016 rollout project:

image

This just shows a simple example and the some of the possibilities for extending the Project Online capability when making use of simple PowerShell scripts and other Microsoft 365 / Azure services for cloud / serverless solutions. Look out for more examples in the future.

Running #ProjectOnline #PowerShell in #Azure using #AzureFunctions #PPM #Cloud #Flow #LogicApp Part1

July 28, 2017 at 4:50 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Fixes, Functionality, Information, PowerShell, Workflow | 4 Comments
Tags: , , , , , ,

Following on from my last post where I published an example solution starter PowerShell script for adding project team users to the Project Site, here I mentioned about running the script in an Azure Function and even running this sync from a Project Online event. The blog post can be seen below if you missed that:

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

Whilst I will use that example PowerShell script from my last blog post as an example, the concept will work for any PowerShell script.

I wont cover the details in setting up the Azure Function in part 1 as there is plenty of support out there for this – for this example I created an Http Trigger – PowerShell function.

image

I then uploaded the SharePoint DLLs and copied in the PowerShell script into the editor:

image

The project site user sync script didn’t work as is as I had to make some minor changes to get this to run from the Azure Function. This included change the way the SharePoint CSOM DLLs where loaded in. In the example script I used Add-Type to load the DLLs but in the Azure Function I had to switch this out to use Import-Module:

image

The next change I had to make was to comment out all of the feedback to the console, so all of the write-hosts. I also had to remove the functions within the script so that is was one block. After these changes I could execute the PowerShell script to add the project team members from my example project into the associated project site from the Azure Function. As this was an HTTP Trigger Azure Function, you can get the URL to the function and access that URL to execute the function.

This opens up lots of possibilities to easily execute this Azure Function from other applications that can make the HTTP call. For example you build easily execute this script once the project has been published either using a remote event receiver (RER) or a Microsoft Flow / Azure Logic App. The example script would need to be made generic and pass in a variable into the Azure Function for it to be a workable solution.

In part 2 of this blog post we will look at make a full event driven solution that is fired on project publish then executing the Azure Function and passing in a variable.

« Previous PageNext Page »

Create a free website or blog at WordPress.com.
Entries and comments feeds.