Filtering and Searching Google Sheets Data via REST API

Why filter server-side?

The naive approach to searching a spreadsheet is to fetch all rows and filter in the browser. It works until the sheet grows past a few hundred rows — then you're downloading megabytes of data just to show a handful of matches.

GKit SheetsAPI lets you push filtering to the server. You send a search term, and only matching rows come back. Combined with limit and offset, this means you can build a fully functional search-and-paginate UI that never touches more data than it needs.

The search parameter

search performs a substring match across a specific column. The format is field:value:

GET /api/spreadsheets/{userKey}/{sheet}?search=email:@gmail.com

This returns every row where the email column contains the string @gmail.com. The match is case-insensitive.

const BASE = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets";
 
async function searchRows(
  userKey: string,
  sheet: string,
  field: string,
  value: string
) {
  const url = new URL(`${BASE}/${userKey}/${sheet}`);
  url.searchParams.set("search", `${field}:${value}`);
 
  const res = await fetch(url.toString(), {
    headers: { Authorization: `Bearer ${process.env.GKIT_API_KEY}` },
  });
  return res.json();
}
 
// Find all contacts with "hopper" anywhere in the name column
await searchRows("uk_abc123", "Contacts", "name", "hopper");

The response shape is the same as a normal list — { data: [...], meta: { total, limit, offset } } — so your existing pagination logic works unchanged. meta.total reflects the number of matching rows, not the total sheet size.

Exact match with search_exact

Add search_exact=1 to switch from substring to exact match:

GET /api/spreadsheets/{userKey}/{sheet}?search=status:active&search_exact=1

Without search_exact, a search for status:act would also match inactive. With it, only rows where status is exactly active are returned.

const url = new URL(`${BASE}/${userKey}/${sheet}`);
url.searchParams.set("search", "status:active");
url.searchParams.set("search_exact", "1");

Use exact match when you're filtering on enum-like columns — status, plan tier, category — where partial matches would cause false positives.

Combining search with sort

sort and sort_dir work alongside search. Sort the filtered results by any column:

GET /api/spreadsheets/{userKey}/{sheet}?search=plan:pro&sort=name&sort_dir=asc

This returns all rows where plan contains pro, sorted alphabetically by name.

const url = new URL(`${BASE}/${userKey}/${sheet}`);
url.searchParams.set("search", "plan:pro");
url.searchParams.set("sort", "created_at");
url.searchParams.set("sort_dir", "desc"); // newest first

sort_dir accepts asc or desc. The default is asc.

Lean payloads with fields

When you only need a subset of columns, use fields to keep the response small:

GET /api/spreadsheets/{userKey}/{sheet}?search=country:Germany&fields=name,email

Only name and email are returned for each matching row — other columns are stripped server-side. This is especially useful when your sheet has many columns and you're only displaying a few in a search result list.

url.searchParams.set("fields", "name,email,plan");

Multi-field search workaround

The search parameter filters on one field at a time. If you need to match on two fields simultaneously — say, plan:pro AND country:Germany — the current workaround is to run one request and filter the result client-side, or to combine both conditions into a single computed column in your sheet.

A planned future version of SheetsAPI will support multi-condition queries natively. Until then, filtering on the most selective field server-side (the one that eliminates the most rows) and post-filtering in code is the practical approach.

// Fetch all "pro" rows, then filter by country in JS
const { data } = await searchRows("uk_abc123", "Contacts", "plan", "pro");
const german = data.filter(row => row.country === "Germany");

Client-side vs server-side filtering

For sheets under ~200 rows, either approach is fine. Above that, always filter server-side.

What's next

• Paginate results with limit and offset
• CRUD operations on Sheets data
• Export filtered rows as CSV