BOM Structurer
Build your BOM visually, export a production-ready XLSX, and protect your sheets in Google Sheets. 1,500+ part numbers, 160+ assemblies, multi-user collaboration, tested on real hardware builds.
Parts Master for components. Tree builder for assemblies. XLOOKUP-linked export. Sheet protection script included — lock down your BOM in Google Sheets with one click.
Launch Tool →The web tool runs entirely in your browser — nothing is sent to a server. The Apps Script runs inside your Google Sheet.
Getting Started
Create a workspace
Name it, name your top-level assembly, and set your hierarchy depth (2–10 levels). Each level can be renamed to match your industry language.
Build your Parts Master
Switch to the Parts Master tab and add your leaf-level components — purchased parts, raw materials, hardware. Each gets a unique internal part number and optional vendor PN, expected quantities, and enrichment data.
Structure your assemblies
Switch to BOM Structure and build your hierarchy. When adding children, type a part number to autocomplete from your Parts Master. Set quantities per assembly. The same part can appear under multiple parents.
Export, enrich, and iterate
Export a multi-sheet XLSX with XLOOKUP formulas. Fill in pricing and suppliers in Excel or Sheets, then import the XLSX back — the tool becomes your single source of truth. Upload to Google Sheets and add the Sheet Lock script to protect your data.
Data saves to your browser's local storage. For real-time multi-user collaboration with cloud sync, get in touch about the hosted version.
Two Views, One BOM
Parts Master
A flat table of your leaf-level components. Each part gets a unique internal part number, name, description, optional vendor PN, and expected quantity. This is your single source of truth for components.
BOM Structure
A visual tree of your assembly hierarchy. Assemblies get their own PNs. Children reference parts from the Parts Master via autocomplete — type a PN and select from the dropdown.
Part Number Autocomplete
When adding items to an assembly, start typing a part number. The tool searches your Parts Master and shows matching entries. Select one to auto-fill name and description.
PN Validation
Three checks in one click: duplicate PNs in Parts Master, duplicate assembly PNs (with structural fingerprinting — identical sub-assemblies sharing a PN are fine), and stale PNs in the tree that no longer exist in Parts Master.
Expected Qty & Delta
Set expected quantities in the Parts Master. The structure tab shows a live delta between actual BOM usage and expected — green check when matched, red/yellow when off. Catches quantity mistakes before they reach production.
Shared Components
The same part can appear under multiple assemblies — a common fastener used in three different sub-assemblies shows up in each one. The Parts Master stays deduplicated.
Built for Difficult BOMs
This isn't a toy. It's been stress-tested on a 1,500-part production BOM with 160+ assemblies, 33 categories, and a team building it simultaneously.
Real-Time Collaboration
Multiple users building the same BOM simultaneously via Firebase. Each person's expand/collapse state is local — you won't step on each other's view. Live sync status shows who's connected.
Flag for Review
Click the flag icon on any item to mark it for team review. Flagged items get a red highlight visible to all users. When branches are collapsed, a red badge bubbles up showing how many flagged items are nested inside — drill down without expanding everything.
Smart Search
Search by part number, name, or description. Matches highlight in red and collapsed branches auto-expand to reveal results. Your expand/collapse state is restored when you clear the search.
PN Rename Across BOM
Changed a part number? One click updates every instance across the entire tree. No manual find-and-replace. Undoable.
Dynamic Part Names
Rename a part in the Parts Master and it updates everywhere — tree display, search results, edit modals, and exports. The Parts Master is the single source of truth for component data.
Multiple Product Lines
One workspace can hold multiple top-level assemblies. Build your product platform, options packages, and accessory lines as separate trees sharing the same Parts Master.
Building Your Tree
Add & Bulk Add
Click + to add a single child with Part Number autocomplete from the Parts Master. Or use bulk add — paste part numbers one per line and names auto-fill. Tab-delimited paste also supported for Excel/Sheets.
Edit, Duplicate, Move, Flag
Edit any node inline. Duplicate branches for repeating sub-assemblies (only the parent gets renamed). Move items with a collapsible tree picker + search. Flag items for team review.
Drag to Reorder
Grip handle (six dots) to reorder items within the same parent. Drag and drop — no cutting and pasting.
Undo
Ctrl+Z or click the undo button. 10 levels of history. Delete something by accident? Undo brings the whole branch back.
Multi-Sheet XLSX with XLOOKUPs
One click generates a professionally formatted Excel workbook — branded headers, color-coded assembly rows, auto-filters, frozen panes, and XLOOKUP formulas that cascade quantity changes dynamically. Four linked sheets including an Instructions tab.
Sheet 1: BOM Structure
Full hierarchy with level columns, part numbers, parent PNs, effective quantities (cascading through parents), and type (Assembly/Part). XLOOKUP columns pull attributes from Parts Master with bounded ranges for fast recalculation.
Sheet 2: Parts Master
Your leaf-level components with all stored data pre-filled and a dynamic Total Qty Used column (SUMIF formula). Empty cells highlighted yellow for easy filling. UOM and Make/Buy columns have dropdown validation.
Sheet 3: BOM Lines
Every parent-to-child relationship as a flat row — the ERP recipe format. 100% formula-driven: PNs, names, quantities, unit prices, and line totals all reference the other sheets. Change anything upstream and BOM Lines updates automatically.
Protect Your Sheets
Upload your exported XLSX to Google Sheets and paste in the included Sheet Lock script. One-click protection keeps your BOM safe from accidental edits.
One-Click Lock / Unlock
Toggle sheet protection from a custom menu — no digging through Google Sheets settings. Locks BOM Structure, Parts Master, and any other tabs you specify. Unlock when you need to edit, re-lock when you're done.
Automatic Protection
When enabled, sheets automatically re-lock after every change via an onChange trigger. Even if someone unlocks a sheet and makes an edit, protection snaps back. Set it and forget it.
Editor Whitelist
Add trusted editor emails to the config — they can edit locked sheets without turning protection off. The sheet owner always retains access. Status and editor list viewable from the menu.
Choose What to Lock
Define which sheets to protect and which to skip in a simple config block at the top of the script. No code changes needed — just add or remove sheet names.
Import & Backup
XLSX Round-Trip
Export your BOM as XLSX, fill in pricing and suppliers in Excel or Sheets, then import it back. The tool merges all enrichment data — no re-entry on the next export.
CSV Import
Import multi-level columns (L0 Name, L1 Name, ...) or flat format with Level and Item Name. Detects Qty and Vendor PN columns automatically.
JSON Backup
Full export of your BOM tree and Parts Master as JSON. Use JSON Import to restore, or transfer your work to another browser.
Adding Sheet Lock to Your Sheet
Upload your XLSX to Google Sheets
Open Google Drive, right-click the exported XLSX, and select "Open with Google Sheets." This preserves all formulas, formatting, and dropdown validation.
Open Apps Script
In Google Sheets, go to Extensions → Apps Script. Delete the default function myFunction() code.
Paste the script and save
Download the BOM_Sheet_Lock.gs file from this page, copy its contents, and paste into the Apps Script editor. Hit Ctrl+S to save.
Run installTrigger
Select installTrigger from the function dropdown in Apps Script and click Run. Authorize when prompted. This sets up the auto-relock trigger.
Reload and use
Reload your spreadsheet. A "Sheet Lock" menu appears. Click "Turn ON" to protect your BOM Structure and Parts Master sheets. That's it.
Workspace & Navigation
Level Configuration
Click Levels in the toolbar to set hierarchy depth (2–10) and rename each level to match your product structure. Changes apply immediately to the entire tree.
Expand / Collapse
Click the chevron on any parent, or use toolbar buttons to expand/collapse the entire tree. Each user's state is independent in collaborative mode.
Light & Dark Mode
Toggle the sun/moon icon in the top right. Your preference is remembered between sessions.
Keyboard Shortcuts
Ctrl+Z to undo, Ctrl+F to focus search. More shortcuts coming.
Get the Most Out of It
Build the Parts Master first. Get your leaf-level components defined before building assemblies. It's faster to autocomplete from a populated Parts Master than to type everything manually.
Use bulk add for hardware. If you have a list of 50 fasteners going into an assembly, paste them all at once from Excel (tab-delimited: Name, PN, Description, Qty, Vendor PN).
Set expected quantities early. Enter expected quantities in the Parts Master before your BOM is complete. The delta indicator catches mistakes as you build — not after export.
Flag before meetings. Use the flag-for-review feature to mark items that need team discussion. Flags bubble up through collapsed branches so nothing gets missed.
Round-trip your data. Export the XLSX, fill in pricing and suppliers in Excel, then import it back. The tool remembers everything. Next time you export, all your data comes with it.
Lock your sheets early. Once your BOM is uploaded to Google Sheets, paste in the Sheet Lock script and turn it on. Prevents accidental edits from collaborators while you're still enriching data.
Want Real-Time Collaboration?
This demo runs locally in your browser. The full hosted version adds cloud persistence, real-time multi-user editing, and workspace sharing — so your whole team works from the same BOM.
Get in Touch