Forum Discussion

bwhdegroot's avatar
bwhdegroot
Frequent Visitor
6 years ago

Paginate Rest API via Offset and Limit method

Hello all,

 

I know this has been covered quite a frequent amount of times, but it seems I cannot get it to work with my api.

Currently I am trying to connect to the following API: https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=XXX

 

This is our CRM system. As there is a limit of 500, I want to paginate using the offset/limit method. I tried several examples but they all seam to fail with me.

 

 

= List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY")) else Json.Document(Web.Contents("https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY&offset="&Last_Key)), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1)

 

 

 

Can anyone tell me what I am doing wrong here? I get a list of 50 records as per the standard limit on the API. The second record shows an error that the operator cannot be applied to text and number.

 

Many thanks!

Need Help

8 Replies

    • bwhdegroot's avatar
      bwhdegroot
      Frequent Visitor
      6 years ago

      amitchandak Unfortunately I have been going through all these links already, but no success to far.

       

      Also tried this example, adjusted it a bit but also seems not to work properly.

       

       

      The API documentation shows the following specs of the API:

       

      Param Type Default Required

      includestring optional
      offsetint0optional
      limitint50optional
      access_tokenstring required

      limit = max 500

       

      https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=XXX

      Response

      Field Type Description

      statusstringok or error
      total_countintTotal result count
      jobsarrayArray of jobs

       

      let 
   Url         = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY",
    EntitiesPerPage = 50,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = Url & "$count=true&$top=0",
            Json  = GetJson(Url),
    Count = Json[#"total_count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = Url & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI bwhdegroot,

        It seems like you are copying the sample query formula and try to use it with your scenario, right?
        I think you need to do some modification on internal functions with their URL query string and returned properties. (your API does not contain 'top, skip, count' parameters)

        Please take a look at below formula if it meets to your requirement:

        let 
	MYKEY="xxxxxx",//token string
    EntitiesPerPage = 500,
	Token= "&access_token=" & MYKEY,
	Limit="&limit=" & Text.From(EntitiesPerPage),
    Url = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts" & Limit,
    GetJson = (Url) =>
        let 			
            RawData = Web.Contents(Url & Token),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = Url & "&offset=0" & Token,
            Json  = GetJson(Url),
			Count = Json[#"total_count"]
        in  
			Count,
 
    GetPage = (Index) =>
        let 
			//(option A)offset equal to previous row count 
			offset  = "$offset=" & Text.From(Index * EntitiesPerPage), 
			//(option B)offset equal to page numer 
            //offset  = "$offset=" & Text.From(Index),            
			Url   = Url & offset & Token,
            Json  = GetJson(Url),
            Value = Json[#"jobs"]
        in  
			Value,
 
    EntityCount =  GetEntityCount(),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

        Notice: I'm not so sure what type of offset parameter your API used, please choose one of two optional offset steps based on your scenario.

        Regards,

        Xiaoxin Sheng