Grab all rows from API source with offset and limit in body

[suchomelb]

Hi all!

I am trying to set it up so I can grab all the rows of data from the Leads table in this query rather then doing multiple queries and then appending them. Unfortuntely I cannot find any source that specifies how to do this with the limit/offset in the body separated from the URL like I have to have it. There is over 8k rows and I can only pull 500 at once.

Here is the code I have right now:

let
  body = "
  {
      ""id"":""123"",
      ""method"":""getLeads"",
      ""params"":{
        ""where"":{
        },
        ""limit"":""500"",
        ""offset"":""0""
  }
  }",

  Data = Json.Document(Web.Contents("https://api.sharpspring.com/pubapi/v1.2/?accountID=###########&secretKey=###########", [Content=Text.ToBinary(body), Headers=[#"Content-Type"="application/json"]]))
in
    Data

I have never struggled this bad to obtain the data I need for a project, any and all help would be greatly appreciated.

Thanks in advance!

[lbendlin]

Handling paging for Power Query connectors - Power Query | Microsoft Learn

[suchomelb]

You must have that link ready to copy/paste at a moments notice because I've seen you post that as a response to this type of issue multiple times in other threads.

Unfortunately there is no detail in that link to actually how to apply the solution. I have no clue what to do with it or where to start.

Thank you though for trying to help. I'm just glad to get a response finally.

[lbendlin]

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons)

[suchomelb]

Yeah not easy at all it seems. I did get some help from a guy on reddit after I hadn't received any responses on this forum. Seems to have got me a few steps in at least but now I am at a "Expression.Error: We cannot convert a value of type Record to type List." error. I made a separate post here just now in case somebody had an original idea for my code in this thread.

[lbendlin]

You must have that link ready to copy/paste at a moments notice because I've seen you post that as a response to this type of issue multiple times in other threads

I consider that link to be the most comprehensive discussion of the topic.  There are others like biccountant.com that have excellent articles as well, but usually they describe scenarios that are nearly but not entirely unlike yours (with apologies to DNA).