Tachytelic.net

Super Simple flow to get more than 5000 SharePoint Items

by 10 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.

Reader Interactions

Comments

  2. Damir says

    Hi Paul, thank you for a great job! Would you please provide a link to some (better official) confirmation of is Power Automate Response premium action it’s free if we are using it to respond to PowerApps?

  3. Tom says

    Great Solution and many thanks for sharing.

    Very curious if you are going to work on this again like you previously did (I worked but you improved it =))

  4. Paulie says

    Thank you. I will happily work on it again if you can find any further improvements that can be made! But I’ve slimmed it right down as much as I can think of. I have used the same technique in this flow for many things in Power Automate though. Does your execution still take a long time?

  5. Kamilo Rudeš says

    Hi Paul,

    Thank you so much for sharing the knowledge in the community to non-IT guys like me.
    This is awesome! 🙂

    I applied it for my case, but I got stuck on one thing. I’m fetching string from SharePoint and it has “-” character in the text. When checking the data in Power App part of the string after “-” is not there.
    Appreciate if you can help me on this one.

    Thank you in advance for the time!
    Best
    Kamilo

  6. Hao, Bui says

    Hello Paul,
    Please help me. I download your flow. When I change Share point list, but Schema not change:
    This is my “Body”
    [
    {
    “Id”: 1,
    “Title”: “DONGNAI”,
    “STT”: 1,
    “Item”: “GRL_B?ng keo ?en”,
    “Department”: “NCF”,
    “Unit”: “Cu?n”,
    “Price”: 18500,
    “Supplier”: “AB”,
    “idStatus”: “USE”,
    “Kind_Sundry”: “FIX”,
    “idStatus_Use”: “LOCAL”,
    “Item_Code”: “NCF00000001”,
    “ID”: 1
    },
    {
    “Id”: 2,
    “Title”: “DONGNAI”,
    “STT”: 2,
    “Item”: “GRL_M?c in pallet lo?i nh?”,
    “Department”: “NCF”,
    “Unit”: “Cu?n”,
    “Price”: 254500,
    “Supplier”: “AB”,
    “idStatus”: “USE”,
    “Kind_Sundry”: “FIX”,
    “idStatus_Use”: “LOCAL”,
    “Item_Code”: “NCF00000002”,
    “ID”: 2
    },…
    This is Schema:
    {
    “type”: “array”,
    “items”: {
    “type”: “object”,
    “properties”: {
    “Id”: {
    “type”: “integer”
    },
    “Title”: {
    “type”: “string”
    },
    “EmpNo”: {
    “type”: “integer”
    },
    “Last”: {
    “type”: “string”
    },
    “Furloughed”: {
    “type”: “integer”
    },
    “Telephone”: {
    “type”: “string”
    },
    “Email”: {
    “type”: “string”
    },
    “Brand”: {
    “type”: “string”
    },
    “ID”: {
    “type”: “integer”
    }
    },
    “required”: [
    “Id”,
    “Title”,
    “EmpNo”,
    “Last”,
    “Furloughed”,
    “Telephone”,
    “Email”,
    “Brand”,
    “ID”
    ]
    }
    }

  7. Stuart Smith says

    Hi Paul, the above is/would be a god send in dealing with the inadequacies of Power Apps and large sharepoint list, but im getting an error on the “Each Thread\querySharepoint” flow step. The error says…

    {“odata.error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”The expression \”ID, Site, Floor, RoomIDLocation, SwitchName, SwitchUnitNo., SwitchType, SwitchSerial, SwitchAssetNo., InstallDate, SwitchPortNo., FloorPortNo., EquipmentType, Comments\” is not valid.”}}}

    I guess flow is getting the above from the “QuerySettings” input section…

    {
    “listName”: “LON01 Patch Panel Inventory”,
    “fields”: “ID, Site, Floor, RoomIDLocation, SwitchName, SwitchUnitNo., SwitchType, SwitchSerial, SwitchAssetNo., InstallDate, SwitchPortNo., FloorPortNo., EquipmentType, Comments”
    }

    but cant figure out why its failing as still new to flow. Any help would be great.

  8. Stuart Smith says

    I figured out the issue, it was the column names, I was using the friendly names and not the actual column names and not the flow runs pass that section, but now get an error on the “Response” section…

    Unable to process template language expressions in action ‘Response’ inputs at line ‘1’ and column ‘2528’: ‘The template language function ‘union’ expects either a comma separated list of arrays or a comma separated list of objects as its parameters. The function was invoked with ‘1’ parameter(s). Please see https://aka.ms/logicexpressions#union for usage details.’.

    Any ideas? Thanks in advance.

  9. tachytelic says

    Yes, it sounds like you have invoked Union with a single parameter, but it needs to be as per the code above and union all the threads (arrays) together. Like this:

    union(
    outputs(‘querySharepoint’)[0][‘body’][‘value’],
    outputs(‘querySharepoint’)[1][‘body’][‘value’],
    outputs(‘querySharepoint’)[2][‘body’][‘value’],
    outputs(‘querySharepoint’)[3][‘body’][‘value’]
    )

  10. Stuart Smith says

    Hi Paul, thanks for getting back to me and that worked. I have edited the above union code and removed outputs 1,2 & 3 as my SP List has less than 5000 rows, so was trying to speed up the import, but guess I done something wrong. Anyway, I will leave them in there to future proof the flow. Any once again, thanks and this is going to be so helpful. Think I will have to have a celebratory pint tomorrow, now that the pubs are back open.

Leave a Reply