Batching and bulk operations

Strategies for reading many sheets efficiently, batching writes, and avoiding rate limits when working with large datasets.

SheetsAPI exposes a single REST endpoint per sheet, which keeps individual requests simple. When you need to work with many rows or multiple sheets at once, a few patterns keep your application fast and within rate limits.

Reading a full sheet at once

By default SheetsAPI returns up to 100 rows. To read all rows in one call, set limit to a number larger than your row count:

GET /api/spreadsheets/YOUR_USER_KEY/Products?limit=5000

The response meta.total field tells you the actual row count. If meta.total > limit, you truncated the result - increase limit or paginate.

Paginated bulk reads

For very large sheets (tens of thousands of rows), fetch in pages and process each page as it arrives:

async function readAll(userKey: string, sheet: string): Promise<Record<string, string>[]> {
  const PAGE = 500;
  const rows: Record<string, string>[] = [];
  let offset = 0;
 
  while (true) {
    const res = await fetch(
      `https://api.sheetsapi.io/api/spreadsheets/${userKey}/${sheet}` +
        `?limit=${PAGE}&offset=${offset}`,
      { headers: { Authorization: `Bearer ${userKey}` } },
    );
    const { data, meta } = await res.json();
    rows.push(...data);
    offset += PAGE;
    if (offset >= meta.total) break;
  }
 
  return rows;
}

Reading multiple sheets in parallel

If your application needs data from several sheets in one render, fire requests concurrently rather than serially:

const [products, orders, customers] = await Promise.all([
  fetch(`/api/spreadsheets/${KEY}/Products?limit=200`).then((r) => r.json()),
  fetch(`/api/spreadsheets/${KEY}/Orders?limit=200`).then((r) => r.json()),
  fetch(`/api/spreadsheets/${KEY}/Customers?limit=200`).then((r) => r.json()),
]);

Three serial requests at 200 ms each = 600 ms total. Three parallel requests = ~200 ms total.

Selecting only the fields you need

Use the fields parameter to fetch only the columns your UI needs. This reduces payload size and speeds up parsing, especially in spreadsheets with many columns:

GET /api/spreadsheets/YOUR_USER_KEY/Products?fields=Name,Price,Stock

Only Name, Price, and Stock are returned - other columns are omitted from the response.

Filtering server-side vs client-side

Always prefer server-side filtering (the search param) over loading the entire sheet and filtering in JavaScript:

// Efficient - only matching rows transferred
const res = await fetch(`/api/spreadsheets/${KEY}/Orders?search=Status:Pending&limit=50`);
 
// Inefficient - transfers all rows, filters in the browser
const all = await fetch(`/api/spreadsheets/${KEY}/Orders?limit=5000`).then((r) => r.json());
const pending = all.data.filter((r: Record<string, string>) => r.Status === "Pending");

Server-side filtering is faster, uses less bandwidth, and keeps your client code simpler.

Writing data in batches

The SheetsAPI write endpoint (POST) appends one row at a time. For bulk inserts, fire requests in parallel with a concurrency cap to avoid overwhelming the API:

async function batchWrite(
  userKey: string,
  sheet: string,
  rows: Record<string, string>[],
): Promise<void> {
  const CONCURRENCY = 5;
 
  for (let i = 0; i < rows.length; i += CONCURRENCY) {
    const batch = rows.slice(i, i + CONCURRENCY);
    await Promise.all(
      batch.map((row) =>
        fetch(`https://api.sheetsapi.io/api/spreadsheets/${userKey}/${sheet}`, {
          method: "POST",
          headers: {
            Authorization: `Bearer ${userKey}`,
            "Content-Type": "application/json",
          },
          body: JSON.stringify(row),
        }),
      ),
    );
  }
}

A concurrency of 5 gives good throughput while staying well inside rate limits.

Caching responses to reduce API calls

If your data changes infrequently, cache responses on your server rather than fetching from SheetsAPI on every request. A simple in-memory TTL cache:

const cache = new Map<string, { data: unknown; expiresAt: number }>();
 
async function cachedFetch(url: string, ttlMs = 60_000): Promise<unknown> {
  const now = Date.now();
  const cached = cache.get(url);
  if (cached && cached.expiresAt > now) return cached.data;
 
  const data = await fetch(url).then((r) => r.json());
  cache.set(url, { data, expiresAt: now + ttlMs });
  return data;
}

For production deployments use a shared cache (Redis, Vercel KV, Cloudflare KV) so all instances share the same TTL window.

Rate limit guidance

Scenario	Recommendation
Single-page app fetching on load	Cache at the CDN edge (60–300 s)
Server-side render per request	Cache in-memory or in KV with 30–60 s TTL
Background sync job	Paginate with 500-row pages; sleep 200 ms between pages
Bulk import (100+ rows)	Write 5 concurrent requests, retry on 429 with exponential backoff
Multiple sheets on one page	Use `Promise.all` for parallel fetches

See rate limits for the full quota breakdown per plan.