Retrieving More Than 2000 Rows from SharePoint in Power Apps | Canvas Apps

 

Introduction:

Power Apps is a powerful tool for building custom applications, but when working with large datasets in SharePoint, you may encounter limitations. SharePoint has a default limit of returning only 2000 rows of data, which can be a challenge if you need to retrieve more. In this tutorial, we will explore a method to overcome this limitation and retrieve more than 2000 rows from SharePoint in a Canvas app using Power Apps.

Prerequisites:

• Power Apps account with access to create Canvas apps.
• Connection to the SharePoint site where the data resides.

Step 1: Create a Canvas app and connect to SharePoint:

• We need only to create an additional column of type "number" that ideally has matching values to the corresponding ID column.

• Open Power Apps and create a new Canvas app.

• Go to the "Data" tab and click on "Add data source."

• Select SharePoint and provide the necessary details to connect to your SharePoint site.

Step 2: Configure the app to retrieve data:

• Add a Gallery control to the app screen. This control will store the retrieved data from SharePoint.

• On the app screen, add a Button control and set its OnSelect property to the following formula:

Set (

    firstRecord,

    First(Documents)

);

Set(

    lastRecord,

    First(

        Sort(

            Documents,

            ID,

            SortOrder.Descending

        )

    )

);

Set(

    iterationsNo,

    RoundUp(

        (lastRecord.ID - firstRecord.ID) / 500,

        0

    )

);

Collect(

    iterations,

    Sequence(

        iterationsNo,

        0

    )

);

Clear(colDocuments);

ForAll(

    iterations,

    With(

        {

            prevThreshold: Value(Value) * 500,

            nextThreshold: (Value(Value) + 1) * 500

        },

        If(

            lastRecord.ID > Value,

            Collect(

                colDocuments,

                Filter(

                    Documents,

                    ID_val > prevThreshold && ID_val <= nextThreshold

                )

            )

        )

    )

);

Step 3: Understanding the code:

The provided code is a way to retrieve more than 2000 rows from a SharePoint list in a Canvas app using Power Apps formulas:

• Set firstRecord: This sets the variable firstRecord to the first record in the SharePoint list. It uses the First function to retrieve the first record based on the list's name.

• Set lastRecord: This sets the variable lastRecord to the last record in the SharePoint list. It uses the Sort function to sort the list by the ID in descending order and retrieves the first record using the First function.

• Set iterationsNo: This calculates the number of iterations required to retrieve all the records. It uses the RoundUp function to round up the division of the difference between the last and first record IDs by 500. The value 500 is used as a batch size, indicating that 500 records will be retrieved in each iteration.

• Collect(iterations, Sequence(iterationsNo, 0)): This collects a sequence of numbers from 0 to iterationsNo into a collection named iterations. This collection will be used to control the number of iterations in a loop.

• Clear(your collection name): This clears the collection that will store the retrieved records from SharePoint.

• ForAll iterations: This loop iterates over each item in the iterations collection.

• With: This function allows you to group multiple actions together and apply the same formula or operation to each item in a collection.

• prevThreshold: This sets the variable prevThreshold to the current value in the iterations collection multiplied by 500. It represents the lower threshold for the batch of records to retrieve.

• nextThreshold: This sets the variable nextThreshold to the next value in the iterations collection multiplied by 500. It represents the upper threshold for the batch of records to retrieve.

• If: This condition checks if the last record's ID is greater than the current value in the iterations collection.

• Collect(your collection name, Filter('the name of your sharepoint list', ID_val > prevThreshold && ID_val <= nextThreshold)): If the condition is true, this collects the filtered records from the SharePoint list into the specified collection. The Filter function retrieves the records where the ID value is greater than the previous threshold and less than or equal to the next threshold.

Step 4: Customize and test the app:

• Customize the app's user interface based on your requirements. You can add tables, galleries, or other controls to display the retrieved data.
• Save and publish the app.
• Test the app by clicking the button you added. It will trigger the retrieval process, and you should see the data being fetched from SharePoint in batches.

Conclusion:

By utilizing the provided code, you can overcome the limitation of retrieving only 2000 rows from SharePoint in Power Apps. This method allows you to retrieve larger datasets and display or process them within your Canvas app