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.

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. 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 always return some data, even after the results set is complete. To address, you can use JMESPath filtering to select just the array or objects that stop returning data once the complete record set has been returned, or you can 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 send over details of your API so we can consider adding support in a future release.

2 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

Leave a Comment