Create a searchable, filterable directory

Northwind’s staff directory lives in a Sheet, which is fine until someone needs to find Priya in Operations and there are 240 rows. Sorting by name works if you remember the spelling; filter views work if you remember they exist. Neither helps the new starter trying to figure out who owns what.

This is a directory web app: one text box, one list, type-ahead filtering on every field. The data still lives in the Sheet — you edit it there — but the page renders a tiny client-side index so search feels instant. There is no database, no API, and nothing to keep in sync.

What you’ll need

• A Google Sheet acting as the directory source. Headers in row 1 — at minimum name and role; add any columns you like and they become searchable automatically.
• An Apps Script project with one .gs file and one HTML file called Directory.html.
• Editor access to the directory sheet from the account that deploys the web app.

The HTML (Directory.html)

<!-- The whole UI: one search box, one list. The data is injected at
     render time so the page works with no further server calls. -->
<input id="q" placeholder="Search..." autofocus>
<ul id="list"></ul>
<script>
  // Server-side data, baked into the page once on render. Edits to the
  // sheet show up the next time someone loads the URL — there is no
  // client polling.
  const items = <?!= JSON.stringify(items) ?>;
  const ul = document.getElementById('list');

  // Renders the list filtered by `term`. We serialise the whole row to
  // JSON before searching so the query matches across every column —
  // name, role, team, anything else the sheet contains.
  const render = (term) => {
    const matches = items.filter((i) =>
      JSON.stringify(i).toLowerCase().includes(term)
    );
    ul.innerHTML = matches
      .map((i) => '<li>' + escape(i.name) + ' &mdash; ' + escape(i.role) + '</li>')
      .join('');
  };

  // Minimal HTML escape — sheet data is trusted-ish but never trustworthy
  // enough to interpolate raw.
  const escape = (s) => String(s == null ? '' : s)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;');

  document.getElementById('q')
    .addEventListener('input', (e) => render(e.target.value.toLowerCase()));

  // Render once on load so the full list is visible before anyone types.
  render('');
</script>

The script

// The sheet that holds the directory rows. Headers in row 1; one row
// per person.
const DIRECTORY_SHEET_ID = '1abcDirectoryId';

/**
 * Serves the directory page with the whole roster pre-loaded. We do
 * this once at render time rather than on every keystroke so the page
 * is responsive and the script quota stays small.
 */
function doGet() {
  const t = HtmlService.createTemplateFromFile('Directory');
  t.items = readDirectory();
  return t.evaluate()
    .setTitle('Northwind directory')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

/**
 * Reads the directory sheet into an array of objects keyed by header.
 * Drops blank rows so the rendered list never includes empty lines.
 *
 * @returns {Object[]} Directory rows.
 */
function readDirectory() {
  const [headers, ...rows] = SpreadsheetApp.openById(DIRECTORY_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  return rows
    .filter((r) => r.some((cell) => cell !== '' && cell !== null))
    .map((r) => Object.fromEntries(headers.map((k, i) => [k, r[i]])));
}

How it works

1. doGet opens Directory.html as a template, sets items to the parsed directory rows, and evaluates the template. The whole roster is baked into the page as JSON.
2. readDirectory reads the sheet once with getDataRange().getValues() and turns each row into a {header: value} object — adding a column to the sheet automatically makes it searchable without a code change.
3. The page renders the full list on load, then re-filters on every keystroke. Filtering is client-side: it serialises each row to JSON and runs a case-insensitive includes on the lower-cased query.
4. Because every column is included in the search string, typing ops matches anyone whose row contains the word — name, team, role, notes.
5. Output is escaped before being injected into the DOM. Sheet data is not user-submitted in this example, but the habit is cheap and the bug it prevents is expensive.

Example run

The directory sheet:

name	role	team
Ada Lovelace	Lead engineer	Platform
Priya Patel	Operations	Ops
Marco Rossi	Account manager	Sales
Holt & Sons	Customer	—

Typing ops filters the list to one entry: “Priya Patel — Operations”. Clearing the box restores all four rows. Typing lovelace matches Ada by name; typing platform matches Ada by team. The filter is instant — no network roundtrip.

Deploy it

1. Click Deploy → New deployment, choose Web app, set Execute as to your account and Who has access to Anyone in your domain so only staff can open the directory.
2. Open the /exec URL. The directory loads once with every row inlined; typing into the box filters without any further network calls.
3. To pick up sheet edits, just reload the page. There is no cache to invalidate — the data is whatever getDataRange returned at render time.

Watch out for

• The full roster is sent to the browser on every page load. For a few hundred people that is a few kilobytes of JSON — fine. Past a couple of thousand rows, switch to server-side search (a google.script.run call per query) or paginate.
• Search is a plain substring match, not fuzzy. Misspellings will not match. For typo-tolerant search, ship a small Levenshtein helper to the page — but resist the urge to install a full search library for what is essentially a table.
• HTML templating with <?!= ?> does not auto-escape. Always escape before injecting into innerHTML, even when the data looks safe. The escape helper above is the minimum.
• Sensitive columns get shipped to the browser if you read them. Trim readDirectory to only the fields you want exposed — phone numbers, internal IDs and the like should stay on the server side.
• Anyone in your domain can view; nobody can edit through the page. If you need self-service updates (a person updating their own role), wire a second google.script.run call that writes back to the sheet and check the caller’s email against the row they are editing.