How to achieve 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,
 
    GetRecordCount = () =>
        let Url   = BaseUrl & "?$count=true&$top=1",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "?$skip=" & Text.From(Index * RecordsPerPage),
            Top   = "$top=" & Text.From(RecordsPerPage),
            Url   = BaseUrl & Skip,
            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"