Build a staff inventory-lookup app

Northwind keeps its stock figures in a Sheet, but the shop floor team does not. Asking someone to open Drive on a phone, find the right tab, scroll to the SKU and read the column off a tiny grid is exactly the friction that makes people guess instead. So they guess — and the order goes out short.

This web app puts a single text box on any phone or tablet. Staff type the SKU, tap Find, and see the row that matches: name, quantity, location, whatever your sheet holds. There is no login, no styling to maintain, no second spreadsheet to keep in sync. It is the smallest possible UI on top of the data that already exists.

What you’ll need

• A Google Sheet of stock, with headers in row 1. One column must be named sku (lower case) so the script can find it; the other columns can be whatever you track.
• Two files in one Apps Script project: the .gs file below and an HTML file called Lookup.html.
• Editor access to the Sheet from the account that deploys the web app.

The script

// The spreadsheet that holds your stock — one row per SKU, headers in row 1.
const INVENTORY_SHEET_ID = '1abcInventoryId';

// The header name we treat as the lookup key. Lower case to match the sheet.
const SKU_COLUMN = 'sku';

/**
 * Serves the lookup page. Anyone with the web-app URL can open it.
 */
function doGet() {
  return HtmlService.createHtmlOutputFromFile('Lookup')
    .setTitle('Northwind stock lookup');
}

/**
 * Finds the row matching a SKU and returns it as a plain object.
 * Returns null when nothing matches — the front-end shows a "not found"
 * message in that case.
 *
 * @param {string} sku The SKU the user typed.
 * @returns {Object|null} The matching row, keyed by header name.
 */
function findSku(sku) {
  // 1. Guard against empty input — google.script.run will pass through
  //    whatever the textbox contained, including a stray space.
  const needle = String(sku || '').trim();
  if (!needle) return null;

  // 2. Read the whole sheet once. For a few thousand rows this is fine;
  //    for tens of thousands, see "Watch out for".
  const [headers, ...rows] = SpreadsheetApp.openById(INVENTORY_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // 3. Build a {headerName: columnIndex} map so we can look up the SKU
  //    column by name rather than hard-coding column letters.
  const col = Object.fromEntries(headers.map((k, i) => [k, i]));
  if (col[SKU_COLUMN] === undefined) {
    throw new Error('Sheet is missing a "' + SKU_COLUMN + '" header.');
  }

  // 4. Find the first row whose SKU matches and return it as an object.
  const match = rows.find((r) => String(r[col[SKU_COLUMN]]) === needle);
  if (!match) return null;
  return Object.fromEntries(headers.map((k, i) => [k, match[i]]));
}

The HTML (Lookup.html)

<!-- Single-input lookup page. No framework, no styling beyond the basics. -->
<input id="sku" placeholder="SKU" autofocus>
<button onclick="go()">Find</button>
<pre id="out"></pre>
<script>
  // Calls the server-side findSku and renders whatever comes back.
  function go() {
    const sku = document.getElementById('sku').value;
    const out = document.getElementById('out');
    out.textContent = 'Looking up...';
    google.script.run
      .withSuccessHandler((r) => {
        // Null means "no row matched" — show a friendly message instead
        // of the raw "null" the JSON serialiser would produce.
        out.textContent = r
          ? JSON.stringify(r, null, 2)
          : 'No SKU matched.';
      })
      .withFailureHandler((err) => {
        out.textContent = 'Error: ' + err.message;
      })
      .findSku(sku);
  }

  // Pressing Enter in the textbox triggers the lookup, same as the button.
  document.getElementById('sku').addEventListener('keydown', (e) => {
    if (e.key === 'Enter') go();
  });
</script>

How it works

1. doGet serves Lookup.html whenever someone visits the web-app URL — the whole UI is one input, one button, and one output area.
2. The page calls findSku over google.script.run when the user clicks Find or hits Enter, passing the typed SKU as a string.
3. findSku trims the input and bails out for empty strings, so an idle click does not trigger a sheet read.
4. The script reads the whole sheet once with getDataRange().getValues() and builds a header-to-column-index map. That lets the rest of the code refer to columns by name (col.sku) instead of brittle indices.
5. It finds the first row whose sku cell matches and returns the row as an object keyed by header name — so adding a column to the sheet automatically shows up in the output without any code change.
6. The page renders the object as pretty-printed JSON. If nothing matched, the null return turns into a “No SKU matched” message.

Example run

The inventory sheet looks like this:

sku	name	qty	location
NW-001	Brass hinge	42	Aisle 3
NW-002	Oak shelf	7	Aisle 1
NW-003	Steel bracket	0	Out of stock

A staffer opens the web app on a phone, types NW-002, taps Find, and sees:

{
  "sku": "NW-002",
  "name": "Oak shelf",
  "qty": 7,
  "location": "Aisle 1"
}

Typing NW-999 instead shows No SKU matched. — no row, no error.

Deploy it

1. In the Apps Script editor click Deploy then New deployment.
2. Pick Web app, set Execute as to your account and Who has access to Anyone with the link (or Anyone in your domain for staff-only).
3. Copy the web-app URL and share it with the floor team — bookmark it on the shared tablet so it is one tap away.

When you change the script you must redeploy: Deploy → Manage deployments → edit → New version.

Watch out for

• Anyone with the URL can run lookups. If the data is sensitive, set access to Anyone in your domain and share the link internally only.
• getDataRange().getValues() reads the entire sheet on every call. For a few thousand rows that is fine; past ten thousand, switch to a cached map (see Cache API responses to stay under quotas) or use a query-style filter at read time.
• SKU matching is case-sensitive and exact — nw-002 will not match NW-002. If you want a forgiving match, .toUpperCase() both sides before comparing.
• Edits to the sheet are reflected on the next lookup, but Apps Script does cache reads briefly within a single execution. If a teammate just edited a row and the new value is missing, retry a few seconds later.