5. Web Data, APIs and Google Sheets

5.3 Many Pages: Pagination with a Parameter and a Custom Function

By Ravindra Bagale · Last updated: 6 October 2026

Start with a problem

A partner API returns dark stores 2 per page. Ravina's query reads only page 1 and misses stores.

Explain simply

Build a query for one page, turn the page number into a parameter, convert the query into a function, then call the function for a list of page numbers and expand.

Show the data

Practice pages (JSON on this course site):

About this data: This table uses columns: File, Stores. This table uses columns: Page 1 query. This table uses columns: Page. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: web_darkstores_page_1.json, web_darkstores_page_2.json, web_darkstores_page_3.json (source: src/practice/).

Demonstrate

Show the result

Your result should look like this (fictional sample):

Explain what happened

The function wrapped the one-page logic; invoking it on the Page list ran it three times and stacked the results. The base URL stayed fixed and only RelativePath changed, which keeps Service refresh possible.

Explore further

• Each page also returns pageCount (3). Use it to build the list: {1..Json.Document(...)[pageCount]} instead of hard-coding 3.

Detailed reference for this lesson:

Some sites or APIs show data page by page: …/stores?page=1, …/stores?page=2 … Build one query for a single page, turn it into a function, and call it for a list of page numbers.

// fnGetStoresPage
(PageNo as number) as table =>
let
    Source = Web.Contents("https://example.com",
                [RelativePath = "darkstores", Query = [page = Text.From(PageNo)]]),
    Page   = Html.Table(Text.FromBinary(Source), {{"Store", ".store-name"}, {"City", ".store-city"}},
                [RowSelector = ".store-card"])
in
    Page

Fix common mistakes

Practise independently

Think like a professional

Respect API page sizes and rate limits; fetch only the pages you need and log the row count each refresh.

Ravi Sir's Cards for this lesson

Revision card

मराठी cards