Handling pagination in #PowerAutomate #SharePoint HTTP flow action for #ProjectOnline flows #Office365 #Automation #Workflows #PowerPlatform #PowerAddicts #NoCode #LessCodeMorePower #MSProject

When building Power Automate flows for Office 365 Project Online the Project Online connector has it’s limitations but using the SharePoint “Send an HTTP request to SharePoint” action opens up lots of options.

HTTP SP Action

I’ve been using this action in may of the example Power Automate flows I’ve blogged about over the last few years. Using this action you can easily access the Project Online Report API – ({pwaSite}/_api/ProjectData) or the Project Online CSOM REST API – ({pwaSite}/_api/ProjectServer). One thing I always state in my Power Automate flows when using the “Send an HTTP request to SharePoint” action is that you need to handling the pagination for the Project Online Reporting API in the flow. The page limits of each endpoint can be found here: https://docs.microsoft.com/en-us/previous-versions/office/project-odata/jj163015(v=office.15). In the Microsoft Docs you can see that each ProjectData endpoint is limited in the number of records it will return per page, for example Projects returns 300 projects per page. So if you have 600 projects in your PWA site collection, without handling the pagination when using the /_api/ProjectData/Projects endpoint in a “Send an HTTP request to SharePoint” action, your flow will only ever have access to 300 projects. Unlike the default “HTTP” action, there is no option to get the “Send an HTTP request to SharePoint” action to handle the pagination out of the box. The “HTTP” action is a premium connection that enables you to access HTTP APIs.

HTTP action

This HTTP action does have a setting to handle pagination:

HTTP Settings

Unfortunately there no equivalent setting available on the SharePoint HTTP Power Automate action so we have to build the logic into the flow using other Power Automate actions. With the power of Power Automate it is very simple to build this logic into your Power Automate flows using a couple of additional actions – a Do Until and a Condition action. The full sample flow that handles pagination can be seen below:

Full flow

Each action is detailed below including where you would build in your flow logic as this example flow doesn’t do anything part from build an array of project IDs. The first action is to create a variable and set the initial value:

uri query variable

This is used to define the query used in the SharePoint HTTP action later in the flow. As this is just to show how to handle pagination, my example query is very basic just to return some project level data where the project type is not equal to 7 (the timesheet admin project line). The next action I have in my flow is to create a new array variable to store the project IDs returned from each batch of results (each page) but this might not be needed in your flow as it depends what your flow will do. My flow is just a demo to show how to handle paging so I’m just pushing the project IDs to an array.

array variable

The third action is a Do Until action, this is used to create a loop in the flow until the criteria for the do until loop is met.

Do Until

Here I’m using the nextLinkUri variable and checking for the the variable value being set to “Completed”. The actions in this Do Until will repeat until the nextLinkUri variable is equal to “Completed”. The first action inside the Do Until loop is a “Send an HTTP request to SharePoint” action. This action is used to query the Project Online Reporting API. The Uri is set using the nextLinkUri variable:

SP HTTP

This action will return JSON data, the JSON data returned will depend on the select query used in the uri query. The next action is a parse JSON action to make it easier to use the JSON data returned from the SharePoint HTTP action.

parse JSON

The data from the HTTP action is passed in using “body(‘getAllProjects’)” where getAllProjects is the name of the SharePoint HTTP action. The schema used in the parse JSON action will depend on the properties included in the select query in the uri. The third action inside the Do Until action is an Apply to each action, this is where your flow logic would go. For example, inside the Apply to each action you might have other actions to get certain project tasks or list data from from the project site.

Apply to each

In this example I’m passing in the value property from the previous parse JSON action into the Apply to each action. As mentioned earlier, as this is just a demo flow to show a technique for handling pagination, the only action inside the Apply to each loop is an action to append the ProjectID from the parsed JSON into the projectData array. Your flows would have other actions in place of the appendProjectIdToArray action I have here, the actions will depend on your flow requirements. As mentioned before, this is where you might have actions to interact with the project tasks or project site data for example.

The final action inside the Do Until is a Condition action that executes after the Apply to each action. This is used to check if there is a next link property value in the results from the Project Data API call. Here we use the odata.nextlink from the parsed JSON output, if there is a value this means that there is another batch / page of data to retrieve from the API. If there is no nextlink property this means all of the data is returned from the API.

Condition check

If there is another page of data the odata.nextlink property contains the full URL to get the next batch of data. The full URL is not required so this needs to be trimmed when setting the nextLinkUri variable to remove the site collection URL using an expression:

substring exression

The expression is using the substring function to trim the site collection URL from the odata.nextlink property value, in this example the full expression is “substring(body(‘parseGetAllProjectsJson’)?[‘odata.nextLink’],46)”. Depending on the tenant name and site collection name, this will determine the number of characters to trim off, in my example it was 46. If there is no more data to retrieve from the API, the nextLinkUri variable is set to “Completed”.

That’s it, a nice simple technique to handle pagination for the Project Online _api/ProjectData API in a Power Automate flow when using the SharePoint HTTP action. I have made this solution starter available on GitHub: https://github.com/pwmather/ProjectOnline-SharePointOnline_PowerAutomate_HTTP_Pagination to download. I hope this helps.

Comments are closed.

Blog at WordPress.com.

Up ↑

%d bloggers like this: