Search API Connector Documentation

Print

Pagination Handling

premium

Many API servers return only a limited number of rows in each response, and require that you loop through multiple pages to retrieve records for a given request. This is known as “pagination”.

The API Connector add-on for Sheets provides automatic pagination handling for most types of paginated API responses.

pagination-dropdown


Contents

Overview of Pagination Types

There are several common types of pagination:

  1. Cursor (Examples: Asana, Facebook, Stripe).
    The API responds to each request with a token (aka ‘cursor’). Sending this token back in a URL parameter tells the API where to start the next response.
    • Page 1: https://example.com/api
    • Page 2: https://example.com/api?starting_after=ch_1GFKhoIdg4lVxjo0eKhB4dkD

  2. Next-page URL (Examples: Asana, Facebook, Harvest, Shopify)
    The API returns a field containing the URL for the next page. This URL may be provided in the body of the response or the response headers, and may contain the entire URL or just the relative path. Next page URLs are usually provided in conjunction with another pagination type.

  3. Offset-limit (Examples: ActiveCampaign, Mailchimp)
    Parameters are set in the URL to tell the API the starting point and size of each slice of data.
    • Page 1: https://example.com/api?offset=0&limit=100
    • Page 2: https://example.com/api?offset=100&limit=100

  4. Offset-limit Body (Examples: Hubspot, Google Search Console)
    Parameters are set in the request body to tell the API the starting point and size of each slice of data.
    • Page 1 body: {"startRow":0, "rowLimit": 1000}
    • Page 2 body: {"startRow":1000, "rowLimit": 1000}

  5. Page (Examples: Harvest, Toggl)
    A parameter is set in the URL that identifies which page number you’d like to fetch.
    • Page 1: https://example.com/api?page=1
    • Page 2: https://example.com/api?page=2

The pagination type varies for each API, so if you need to retrieve paginated responses, please consult your API documentation for details. You can usually find this information by searching for terms like “paging”, “pagination”, and “limit”.

Set Cursor Pagination

  • Pagination type: cursor
  • Next token parameter: enter the name of the parameter in the URL that sends the token.
  • Next token path: enter the path to the field that contains the token, using dot notation for nested values. This field will be pre-populated with a drop-down list if the request has been run at least once.
  • Run until: choose when the request should stop running.
    pagination-cursor

Set Next Page URL Pagination

  • Pagination type: next page URL
  • Next page path: enter the path to the field containing the next page URL, using dot notation for nested values. This field will be pre-populated with a drop-down list if the request has been run at least once.
  • Run until: choose when the request should stop running.
    pagination-nextpageurl

Next Page URL Variations

Two variations on next page URLs are also supported:

  1. Next page URLs in response headers. If the next page URL field name is not found in the response body, API Connector will automatically check the response header. This is useful for APIs like Shopify and Okta, which paginate via next page URLs in the response headers.
  2. “Relative” next page URLs. In some cases the next page URL contains only the path and not the full URL. API Connector will automatically detect and append the hostname for these cases.

Set Offset-Limit Pagination

  • Pagination type: offset-limit
  • Offset parameter: enter the name of the offset parameter in the URL. This is usually offset, but some APIs use a different term like after or start.
  • Limit parameter: enter the name of the limit parameter in the URL. This is usually limit, but some APIs use a different term like count or num.
  • Run until: choose when the request should stop running.
    pagination-offset-limit

Set Offset-Limit Body Pagination

  • Pagination type: offset-limit body
  • Offset body parameter: enter the path to the offset parameter in the request body.
  • Limit body parameter: enter the path to the limit parameter in the body.
  • Run until: choose when the request should stop running.
    pagination-offset-limit-body

Set Page Parameter Pagination

  • Pagination type: page parameter
  • Page parameter: enter the name of the page parameter in the URL. This is almost always page, but some APIs use a different parameter like p.
  • Run until: choose when the request should stop running.
    pagination-page-parameter

“Run Until” Options

The “Run until” option tells API Connector when to stop looping through your paginated request.

  • Page count equals
    pagination-run-until-page-count
    Run your request a specific number of times.

  • Response field empty
    pagination-run-until-response-field
    Run your request until a specific field is no longer populated. This field will be pre-populated with a drop-down list if the request has been run at least once. Choose a field that appears in every record of your main results set, e.g. an ID or name. When this field is no longer populated, the request will stop looping.

  • No data returned
    pagination-run-until-no-data
    Run your request until the API stops returning data.
    Tip: This option is most suitable for APIs that use cursor or next page URL pagination. Other pagination types may time out (see the Troubleshooting section for more info).

Troubleshooting

  • Running pagination until no data is returned may cause some requests to time out, particularly those that use page parameter or offset-limit pagination. This is because these pagination types often continue returning metadata even after the results set is complete, such that the response is never totally empty. To address, set the request to stop running once a page count has been reached or a specific field is empty.
  • Large levels of pagination tend to bump up against Google Sheets’ limits (most commonly the 6 mins/execution limit) and risk failing altogether. In those cases you will see a ‘Failed to run request’ error message. If you run into issues with queries timing out, please try splitting your requests by other parameters, and then paginating through them separately.
  • If you have a choice, next page URL is usually the best type. This is because it will continue to work even if the API changes its underlying pagination method. It also avoids the potential timing-out issue noted above.

Notes

  • Google tracks each call as a urlfetch, so if you run a request that fetches 5 pages, Google will count 5 urlfetch calls towards your daily quota.
  • When running paginated requests, a 1.5 second delay is automatically inserted between requests to avoid rate limits.
  • The seven pagination methods (5 main methods + 2 variations) listed above are the most common pagination types, but it’s possible you will encounter an API that uses some other method of pagination. In those cases, feel free to post below or contact us with details of your API so we can consider adding support in a future release.

Previous Multi-Query Requests
Next Scheduling

26 thoughts on “Pagination Handling”

  1. Can you add a feature for a data range in the pagination parameters? I have an API that can only pull data 180 days at a time, and don’t know how to do that here. Thank you!

    Reply
  2. hi Ana,
    with regards to pagination handling for shopify, i can’t seem to get the next page example to work.

    i’m still hitting the 250 records limit.

    Reply
      • When using that method, it only brought up the default 100 line items.

        So I tried: offset-limit and that worked.

        Offset parameter: $skip
        Limit parameter: count
        Limit value: 100
        Number of pages: 5

        That gave me 500 records.

        I tried ‘all pages’ but that timed out.

        Is there a way to download to google sheets, say, 2500 records at a time until all records are downloaded?

        I’m able to do it manually by adding a url parameter of $skip=2500 (where 2500 = the number of rows already in the spreadsheet) using an append output mode. But it’d be cool if this could be done automatically!

        Thanks!

      • Interesting, I’m not sure why that didn’t work. I wonder if it’s because you have to specify the $top variable in the initial request URL, like https://api.karbonhq.com/v3/WorkItems?$top=100
        Their docs aren’t that clear, but anyway I’m glad you’ve got it sorted with offset/limit pagination instead.

  3. Hi!
    My API request is https://api.u-on.ru/APIKEY/requests/2021-08-01/2021-09-30/1.json

    where 1 is page number.

    As you can see there isn’t a parameter like “page”.

    So can I handle with such URL using pagination feature? I need that so much.

    Reply
    • Hi Alex, you can use multi-query requests for this. If you check the section called “Why Use the Multi-Query Function”, there’s an example of using that function for this use case (it’s called ‘page directory pagination’). Basically you just list your URLs in the URL box, like this:
      https://api.u-on.ru/APIKEY/requests/2021-08-01/2021-09-30/1.json
      https://api.u-on.ru/APIKEY/requests/2021-08-01/2021-09-30/2.json

      Reply
      • Yes, thanks. And I am using this option now. I have no choice)

        But it is very uncomfortable for us. Because requests dates are changing very often and we have a lot of pages. So we need to use CONCAT Google Sheet function all the time to generate URLs and then copy/paste them.

        May be you can consider such option.

        For example there is a parameter like +++pagination+++. We insert it in our URL, indicate a starting point (for example “1”) and get incremental pagination.

        Like
        https://api.u-on.ru/APIKEY/requests/2021-08-01/2021-09-30/+++pagination+++.json

        It would be fantastic for us.

      • Thank you for the suggestion, I’ll definitely consider adding a new type of pagination for this.
        For now, how about creating a tab that contains all the numbers you need in a column, say from 1 to 100. Then you can just make a single URL that says https://api.u-on.ru/APIKEY/requests/2021-08-01/2021-09-30/+++Sheet1!A1:A100+++.json and it will substitute in all 100 digits so it runs through all 100 pages. You can do that with the existing multi-query function.

      • Wow! I didn’t know about this option. It is much easier. Thank you!

        There is only one problem. In our case we don’t know exact number of pages. It may be 1 page, and may be 100 or even 150 pages. So we have to make 100 calls everytime and thus make excessive load on the server. And our provider is very angry for that.

      • It seems a bit unreasonable that they provide non-standard pagination, and then don’t like that people can’t easily paginate it :p
        I suppose you can make one call and then set your cycle count based on the total number of records (assuming they return that in the first response).
        Feel free to email me through support if you’d like to think through some ideas together.

  4. Hi Ana – I’m running into a couple issues with pagination for Recurly’s API: https://developers.recurly.com/api/v2021-02-25/index.html#tag/account

    When it returns the paginated URL, it returns it without the https://v3.recurly.com. So when I use next page pagination, I get the below error message:

    Bad request: http:///accounts?cursor=ppgsqfv41tq3%3A1634130820.0&limit=10&sort=created_at&subscriber=true

    When I try to use cursor pagination, the response appears to add additional characters to the id (see above, after the % in the cursor value) which causes it to throw an error for invalid request.

    {“error”:{“type”:”validation”,”message”:”Please provide valid values for these parameters: cursor”,”params”:[{“param”:”cursor”,”message”:”format is invalid”}]}}

    Any thoughts?

    Reply
    • Thanks, I created a Recurly account and verified what you said. Recurly’s next page URL isn’t the complete URL, and the cursor they provide isn’t the complete cursor either. So unfortunately we don’t currently have a pagination method for this. I’ll try to add pagination handling for “next-page relative URLs” in the future, that should take care of it. For now, though, I’m afraid you can only do this semi-manually, by running your request with the limit=200 maximum, then creating a new URL with https://v3.recurly.com + the value in the “next” field, and running it in append mode to stick it onto the end of your existing setup. Of course that might not be practical at all if you’re pulling a lot of data. Sorry about that.

      Reply
      • Thanks for the quick response and for taking the time to investigate! Your suggestion should work for us in the short term as we’ll only need ~2000 records.

      • Update: we now support relative URLs through the “next page” pagination type. I’ve updated the article to reflect this.

  5. Ana! First of all, thank you for of your help on the forums!
    If we have a large data set in harvest with over 1300 pages, what would be the command to request that? Is there a way to place a timed break in between of the multi requests?

    Reply
    • Hello Art! Thank you for your nice comment 🙂 1300 pages will be hard to manage through this extension, since requests time out after about 6 minutes. Even just pasting in 130,000 records (1300 pages * 100 records each) without calling an API can make Sheets quite slow, since Sheets isn’t great at handling large data sets.
      So this might not be the best tool for the job, but this is what I would try:

      1. set up filters to reduce your data set as much as possible, as this will increase the speed of your request. This can include both filters on Harvest’s side and filters set through our field editor.
      2. run a request for as many pages as you can get in one go.
      3. create a new request that starts from the end of your subsequent request. You can set a starting point by using the page=X parameter in your request URL. Repeat as many times as you need until you get all your data, using Append mode so it all appends into the same sheet.

      Depending on what you’re looking for, an alternative method could be to export all the historical data while logged into Harvest, then use the API just to fetch new data.

      Reply
    • Hey Tim, that would be cursor pagination, you should be able to set it up like this:
      Pagination type: cursor
      Next token parameter: next_page_token
      Next token path: next_page_token

      Please check if that works for you.

      Reply

Leave a Reply to Tim Cancel reply

Table of Contents