How to pull a all data from a paged REST API

I have a REST API that does not support sorting or search/filter. So I have to do it myself. But in order to sort and filter I have to have the complete data set. The api returns data only in pages with 100 entries. I don't know how many pages there are so I have to walk through all the pages until I reach the end (get a result with <100 entries).
How would a module, transformer, ... look like that does this job? Or is there a better way?

1 Like

Hey @curesw! What kind of pagination does your API support? Would something like this work for you?

http://community.retool.com/t/returning-all-results-for-a-cursor-based-paginated-api/3387

Hey there, I tried the linked response but it keeps returning undefined. I'm using hubspot apis that return both an "after" and a link that I could use to requery.

Hey @karasharp! Would you mind sharing a screenshot of what you currently have set up?

Sure.


I've since changed things a bit and although a now can loop through the data it doesn't seem to stop once I've retrieved all the data. On the last call this loop would trigger an 400 error with the HubspotContacts query. My thought was that as long as the status was 200 it should loop through but the status doesn't seem to update.

Sounds like you're getting really close! Actually, would it be alright if I stepped into your app to take a look at this? It might be easier for me to debug and see exactly what your API is returning.

And we'll make sure to post here with the fix for any other users running into a similar issue!

Hey, Thanks so much for the offer. Amazing support from you guys. Turns out I was using the wrong API call for what I was trying to accomplish. I've got it figured out now.

Oh, no way! That's awesome to hear :blush: I'm glad you got such a notoriously trick setup to work (recursive API pagination :exploding_head:). Please do let us know if you hit any other blockers/

Hi @karasharp how did the testvar JS looked like?

I had the same issue, and wound up getting stuck and figuring it out the hard way (guessing a lot).

In my case, the query was looping and would keep looping forever. One mistake I made earlier was defining my 'next' cursor as a const, then passing it to the resolve statement on a further line (to clean up the code, I guess). But I later realized that declaring as a const outside of the resolve statement would mean that was no case where it would ever be a null, so it never stopped the loop and exited through the return at the top.

If you're using this example as a template, make sure that you address the pagination cursor properly! Note that I send the initial cursor as '' - a blank string.

So you can see the schema of the response. I think JSON bracket notation is definitely a better way to address the pagination cursor, since the key contains a hyphen (why, tracket, why?!)
image
The REST query that's repeated one time per page:


The javascript query:

FOR HUBSPOT USERS: I just got it to work thanks to GPT-4. Here is my use case and how I got it working:

**Use case **

  • I wanted to get all Tasks and display them in a table in Retool

Solution

  • Create a HubSpot call to Tasks (or whichever object you are doing) with the following parameters - called it getTasks
  • Create a new JS query called getALLData with the following code

One thing I noticed is that when you reach the last page, HubSpot omits the paging parameter. So you have to end the loop when you cannot find the paging parameter. BOOM! It works!

1 Like

Marwan, this is great. I'm glad you were able to get your HubSpot paginating use case working here and thank you for posting this to save countless hours for other users! :pray: