Build a Sheets-powered mini CRM app

Northwind’s contact list lives in a spreadsheet, which is fine for storage but awkward for daily use. Anyone who needs to look someone up has to scroll a wide grid, and logging a call means finding the right row and typing into a cramped cell. People stop bothering, and the activity history quietly goes stale.

This automation puts a proper front end on that same Sheet. A small web app lists every contact, opens a detail page for any one of them, and lets the team add timestamped notes from a form. The data never leaves Sheets — the app is just a friendlier window onto it — so you keep the spreadsheet you already trust and gain an interface people will actually use.

What you’ll need

• A Contacts spreadsheet with a header row. The script expects an id column plus whatever fields you track — name, email, company, and so on.
• A Notes spreadsheet with three columns: contactId, note, date. Each note row links back to a contact by its id.
• Two HTML files in the same Apps Script project — CrmList.html and CrmDetail.html — for the list and detail views.
• The project deployed as a web app (Deploy → New deployment → Web app).

The script

// The two spreadsheets that back the app.
const CONTACTS_SHEET_ID = '1abcContactsId';
const NOTES_SHEET_ID = '1abcNotesId';

/**
 * Web app entry point. With no parameters it shows the contact list;
 * with ?id=X it shows the detail page for that one contact.
 */
function doGet(e) {
  return e.parameter.id
    ? renderContact(e.parameter.id)
    : renderList();
}

/**
 * Renders the full contact list into the CrmList template.
 */
function renderList() {
  const t = HtmlService.createTemplateFromFile('CrmList');
  // Hand the template every contact as an array of plain objects.
  t.contacts = readSheet(CONTACTS_SHEET_ID);
  return t.evaluate();
}

/**
 * Renders the detail page for a single contact, including its notes.
 */
function renderContact(id) {
  const contacts = readSheet(CONTACTS_SHEET_ID);
  // Keep only the notes that belong to this contact.
  const notes = readSheet(NOTES_SHEET_ID).filter((n) => n.contactId === id);

  const t = HtmlService.createTemplateFromFile('CrmDetail');
  t.contact = contacts.find((c) => c.id === id);
  t.notes = notes;
  return t.evaluate();
}

/**
 * Appends a timestamped note for a contact. Called from the detail
 * page via google.script.run.
 */
function addNote(id, text) {
  // Bail out early on an empty note so blank rows never land in the sheet.
  if (!text || !String(text).trim()) return;

  SpreadsheetApp.openById(NOTES_SHEET_ID).getSheets()[0]
    .appendRow([id, text, new Date()]);
}

/**
 * Reads a sheet and returns its rows as objects keyed by the header row.
 * One helper, used for both the contacts and the notes sheets.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. doGet is the single entry point Apps Script calls for every request. It checks the URL: if there is an id parameter it shows one contact, otherwise it shows the list.
2. renderList reads the contacts spreadsheet, hands the whole array to the CrmList template, and returns the evaluated HTML — a clickable list.
3. renderContact reads both sheets, finds the matching contact, filters the notes down to that contact’s contactId, and passes both to the CrmDetail template.
4. addNote is called from the detail page through google.script.run. It guards against empty input, then appends a row of contactId, note text, and the current timestamp to the notes sheet.
5. readSheet does the shared work — it pairs each header cell with each row cell so the rest of the code works with named objects instead of array indexes.

Example run

The Contacts sheet holds plain rows:

id	name	company	email
c1	Priya Shah	Lumen Co	[email protected]
c2	Tom Reed	Harbour Ltd	[email protected]

Visiting the deployed web app URL shows both names as links. Clicking Priya opens ?id=c1, which renders her details plus any notes already filed against c1. Submitting “Called re: renewal — happy to extend” through the form appends a row to the Notes sheet:

contactId	note	date
c1	Called re: renewal — happy to extend	2026-05-25 14:32

Reload the detail page and the new note appears at the bottom of her history.

Run it

This is an on-demand app, so the “running” step is the deployment:

1. In the Apps Script editor, choose Deploy → New deployment → Web app.
2. Set Execute as to yourself and Who has access to your Northwind domain (or a wider audience if needed).
3. Share the deployment URL with the team — that link is the app.

After any code change, redeploy to the same deployment so the URL stays stable.

Watch out for

• Every request re-reads the spreadsheets. That is fine for a few hundred contacts; for thousands, the list page will feel slow. Cache the contact list with CacheService or paginate the results.
• There is no locking on addNote. appendRow is safe for occasional use, but if many people file notes at once you may want LockService to serialise the writes.
• doGet returns plain HTML — anyone with the URL can read every contact. Set Who has access carefully, and never widen it to “Anyone” for real customer data.
• IDs must be unique and stable. renderContact matches on id, so reusing or renumbering an id will point notes at the wrong person.
• HtmlService renders inside a sandboxed iframe. Keep the templates simple — some external scripts and styles will not load.