• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

SharePoint Online

How to Encode SharePoint Column Display Names to Internal names

June 22, 2021 by Paulie Leave a Comment

When you create a field in a SharePoint list it creates two things:

  • An Internal name
  • A display name, for use in views forms etc.

It is simple to change the display name, but the internal name can never be changed. Sometimes, when you create a column, if the column name contains certain characters, it will be encoded. For example, Paulie’s Test List would become Paulie_x0027_s_x0020_Test_x0020_List.

For this reason I always like to create my field names using camelCase such as pauliesTestList and then modify the display name afterwards. But if you’re working with SharePoint, you are going to come across field names like this on a regular basis.

For much more information on internal column names, check out this useful post written by Tom Riha.

SharePoint Column Name Converter

To make it super easy for you, simply type the name of your SharePoint column here and the correct escaped name will be generated for you in the box below.


List of SharePoint Character Mappings

Below is a complete list of characters that are escaped in SharePoint Column names. I hope you find it useful.

CharacterDescriptionHex CodeInternal SharePoint Code
Space20_x0020_
~Equivalency sign – tilde7E_x007e_
!Exclamation mark!_x0021_
@At symbol40_x0040_
#Number23_x0023_
$Dollar24_x0024_
%Per cent sign25_x0025_
^Caret – circumflex5E_x005E_
&Ampersand26_x0026_
*Asterisk2A_x002a_
(Open parenthesis (or open bracket)28_x0028_
)Close parenthesis (or close bracket)29_x0029_
+Plus2B_x002B_
–Minus or Hyphen2D_x002D_
=Equals3D_x003D_
{Opening brace7B_x007B_
}Closing brace7D_x007D_
:Colon3A_x003A_
“Double quotes (or speech marks)22_x0022_
|Vertical bar or Pipe7C_x007C_
;Semi Coloin3A_x003A_
‘Single Quote27_x0027_
\Backslash5C_x005C_
<Less than (or open angled bracket)3C_x003C_
>Greater than (or close angled bracket)3E_x003E_
?Question mark3F_x003F_
,Comma2C_x002C_
.Period, dot or full stop2E_x002E_
/Slash or divide2F_x002F_
`Grave accent or back tick60_x0060_

Filed Under: Office 365 Tagged With: SharePoint Online

Super Simple flow to get more than 5000 SharePoint Items

December 9, 2020 by Paulie 52 Comments

I’ve done a series of blog posts on how to get more than 5000 items from a SharePoint list using Power Automate. While helping someone else out I figured out a way to make it much easier.

If you’re not interested in knowing how it works, just download the flow and reconfigure it for your environment.

Overview

In this example the flow is triggered by PowerApps, but the same method could be used for any trigger. Let’s take a look at the complete flow and then I will explain each step in detail:

Super easy flow to get more than 5,000 items from a SharePoint list

Step by Step Explanation

Here is a explanation of every step of the flow in the order they appear in the screenshot above

  1. Trigger Action
    Is the flow trigger action. This could be PowerApps, the HTTP connector or anything that suits your situation.
  2. Compose Action ‘querySettings’
    This is a compose action which specifies the query settings for the flow.
    Image of a Compose Action which specifies the query settings for the SharePoint API Query
    It has two properties:
    • listName – Specifies the name of the SharePoint list that you want to retrieve items from.
    • fields – Is a comma separated list of fields that will be retrieved from the SharePoint list.
  3. Send an HTTP Request to SharePoint ‘getLowestID’
    This step gets the ID of the first record in the SharePoint List.
  4. Send an HTTP Request to SharePoint ‘getHighestID’
    Gets the ID of the last record in the SharePoint List.
  5. Compose Action ‘threads’
    This flow is going to execute multiple simultaneous queries to SharePoint. Each of which will return a maximum of 5,000 records. Specify the number of threads you will need to cover the number of records that you have in total.
    For example, if you have 9,000 records you will need 2 threads. If you have 23,000 you will need 5 threads. For example:
    [ 0, 1, 2, 3, 4 ]
  6. Compose Action ‘itemsPerThread’
    This action calculates the number of items to fetch in each thread. If you have 9,000 items it will get 4,500 items per thread. The expression is:
    add(div(sub(body('getHighestID')?['value'][0]['ID'],body('getLowestID')?['value'][0]['ID']), length(variables('threads'))),1 )
  7. Apply to each
    The apply to each action is where the SharePoint queries take place. It is important to configure the concurrency options on this step to ensure it is set to at least the number of threads you have configured:
    Image showing how to specify apply to each concurrency
    Within the apply to each the following actions take place:
    1. Compose action ‘lowID’
      Calculates the lowest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), item()))
    2. Compose action ‘highID’
      Calculates the highest ID to find in the current iteration:
      add(body('getLowestID')?['value'][0]['ID'], mul(outputs('itemsPerThread'), add(item(), 1))
    3. Send an HTTP Request to SharePoint
      This is where the actual SharePoint query takes place. Using the inputs from the previous steps. The expression in the Uri field is:
      _api/Web/Lists/GetByTitle('@{outputs('querySettings')['listName']}')/[email protected]{outputs('querySettings')['fields']}&$filter=ID ge @{outputs('lowID')} and ID le @{outputs('highID')}&$top=5000
      Image of SharePoint HTTP Action being used to collect 5000 records from SharePoint
  8. Compose Action ‘Sample Output’
    This action isn’t required for the flow to run. It is used to output two records from the first iteration of the SharePoint API query. This can be used to generate the required JSON Schema in the final step.
    take(outputs('querySharepoint')[0]['body']['value'],2)
  9. Response Action
    This is used to send the data back to PowerApps. You need to modify the Response Body JSON Schema. If you run the flow once you can use the output of the “Sample Data” step and use the “Generate from Sample” function.

    The body content unions all of the data that is returned from SharePoint into a single array. This will need to be adjusted according to the number of threads that you have configured:
union(
outputs('querySharepoint')[0]['body']['value'],
outputs('querySharepoint')[1]['body']['value'],
outputs('querySharepoint')[2]['body']['value'],
outputs('querySharepoint')[3]['body']['value']
)

Conclusion

This is an incredibly easy way to get more than 5000 items from a SharePoint list. It is also very fast. I was able to get 17,000 rows in 5 seconds and someone I am working with was able to get 5,000 rows in two seconds!

Download the Flow

This flow is simple enough that you don’t really need to understand how it works, you can just import it into your environment and reconfigure to your needs.

Download the flow here and import it into your environment.

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

Combine SharePoint API & Flow Parallel branches for Performance

April 19, 2020 by Paulie 8 Comments

This post covers how to configure a Power Automate Flow to get items from a SharePoint list at maximum speed. Super useful if you have an interactive process and you do not want to keep users waiting.

This post is part of a three part series of blog posts, before proceeding please read this post which describes the objectives, pros and cons of each method. It also provides links to the exported flows and sample data which I used to build the flows:

The many ways to retrieve more than 5,000 items from a SharePoint list using Power Automate

If you have not used a parallel branch before, this blog post is worth reading.

Before getting into the technicalities, have a look at the complete flow. If you have already built the more SharePoint API version detailed in this post, it will make sense.

Image of an entire Power Automate Flow that uses Parallel Branches to get records from a SharePoint list

General Premise

This flow retrieves the SharePoint items using the same method as the flow provided in this post. But the work of collecting data from SharePoint is split over four parallel branches, which execute at the same time. Instead of waiting for a single batch of 5,000 to be collected before proceeding to get the next batch.

Changes from the SharePoint API Flow

Several changes were made to the SharePoint API flow:

Variable Declarations

There are some extra values declared and defined at the start of the flow which control how the four branches choose which section of data to return:

Get the Lowest ID from the SharePoint list using the REST API

The lowest ID from the list is retrieved using the HTTP request to SharePoint action and uses the following code:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID asc
Image showing Sharepoint API being used to find the lowest ID of a row in a SharePoint list

Get the Highest ID from SharePoint list using the REST API

Same as the above flow step, but repeated to get the highest ID, uses the following code:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID desc

Gets the Total Item count from the SharePoint List using the REST API

The total item count of the list is returned by using the ItemCount method of the SharePoint API:

_api/Web/Lists/GetByTitle('5000 Item Test')/ItemCount
Image of the SharePoint API being used to get the total item count of a list.

Declare the control variables for each parallel branch

Because there are four parallel threads, all of the variables which operate within each branch are declared four times.

In the previous example, there was a single integer, array and boolean variable to control the do until loop that was retrieving SharePoint data. Now those variables need to be declared for each parallel branch:

Image showing four arrays being defined in Power Automate, ready for parallel execution.

The main control block for the Parallel Branches

In the control block for the parallel branches, a compose action is used to divide the total item count by four to determine the amount of data each branch should retrieve:

div(variables('itemCount'),4)

The control mechanism for the do until loops was modified to be more efficient, by changing the step which checks if there were no results from SharePoint

if(
	empty(body('HTTP_to_SharePoint_to_get_First_Quarter_of_Results').value),true,
	if (
		less(length(body('HTTP_to_SharePoint_to_get_First_Quarter_of_Results').value), 5000),true,false
	)
	
)

This removes an iteration of the loop, by checking if either of the following conditions are true:

  • The result set from SharePoint was empty
  • The number of results returned from SharePoint was less than 5000

SharePoint API Query for each branch

The SharePoint API query is different for each parallel execution of the data collection.

Each branch collects data according to the batch size defined by dividing the total item count by four and begins at the lowest ID in branch one. ?Next, branch two begins at the end of branch one and so on, so that all items are collected.

Branch 1

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{variables('intID')} and ID le @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 1))}&$top=5000"

Branch 2

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 1))}  and ID gt @{variables('intID2')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}&$top=5000

Branch 3

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID ge @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}  and ID gt @{variables('intID3')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 3))}&$top=5000

Branch 4

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID ge @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}  and ID gt @{variables('intID3')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 3))}&$top=5000

Compile all results and send back to PowerApps

The response step simply uses a Union action to bring all the SharePoint data back in a single step:

union(variables('arrItems'),variables('arrItems2'),variables('arrItems3'),variables('arrItems4'))

Summary

Using Parallel branches to fetch SharePoint data can provide a huge speed benefit over a standard do until loop at the expense of creating a more complicated flow but it can be worth it to provide the best possible user experience.

It may be easier to just download the exported flow and modify to your requirements than build it from scratch.

You can download the flow and sample data from this post.

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

Solutions to the SharePoint 5000 item limit using Power Automate

April 19, 2020 by Paulie 46 Comments

If you need to get more than 5000 items from a SharePoint list, there are a number of different approaches. I’ve written up three different ways to solve the problem, which return exactly the same result, but have very different execution times. To get an overall understanding of this problem, check out the YouTube video I produced:

My requirement is to return 17,000 SharePoint list items to a PowerApp. I used Power Automate to get the data but found the standard “Get Items” action to be too slow. A good user experience is essential for application acceptance.

Eventually, I was able to reduce the amount of time that the Flow takes to retrieve the items from 2 minutes down to 8 seconds! That’s 13 times faster!

Since I originally wrote this post, I have figured out a method that is even easier and faster! Check it out here.

Links to my sample data and all the flows created are at the end of the post.

My example data looks like this:

Image showing data from SharePoint list which is going to be used as a DataSource for a flow

It’s 7 columns wide, 17,000 rows of data. Here is how long it took Power Automate to execute the flow with execute each method:

Method Time to Execute
Standard “Get Items” 111 Seconds
Standard “Get Items” with Select Action 67 Seconds
Retreiving same data with SharePoint API 31 Seconds
Retreiving same data with SharePoint API and two parralel queries 19 Seconds
Retreiving same data with SharePoint API and four parralel queries 13 Seconds
Same as above with optimised flow 8 Seconds

The table shows the results from six variations on three different themes. The fundamental principal behind each method is a “Do until” loop which collects the entire contents of a SharePoint list into an array. Each method has some pro’s and cons.

Which method to use?

The right method to use, depends on your circumstances. Here are some suggestions on what method may work best for you:

  • If your flow is not time sensitive, just use the standard “Get Items” action with a “Do Until” Loop in Power Automate. The flow is simple and easy to build.
    Get more than 5000 items from SharePoint with Power Automate
  • If you are already using the “Get Items” method but need it to be a bit faster, enhance the speed of it by adding the “Select” action.
    Get more than 5000 items from SharePoint with Power Automate
  • If the performance gained from the “Select” action still does not meet your requirements, switch to using the SharePoint API. It is almost the same as using “Get Items”, but drastically faster.
    Use the REST API in Flow to get over 5000 items from SharePoint
  • Finally, using parallel execution to retrieve chunks of SharePoint list data simultaneously increases speed further. But it does make the flow more complicated.
    Combine SharePoint API & Flow Parallel branches for Performance

Why is the SharePoint Get Items action so slow?

The “Get Items” from SharePoint in Power Automate is very slow. The primary reason is the size of the payload that it returns from SharePoint. In addition to returning the columns defined in your SharePoint view, it also returns many other columns and metadata.

A single row of the sample data above results in this from SharePoint:

{
	"@odata.etag": "\"1\"",
	"ItemInternalId": "1",
	"ID": 1,
	"Title": "Terry",
	"EmpNo": 1.0,
	"Last": "Carter",
	"Furloughed": 1.0,
	"Telephone": "(462) 723-7695",
	"Email": "[email protected]",
	"Brand": "PowerBI",
	"{Identifier}": "Lists%252f5000%2bItem%2bTest1%252f1_.000",
	"{IsFolder}": false,
	"{Thumbnail}": {
		"Large": null,
		"Medium": null,
		"Small": null
	},
	"{Link}": "https://accendo1.sharepoint.com/sites/DocumentManagementTest/.....",
	"{Name}": "Terry",
	"{FilenameWithExtension}": "Terry",
	"{Path}": "Lists/5000 Item Test1/",
	"{FullPath}": "Lists/5000 Item Test1/1_.000",
	"{HasAttachments}": false,
	"{VersionNumber}": "1.0"
}

That’s a huge amount of extra fields, which results in a massive payload. 5,000 rows of my sample data created a JSON response which was 3,545 KB.

The same 5,000 rows of data when passed through the “Select” action produces a JSON response which looks like this:

{
	"Id": 1,
	"Title": "Terry",
	"EmpNo": 1.0,
	"Last": "Carter",
	"Furloughed": 1.0,
	"Telephone": "(462) 723-7695",
	"Email": "[email protected]",
	"Brand": "PowerBI",
	"ID": 1
}

For the same 5,000 records, the JSON response in flow was only 772 KB – A massive reduction. So passing the response from Get Items through the select action can make a big difference to how quickly your flow will run, and it is super easy to do.

Image of output of Sharepoint Get Items action in Power Automate being reduced down with the select action.

Use the Sharepoint API for maximum speed

Although the “Select” action in Power Automate described above improves the speed of a flow within a do…until loop, the “Get Items” action is still retrieving a lot of unwanted data from SharePoint.

Using the SharePoint API allows you to get exactly the data you want without retrieving any unwanted columns or metadata.

Fortunately, this is very easy to do using the SharePoint API using the “Send an HTTP request to SharePoint” action. Compare these two flow actions:

Standard “Get-Items” action.
The same request using the SharePoint API

These two actions, essentially perform the same operation, but the second one returns only the requested columns. Here is the difference in execution time:

Image displaying the difference in execution time between Power Automate Get Items from SharePoint Action and the Same request to the SharePoint API.

Use Parallel Branching to get batches of 5000 items simultaneously.

Using the SharePoint API to get list data into Power Automate makes a huge difference to the speed of the operation. But it still gets the data in batches of 5000 blocks. With my 17,000 row sample the do-until loop has to execute four times to collect all of the data, all of which takes valuable time.

By using the parallel branch actions in Microsoft Power Automate we can engineer a solution which works much faster.

I tried collecting SharePoint data with two and four parallel branches. With four, I was able to get all 17,000 items in 8 seconds. The downside is that the flow is more complicated.

Conclusion

The standard Get Items action in Power Automate is fine for basic operations, but if you need more control, or speed, then there are better options.

I’m happy with the performance improvements I managed to create. Retrieving data from SharePoint is key to many applications and SharePoint handles the simultaneous requests well.

Example Data and Flows

You can download both my sample data and the flows discussed in these posts from here:

  • Test Data to upload to SharePoint list
  • v1 – Using the Get Items Action
  • v2 – Using the Get Items Action with Select action
  • v3 – Using SharePoint API to retrieve items
  • v4 – Using SharePoint API and Two Parallel Execution Branches
  • v6 – Using SharePoint API and Four Parallel Execution Branches

Adapting the flow to return SharePoint data to a jQuery grid

In the YouTube video above I also provided a demo of a jQuery grid that drew data from the SharePoint list using Power Automate, you can view that demo here:

Demo of jQuery based grid retrieving SharePoint data via Flow

You can easily copy the source code from that page, the flow has been left unsecured so that you can try it out.

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

Use the REST API in Flow to get over 5000 items from SharePoint

April 19, 2020 by Paulie 2 Comments

This post describes how to use the SharePoint API in Microsoft Power Automate to retrieve over 5,000 items from a SharePoint list. It is the second part of a series of blog posts which covers this topic in detail. Please read this post to get an overall picture of the objective and to download my example data and exported flows:

The many ways to retrieve more than 5,000 items from a SharePoint list using Power Automate

The SharePoint API can be a better option than the “get items” action, because it executes much faster. This post builds on the flow created in the previous post which you should read first:

Get more than 5000 items from SharePoint with Power Automate

The aim is create a simple modification to the flow created in the previous post, to improve performance. The previous flow works fine, but it’s slow. If you need to return data to an interactive process, the execution time may be unacceptable.

In the previously created flow the data from the “Get Items” action was passed into the “Select” action like this:

Image of the Power Automate Get Items from SharePoint Action being reshaped by the Select action

This method works, but uses “Get Items” to retrieve data from SharePoint and then “Select” action to reshape it. Both actions can be replaced with a single request, like this:

Image of the Power Automate action "Send an HTTP Request to SharePoint" accessing the GetByTitle method

By requesting only the required columns, the need to reshape the data with the select action can be removed. The Uri code above is:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{variables('intID')}&$top=5000

Using Power Automate, with my sample data, the “Get Items from SharePoint” took 12 seconds to complete the request to SharePoint, whereas the API method took just 1 second:

Image showing how fast a SharePoint API request is compared to the Get Items action in Microsoft Power Automate.

It’s simple to replace the “Get Items” action with the API action and the execution time is greatly improved. You can also specify any odata filter query that you like, to speed up the action by reducing the results further.

Reduce the metadata output

It’s really important when using the SharePoint API to reduce the metadata output for the fastest possible results. As show in the image above, be sure to include the following in the request header:

application/json; odata=nometadata

This reduces the output payload from SharePoint to contain only the list item data. There is a good explanation of all the options on this Microsoft Blog.

Summary

Getting data from SharePoint lists using the SharePoint API is easy and can provide a significant performance increase over the standard “Get Items” action in Microsoft Power Automate.

It provides the basis for the next post in this series which details how to use parallel execution actions in Flow to get the data even faster:

Combine SharePoint API & Flow Parallel branches for Performance

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 270 other subscribers.

Go to mobile version