Guide to Converting XLS Reports and Ad-hoc Queries into Paginated JSON Endpoints for BI

By Kevin Marcondes · Published on Sep 29, 2026 · Updated on Oct 11, 2026 · 8 min read

This guide walks you step by step through converting XLS reports and ad‑hoc queries into paginated JSON endpoints for BI. You’ll see a reference architecture, contract patterns, pagination strategies, performance, security, and testing. The goal is to publish consistent data for dashboards without rewriting everything from scratch.

When it makes sense to expose spreadsheets and queries as APIs

Turning spreadsheets and one-off queries into paginated JSON endpoints is useful when BI needs frequent updates, consistent formats, and automated access. Instead of email attachments, you provide a single, versioned source.

This approach also prevents discrepancies across teams. Everyone consumes the same JSON contract with predictable filters and pagination. That simplifies governance, audits access, and speeds up new dashboards.

Not every case is ideal. If the data changes every second or the spreadsheet relies on complex macros, stabilize the source first. When a query is very heavy, extract and materialize results in off-peak steps.

Signs it’s time: huge spreadsheets circulating by email, inconsistent columns, divergent versions of numbers, and repeated manual work to update dashboards.

• Dashboards rely on recurring manual extraction.
• Frequent errors due to different spreadsheet versions.
• SQL queries shared by copy/paste.
• Need for consistent filters and slices across teams.

Reference architecture: from spreadsheet/SQL to the paginated endpoint

A simple architecture solves most cases. Separate ingestion, transformation, publication, and observability. Each step can be iterated without blocking the others.

Ingestion: obtain the XLS from a trusted source or run the ad‑hoc query in a scheduled job. Save to storage with versioning for traceability. Record minimal metadata: source, timestamp, and file hash.

Transformation: standardize headers, types, dates, and apply light normalization (for example, separate dimensions and facts). Validate problematic rows and produce a data quality report.

Publication: load results into a read-optimized store (relational or columnar) or materialize into partitioned files. Expose via a REST API with stable filters and pagination. Include metadata such as total and next/prev links when appropriate.

Observability: ingestion metrics, response times, and error rates. Logs with correlation to the execution that produced the published dataset.

Start simple: even an endpoint over a dataset materialized daily removes attachments and reduces manual work. Evolve later to partial loads and near-real-time updates.

• Scheduled ingestion with versioning.
• Transformation with type validation.
• Publication via REST API with filters/pagination.
• Metrics and logs for traceability.

Consistent JSON contracts: fields, types and metadata

Define a clear and predictable contract. Choose a field naming convention—snake_case or camelCase—and stick to it. Use consistent types: numbers as numbers, not strings. Dates in ISO 8601 with timezone.

Include pagination metadata and dataset version when needed. Avoid renaming fields without versioning the route. Document nulls, defaults, and valid ranges.

Below is a practical mapping of common spreadsheet and query types to the endpoint JSON.

• Dates in ISO 8601: 2024-05-08T13:45:00Z.
• Currencies as decimal with a dot and fixed scale.
• Booleans as true/false, not "Sim/Não".
• Optional fields documented; avoid surprises.
• Errors standardized with code and message.

Pagination and filters that work for BI

Pagination ensures stable, predictable responses. Two common patterns: offset/limit and cursor (keyset). Offset/limit is simple but degrades on large sets. Cursor uses a marker derived from sorting, keeping performance more consistent.

Choose an immutable, indexed sort key like id or creation timestamp. For cursor, return next_cursor and optionally prev_cursor. Accept a page_size with reasonable limits.

Filters should be whitelisted and documented. Prefer equality, ranges, and prefixes, all backed by indexes. Avoid allowing arbitrary expressions.

Example route: GET /api/v1/sales?start_date=2024-01-01&end_date=2024-01-31&cursor=abc&limit=500. The response body can include items[], next_cursor and total_estimate when computing total is expensive.

• Offset/limit: simple, worsens with large tables.
• Cursor/keyset: fast and stable; requires fixed ordering.
• Maximum page size prevents abuse.
• Return next/prev links when possible.
• Filter only on indexed fields.

Practical steps: from XLS/SQL to the endpoint

A straightforward checklist helps you get started quickly. Adapt it to your tools and SLAs.

1) Locate the authoritative source of the report or query and automate extraction. Save each run with a timestamp and hash.

2) Parse the XLS, standardize headers and detect invalid rows; produce a rejection file for review.

3) Normalize types: dates, decimals, booleans and nulls. Document applied conversions.

4) Materialize the transformed set into a read area. Optional: partition by date.

5) Design the API: routes, filters, pagination, sorting and error codes. Provide request/response examples.

6) Implement the endpoint with page limits, compression and input validation. Log correlations.

7) Publish documentation and a simple sandbox for BI consumers to test filters and pagination.

8) Monitor latency, error rates and consumption. Plan schema reviews with versioning.

Performance and updates: caching, materialization and partial loads

Consistent latency is crucial for dashboards. Use HTTP caching with ETag/Last-Modified and return 304 when nothing changed. Reference HTTP caching best practices where helpful.

Materialize expensive results and refresh them on a scheduled window. If your query is heavy, run it off-peak and publish the final read artifact.

For more frequent updates, implement partial loads. Use update timestamps, change logs, or change-data-capture strategies.

Optimize queries and indexes before exposing filters. Reduce round-trips and serialize compact responses.

• ETag and Last-Modified to avoid unnecessary transfers.
• Daily/hourly precomputation of heavy aggregates.
• Incremental loads using last-updated markers.
• HTTP compression and field selection.

Security, governance and observability

Security must be designed from the start. Implement authentication, authorization via scopes, and rate limits. Separate datasets by sensitivity and remove PII when not required.

Audit access and keep trails of who consumed which dataset version. Publish usage limits and clear error messages. Structured logs and metrics make diagnosis easier and help prevent SLA breaches.

For governance, versioning is essential. Prefer /api/v1/... and document breaking changes. Include contact and deprecation policy in the docs.

• OAuth2/Bearer tokens and scopes per dataset.
• Rate limiting to protect infrastructure.
• Logs with correlation_id on every request.
• Route versioning and breaking-change policy.

Testing, validation and automated publishing

Create a representative sample dataset and golden JSON files to validate the contract. Adopt contract and consumer-driven tests to ensure changes don’t break dashboards.

Cover pagination, filters, and stable ordering. Generate edge cases: empty pages, max limits, and invalid filters.

Automate publishing of the API and materialized dataset in a pipeline. When schema changes are detected, require review and a new version. Record success metrics for each pipeline step.

• Contract tests with real examples.
• Edge cases for pagination and filters.
• Automated pipeline with quality gates.
• Alerts when latency or errors rise.

Frequently asked questions

Should I read directly from the XLS on every request or materialize the result?

For BI, materializing the result and serving from a read-optimized store is generally more predictable and cost-effective. XLS files vary in format and may contain inconsistent cells; materialize, validate and version each run, record metadata (source, timestamp, hash), and update in controlled windows to ensure a stable JSON contract and reduce manual work.

Offset/limit or cursor: which pagination should I choose?

Offset/limit is simple and suitable for small datasets or when consumers need arbitrary page jumps. For large tables, cursor (keyset) tends to maintain performance and stability if there is an immutable, indexed ordering key (for example id or timestamp). Evaluate the need for arbitrary jumps and cost before choosing.

How do I handle inconsistent types in a spreadsheet?

Handle inconsistencies in the transformation step: standardize headers, convert dates to ISO 8601 with timezone, normalize numbers to decimal with a dot, and map booleans to true/false. Validate invalid rows, produce a rejection file for review, and document optional fields and default values to keep the JSON contract predictable.

How often should I update BI endpoints?

Frequency depends on use: operational dashboards may need hourly updates; executive dashboards often accept daily windows. If the query is heavy, materialize off‑peak and publish the artifact. For higher freshness needs, implement partial loads using update columns or change logs, balancing cost and timeliness.

How do I protect access to the endpoints?

Implement authentication (e.g., OAuth2/Bearer), authorization by scopes, and rate limiting to protect infrastructure. Separate datasets by sensitivity, remove PII when unnecessary, and audit access with logs that include a correlation_id. Version routes and communicate deprecations to reduce breaking changes for consumers.

See also

• Pre-deploy checklist and quick rollback plan for PHP applications in production
• Security checklist for CI/CD pipelines with self‑hosted runners and secrets management
• Guide: Centralize logs with Fluentd and Elasticsearch for PHP apps on a VPS
• Guide: Organizing PostgreSQL Migrations and Rollbacks for Small Teams