API Pagination in Google Sheets using Apipheny

What is pagination?

Pagination is a process that API providers use to divide a large dataset into smaller sets (pages).

Sometimes, when you import API data, you might not get all the results you were expecting. For example, you might be expecting 1000 rows of data, but you only get 200.

This is because the API only provided the first page of data, which includes 200 results, and you need to make additional API requests to get the rest of the pages.

When an endpoint requires pagination, you usually have to call the endpoint multiple times to get all the results. In this example, you would call the API 5 times to get all of the 1000 results you were expecting.

Pagination using Apipheny

Apipheny is an API Connector for Google Sheets that allows you to call APIs directly from your spreadsheets.

Pagination can be done manually or automatically. If you only have a few pages of data, you can enter multiple URLs with a different page parameter or offset/limit parameters in each URL, for example.

If you have many pages you need to get, Apipheny’s pagination features can automate the process for you by calling the endpoint repeatedly until all of the results have been retrieved. Apipheny currently offers three types of pagination automation:

Cursor
Next Page
Page Number Parameter
Offset Limit

You can check the API documentation of the API your using to confirm if pagination is available and what type of pagination you can use. If you need help determining how to paginate an API, send our support team a link to the API endpoint documentation and we’ll let you know.

1.) Cursor

In cursor pagination, the cursor typically represents the last item returned in the previous query and is a unique identifier that tells the system where to start fetching the next set of results. The cursor is passed as a parameter in the subsequent query to retrieve the next set of results.

Next Token Parameter Name	The name of the URL parameter that sends the cursor
Next Token Path	The name/path in the JSON response that contains the cursor, using dot notation for nested fields
Run Until	Options: Page Count Equals, Response Field Empty, No Data Returned

Next Token Path Example

The Next Token Path needs to be written using dot notation format for nested fields. For example, in the JSON response below, Next Token Path is paging.cursors.after

💡 Tip: Apipheny uses dot notation in the column headers. So if you import the data into your sheet first, you will see the field you need to enter as the Next Token Path, in the column header in dot notation format.

{
  "data": [
    {
      "name": "AAAA......",
      "id": "12345678900"
    },
    {
      "name": "BBBBBB",
      "id": "12345678900"
    }
	.......
  ],
  "paging": {
    "cursors": {
      "before": "XXXX.....",
      "after": "YYYY...."
    }
  }
}

2.) Next Page

In next page pagination, the server returns a fixed number of items per page and users navigate through the pages using a “next page” URL.

Next Page Path	The name/path in the JSON response that contains the cursor, using dot notation for nested fields
Run Until	Options: Page Count Equals, Response Field Empty, No Data Returned

Next Page Path Example

The Next Page Path needs to be written using dot notation format for nested fields. For example, in the JSON response below, Next Page Path is meta.nextPageUrl

💡 Tip: Apipheny uses dot notation in the column headers. So if you import the data into your sheet first, you will see the field you need to enter as the Next Page Path, in the column header in dot notation format.

{
    "opportunities": [
        {
            "id": "AAAAAAA",
            "name": "XXXXXXXX",
            "monetaryValue": 0,
            "pipelineId": "PPPPPP",
            "pipelineStageId": "70ccf705-d479-43da-9181-218ef09f0079",
            "pipelineStageUId": "70ccf705-d479-43da-9181-218ef09f0079",
            "status": "open"
        },
	 ...............
    ],
    "meta": {
        "total": 106,
        "nextPageUrl": "http://rest.gohighlevel.com/v1/pipelines/XXXX/opportunities?startAfter=99999&startAfterId=XXXXXXX",
        "startAfterId": "XXXXXXX",
        "startAfter": 99999,
        "currentPage": 1,
        "nextPage": 2,
        "prevPage": null
    }
}

3.) Page Number Parameter

In Page Number pagination, we start by adding a page number (usually 0 or 1) to the API query parameters. Apipheny will then keep calling the API while incrementing the page number until the required conditions for Run Until are met.

Page Parameter Name	The name of the page parameter in the API URL (eg. `page` or `p`)
Start From	The starting page parameter. Most API’s start from 1, while some start from 0.
Run Until	Options: Page Count Equals, Response Field Empty, No Data Returned

4.) Offset Limit

In offset limit pagination, the server returns a specific number of results (the “limit”) starting from a certain index (the “offset”). The offset and limit values are added as parameters to the API URL.

Apipheny will start from offset 0, and for each subsequent call, automatically increment the offset by the Limit Value you specify. The API documentation should specify the maximum value of the limit that you can specify, and it is a good practice to use that value for the Limit Value. This will help minimize the number of api calls you make.

Offset Options	Offset in Query Parameters or Offset in Body
Offset Parameter Name	The name of the offset parameter in the URL
Limit Parameter Name	The name of the limit parameter in the URL
Limit Value	The maximum limit or less, as specified in the API documentation
Run Until	Options: Page Count Equals, Response Field Empty, No Data Returned

Run Until

For each of the above types of pagination, you have the option to choose when Apipheny should stop retrieving the results by using the Run Until option.

Run Until has three options to choose from:

1.) Page Count Equals: you specify a numeric limit value. This is used when you want to specify a maximum number of pages to retrieve.

2.) Response Field Empty: you specify the field in the JSON response that needs to be empty before Apipheny stops pulling data.

For example, in the API response below, we want to stop pulling data once more_data is empty (null). So the value we enter into the Response Field is more_data.

{
    "uri": "XXXX",
    "first_page_uri": "XXXX",
    "next_page_uri": "XXXX",
    "previous_page_uri": null,
    "page": 0,
    "page_size": 10,
    "more_data":null,
    "messages": [
        {
            "account_sid": "XXXX",
            "api_version": "2010-04-01",
            "body": "XXXX",
            "num_segments": 1essages/20a61d8b-87da-41dd-9dcd-1ebc8bcc5284/Media"
            },
            "messaging_service_sid": null
        },
	........
    ]
}

3.) No Data Returned: you specify the Data Field in the JSON response that needs to be empty before Apipheny stops pulling data.

In the above JSON example, if you want to stop pulling data from the API when messages item is an empty array, you would specify messages as the Data Field value.

💡 Tip: Apipheny uses dot notation in the column headers. So if you import the data into your sheet first, you will see each of the JSON fields in dot notation format in the column header.

Troubleshooting

If you run a request that returns many pages of data, you may run into Google’s service limits, as described here: https://developers.google.com/apps-script/guides/services/quotas

Currently, free Google accounts get 20,000 URL Fetch calls per day, and paid Google Workspace accounts get 100,000 per day.

Google tracks each request as a URL Fetch. For example if you run a request that returns 10 pages, Google will count 10 URL Fetch calls towards your daily quota.

If you think you’ve hit your quota limits, try splitting your requests up into smaller requests spread over a longer period of time.

Final words

API pagination is a technique used to limit the amount of data returned by an API to make it more manageable, typically by breaking up the results into smaller pages or chunks. It allows for the efficient retrieval of large amounts of data.

At Apipheny, we are constantly improving our product and adding commonly requested features, like pagination.

We will continue to improve our pagination automation features and add more pagination automation types as time goes on.

If you have any questions or need help with pagination, please contact our support team for assistance.