BOM + IMS Web App (Google Apps Script) — README

A Google Sheets–backed web app for Bill of Materials (BOM) and Inventory In/Out (IMS) entry with fast search, client filtering, image-rich dropdowns, and auto-fill from a MasterItems sheet. This README explains how it works, how to deploy it, and how to maintain/extend it.

---

What this repo contains

• Code.gs — Server-side Apps Script (runs on Google’s servers)
• index.html — Frontend UI (HTML/CSS/JS) served by Apps Script

The app is bound to a Google Sheet and reads/writes data in real time.

---

1) Data model (Google Sheets)

Create these sheets (tabs) with exact names and headers.

1.1 MasterItems (source of truth for items)

Used by both forms for search/autofill and images.

Required headers (sample superset; your sheet already has these):

SKU Code | Client | Sewing/Packing Trims | Item Specific/Common | Item Type | Item | DESCRIPTION | Int Rate | Image URL | UOM | Vendor Name | Address | GST | Mobile | old_Dups | concat | out_concat | New code | Changed | New dups | Final Dup Check | changed_outward | Image

The code normalizes all cell values to strings; empty cells are ok.

1.2 O2D_Styles

Provides style suggestions per Client.

Client | Style ID

1.3 PO_Indent

Provides supplier search for IMS Entry; also reused by BOM form.

... | Supplier | ...

Only the Supplier column is read; other columns are ignored.

1.4 InOutForm (created on first IMS submission)

Target for IMS entries (In/Out). Created with headers automatically if missing.

1.5 BOM_Entry (you create this sheet)

Target for BOM entries. Recommended headers:

Client | Style ID | Item Type | SKU Code | Item | Supplier | Consumption | Reqd Qty | UOM | CS RATE

If you want Colour/Size capture at BOM-level (not in MasterItems), add: Colour | Size — this avoids multiplying MasterItems rows.

1.6 Challan_Photo (Google Form responses)

If you collect challan images via a Google Form, the script can XLOOKUP a direct link.

1.7 Helper

Holds a single cell A1 = last processed row marker for the XLOOKUP extender.

1.8 ErrorLogs

Created on first error; logs timestamp, message, and payload JSON.

---

2) What the app does (features & flow)

2.1 IMS (Inventory In/Out) form

• Entry (In): validates PO Number & Invoice, Client, Supplier, Date.
• Out: validates Client, Date.
• Add multiple item lines per submission.
• Search: type in SKU or Item; results filtered by Client (and Reliance as a global client).
• Autofill: choosing SKU or Item fills Item, Description, UOM, etc.
• Style suggestions: filtered by Client from O2D_Styles.
• Images in dropdown: shows Image URL thumbnails from MasterItems.
• Submit writes to InOutForm.
• Challan Photo auto-link: optional; fills a formula to look up a photo link by PO Number.

2.2 BOM form

• Select Client → all searches narrow to that client (and Reliance).

• Type Item (wide box) or SKU:

  • Item dropdown shows thumbnail + name + SKU.
  • Pick one → auto-fills Item Type, Item, UOM, CS RATE (Int Rate).

• Supplier field uses datalist from PO_Indent.

• Style ID field suggests results filtered by Client from O2D_Styles.

• Consumption and Reqd Qty are user-entered.

• Submit writes to BOM_Entry.

Both forms use debounced search (300ms) for snappy UX and limit suggestions to top 10.

---

3) Architecture & dataflow

+---------------------+         google.script.run        +---------------------+
|  index.html (UI)    |  <---------------------------->  |  Code.gs (Server)   |
|  - HTML/CSS/JS      |           (RPC bridge)           |  - SpreadsheetApp   |
|  - Autocomplete     |                                   |  - HtmlService      |
|  - Form handling    |  Reads: MasterItems,O2D_Styles,   |  - MailApp (errors) |
|  - Debounce         |        PO_Indent                  |  - Error logging    |
+----------^----------+                                   +----------v----------+
           |                                                         |
           | writes submissions                                       |
           |                                                         |
           v                                                         v
       InOutForm / BOM_Entry                                     ErrorLogs

Frontend flow (typical line add)

1. User types Item → JS filters masterItems by Client and query.
2. User selects hit → JS auto-fills fields.
3. On submit → JS composes payload → google.script.run.submitForm(...) (IMS) or .submitBOM(...) (BOM).

Server flow

• getMasterItems() loads entire MasterItems once; returns array of objects { header: value }.
• Each value is .toString() to avoid type errors (toLowerCase() on numbers).
• Search helpers (searchItems, getItemDetailsBySKU, etc.) use in-memory filtered arrays on the client for speed; server calls are used for styles list, suppliers, and submits.
• Errors are appended to ErrorLogs and emailed.

---

4) Server-side functions (Code.gs)

doGet()

Serves the index.html.

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
    .setTitle('Inventory Management');
}

getMasterItems()

Reads MasterItems; returns array of objects with all headers as keys. Converts all cell values to strings to prevent TypeError: toLowerCase is not a function.

getClients()

Unique, sorted values from the Client column of MasterItems.

getSuppliers()

Reads PO_Indent and returns unique Supplier names (sorted).

getStyleIDs(client, query)

From O2D_Styles: returns up to 10 Style IDs where Client matches selected client (or 'Reliance' allowance) and optional text query matches.

searchItems(query, client)

Optional server-side search (the UI primarily filters client-side over the cached masterItems).

getItemDetailsBySKU(sku, client) / getItemDetailsByItem(item, client)

Find single matching row in MasterItems (respecting client or 'Reliance').

submitForm(formData, formType) (IMS)

Validates Entry/Out payload, formats date (dd-MM-yyyy), looks up UOM from MasterItems, and appends rows to InOutForm. On error: appends to ErrorLogs and emails mis@fabcott.in.

extendXLOOKUP() (optional, IMS)

For each new IMS row: if PO Number present and Challan Photo empty, inserts an XLOOKUP formula to fetch image link from Challan_Photo sheet. Uses Helper!A1 as “last processed row”.

poNumberToDirectLink(poNumber) (helper)

Converts a Google Drive share URL to a direct image link (https://drive.google.com/uc?...) when applicable.

onOpen()

Adds custom menu and also calls extendXLOOKUP() once on open.

BOM Submit: If you created a dedicated submitBOM(formData) function, document its headers mapping here. If you reused submitForm pattern, note which sheet (BOM_Entry) and which headers you append.

---

5) Frontend (index.html)

5.1 UI anatomy

• Header grid: Client (select), Style ID (text with suggestions), Supplier (datalist).

• Items section:

  • Table-like header: Item | SKU | Type | UOM | Cons | Reqd

  • Each row has:

    • Item: wide input with dropdown showing image + item + SKU
    • SKU: input with dropdown
    • Type/UOM: autofilled readonly or editable as required
    • Consumption/Reqd: numeric inputs

  • “+ Add Line” button (always starts with one visible row)

• Submit button + success/error banners

5.2 Client-side data & calls

• On DOM ready:

  • getMasterItems() → caches items
  • getClients() → fills Client select
  • getSuppliers() → fills datalist

• Debounced input handlers (300ms) for Item, SKU, Style ID.

• Autocomplete renders max 10 suggestions and closes on outside click.

• Selection sets all dependent fields in-row.

• google.script.run is used for server calls and error handling.

5.3 Accessibility & performance

• Keyboard focus stays in field; suggestions render directly under the input.
• Simple CSS grid keeps 15–20 rows readable on a standard laptop.
• Debounce avoids excessive filtering; all lookups are O(n) over already-cached data.

---

6) Deployment

1. Open the target Google Sheet → Extensions → Apps Script.

2. Create files:

   • Paste Code.gs into the script editor.
   • Add a new HTML file named index and paste the index.html.

3. Adjust sheet names in Code.gs if yours differ:

   const inOutSheetName = 'InOutForm';
   const o2dStylesSheetName = 'O2D_Styles';
   // etc.

4. Authorize the script on first run (Spreadsheet, Mail).

5. Deploy as web app:

   • Deploy → New deployment → Web app
   • Execute as Me
   • Who has access: Anyone with the link (or domain, as needed)
   • Copy the URL and share with users.

6. (Optional) Add an installable trigger for onOpen if using in a shared context where the custom menu must appear for others (bound script typically suffices).

---

7) Configuration notes

• Image URLs in MasterItems should be direct links. For Google Drive, convert with poNumberToDirectLink style (uc?export=view&id=...) so thumbnails display.
• Client filter logic allows an item row if item’s Client == selected Client or Client == 'Reliance' (acts like a global/shared catalog).
• Ensure the BOM and IMS target sheets exist and have the expected headers in the correct order if you append manually (or let the script create and seed headers on first write—IMS does this).

---

8) Error handling & troubleshooting

What happens on error

• The script writes [Timestamp, Message, JSON Payload] to ErrorLogs.
• An email is sent to mis@fabcott.in with the same info.

Common issues

• TypeError: toLowerCase is not a function Cause: non-string cell (number/date) used in comparison. Fix: getMasterItems() already converts values to string via .toString(). If you add any new server search, always coerce to string before toLowerCase().

• No dropdown suggestions

  • Check you selected a Client (filters apply).
  • Ensure MasterItems has the exact header names (SKU Code, Item, Client, Image URL, UOM, Int Rate, etc.).
  • Images not showing? Confirm URLs are public and direct.

• New rows hidden by Sheet filters Use Filter Views instead of static filter, or ensure the filtered range includes appended rows; otherwise you may need to toggle filter. (This is a Google Sheets behavior, not the script.)

• Challan photo not auto-filling

  • Confirm Helper!A1 exists (holds last processed row index).
  • Confirm Challan_Photo sheet name matches.
  • PO Number must match the lookup key format.

• BOM submit rejects quantities Ensure Consumption/Reqd Qty inputs are numbers (not empty strings); the UI enforces min="0" and type="number".

---

9) Extending the app

• Colour/Size at BOM Add inputs and columns in BOM_Entry; do not duplicate MasterItems rows per colour/size. You can derive SKU-like keys as SKU-Colour-Size in reports if needed.

• Purchase vs Consumption UOM Add columns in MasterItems:

  UOM_Purchase | UOM_Consumption | Conv_Factor
  

  Then show both units in the UI and compute conversions when needed.

• ERP handoff Use a separate export sheet or Apps Script to map your MasterItems → ERP template (often: one logical item + variant attributes). Keep your original SKU in an “Alt Code” column.

---

10) FAQ

Q: Why does the app allow Reliance items for all clients? A: It’s a shared/global catalog rule by design; remove that condition in the client filter if you need strict isolation.

Q: Can I make UOM fields read-only? A: Yes—mark the autofill inputs as readonly in HTML. Or keep them editable if users must override.

Q: Can we post attachments from the form? A: The current app renders images by URL only. For uploads, you’d use a Google Form → Drive pipeline and link via PO Number (as in IMS).

---

11) Quick developer checklist

• Sheet tabs exist with exact names
• MasterItems headers match code
• index.html loads: Client dropdown populates
• Typing Item shows image+SKU dropdown
• Selecting Item auto-fills Type/UOM/Rate
• Style ID suggestions filtered by Client
• Submit writes to the right sheet (IMS: InOutForm, BOM: BOM_Entry)
• ErrorLogs created and emails received on forced error
• Web app deployed and accessible to users

---

12) Support / Handover

This README documents logic, dataflow, and function behavior so a new developer (or an ERP partner) can understand inputs/outputs without knowing Apps Script. If you change header names or add new sheets, reflect them in:

• getMasterItems (header reads)
• Any submit function (append order)
• Frontend autofill mapping

If you need a sample dataset or a mapping script for ERP import (splitting SKU & attributes), add a /tools folder with a small Python notebook or Excel Power Query steps.

---

Happy shipping!