Search API Connector Documentation

Print

Pagination Handling

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-img1

This is a paid feature, please upgrade to access or install API Connector for a free trial.

Contents

Overview of Pagination Types

There are several common types of pagination:

  1. page parameters (Examples: Harvest, Toggl)
    Page 1: https://www.demo.com/api?page=1
    Page 2: https://www.demo.com/api?page=2
  2. offset-based pagination (Examples: ActiveCampaign, Box, Mailchimp)
    Page 1: www.demo.com/api?offset=0&limit=100
    Page 2: www.demo.com/api?offset=100&limit=100
  3. cursor-based pagination (Examples: Asana, Facebook, Stripe)
    Page 1: www.demo.com/api
    Page 2: www.demo.com/api?starting_after=ch_1GFKhoIdg4lVxjo0eKhB4dkD
  4. next-page URL pagination (Examples: Asana, Facebook, Harvest, Shopify)
    Next-page pagination is usually provided alongside a primary pagination type. With next-page pagination, the API returns a “link” field containing the entire URL for the next page, so you can use that link directly rather than constructing the next page URL yourself.

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

There are no real standards for the names given to different types of pagination. What one API calls “cursor” pagination, another API may call “next page” or “offset” pagination. The names used in API Connector reflect the categorization logic provided in the overview above.

Page Parameter Pagination

Paging parameters are the simplest type of pagination. The response is simply split into pages 1, 2, 3, and so on, with each page identified by a page parameter like page=2.

API Connector’s pagination function will automatically cycle through page parameters with these inputs:

  • Page parameter: This is the name of the parameter in the URL that holds the page number. For example, if the URL is https://api.harvestapp.com/v2/time_entries?page=2, the page parameter would be page
  • Number of pages: This is the number of pages you’d like to fetch.

Page Parameter Example in API Connector

While it’s optional to include the page parameter in your starting URL, if you exclude it, the first loop through will just apply the defaults from the server. Therefore it’s generally best to include it, especially if you want to start paginating from, say, page #10 instead of page #1.

This is how a paged Harvest request would get entered into API Connector:

  • API Request URL: https://api.harvestapp.com/v2/time_entries?page=1
  • Pagination type: page parameter
  • Page parameter: page
  • Number of pages: 5

This will pull Harvest time entries, 100 at a time, automatically populating the page parameter with the next integer. It will loop 5 times, for a total of 500 records, and all records will be printed into the same sheet.

Offset-Limit Pagination

In offset-limit pagination, limit refers to the number of records to return each time, while offset is an integer referring to the starting point for each new response page. The offset parameter should equal the sum of the previous offset value and limit, such that, assuming the limit=100, the first page has offset=0, the second page has offset=100, the third page has offset=200, and so on.

API Connector’s pagination function will automatically cycle through offset-limit parameters with these inputs:

  • Offset parameter: This is the name of the parameter in the URL that holds the offset value. This is almost always offset, though you may come across other names (e.g. the Google Custom Search API calls it start)
  • Limit parameter: This is the name of the parameter in the URL that holds the limit value. This is usually named limit, but again you may run into other names (e.g. Mailchimp calls it count and the Google Custom Search API calls it num)
  • Limit value: This is the number of records to retrieved in each request. The offset value will automatically be increased by this same amount.
  • Number of pages: This is the number of pages you’d like to fetch.
pagination-img7

Offset-limit Example in API Connector

While it’s optional to include the offset and limit parameters in your starting URL, if you exclude them, the first loop through will just apply the defaults from the server. Therefore you should include them if you want to use a different limit size, or if you want to start paginating from, say, record #1000 instead of record #1.

This is how a paged Mailchimp request would get entered into API Connector:

  • API Request URL: https://usX.api.mailchimp.com/3.0/campaigns?count=100&offset=0
  • Pagination type: offset-limit
  • Offset parameter: offset
  • Limit parameter: count
  • Limit value: 100
  • Number of pages: 5

This will pull Mailchimp campaigns, 100 at a time, automatically populating the offset and count parameters. It will loop 5 times, for a total of 500 records, and all records will be printed into the same sheet.

Cursor-Based Pagination

Cursor-based pagination provides a page token in the response that is then used in subsequent requests, so you need to first receive the results of your request before proceeding to the next URL.

For example, Stripe requires each paginated URL to contain the starting_after or ending_before parameters, set to the ID from which to start/end the count, like this: https://api.stripe.com/v1/charges?limit=10&starting_after=ch_1GFKhoIdg4lVxjo0eKhB4dkD.
pagination-img10

API Connector’s pagination function will automatically retrieve the tokens required to cycle through the requests, with these inputs:

  • Next token parameter: This is the name of the parameter in the URL that holds the starting or ending token.
  • Next token field: This is the name of the field containing the value that populates the next token parameter.
  • Number of pages: This is the number of pages you’d like to fetch.
pagination-img4

Cursor-Based Example in API Connector

This is how the paginated Stripe request would get entered into API Connector:

  • API Request URL: https://api.stripe.com/v1/charges?limit=100
  • Pagination type: cursor
  • Next token parameter: starting_after
  • Next token field: data.id (use dot notation to show the path to the token, so if your token is returned to a field “data » id”, enter it as data.id)
  • Number of pages: 5

This will pull Stripe charges, 100 at a time, automatically populating the starting_after parameter with the next value in the “id” field. It will loop 5 times, for a total of 500 records, and all records will be printed into the same sheet.

Next Page URL Pagination

Next Page URL pagination is often used in conjunction with other types of pagination. With Next Page URL pagination, the API returns a “link” field containing the entire URL for the next page, so you can use that link directly rather than constructing the next page URL yourself. The link field may be in the body of the response or in the response header.

API Connector’s pagination function will automatically cycle through the URLs in the next page URL field, with these inputs:

  • Field name: This is the field holding the next page URL.
  • Number of pages: This is the number of pages you’d like to fetch.
pagination-img8

Next Page URL Example in API Connector

This is how a paginated Facebook request would get entered into API Connector:

  • API Request URL: https://graph.facebook.com/v6.0/act_123456789/insights....&limit=100
  • Pagination type: next page url
  • Field name: paging.next (use dot notation, so if your token is returned to a field “paging » next”, enter it as paging.next)
  • Number of pages: 5

This will pull Facebook insights, 100 at a time, automatically cycling through the next page URLs. It will loop 5 times, for a total of 500 records, and all records will be printed into the same sheet.

Tip: 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.

Troubleshooting

  • 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.
  • Fetching all pages may also cause requests to time out, not just because of the 6 mins/execution limit, but because some APIs continue returning metadata even after the actual results set is complete. To address, please enter a page count instead of fetching all pages.
  • If you don’t know how many fetches you need, you can enter any number greater than your desired page count. Once the API stops returning records, the extra loops won’t add any data to your sheet, though they may slightly increase processing time. Enter a lower page count when you don’t want to fetch all the data, or if you are running into issues with your query timing out.

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 four pagination methods 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

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

        Pagination handling is pretty automatic but unfortunately you need to manually specify the starting point, we can’t deduce it automatically. Sorry about that.

        One more thing, if the request times out when you fetch all pages, that usually means they’re sending back some metadata. You can avoid this by including a JMESPath that excludes that metadata. It looks like just entering the word “Items” into the JMESPath field should work, though I’m just going off their docs since I haven’t used this API before. Feel free to send a message if you’d like me to take a look.

Leave a Comment