ANJIR

Retrieve more than 2,000 rows from a SharePoint list

—

by

nealanjir
in

Option 1

Add to AppStart property of your app

Set(FirstCreated,First(Sort(DataSource,Created,SortOrder.Ascending)).Created);
Set(LastCreated,First(Sort(DataSource,Created,SortOrder.Descending)).Created);
Set(BatchSize,12); //number of months
Set(PageCount,RoundUp(DateDiff(FirstCreated,LastCreated,TimeUnit.Months)/BatchSize,0));

ClearCollect(
    colSeq,
    AddColumns(Sequence(PageCount,0,1),
        StartDate,DateAdd(FirstCreated,Value*BatchSize,TimeUnit.Months),
        EndDate,DateAdd(DateAdd(FirstCreated,(Value+1)*BatchSize,TimeUnit.Months),-1,TimeUnit.Seconds)
    )
);

Clear(colAllItems);
ForAll(
    colSeq As StartEnd,
    Collect(colAllItems,
        Filter(DataSource, Created >= StartEnd.StartDate And Created <= StartEnd.EndDate).ID
    )
);

Option 2

  1. Create a new column in the SP list called NewID (type: Number) which copies data from the ID field.
  2. Add to AppStart property of your app
Set(LastRowID,First(Sort(YourListName,ID,SortOrder.Descending)).ID);
Set(BatchSize,1000);
Set(PageCount,RoundUp(LastRowID/BatchSize,0));

ClearCollect(
    colSeq,
    AddColumns(RenameColumns(
            Sequence(PageCount,0,BatchSize),
            Value,
            Start
            ), End, Start + BatchSize
    )
);
Clear(colAllItems);
ForAll(
    colSeq As StartEnd,
    Collect(colAllItems,
    Filter(YourListName,NewID > StartEnd.Start && NewID <= StartEnd.End
    )
    )
);