API Connector Documentation

Print

Pagination Handling

Last Updated OnMarch 16, 2024
byAna
< Knowledge Base

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. Cursor body (Examples: Crunchbase, Notion)
    As above, the API responds to each request with a token (aka 'cursor'). Sending this token back in the request body tells the API where to start the next response.
    • Page 1 body: {"after_id":""}
    • Page 2 body: {"after_id":"gh82slf"}

  3. 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.

  4. 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

  5. Offset-limit body (Examples: Google Search Console, Hubspot)
    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}

  6. Page (Examples: Harvest, Toggl)
    A parameter is set in the URL that identifies which page number you'd like to fetch. Sometimes an additional parameter can be included that sets the size of each page.
    • Page 1: https://example.com/api?page=1&pageSize=100
    • Page 2: https://example.com/api?page=2&pageSize=100

  7. Page Parameter Body (Example: Clockify)
    A parameter is set in the request body to identify which page number you'd like to fetch. Sometimes an additional parameter can be included to set the page size.
    • Page 1 body: {"page":1, "pageSize": 1000}
    • Page 2 body: {"page":2, "pageSize": 1000}

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 in your sheet 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. More info >
    pagination-cursor

Set Cursor Body Pagination

  • Pagination type: cursor body
  • Next token parameter: enter the name of the parameter in the request body that sends the token.
  • Next token path: enter the path to the field in your sheet 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. More info >
    pagination-cursorbody

Set Next Page URL Pagination

  • Pagination type: next page URL
  • Next page path: enter the path to the field in your sheet 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, or you can type in your own field name manually (You will need to manually type in the word Link for most APIs that paginate through response headers).
  • Run until: choose when the request should stop running. More info >
    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 relative paths.

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, start, or skip.
  • 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, num, or top.
  • Run until: choose when the request should stop running. More info >
    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. More info >
    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 occasionally APIs use a different parameter like p.
  • Run until: choose when the request should stop running. More info >
    pagination-page-parameter

Set Page Parameter Body Pagination

  • Pagination type: page parameter body
  • Page body parameter: enter the path to the page parameter in the request body.
  • Run until: choose when the request should stop running. More info >
    pagination-pageparameterbody

"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, or you can type in your own field name. Choose a field that appears in every record of your desired 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, try splitting your requests by other parameters, and then paginating through them separately.
  • These pagination methods are designed for standard REST APIs and will not work with GraphQL or XML request bodies.

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 2 second delay is automatically inserted between requests to avoid rate limits.
  • The nine pagination methods (7 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.

42 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.

  4. 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.

  5. 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.

  6. 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

  8. Hello Ana! Thank you for all the information on this forum. I am currently looking to import data from Vimeo into Google Sheets (the name, file, password etc of videos), and I am using the Vimeo API for the video page and I'm unsure which pagination option to use to return all the data into google sheets from all the pages of videos. When I first tried it without the pagination option it only returned the first page of videos that show up on Vimeo.

    Reply

    • The path is for the location of the next page URL in the API response, it shouldn't be a URL.

      In this case I believe you should just be using page parameter pagination, entering the word page as the page parameter.

      Reply

  11. Hi,
    I am using this https://developers.livechat.com/docs/management/configuration-api/v2.0#get-list-of-chats-deprecated which returns the number of pages in the initial call.

    The number of pages available is dynamic - when attempting to use the page parameter pagination - I would like to set the variable to the number of pages to retrieve. Could you advise on how to handle this situation? i.e. not hard code the "Number of pages" variable.

    Thank in advance for your assistance.

    Warren

    Reply

    • We don't have a dynamic page option, but you can set your request to stop running when a field is no longer returning data. For example you could set it to stop running when the chats.id field is empty, then it will stop running when the data set completes without needing to specify a particular number of pages.

      Reply

      • Thank you for this advice! I managed to get the pagination working when doing a test when directly running this function (from the sheet UI).

        When attempting to call this request from a cell using the IMPORTAPI function, the pagination does not seem to work? Is there anything in addition I should be doing?

        Thanks for your prompt assistance so far!

        Regards, Warren

      • Hey Warren, there should be no difference between running the request from the sidebar via calling it via IMPORTAPI so I'm not yet sure why it's not working. Are you getting an error, and, if so, what does it say?

      • Hi Ana,
        Thanks again, there isn’t any error thrown. The importAPI just returns the first page of results, the “UI” paginates and returns all pages.. I also looked at comparison table and see there is a 30 second limit on ImportAPI calls.. could this be what is causing the issue? Thanks again, Warren

      • Sorry, I can't replicate that, in my tests IMPORTAPI always returns any pagination that was applied to the saved request. It's also relatively rare for a request to take more than 30 seconds to complete, so I don't think that's the issue either. Mind contacting support with more information about your request configuration so I can take a look?

  12. Hello Ana I am trying to select the "pagina" for the Page parameter body, but the "param[0].pagina" notation isn't working. I'm using the page parameter body with Run until response field empty. The only part that is missing is the array nesting.

    Request below:

    {
    "call": "ListarProdutos",
    "app_key": "***",
    "app_secret": "***",
    "param": [
    {
    "pagina": 1,
    "registros_por_pagina": 1,
    "apenas_importado_api": "N",
    "filtrar_apenas_omiepdv": "N"
    }
    ]
    }

    Reply

  13. Hi,
    I have a whole series of APIs that end with the following:

    https://......./event/1
    https://......./event/2
    https://......./event/3

    etc

    Is there a way to set up a GET pagination with this suffix?

    Would really love to avoid inputting all 38 (and they are weekly so it would be weekly inputs, or scheduling)

    Thanks

    Reply

Leave a Comment

Jump To