Advanced Filtering

SheetsAPI's query parameters compose cleanly - you can filter, sort, project, and paginate in a single request. This page shows common patterns and how to combine them.

Filter with search

The search parameter filters rows where a field contains the given value (case-insensitive substring match by default):

GET /api/spreadsheets/YOUR_USER_KEY/Products?search=category:electronics

This returns every row where the category column contains electronics.

Exact match

Add search_exact=1 to require a full-value match instead of substring:

GET /api/spreadsheets/YOUR_USER_KEY/Products?search=status:active&search_exact=1

Returns rows where status is exactly active, not inactive or retroactive.

Filtering by numeric ranges

The search parameter is string-based, so numeric range queries require client-side filtering after fetching the data. For large datasets, consider keeping a dedicated column (price_tier, stock_level) that makes filtering possible server-side.

Sort with sort

Sort by any column. Prefix with - to reverse:

# Ascending (A→Z, 0→9)
GET /api/spreadsheets/YOUR_USER_KEY/Products?sort=name

# Descending (Z→A, 9→0)
GET /api/spreadsheets/YOUR_USER_KEY/Products?sort=-price

Sorting is lexicographic by default (strings). Numeric columns are sorted numerically when all non-empty values in the column parse as numbers.

Project fields with fields

Return only the columns you need. This reduces response size significantly for wide sheets:

GET /api/spreadsheets/YOUR_USER_KEY/Products?fields=name,price,stock

Response:

{
  "data": [{ "name": "Widget", "price": "9.99", "stock": "143" }],
  "meta": { "total": 1, "limit": 20, "offset": 0 }
}

Omit fields to receive all columns.

Paginate with limit and offset

GET /api/spreadsheets/YOUR_USER_KEY/Products?limit=50&offset=100

Fetch rows 101–150. Use meta.total to know when you have reached the end.

Default: limit=20, offset=0. Maximum: limit=500.

Full pagination loop (TypeScript)

async function fetchAll<T>(userKey: string, sheet: string): Promise<T[]> {
  const all: T[] = [];
  const pageSize = 100;
  let offset = 0;
 
  while (true) {
    const res = await fetch(
      `https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${userKey}/${sheet}` +
        `?limit=${pageSize}&offset=${offset}`,
    );
    const { data, meta } = await res.json();
    all.push(...data);
    offset += pageSize;
    if (offset >= meta.total) break;
  }
 
  return all;
}

Combining parameters

All parameters compose - any combination is valid:

GET /api/spreadsheets/YOUR_USER_KEY/Orders
  ?search=status:shipped
  &sort=-created_at
  &fields=id,customer,total,created_at
  &limit=25
  &offset=0

Plain English: "Give me the 25 most recently created shipped orders, showing only id, customer, total, and created_at."

TypeScript example

const params = new URLSearchParams({
  search: "status:shipped",
  sort: "-created_at",
  fields: "id,customer,total,created_at",
  limit: "25",
  offset: "0",
});
 
const res = await fetch(
  `https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${userKey}/Orders?${params}`,
  { headers: { Authorization: `Bearer ${apiKey}` } },
);
const { data, meta } = await res.json();

Common patterns

Search + paginate a large product catalog

async function searchProducts(query: string, page = 0) {
  const limit = 20;
  const params = new URLSearchParams({
    search: `name:${query}`,
    sort: "name",
    limit: String(limit),
    offset: String(page * limit),
  });
  const res = await fetch(`${BASE}/${USER_KEY}/Products?${params}`);
  return res.json();
}

Lightweight autocomplete (fields projection)

Return just the name column to power an autocomplete dropdown:

const params = new URLSearchParams({
  search: `name:${prefix}`,
  fields: "name",
  limit: "10",
});

Latest N entries from a log sheet

const params = new URLSearchParams({
  sort: "-timestamp",
  limit: "50",
  fields: "timestamp,event,user",
});

Parameter reference