How to paginate Caspio API using the Web API connector in Bold BI?
Retrieving large datasets from an API can be challenging due to connection timeouts and data volume limitations. However, with Bold BI’s Web API connector, it is possible to efficiently gather all records from the Caspio API by leveraging dashboard parameters and configuring the data source appropriately. This article outlines the steps to accomplish this task.
Configuring Dashboard Parameters
To begin, you need to set up a list mode dashboard parameter that will control the pagination of the data source. Here’s how to configure it:
- In your Bold BI dashboard, create a new dashboard parameter.
- Name the parameter (e.g.,
limit) and set its initial value to include the first two pages (e.g.,1,2). This will allow the data source to fetch records from these pages.
The generate_series function used above is applicable for PostgreSQL as the datastore database. You need to use a similar function with other types of data stores.
- Save the parameter configuration.
Setting Up the Web API URL
With the dashboard parameter in place, you can now use it within the Web API URL to specify the pages you want to retrieve:
- Construct the Web API URL to include the dashboard parameter for pagination. For example:
https://habibi.caspio.com/rest/v2/tables/sample_table/records?q.pageSize=1000&q.pageNumber=@{{:caspio.limit}} - In this URL, the
q.pageSizeparameter is set to1000, indicating the number of records per page, andq.pageNumberis dynamically set using the dashboard parameter. - Upon creating the data source, you will receive 1000 records from page 1, as the iteration limit has been set to 1.
Refreshing Data Source to Retrieve All Records
To load all records from the Caspio API, follow these steps:
- Edit the dashboard parameter (
limit) and increase its value to cover all the pages you need. For instance, if there are 173,300 rows and you are fetching 1,000 records per page, set the limit to174(since 173,300/1,000 = 173.3, which rounds up to 174).
- Trigger the data source refresh process. This will initiate the retrieval of all records across the specified pages.
Ensure that the exact number of pages is set in the dashboard parameter to match the total number of records in the Caspio API. If the total number of records in the Caspio API increases in the future, you will need to adjust the dashboard parameter accordingly to ensure all records are retrieved.
By following these steps, you can effectively reduce the data source creation time with minimal data and later paginate the Caspio API using data refresh.
Additional References
