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 for Sheets add-on provides automatic pagination handling for most types of paginated API responses.
pagination-img1

This is a paid feature, please upgrade to access.

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
  • Fetch count: This is the number of pages you’d like to fetch.

Page Parameter Example in API Connector

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

  • API Request URL: https://api.harvestapp.com/v2/time_entries
  • Pagination type: page parameter
  • Page parameter: page
  • Fetch count: 5

pagination-img5

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.
  • Fetch count: This is the number of pages you’d like to fetch.

pagination-img7

Offset-limit Example in API Connector

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

  • API Request URL: https://usX.api.mailchimp.com/3.0/campaigns
  • Pagination type: offset-limit
  • Offset parameter: offset
  • Limit parameter: count
  • Limit value: 100
  • Fetch count: 5

pagination-img6

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.
  • Fetch count: 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)
  • Fetch count: 5

pagination-img3

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.
  • Fetch count: 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)
  • Fetch count: 5

pagination-img9

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.

Notes & Limitations

  • If you don’t know how many fetches you need, you can enter any number (up to 100). The pagination requests will simply stop running when there is no more data to be fetched. Enter a lower fetch count when you don’t want to fetch all the data, or if you are running into issues with your query timing out.
  • When running paginated requests, a 1.4 second delay is automatically inserted between requests to avoid rate limits.
  • 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.
  • API Connector limits the total fetch count to 100 pages maximum. This is because large levels of pagination tend to bump up against Google Sheets’ limits (most commonly the 6 mins/execution limit) and risk failing altogether. This limit is rarely an issue since most APIs allow increasing the row count per page, but if you need to exceed the 100-page maximum, please try splitting your requests by other parameters, and then paginating through them separately.
  • 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 send over details of your API so we can consider adding support in a future release.

Leave a Comment