Pull every page of a paginated API into Google Sheets (Apps Script)

Most APIs give you one page of results at a time, often 100 rows, plus a way to ask for the next page. If you want all of it in a Google Sheet, you have to follow those pages until they run out. Here is a small Apps Script that does that, and the things that usually break it.

Find the paging style

Check the API docs for one of these:

• Page number: ?page=2
• Offset and limit: ?offset=200&limit=100
• Cursor: the response has a next_cursor (Stripe calls it starting_after)
• Next URL: the response body has a next link
• Link header: the HTTP Link header has rel="next"

A working loop (cursor style)

function importAll() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = [];
  let cursor = null;
  do {
    const url = 'https://api.example.com/items?limit=100' +
      (cursor ? '&cursor=' + encodeURIComponent(cursor) : '');
    const res = fetchWithRetry(url);
    const body = JSON.parse(res.getContentText());
    body.items.forEach(it => rows.push([it.id, it.name, it.created_at]));
    cursor = body.next_cursor;
  } while (cursor);
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 3).setValues([['id', 'name', 'created_at']]);
  if (rows.length) sheet.getRange(2, 1, rows.length, 3).setValues(rows);
}

function fetchWithRetry(url) {
  for (let i = 0; i < 5; i++) {
    const res = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true,
      headers: { Authorization: 'Bearer ' + PropertiesService.getScriptProperties().getProperty('API_KEY') }
    });
    const code = res.getResponseCode();
    if (code === 429 || code >= 500) { Utilities.sleep(1000 * Math.pow(2, i)); continue; }
    return res;
  }
  throw new Error('API kept failing: ' + url);
}

For page numbers, replace the cursor with a counter and stop when a page comes back empty. For a Link header, read res.getHeaders()['Link'] and pull out the rel="next" URL.

Things that break it

• Writing one row at a time. It is very slow. Collect everything in an array and call setValues once.
• Rate limits. Retry on 429 with a growing wait, as above.
• The 6-minute limit. Apps Script stops long runs. For big pulls, save progress in Script Properties and continue on a time trigger.
• Keys in the code. Keep the API key in Script Properties, not in the script.

If you'd rather not write it

I made a sheet that does all of this with no code: six paging styles, OAuth, retries, and an =IMPORTJSON() function. There is a free copy you can try: https://tanglement.ai/sheets-api-connector/

Disclosure: I make it and sell the Pro version.