Passing a parameter to an API for retrieving paginated data

[wcameron14]

I am working with our marketing tools API so I can pull marketing data into a Power BI report. The API data is paginated and limited at 20 by default. I want to pass a parameter (limit = 100) but I've not had much luck finding information on how to do this. I have tried to pass it using the advanced settings for getting web data (this is the same place where I have successfully passed the API Token. When I add the limit as a header parameter, nothing happens (the response is still 20 lines of data, per the default). Ideally, I could set this up to do an offset (another parameter the tool uses to grab the next set of records) and to grab all of the data that is available.

Any help is appreciated.

[lbendlin · Accepted solution]

Do the first call with ?limit=100 , then query the Meta/Total value and calculate how many more calls you need to make to hit the total. Power Query allows you to create lists, and to add columns to these. with the contents of the API call returns.

- make first call with ?limit=100

- let's say the call returned with Meta/Total=760

- create a list with a value from 100 to 700 in steps of 100

- make subsequent calls with ?limit=100&offset=[list value]

- combine all results as needed.

[lbendlin]

Needs more details. Do you have access to that API's documentation?  Does it describe how to do pagination with offsets and limits?

[wcameron14]

Yes. This is directly from the API documentation: https://developers.activecampaign.com/reference#pagination

Endpoints that return collections of resources must limit the number of records returned in a given response. The query parameter limit can be used to alter the number of records returned. A typical endpoint will return 20 records by default and will allow a maximum of 100 records to be returned. The query parameter offset can be used to offset the result set. These query parameters can be combined to recover all records in a collection through a series of requests by incrementing the offset by the value of limit with each request.

The total number of results in a collection can be found in the meta.total property of the response.