How to implement pagination in Microsoft Excel?

In MS Excel, go to Data → New query → From Other Sources → Blank Query

This will open the Power Query Editor.

Click Advanced Editor and paste the code below. Replace <Service link>/<Entity Name> with your Test or Production service links and Entity name. Also, replace columnA, columnB, etc., with the column names in your Entity. When prompted for Credentials, select Basic Authentication and enter your OutSystems username and password for the service link.

This will paginate in blocks of 1000, retrieve all the records in the Entity and add it to your Excel model. Click Close and Load. This will close the Power Query Editor and load the query into the Excel Worksheet.

let 
    BaseUrl         = "<Service link>/<Entity Name>",
    RecordsPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [
        Headers = [ #"accept" = "application/json"]
      ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,

    GetTextData = (Url) =>
        let Options = [        
        Headers = [ #"accept" = "application/json"]       
        ],             
            RawData = Web.Contents(Url, Options),             
            TextData = Text.FromBinary(RawData)         
        in  TextData,     

    GetRecordCount = () =>
        let Url   = BaseUrl & "/$count",
            TextData = GetTextData(Url),
            Count = Number.FromText(TextData)
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "?$skip=" & Text.From(Index * RecordsPerPage),
            Top   = "$top=" & Text.From(RecordsPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    RecordCount = List.Max({ RecordsPerPage, GetRecordCount() }),
    PageCount   = Number.RoundUp(RecordCount / RecordsPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Records    = List.Union(Pages),
    Table       = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"columnA", "columnB", "columnC"})
in
    #"Expanded Column1"

We recommend you assess the implementation of an incremental load approach for these big tables, and you can achieve this by using 1 of 2 ways I list below. You can even combine both for optimal result:

• Delta sync – filtering by date range, to sync only records created or updated after the last sync.
  This article has detailed instructions on how to it.

• Pagination – If applying the approach above, your result set is still bigger than the restriction, you can split it in smaller chunks and use pagination.
  Here is another article you might find useful.

Optionally, we can increase the limit, in case you don’t see the need for the approaches above. From R2.3+ there’s a feature that allows you to customize this limit at a project level.