The goal
Give the team a focused editing UI over a large operational Google Sheet — display only the relevant columns, filter by XO and Unit, and allow edits only to two specific columns (XO Updated, XO Notes) without risking the rest.
The solution
A Google Apps Script project with two surfaces: a modal Data Manager opened from a sheet menu (onOpen → 'Open Manager') and a doGet web-app deployment. Code.gs exposes a CONFIG with DISPLAY_COLUMNS, FILTER_COLUMNS and EDITABLE_COLUMNS whitelists. getAllData returns a normalized JSON payload for the HTML UI; commit handlers accept writes only to whitelisted columns.
Highlights
- One HTML shared by modal + web app surfaces
- Code-enforced column whitelist eliminates accidental overwrites
- Config-driven column references survive sheet growth
The challenge
The team needed fast editing of two specific columns but kept accidentally overwriting formula and PK columns in the raw sheet.
Code-enforced edit surface: the HTML shows all relevant columns, but only EDITABLE_COLUMNS (R, S) expose interactive inputs; server-side handlers reject writes to any other column by letter.
Power users wanted the manager inside the sheet; others wanted it as a bookmarkable web app — without maintaining two UIs.
One Interface.html consumed by showDataManager (modal) and doGet (web app). Same HTML, same API, two entry points — zero duplication.
If users added a column, hard-coded column references would break reads.
effectiveLastCol = Math.max(lastCol, maxConfigCol) + getMaxColumnCount(CONFIG.COLUMNS) ensures read ranges always cover every configured column.
What was delivered
- Code.gs with config, doGet, modal launcher, handlers and audit logging
- Interface.html shared by modal and web app
- appsscript.json manifest with required OAuth scopes
- Deployment notes
Results
2 (modal + web app)
Surfaces
13 display / 2 filter / 2 edit
Whitelist
What it taught me
- A tiny config object beats hard-coded column letters scattered through handlers
- Sharing one HTML between modal + web app is essentially free in Apps Script