FREELEARNING365.COM
🚀 Complete Free ERP — 5 Part Series
Google Sheets + Apps Script · POS · Inventory · Reports · AI
01 · 🏗️ Foundation
02 · 🔐 Authentication
03 · 🛒 POS & Inventory
04 · 📊 Reports & P&L
05 · 🤖 AI & Go-Live
Follow the complete journey: Foundation → Security → POS → Intelligence → Scale
Complete POS, Stock Ledger, Purchases & Customer Dues — for Real Bangladeshi Shops
Part 1 gave you the database. Part 2 gave you secure login and roles. Now comes the part your cashier actually touches every day: the Point of Sale screen, the Product Catalog, the Stock Ledger, Purchase (GRN) entry, and Customer Dues — with thermal receipt printing and five real shop scenarios from grocery to pharmacy to boutique.
POS with cart
Stock Ledger audit
80mm receipt
★
The Real Test: A Customer Walks In
It's 6:47 PM in Mirpur. The shop is busy. A customer walks in with a plastic bag, says "চাল দুই কেজি, তেল এক লিটার, চিনি আধা কেজি" — and expects to walk out in under two minutes with a printed receipt. The owner expects that every rupee of that sale to land in today's report, the stock to decrease by exactly the right amount, and if the customer pays ৳300 and owes ৳47, that due to appear on the customer's account.
That's what Part 3 delivers. It is the largest, most detailed part of the series — because this is where the ERP stops being "an interesting project" and becomes the tool your cashier cannot work without.
🎯
What you will have at the end of Part 3: A fully working POS terminal your staff can use on a phone, tablet or laptop; a product catalog screen where you add products with prices and barcodes; a proper double-entry Stock Ledger that reconciles physical stock; a Purchase (GRN) screen for goods received from suppliers; a Customer page that tracks who owes the shop money; and thermal-printer-ready receipts in Bengali + English.
01
What You Will Build in Part 3
POS Terminal
Search products, build a cart, apply discount, take payment, print receipt. Works on mobile.
Product Catalog
Add / edit / deactivate products. Set cost, sale, reorder level and barcode.
Stock Ledger
Every IN/OUT movement with running balance. Full audit trail.
Purchase / GRN
Goods Received Note with supplier, items, cost, payment and due.
Customer Dues
Track বাকি, record payments, print statements. Ideal for neighbourhood shops.
Thermal Receipts
80mm printable receipts in Bengali + English, with shop info and thank-you.
1
5 Bangladeshi Shop Scenarios We'll Support
Before writing a single line of code, let's picture the five shops we are actually building for. If the design serves all five, it serves almost any small business in Bangladesh.
🛒
1. Karim's Grocery — মিরপুর, ঢাকা
Neighbourhood kacha bazar shop · 300 products · 4 staff · 200 sales/day
- Payment: Mostly cash, some bKash, monthly dues for regular customers.
- Challenge: Rice, oil, sugar prices change weekly. Stock needs to be accurate or the shop runs out.
- Our design: POS with quantity input in kg, barcode scanning for packaged goods, customer due tracking with monthly statements.
💊
2. Rahman Pharmacy — ধানমন্ডি, ঢাকা
Medicine retailer · 1,500 SKUs · 3 staff · 150 sales/day
- Payment: Cash and card. Some institutional billing with 30-day terms.
- Challenge: Expiry dates. Batch numbers. Regulatory receipts with medicine names.
- Our design: Extendable — Part 4 will add batch/expiry. Part 3 supports the core POS and stock ledger.
📱
3. Nasrin Electronics — চট্টগ্রাম
Mobile accessories · 800 SKUs · 5 staff · 60 sales/day (high-value)
- Payment: bKash, Nagad, card, and easy-installment dues.
- Challenge: High ticket values (৳5,000 – ৳45,000). IMEI tracking. Warranty receipts.
- Our design: Product "notes" field can store IMEI. Customer page tracks installment customers.
👗
4. Fatema Boutique — সিলেট
Ladies' clothing · 400 SKUs (variants) · 2 staff · 40 sales/day
- Payment: Cash and bKash. Custom order prepayments.
- Challenge: Size and colour variants. Some items are one-of-a-kind.
- Our design: Variants handled as separate ProductIds with a common name prefix (e.g. "Kameez-Red-M"). Simple and effective.
🍽️
5. Rahim Restaurant — উত্তরা, ঢাকা
Small eatery · 120 menu items · 6 staff · 300 orders/day (peak hours)
- Payment: Cash at counter, occasional bKash.
- Challenge: Multiple orders per second during lunch rush. Table numbers.
- Our design: POS LockService handles up to ~30 simultaneous writes. Table number stored in CustomerId field as "TABLE-07".
🧠
The common thread: Every one of these shops needs the same four things — a fast POS, an accurate stock count, a record of who owes money, and a printed receipt. Part 3 delivers exactly those, for all five.
2
Products Backend (Products.gs)
Add a new Apps Script file called Products.gs. This is where all product catalog operations live. Every function starts with the two-line guard we built in Part 2.
/**
* Products.gs
* Product catalog: create, read, update, deactivate, search.
*
* Sheet: Products
* Columns: A=ProductId B=Name C=Category D=Unit
* E=CostPrice F=SalePrice G=IsActive H=ReorderLevel
* I=CreatedAt J=Barcode K=Notes
*/
/**
* Returns all ACTIVE products for the POS screen.
* Skips inactive products so they cannot be sold.
*/
function getActiveProducts(token) {
validateSession(token);
const all = readAll('Products');
return all
.filter(function(p) { return p.IsActive === true; })
.map(function(p) {
return {
id: p.ProductId,
name: p.Name,
category: p.Category || '',
unit: p.Unit || 'pcs',
costPrice: Number(p.CostPrice) || 0,
salePrice: Number(p.SalePrice) || 0,
reorderLevel: Number(p.ReorderLevel) || 0,
barcode: p.Barcode || '',
notes: p.Notes || ''
};
});
}
/**
* Returns ALL products (active and inactive) for the admin UI.
* Admin / Manager only.
*/
function getAllProducts(token) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
const all = readAll('Products');
return all.map(function(p) {
return {
id: p.ProductId,
name: p.Name,
category: p.Category || '',
unit: p.Unit || 'pcs',
costPrice: Number(p.CostPrice) || 0,
salePrice: Number(p.SalePrice) || 0,
isActive: p.IsActive === true,
reorderLevel: Number(p.ReorderLevel) || 0,
createdAt: p.CreatedAt,
barcode: p.Barcode || '',
notes: p.Notes || ''
};
});
}
/**
* Creates a new product. Returns the new ProductId.
* Admin / Manager only.
*/
function createProduct(token, data) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
const name = trim(data.name);
if (!name) throw new Error('Product name is required.');
const salePrice = Number(data.salePrice);
if (!salePrice || salePrice <= 0) {
throw new Error('Sale price must be a positive number.');
}
const costPrice = Number(data.costPrice) || 0;
if (costPrice > salePrice) {
throw new Error('Cost price cannot exceed sale price. Check your numbers.');
}
// Check for duplicate barcode
const barcode = trim(data.barcode);
if (barcode) {
const all = readAll('Products');
for (let i = 0; i < all.length; i++) {
if (trim(all[i].Barcode) === barcode) {
throw new Error('Barcode already used by another product.');
}
}
}
const productId = 'P' + uuid().substring(0, 8).toUpperCase();
appendRow('Products', [
productId,
name,
trim(data.category),
trim(data.unit) || 'pcs',
costPrice,
salePrice,
true,
Number(data.reorderLevel) || 0,
new Date(),
barcode,
trim(data.notes)
]);
// If opening stock is provided, record it in the ledger.
const openingStock = Number(data.openingStock) || 0;
if (openingStock > 0) {
appendRow('StockLedger', [
new Date(), productId, 'OPENING', 'OPENING',
openingStock, 0, openingStock, 'Opening stock at creation'
]);
}
logAction(session.email, 'PRODUCT_CREATED ' + productId + ' ' + name);
return {
success: true,
productId: productId,
message: 'Product "' + name + '" created.'
};
}
/**
* Updates an existing product's fields.
* Supports partial updates — only provided fields are changed.
*/
function updateProduct(token, productId, updates) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
const s = sheet('Products');
const values = s.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
if (values[i][0] === productId) {
// Column mapping: A=1 B=2 ... K=11
const row = i + 1;
const toUpdate = {};
if (updates.name !== undefined) toUpdate[2] = trim(updates.name);
if (updates.category !== undefined) toUpdate[3] = trim(updates.category);
if (updates.unit !== undefined) toUpdate[4] = trim(updates.unit);
if (updates.costPrice !== undefined) toUpdate[5] = Number(updates.costPrice);
if (updates.salePrice !== undefined) toUpdate[6] = Number(updates.salePrice);
if (updates.isActive !== undefined) toUpdate[7] = updates.isActive === true;
if (updates.reorderLevel !== undefined) toUpdate[8] = Number(updates.reorderLevel);
if (updates.barcode !== undefined) toUpdate[10] = trim(updates.barcode);
if (updates.notes !== undefined) toUpdate[11] = trim(updates.notes);
Object.keys(toUpdate).forEach(function(col) {
s.getRange(row, Number(col)).setValue(toUpdate[col]);
});
logAction(session.email, 'PRODUCT_UPDATED ' + productId);
return { success: true, productId: productId };
}
}
throw new Error('Product not found: ' + productId);
}
/**
* Deactivates a product. Never deletes — preserves history.
*/
function deactivateProduct(token, productId) {
return updateProduct(token, productId, { isActive: false });
}
/**
* Reactivates a previously deactivated product.
*/
function reactivateProduct(token, productId) {
return updateProduct(token, productId, { isActive: true });
}
/**
* Fast product search for the POS. Matches by:
* · name (startsWith then contains)
* · barcode (exact)
* · category (contains)
* · productId (exact)
* Returns at most `limit` results, prioritising barcode and startsWith matches.
*/
function searchProducts(token, query, limit) {
validateSession(token);
query = trim(query).toLowerCase();
limit = limit || 20;
if (!query) return [];
const all = readAll('Products');
const exactBarcode = [];
const startsWith = [];
const contains = [];
for (let i = 0; i < all.length; i++) {
const p = all[i];
if (p.IsActive !== true) continue;
const name = String(p.Name || '').toLowerCase();
const bc = String(p.Barcode || '').toLowerCase();
const cat = String(p.Category || '').toLowerCase();
const pid = String(p.ProductId || '').toLowerCase();
const mapped = {
id: p.ProductId, name: p.Name, category: p.Category,
unit: p.Unit, salePrice: Number(p.SalePrice) || 0,
costPrice: Number(p.CostPrice) || 0,
barcode: p.Barcode || ''
};
if (bc === query || pid === query) {
exactBarcode.push(mapped);
} else if (name.indexOf(query) === 0) {
startsWith.push(mapped);
} else if (name.indexOf(query) !== -1 || cat.indexOf(query) !== -1) {
contains.push(mapped);
}
}
return exactBarcode
.concat(startsWith)
.concat(contains)
.slice(0, limit);
}
/**
* Returns all distinct categories. Used to build the category filter.
*/
function getCategories(token) {
validateSession(token);
const all = readAll('Products');
const seen = {};
const out = [];
all.forEach(function(p) {
const c = trim(p.Category);
if (c && !seen[c]) { seen[c] = true; out.push(c); }
});
return out.sort();
}
⚡
Performance note:
searchProducts does a single read of the Products sheet and filters in memory. For up to ~5,000 products this is faster than incremental sheet reads, because Apps Script charges a fixed cost per sheet call regardless of row count. When you approach 20,000 products, move the search into the frontend by loading all products once and filtering in JavaScript.
3
Products UI (Products.html)
Here is what the Products catalog page looks like. It combines a data table with a modal editor and inline deactivate/reactivate actions.
<!DOCTYPE html>
<html lang="bn">
<head>
<meta charset="UTF-8">
<title>Products — <?= COMPANY_NAME ?></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
body { background: #f8fafc; font-family: "Segoe UI", Roboto, "Noto Sans Bengali", sans-serif; }
.fl3-table th { background: #f1f5f9; font-size: 12px; letter-spacing: .3px;
text-transform: uppercase; color: #475569; }
.fl3-table td { vertical-align: middle; font-size: 14px; }
.fl3-badge-active { background: #dcfce7; color: #166534; }
.fl3-badge-inactive { background: #fee2e2; color: #991b1b; }
.fl3-taka { color: #0e7490; font-weight: 700; }
.fl3-searchbar { position: relative; }
.fl3-searchbar input { padding-left: 38px; }
.fl3-searchbar svg { position: absolute; left: 12px; top: 11px; width: 18px; height: 18px; color: #94a3b8; }
.fl3-barcode-chip {
font-family: ui-monospace, monospace; font-size: 11.5px;
background: #f1f5f9; padding: 2px 6px; border-radius: 6px; color: #475569;
}
</style>
</head>
<body>
<nav class="navbar navbar-dark" style="background: linear-gradient(90deg,#6d28d9,#06b6d4);">
<div class="container-fluid">
<a class="navbar-brand fw-bold" href="?page=Dashboard"><?= COMPANY_NAME ?> ERP</a>
<div class="d-flex align-items-center text-white">
<a class="text-white me-3 small text-decoration-none" href="?page=POS">POS</a>
<a class="text-white me-3 small text-decoration-none" href="?page=Stock">Stock</a>
<span id="userBadge"></span>
<button class="btn btn-outline-light btn-sm ms-3"
onclick="fl365Logout()">Logout</button>
</div>
</div>
</nav>
<div class="container py-4">
<div class="d-flex flex-wrap justify-content-between align-items-center mb-4 gap-2">
<div>
<h4 class="mb-0">পণ্য তালিকা · Product Catalog</h4>
<small class="text-muted">Add, edit, and manage your shop's products.</small>
</div>
<button class="btn btn-primary" id="newProductBtn">
+ নতুন পণ্য / New Product
</button>
</div>
<div class="row g-3 mb-3">
<div class="col-md-6">
<div class="fl3-searchbar">
<svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2">
<circle cx="11" cy="11" r="7"/><path d="m21 21-4.3-4.3"/>
</svg>
<input type="text" class="form-control" id="searchInput"
placeholder="Search by name, barcode or category...">
</div>
</div>
<div class="col-md-3">
<select class="form-select" id="categoryFilter">
<option value="">All Categories</option>
</select>
</div>
<div class="col-md-3">
<select class="form-select" id="statusFilter">
<option value="active">Active only</option>
<option value="all">All products</option>
<option value="inactive">Inactive only</option>
</select>
</div>
</div>
<div class="card">
<div class="table-responsive">
<table class="table fl3-table mb-0">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Category</th>
<th>Unit</th>
<th class="text-end">Cost</th>
<th class="text-end">Sale</th>
<th class="text-end">Stock</th>
<th>Status</th>
<th style="width:170px;">Actions</th>
</tr>
</thead>
<tbody id="productTableBody">
<tr><td colspan="9" class="text-center text-muted py-5">
Loading products...
</td></tr>
</tbody>
</table>
</div>
</div>
</div>
<!-- Product Editor Modal -->
<div class="modal fade" id="productModal" tabindex="-1">
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="modalTitle">New Product</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
</div>
<div class="modal-body">
<div class="alert alert-danger d-none" id="formError"></div>
<input type="hidden" id="editProductId">
<div class="row g-3">
<div class="col-md-8">
<label class="form-label">Product Name *</label>
<input type="text" class="form-control" id="fName"
placeholder="e.g. চাল ৫ কেজি / Rice 5kg">
</div>
<div class="col-md-4">
<label class="form-label">Unit</label>
<select class="form-select" id="fUnit">
<option value="pcs">pcs</option>
<option value="kg">kg</option>
<option value="gm">gm</option>
<option value="litre">litre</option>
<option value="bag">bag</option>
<option value="box">box</option>
<option value="dozen">dozen</option>
</select>
</div>
<div class="col-md-4">
<label class="form-label">Category</label>
<input type="text" class="form-control" id="fCategory"
placeholder="Grocery / Medicine / Electronics"
list="categoryList">
<datalist id="categoryList"></datalist>
</div>
<div class="col-md-4">
<label class="form-label">Barcode</label>
<input type="text" class="form-control" id="fBarcode"
placeholder="Scan or type barcode">
</div>
<div class="col-md-4">
<label class="form-label">Cost Price (৳)</label>
<input type="number" class="form-control" id="fCost"
step="0.01" placeholder="0.00">
</div>
<div class="col-md-4">
<label class="form-label">Sale Price (৳) *</label>
<input type="number" class="form-control" id="fSale"
step="0.01" placeholder="0.00">
</div>
<div class="col-md-4">
<label class="form-label">Reorder Level</label>
<input type="number" class="form-control" id="fReorder"
placeholder="Alert when stock drops below this">
</div>
<div class="col-md-4" id="openingStockField">
<label class="form-label">Opening Stock</label>
<input type="number" class="form-control" id="fOpening"
placeholder="Optional, only at creation">
</div>
<div class="col-md-12">
<label class="form-label">Notes</label>
<textarea class="form-control" id="fNotes" rows="2"
placeholder="Optional. IMEI, batch, expiry, or any note."></textarea>
</div>
</div>
</div>
<div class="modal-footer">
<button class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
<button class="btn btn-primary" id="saveProductBtn">Save Product</button>
</div>
</div>
</div>
</div>
<?!= include('JS') ?>
<script>
let productModal, allProducts = [];
renderUserBadge('userBadge');
function loadProducts() {
apiCall('getAllProducts')
.then(function(list) {
allProducts = list;
populateCategoryFilter(list);
renderProducts();
})
.catch(function(err) {
document.getElementById('productTableBody').innerHTML =
'<tr><td colspan="9" class="text-center text-danger py-5">'
+ err.message + '</td></tr>';
});
}
function populateCategoryFilter(list) {
const cats = {};
list.forEach(function(p) { if (p.category) cats[p.category] = true; });
const sel = document.getElementById('categoryFilter');
const cur = sel.value;
sel.innerHTML = '<option value="">All Categories</option>'
+ Object.keys(cats).sort().map(function(c) {
return '<option value="' + c + '">' + c + '</option>';
}).join('');
sel.value = cur;
// Also fill the datalist for the editor.
document.getElementById('categoryList').innerHTML =
Object.keys(cats).map(function(c) { return '<option value="' + c + '">'; }).join('');
}
function renderProducts() {
const q = document.getElementById('searchInput').value.trim().toLowerCase();
const cat = document.getElementById('categoryFilter').value;
const status = document.getElementById('statusFilter').value;
const filtered = allProducts.filter(function(p) {
if (status === 'active' && !p.isActive) return false;
if (status === 'inactive' && p.isActive) return false;
if (cat && p.category !== cat) return false;
if (q) {
const hay = (p.name + ' ' + p.barcode + ' ' + p.category).toLowerCase();
if (hay.indexOf(q) === -1) return false;
}
return true;
});
const tbody = document.getElementById('productTableBody');
if (!filtered.length) {
tbody.innerHTML = '<tr><td colspan="9" class="text-center text-muted py-5">'
+ 'No products match your filters.</td></tr>';
return;
}
tbody.innerHTML = filtered.map(function(p) {
const activeBadge = p.isActive
? '<span class="badge fl3-badge-active">Active</span>'
: '<span class="badge fl3-badge-inactive">Inactive</span>';
const bc = p.barcode
? '<div class="fl3-barcode-chip">' + p.barcode + '</div>' : '';
return
'<tr>' +
'<td><code style="font-size:11px;">' + p.id + '</code>' + bc + '</td>' +
'<td><strong>' + p.name + '</strong></td>' +
'<td>' + (p.category || '—') + '</td>' +
'<td>' + p.unit + '</td>' +
'<td class="text-end"><span class="fl3-taka">৳</span> '
+ Number(p.costPrice).toFixed(2) + '</td>' +
'<td class="text-end"><span class="fl3-taka">৳</span> '
+ Number(p.salePrice).toFixed(2) + '</td>' +
'<td class="text-end"><span id="stock-' + p.id + '">—</span></td>' +
'<td>' + activeBadge + '</td>' +
'<td>' +
'<button class="btn btn-sm btn-outline-primary me-1" onclick="editProduct(\''
+ p.id + '\')">Edit</button>' +
(p.isActive
? '<button class="btn btn-sm btn-outline-danger" onclick="toggleProductActive(\''
+ p.id + '\', false)">Disable</button>'
: '<button class="btn btn-sm btn-outline-success" onclick="toggleProductActive(\''
+ p.id + '\', true)">Enable</button>') +
'</td>' +
'</tr>';
}).join('');
// Async-fill stock levels in the background
filtered.forEach(function(p) {
apiCall('getStockLevel', p.id)
.then(function(stock) {
const el = document.getElementById('stock-' + p.id);
if (el) {
el.textContent = stock + ' ' + p.unit;
if (p.reorderLevel && stock <= p.reorderLevel) {
el.classList.add('text-danger', 'fw-bold');
}
}
})
.catch(function() { /* ignore */ });
});
}
window.editProduct = function(id) {
const p = allProducts.find(function(x) { return x.id === id; });
if (!p) return;
document.getElementById('modalTitle').textContent = 'Edit Product — ' + p.name;
document.getElementById('editProductId').value = p.id;
document.getElementById('fName').value = p.name;
document.getElementById('fUnit').value = p.unit;
document.getElementById('fCategory').value = p.category;
document.getElementById('fBarcode').value = p.barcode;
document.getElementById('fCost').value = p.costPrice;
document.getElementById('fSale').value = p.salePrice;
document.getElementById('fReorder').value = p.reorderLevel;
document.getElementById('fNotes').value = p.notes;
document.getElementById('openingStockField').style.display = 'none';
document.getElementById('formError').classList.add('d-none');
productModal.show();
};
window.toggleProductActive = function(id, activate) {
if (!confirm((activate ? 'Enable ' : 'Disable ') + 'this product?')) return;
const fn = activate ? 'reactivateProduct' : 'deactivateProduct';
apiCall(fn, id)
.then(loadProducts)
.catch(function(err) { alert(err.message); });
};
document.getElementById('newProductBtn').addEventListener('click', function() {
document.getElementById('modalTitle').textContent = 'New Product';
document.getElementById('editProductId').value = '';
['fName','fCategory','fBarcode','fCost','fSale','fReorder','fNotes','fOpening']
.forEach(function(id) { document.getElementById(id).value = ''; });
document.getElementById('fUnit').value = 'pcs';
document.getElementById('openingStockField').style.display = '';
document.getElementById('formError').classList.add('d-none');
productModal.show();
});
document.getElementById('saveProductBtn').addEventListener('click', function() {
const errBox = document.getElementById('formError');
errBox.classList.add('d-none');
const id = document.getElementById('editProductId').value;
const payload = {
name: document.getElementById('fName').value.trim(),
unit: document.getElementById('fUnit').value,
category: document.getElementById('fCategory').value.trim(),
barcode: document.getElementById('fBarcode').value.trim(),
costPrice: Number(document.getElementById('fCost').value) || 0,
salePrice: Number(document.getElementById('fSale').value) || 0,
reorderLevel: Number(document.getElementById('fReorder').value) || 0,
notes: document.getElementById('fNotes').value.trim()
};
let promise;
if (id) {
promise = apiCall('updateProduct', id, payload);
} else {
payload.openingStock = Number(document.getElementById('fOpening').value) || 0;
promise = apiCall('createProduct', payload);
}
promise
.then(function() {
productModal.hide();
loadProducts();
})
.catch(function(err) {
errBox.textContent = err.message;
errBox.classList.remove('d-none');
});
});
document.getElementById('searchInput').addEventListener('input', renderProducts);
document.getElementById('categoryFilter').addEventListener('change', renderProducts);
document.getElementById('statusFilter').addEventListener('change', renderProducts);
productModal = new bootstrap.Modal(document.getElementById('productModal'));
loadProducts();
</script>
</body>
</html>
4
Stock Ledger Deep Dive
Part 1 introduced the ledger concept. In Part 3 we implement it, because every POS sale and every purchase writes to it. Get this right and stock will never disagree with reality.
The Ledger Sheet Revisited
| Col | Header | Example |
|---|---|---|
| A | Date | 2026-09-10 10:12:00 |
| B | ProductId | P8F3A9B2C |
| C | Type | OPENING / PURCHASE / SALE / ADJUST / RETURN / DAMAGE |
| D | RefNo | INV-2026-00042 / GRN-2026-00018 |
| E | QtyIn | 100 |
| F | QtyOut | 5 |
| G | Balance | 95 |
| H | Note | Optional free text |
What a Real Ledger Looks Like After a Week
Date | ProductId | Type | RefNo | QtyIn | QtyOut | Balance
------------|------------|----------|----------------|-------|--------|--------
2026-09-01 | P-RICE-5KG | OPENING | OPENING | 50 | 0 | 50
2026-09-02 | P-RICE-5KG | PURCHASE | GRN-2026-00001 | 100 | 0 | 150
2026-09-02 | P-RICE-5KG | SALE | INV-2026-00001 | 0 | 2 | 148
2026-09-02 | P-RICE-5KG | SALE | INV-2026-00002 | 0 | 1 | 147
2026-09-03 | P-RICE-5KG | SALE | INV-2026-00005 | 0 | 3 | 144
2026-09-03 | P-RICE-5KG | RETURN | INV-2026-00005 | 3 | 0 | 147
2026-09-04 | P-RICE-5KG | DAMAGE | DAMAGE-001 | 0 | 1 | 146
2026-09-05 | P-RICE-5KG | SALE | INV-2026-00012 | 0 | 4 | 142
Read the last line: the shop now has 142 bags of rice. Every number before it explains how we got there. That's the difference between a ledger and a stock counter.
The Stock.gs Module
/**
* Stock.gs
* Read and write helpers for the StockLedger sheet.
* The StockLedger is append-only: we never edit or delete rows.
* To "correct" a mistake, we append a compensating entry.
*/
/**
* Returns the current stock for a product.
* Reads the last row for that product and returns its Balance.
* This is O(n) in the number of ledger rows for that product.
*/
function getStockLevel(token, productId) {
validateSession(token);
const s = sheet('StockLedger');
const values = s.getDataRange().getValues();
for (let i = values.length - 1; i >= 1; i--) {
if (values[i][1] === productId) {
return Number(values[i][6]) || 0;
}
}
return 0;
}
/**
* Returns the current stock for MANY products in a single pass.
* Much faster than calling getStockLevel in a loop.
* @returns {Object} map of productId → stockLevel
*/
function getStockLevelsBulk(token, productIds) {
validateSession(token);
const wanted = {};
(productIds || []).forEach(function(id) { wanted[id] = true; });
const s = sheet('StockLedger');
const values = s.getDataRange().getValues();
const out = {};
for (let i = 1; i < values.length; i++) {
const pid = values[i][1];
if (wanted[pid]) {
out[pid] = Number(values[i][6]) || 0;
}
}
return out;
}
/**
* Appends a ledger entry and returns the new balance.
* MUST be called inside a LockService lock.
* @param {Object} entry — { productId, type, refNo, qtyIn, qtyOut, note }
* @returns {number} new balance
*/
function appendLedgerEntry(entry) {
const s = sheet('StockLedger');
// Current balance = last row's Balance for this product
const values = s.getDataRange().getValues();
let current = 0;
for (let i = values.length - 1; i >= 1; i--) {
if (values[i][1] === entry.productId) {
current = Number(values[i][6]) || 0;
break;
}
}
const qtyIn = Number(entry.qtyIn) || 0;
const qtyOut = Number(entry.qtyOut) || 0;
const balance = current + qtyIn - qtyOut;
if (balance < 0) {
throw new Error(
'Insufficient stock. Available: ' + current +
', requested: ' + qtyOut
);
}
appendRow('StockLedger', [
new Date(),
entry.productId,
entry.type,
entry.refNo,
qtyIn,
qtyOut,
balance,
entry.note || ''
]);
return balance;
}
/**
* Returns the last N ledger rows for a product.
* Useful for the stock history view.
*/
function getStockHistory(token, productId, limit) {
validateSession(token);
limit = limit || 50;
const s = sheet('StockLedger');
const values = s.getDataRange().getValues();
const out = [];
for (let i = values.length - 1; i >= 1 && out.length < limit; i--) {
if (values[i][1] === productId) {
out.push({
date: values[i][0],
type: values[i][2],
refNo: values[i][3],
qtyIn: Number(values[i][4]) || 0,
qtyOut: Number(values[i][5]) || 0,
balance: Number(values[i][6]) || 0,
note: values[i][7] || ''
});
}
}
return out;
}
/**
* Manual stock adjustment. Used for damage, theft, or correction.
* Admin / Manager only. Always requires a reason in the note.
*/
function adjustStock(token, data) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
if (!data.productId) throw new Error('Product is required.');
if (!data.type) throw new Error('Adjustment type is required.');
if (!data.note) throw new Error('A reason (note) is required for adjustments.');
const qty = Number(data.qty);
if (!qty || qty <= 0) throw new Error('Quantity must be positive.');
const isIncrease = data.type === 'INCREASE';
const ledgerType = data.ledgerType || (isIncrease ? 'ADJUST_IN' : 'ADJUST_OUT');
const lock = LockService.getScriptLock();
try {
lock.waitLock(15000);
const newBalance = appendLedgerEntry({
productId: data.productId,
type: ledgerType,
refNo: 'ADJ-' + uuid().substring(0, 6).toUpperCase(),
qtyIn: isIncrease ? qty : 0,
qtyOut: isIncrease ? 0 : qty,
note: data.note
});
logAction(session.email,
'STOCK_ADJUST ' + data.productId +
' ' + (isIncrease ? '+' : '-') + qty +
' → ' + newBalance);
return { success: true, newBalance: newBalance };
} finally {
lock.releaseLock();
}
}
/**
* Returns products whose current stock is at or below reorder level.
* Used for the low-stock alert on the dashboard.
*/
function getLowStockProducts(token) {
validateSession(token);
const products = readAll('Products').filter(function(p) {
return p.IsActive === true && Number(p.ReorderLevel) > 0;
});
const stockMap = getStockLevelsBulk(
token,
products.map(function(p) { return p.ProductId; })
);
return products
.map(function(p) {
return {
id: p.ProductId,
name: p.Name,
unit: p.Unit,
stock: stockMap[p.ProductId] || 0,
reorderLevel: Number(p.ReorderLevel)
};
})
.filter(function(p) { return p.stock <= p.reorderLevel; })
.sort(function(a, b) { return a.stock - b.stock; });
}
⚠️
Never edit or delete ledger rows. If a mistake happens, append a compensating entry. For example, if a sale of 5 was wrongly recorded, do not "fix" the row — instead record a RETURN of 5 with the same reference number. This keeps the ledger immutable and auditable.
5
Purchase Backend (GRN)
A Purchase is when the shop buys stock from a supplier. We call it a GRN — Goods Received Note — because that is the sheet name, and because it emphasises that the record is about what physically arrived, not just what was ordered.
/**
* Purchases.gs
* Goods Received Notes.
*
* Sheet: Purchases
* Columns: A=GRNNo B=Date C=SupplierId D=Total E=Paid F=Due
* G=UserId H=Notes
*
* Sheet: PurchaseDetails
* Columns: A=GRNNo B=ProductId C=Qty D=UnitCost E=LineTotal
*/
/**
* Creates a GRN, writes all detail lines, and updates the stock ledger.
* Also updates the CostPrice of each product to the latest purchase price.
* Admin / Manager only.
*/
function createPurchase(token, data) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
if (!data.items || !data.items.length) {
throw new Error('A purchase must have at least one line item.');
}
if (!data.supplierId) {
throw new Error('Supplier is required.');
}
const lock = LockService.getScriptLock();
try {
lock.waitLock(20000);
// 1. Compute totals and validate.
let total = 0;
data.items.forEach(function(it) {
const qty = Number(it.qty) || 0;
const cost = Number(it.unitCost) || 0;
if (qty <= 0) throw new Error('Each line must have positive quantity.');
if (cost < 0) throw new Error('Unit cost cannot be negative.');
total += qty * cost;
});
const paid = Number(data.paid) || 0;
const due = total - paid;
// 2. Generate GRN number.
const grnNo = nextGrnNo();
// 3. Write the GRN header.
appendRow('Purchases', [
grnNo,
new Date(),
data.supplierId,
total,
paid,
due,
session.email,
trim(data.notes)
]);
// 4. Write detail lines and update stock ledger.
const detailRows = [];
const ledgerRows = [];
const now = new Date();
const ledgerSheet = sheet('StockLedger');
const ledgerData = ledgerSheet.getDataRange().getValues();
data.items.forEach(function(it) {
const qty = Number(it.qty);
const cost = Number(it.unitCost);
const lineTotal = qty * cost;
detailRows.push([grnNo, it.productId, qty, cost, lineTotal]);
// Running balance for this product
let current = 0;
for (let i = ledgerData.length - 1; i >= 1; i--) {
if (ledgerData[i][1] === it.productId) {
current = Number(ledgerData[i][6]) || 0;
break;
}
}
// Account for previous lines in this same GRN
ledgerRows.forEach(function(r) {
if (r[1] === it.productId) current += r[4];
});
ledgerRows.push([
now, it.productId, 'PURCHASE', grnNo, qty, 0, current + qty,
'From ' + data.supplierId
]);
});
appendRows(sheet('PurchaseDetails'), detailRows);
appendRows(ledgerSheet, ledgerRows);
// 5. Update CostPrice on each product (last purchase cost becomes the new cost).
const productSheet = sheet('Products');
const productData = productSheet.getDataRange().getValues();
data.items.forEach(function(it) {
for (let i = 1; i < productData.length; i++) {
if (productData[i][0] === it.productId) {
if (data.updateCost !== false) {
productSheet.getRange(i + 1, 5).setValue(Number(it.unitCost));
}
break;
}
}
});
logAction(session.email,
'GRN_CREATED ' + grnNo +
' total=' + total +
' due=' + due);
return {
success: true,
grnNo: grnNo,
total: total,
paid: paid,
due: due,
lineCount: data.items.length
};
} finally {
lock.releaseLock();
}
}
/**
* Generates the next GRN number in sequence.
* MUST be called inside a LockService lock.
*/
function nextGrnNo() {
const s = sheet('Purchases');
const lastRow = s.getLastRow();
const year = new Date().getFullYear();
if (lastRow <= 1) return 'GRN-' + year + '-00001';
const last = String(s.getRange(lastRow, 1).getValue());
const parts = last.split('-');
const lastNum = parseInt(parts[2], 10) || 0;
return 'GRN-' + year + '-' + (lastNum + 1).toString().padStart(5, '0');
}
/**
* Returns purchases in a date range.
*/
function getPurchases(token, fromDate, toDate) {
validateSession(token);
const all = readAll('Purchases');
const from = fromDate ? new Date(fromDate).getTime() : 0;
const to = toDate ? new Date(toDate).getTime() + 86400000 : Date.now();
return all.filter(function(p) {
const t = new Date(p.Date).getTime();
return t >= from && t <= to;
}).map(function(p) {
return {
grnNo: p.GRNNo,
date: p.Date,
supplierId: p.SupplierId,
total: Number(p.Total),
paid: Number(p.Paid),
due: Number(p.Due),
userId: p.UserId,
notes: p.Notes
};
}).sort(function(a, b) {
return new Date(b.date) - new Date(a.date);
});
}Suppliers Backend (quick addition)
Purchases need suppliers. Add these to Purchases.gs or a new file Suppliers.gs:
/**
* Suppliers.gs — simple supplier management.
* Sheet columns: A=SupplierId B=Name C=Mobile D=Address E=OpeningBalance F=CreatedAt
*/
function getAllSuppliers(token) {
validateSession(token);
return readAll('Suppliers').map(function(s) {
return {
id: s.SupplierId,
name: s.Name,
mobile: s.Mobile || '',
address: s.Address || '',
openingBalance: Number(s.OpeningBalance) || 0
};
});
}
function createSupplier(token, data) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
if (!trim(data.name)) throw new Error('Supplier name is required.');
const id = 'S' + uuid().substring(0, 8).toUpperCase();
appendRow('Suppliers', [
id, trim(data.name), trim(data.mobile),
trim(data.address),
Number(data.openingBalance) || 0,
new Date()
]);
logAction(session.email, 'SUPPLIER_CREATED ' + id + ' ' + data.name);
return { success: true, id: id };
}
function getSupplierById(token, id) {
validateSession(token);
const all = readAll('Suppliers');
for (let i = 0; i < all.length; i++) {
if (all[i].SupplierId === id) {
return {
id: all[i].SupplierId,
name: all[i].Name,
mobile: all[i].Mobile,
address: all[i].Address,
openingBalance: Number(all[i].OpeningBalance) || 0
};
}
}
throw new Error('Supplier not found: ' + id);
}
6
Purchase UI (GRN Entry)
The purchase screen mirrors the POS screen but from the other side — you pick a supplier, add products, enter costs, and save. Then the stock ledger updates automatically.
<!DOCTYPE html>
<html lang="bn">
<head>
<meta charset="UTF-8">
<title>Purchase / GRN — <?= COMPANY_NAME ?></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
body { background: #f8fafc; font-family: "Segoe UI", Roboto, "Noto Sans Bengali", sans-serif; }
.pur-line td { vertical-align: middle; font-size: 13.5px; }
.pur-taka { color: #0e7490; font-weight: 700; }
.pur-total-box {
background: #f5f3ff; border: 1px solid #ddd6fe;
border-radius: 12px; padding: 16px;
}
</style>
</head>
<body>
<nav class="navbar navbar-dark" style="background: linear-gradient(90deg,#6d28d9,#06b6d4);">
<div class="container-fluid">
<span class="navbar-brand fw-bold"><?= COMPANY_NAME ?> · Purchase / GRN</span>
<div class="d-flex align-items-center text-white">
<span id="userBadge"></span>
<button class="btn btn-outline-light btn-sm ms-3"
onclick="fl365Logout()">Logout</button>
</div>
</div>
</nav>
<div class="container py-4">
<div class="row g-3 mb-3">
<div class="col-md-4">
<label class="form-label fw-bold">Supplier *</label>
<div class="input-group">
<select class="form-select" id="supplierId">
<option value="">— Select supplier —</option>
</select>
<button class="btn btn-outline-primary" type="button"
id="newSupplierBtn">+ New</button>
</div>
</div>
<div class="col-md-4">
<label class="form-label fw-bold">Notes</label>
<input type="text" class="form-control" id="notes"
placeholder="e.g. Weekly order, cash paid">
</div>
<div class="col-md-4 d-flex align-items-end">
<div class="form-check form-switch">
<input class="form-check-input" type="checkbox" id="updateCost" checked>
<label class="form-check-label small" for="updateCost">
Update product Cost Price
</label>
</div>
</div>
</div>
<div class="card mb-3">
<div class="card-body">
<div class="row g-2 mb-3">
<div class="col-md-9 position-relative">
<input type="text" class="form-control" id="productSearch"
placeholder="🔍 Search product or scan barcode..."
autocomplete="off">
<div id="searchResults"
class="list-group position-absolute w-100 shadow-sm"
style="z-index:999;max-height:280px;overflow-y:auto;"></div>
</div>
</div>
<div class="table-responsive">
<table class="table table-hover">
<thead>
<tr>
<th>Product</th>
<th style="width:100px;">Qty</th>
<th style="width:120px;">Unit Cost</th>
<th class="text-end" style="width:110px;">Line Total</th>
<th style="width:50px;"></th>
</tr>
</thead>
<tbody id="purLines"></tbody>
</table>
</div>
</div>
</div>
<div class="row g-3">
<div class="col-md-6">
<div class="pur-total-box">
<div class="d-flex justify-content-between mb-2">
<span>Total</span>
<strong>৳ <span id="totalAmount">0.00</span></strong>
</div>
<div class="mb-2">
<label class="form-label small">Paid Now (৳)</label>
<input type="number" class="form-control" id="paidAmount"
step="0.01" value="0">
</div>
<div class="d-flex justify-content-between">
<span>Due</span>
<strong class="text-danger">৳ <span id="dueAmount">0.00</span></strong>
</div>
</div>
</div>
<div class="col-md-6 d-flex flex-column justify-content-end">
<button class="btn btn-success btn-lg" id="saveGrnBtn">
Save Purchase / GRN
</button>
</div>
</div>
</div>
<?!= include('JS') ?>
<script>
let cart = [];
let allProducts = [];
let allSuppliers = [];
renderUserBadge('userBadge');
function loadData() {
Promise.all([
apiCall('getActiveProducts'),
apiCall('getAllSuppliers')
]).then(function(results) {
allProducts = results[0];
allSuppliers = results[1];
const sel = document.getElementById('supplierId');
sel.innerHTML = '<option value="">— Select supplier —</option>'
+ allSuppliers.map(function(s) {
return '<option value="' + s.id + '">'
+ s.name + (s.mobile ? ' · ' + s.mobile : '')
+ '</option>';
}).join('');
});
}
document.getElementById('productSearch').addEventListener('input', function(e) {
const q = e.target.value.trim().toLowerCase();
const box = document.getElementById('searchResults');
if (q.length < 2) { box.innerHTML = ''; return; }
const matches = allProducts.filter(function(p) {
return p.name.toLowerCase().indexOf(q) !== -1
|| (p.barcode && p.barcode === q);
}).slice(0, 10);
box.innerHTML = matches.map(function(p) {
return '<button type="button" class="list-group-item list-group-item-action" '
+ 'onclick="addLine(\'' + p.id + '\')">'
+ '<strong>' + p.name + '</strong> '
+ '<small class="text-muted">· Last cost ৳'
+ Number(p.costPrice).toFixed(2) + '</small>'
+ '</button>';
}).join('');
});
window.addLine = function(id) {
const p = allProducts.find(function(x) { return x.id === id; });
if (!p) return;
const exists = cart.find(function(l) { return l.productId === id; });
if (exists) {
exists.qty += 1;
} else {
cart.push({
productId: id, name: p.name, unit: p.unit,
qty: 1, unitCost: p.costPrice || 0
});
}
document.getElementById('productSearch').value = '';
document.getElementById('searchResults').innerHTML = '';
renderCart();
};
window.removeLine = function(i) {
cart.splice(i, 1);
renderCart();
};
window.updateQty = function(i, v) {
const q = Number(v);
if (q > 0) { cart[i].qty = q; renderCart(); }
};
window.updateCost = function(i, v) {
const c = Number(v);
if (c >= 0) { cart[i].unitCost = c; renderCart(); }
};
function renderCart() {
const tbody = document.getElementById('purLines');
if (!cart.length) {
tbody.innerHTML = '<tr><td colspan="5" class="text-center text-muted py-4">Search a product above to begin.</td></tr>';
} else {
tbody.innerHTML = cart.map(function(l, i) {
return '<tr class="pur-line">'
+ '<td>' + l.name + ' <small class="text-muted">(' + l.unit + ')</small></td>'
+ '<td><input type="number" class="form-control form-control-sm" '
+ 'min="1" value="' + l.qty + '" onchange="updateQty(' + i + ', this.value)"></td>'
+ '<td><input type="number" class="form-control form-control-sm" '
+ 'step="0.01" value="' + l.unitCost + '" onchange="updateCost(' + i + ', this.value)"></td>'
+ '<td class="text-end pur-taka">৳ ' + (l.qty * l.unitCost).toFixed(2) + '</td>'
+ '<td><button class="btn btn-sm btn-outline-danger" onclick="removeLine(' + i + ')">×</button></td>'
+ '</tr>';
}).join('');
}
updateTotals();
}
function updateTotals() {
const total = cart.reduce(function(s, l) { return s + l.qty * l.unitCost; }, 0);
const paid = Number(document.getElementById('paidAmount').value) || 0;
const due = total - paid;
document.getElementById('totalAmount').textContent = total.toFixed(2);
document.getElementById('dueAmount').textContent = due.toFixed(2);
}
document.getElementById('paidAmount').addEventListener('input', updateTotals);
document.getElementById('saveGrnBtn').addEventListener('click', function() {
const supplierId = document.getElementById('supplierId').value;
if (!supplierId) return alert('Please select a supplier.');
if (!cart.length) return alert('Add at least one product.');
const paid = Number(document.getElementById('paidAmount').value) || 0;
const notes = document.getElementById('notes').value.trim();
const updateCost = document.getElementById('updateCost').checked;
const items = cart.map(function(l) {
return { productId: l.productId, qty: l.qty, unitCost: l.unitCost };
});
apiCall('createPurchase', { supplierId, items, paid, notes, updateCost })
.then(function(res) {
alert('GRN saved: ' + res.grnNo +
'\nTotal: ৳' + Number(res.total).toFixed(2) +
'\nDue: ৳' + Number(res.due).toFixed(2));
cart = [];
document.getElementById('paidAmount').value = 0;
document.getElementById('notes').value = '';
renderCart();
})
.catch(function(err) { alert('Error: ' + err.message); });
});
document.getElementById('newSupplierBtn').addEventListener('click', function() {
const name = prompt('Supplier name:');
if (!name) return;
const mobile = prompt('Mobile (optional):') || '';
apiCall('createSupplier', { name, mobile })
.then(loadData)
.catch(function(err) { alert(err.message); });
});
loadData();
renderCart();
</script>
</body>
</html>
7
POS Backend (Pos.gs)
This is the most important file in Part 3. The createSale function must be bullet-proof: it writes to Sales, SaleDetails, StockLedger, and (optionally) Customers — all inside a single lock, all-or-nothing.
/**
* Pos.gs
* Point-of-Sale backend.
*
* Sheet: Sales
* Columns: A=InvoiceNo B=Date C=CustomerId D=SubTotal E=Discount
* F=Total G=Paid H=Due I=UserId J=PaymentMethod K=Notes
*
* Sheet: SaleDetails
* Columns: A=InvoiceNo B=ProductId C=Qty D=UnitPrice E=LineTotal F=Discount
*/
/**
* Creates a sale. Runs inside a LockService lock so concurrent cashiers
* never collide on invoice numbers or stock balances.
* All logged-in roles can create sales.
*/
function createSale(token, saleData) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager', 'Cashier']);
// --- Validate the payload ---
if (!saleData || !saleData.items || !saleData.items.length) {
throw new Error('Sale must have at least one item.');
}
const lock = LockService.getScriptLock();
try {
lock.waitLock(20000);
// --- Recompute totals on the server (never trust the client) ---
let subTotal = 0;
saleData.items.forEach(function(it) {
const qty = Number(it.qty) || 0;
const price = Number(it.unitPrice) || 0;
if (qty <= 0) throw new Error('Each line must have positive quantity.');
if (price < 0) throw new Error('Unit price cannot be negative.');
subTotal += qty * price;
});
const discount = Number(saleData.discount) || 0;
const total = subTotal - discount;
const paid = Number(saleData.paid) || 0;
const due = total - paid;
if (total < 0) throw new Error('Discount cannot exceed subtotal.');
if (due > 0 && saleData.customerId === 'WALK-IN') {
throw new Error('A customer is required for a credit sale (due > 0).');
}
// --- Verify stock availability BEFORE writing anything ---
const ledgerSheet = sheet('StockLedger');
const ledgerData = ledgerSheet.getDataRange().getValues();
const stockNeeded = {};
saleData.items.forEach(function(it) {
stockNeeded[it.productId] = (stockNeeded[it.productId] || 0) + Number(it.qty);
});
const currentStock = {};
const wantedIds = Object.keys(stockNeeded);
for (let i = ledgerData.length - 1; i >= 1; i--) {
const pid = ledgerData[i][1];
if (currentStock[pid] === undefined && stockNeeded[pid] !== undefined) {
currentStock[pid] = Number(ledgerData[i][6]) || 0;
}
}
const products = readAll('Products');
const productMap = {};
products.forEach(function(p) { productMap[p.ProductId] = p; });
wantedIds.forEach(function(pid) {
const available = currentStock[pid] || 0;
const needed = stockNeeded[pid];
const name = productMap[pid] ? productMap[pid].Name : pid;
if (available < needed) {
throw new Error(
'Insufficient stock for "' + name + '". Available: ' +
available + ', needed: ' + needed
);
}
});
// --- Generate the invoice number ---
const invoiceNo = nextInvoiceNo();
const now = new Date();
const customerId = saleData.customerId || 'WALK-IN';
const paymentMethod = saleData.paymentMethod || 'Cash';
// --- Write the Sales header ---
appendRow('Sales', [
invoiceNo, now, customerId, subTotal, discount,
total, paid, due, session.email, paymentMethod,
trim(saleData.notes)
]);
// --- Write SaleDetails + StockLedger ---
const detailRows = [];
const ledgerRows = [];
const runningBalance = {};
wantedIds.forEach(function(pid) { runningBalance[pid] = currentStock[pid] || 0; });
saleData.items.forEach(function(it) {
const qty = Number(it.qty);
const price = Number(it.unitPrice);
const lineDiscount = Number(it.discount) || 0;
const lineTotal = qty * price - lineDiscount;
detailRows.push([invoiceNo, it.productId, qty, price, lineTotal, lineDiscount]);
runningBalance[it.productId] -= qty;
ledgerRows.push([
now, it.productId, 'SALE', invoiceNo,
0, qty, runningBalance[it.productId],
'POS sale by ' + session.name
]);
});
appendRows(sheet('SaleDetails'), detailRows);
appendRows(ledgerSheet, ledgerRows);
// --- If there's a due, make sure the customer is on the customer list.
if (due > 0 && customerId !== 'WALK-IN') {
try {
ensureCustomerExists(customerId, saleData.customerName);
} catch (e) {
console.error('Failed to ensure customer exists:', e);
}
}
logAction(session.email,
'SALE_CREATED ' + invoiceNo +
' total=' + total + ' +
' due=' + due + ' ' + paymentMethod);
return {
success: true,
invoiceNo: invoiceNo,
subTotal: subTotal,
discount: discount,
total: total,
paid: paid,
due: due,
paymentMethod: paymentMethod,
cashier: session.name,
date: now
};
} finally {
lock.releaseLock();
}
}
/**
* Ensures a customer row exists in the Customers sheet.
* Called when a credit sale is made for a new customer.
*/
function ensureCustomerExists(customerId, name) {
const all = readAll('Customers');
for (let i = 0; i < all.length; i++) {
if (all[i].CustomerId === customerId) return all[i];
}
// Create it
appendRow('Customers', [
customerId, name || 'Walk-in Customer', '', '', 0, new Date()
]);
return { CustomerId: customerId, Name: name };
}
/**
* Returns one sale with all its detail lines.
* Used for receipt reprinting.
*/
function getSaleByInvoice(token, invoiceNo) {
validateSession(token);
const sales = readAll('Sales');
let header = null;
for (let i = 0; i < sales.length; i++) {
if (sales[i].InvoiceNo === invoiceNo) { header = sales[i]; break; }
}
if (!header) throw new Error('Invoice not found: ' + invoiceNo);
const details = readAll('SaleDetails').filter(function(d) {
return d.InvoiceNo === invoiceNo;
}).map(function(d) {
return {
productId: d.ProductId,
qty: Number(d.Qty),
unitPrice: Number(d.UnitPrice),
lineTotal: Number(d.LineTotal),
discount: Number(d.Discount) || 0
};
});
// Enrich with product names
const products = readAll('Products');
const pMap = {};
products.forEach(function(p) { pMap[p.ProductId] = p; });
details.forEach(function(d) {
d.productName = pMap[d.productId] ? pMap[d.productId].Name : d.productId;
d.unit = pMap[d.productId] ? pMap[d.productId].Unit : '';
});
return {
invoiceNo: header.InvoiceNo,
date: header.Date,
customerId: header.CustomerId,
subTotal: Number(header.SubTotal),
discount: Number(header.Discount),
total: Number(header.Total),
paid: Number(header.Paid),
due: Number(header.Due),
userId: header.UserId,
paymentMethod: header.PaymentMethod,
notes: header.Notes,
details: details
};
}
/**
* Returns all sales in a date range. For reports.
*/
function getSales(token, fromDate, toDate) {
validateSession(token);
const all = readAll('Sales');
const from = fromDate ? new Date(fromDate).getTime() : 0;
const to = toDate ? new Date(toDate).getTime() + 86400000 : Date.now();
return all.filter(function(s) {
const t = new Date(s.Date).getTime();
return t >= from && t <= to;
}).map(function(s) {
return {
invoiceNo: s.InvoiceNo,
date: s.Date,
customerId: s.CustomerId,
subTotal: Number(s.SubTotal),
discount: Number(s.Discount),
total: Number(s.Total),
paid: Number(s.Paid),
due: Number(s.Due),
userId: s.UserId,
paymentMethod: s.PaymentMethod
};
}).sort(function(a, b) {
return new Date(b.date) - new Date(a.date);
});
}
/**
* Voids a sale. Admin only, and only same-day.
* Writes a REVERSAL entry to the ledger and does NOT delete the sale.
*/
function voidSale(token, invoiceNo, reason) {
const session = validateSession(token);
requireRole(session, ['Admin']);
if (!reason) throw new Error('A reason is required to void a sale.');
const sale = getSaleByInvoice(token, invoiceNo);
// Only allow same-day void
const saleDate = new Date(sale.date);
const today = new Date();
if (saleDate.toDateString() !== today.toDateString()) {
throw new Error('Only same-day voids are allowed. Use a RETURN entry instead.');
}
const lock = LockService.getScriptLock();
try {
lock.waitLock(15000);
const ledgerSheet = sheet('StockLedger');
const ledgerData = ledgerSheet.getDataRange().getValues();
const rows = [];
const now = new Date();
sale.details.forEach(function(d) {
let current = 0;
for (let i = ledgerData.length - 1; i >= 1; i--) {
if (ledgerData[i][1] === d.productId) {
current = Number(ledgerData[i][6]) || 0;
break;
}
}
rows.push([
now, d.productId, 'VOID', invoiceNo,
d.qty, 0, current + d.qty,
'Void: ' + reason
]);
});
appendRows(ledgerSheet, rows);
logAction(session.email, 'SALE_VOIDED ' + invoiceNo + ' reason=' + reason);
return { success: true, invoiceNo: invoiceNo };
} finally {
lock.releaseLock();
}
}
🛡️
Why server-side recomputation matters: A malicious user could open DevTools and call
createSale with a fake total of ৳1 for 100 bags of rice. If we trusted the client-side total, we'd record a loss. Because we recompute subTotal, total and due from items[] on the server, the client cannot lie about the amount.
8
POS Screen (Pos.html)
Here is what your cashier sees. Fast search, barcode scanning, a cart, one-tap payment, and a printed receipt.
The Full POS HTML
<!DOCTYPE html>
<html lang="bn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>POS — <?= COMPANY_NAME ?></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
html, body { height: 100%; margin: 0; background: #f1f5f9;
font-family: "Segoe UI", Roboto, "Noto Sans Bengali", sans-serif; }
.pos-wrap { display: grid; grid-template-columns: 1.6fr 1fr;
height: calc(100vh - 56px); gap: 0; }
.pos-left { display: flex; flex-direction: column;
background: #fff; border-right: 1px solid #e2e8f0; overflow: hidden; }
.pos-right { display: flex; flex-direction: column;
background: #f8fafc; padding: 16px; overflow-y: auto; }
.pos-search { padding: 12px; border-bottom: 1px solid #e2e8f0; position: relative; }
.pos-search input { font-size: 16px; padding: 12px 14px; border-radius: 10px; }
.pos-results {
position: absolute; top: 100%; left: 12px; right: 12px;
background: #fff; z-index: 100; border-radius: 10px;
box-shadow: 0 10px 25px rgba(0,0,0,.12); max-height: 340px; overflow-y: auto;
}
.pos-cart { flex: 1; overflow-y: auto; padding: 0 12px 12px; }
.pos-cart-row {
display: grid; grid-template-columns: 1fr 80px 100px 90px 40px;
gap: 8px; align-items: center;
padding: 8px 0; border-bottom: 1px dashed #e2e8f0; font-size: 14px;
}
.pos-cart-row .name { font-weight: 600; }
.pos-cart-row input { padding: 4px 6px; font-size: 13px; }
.pos-right h6 { font-size: 12.5px; text-transform: uppercase;
letter-spacing: .4px; color: #64748b; margin-top: 6px; }
.pos-total { font-size: 20px; font-weight: 800; }
.pos-total.taka { color: #0e7490; }
.pos-btn-save {
width: 100%; padding: 14px; border: 0; border-radius: 12px;
background: linear-gradient(135deg,#16a34a,#22c55e);
color: #fff; font-size: 16px; font-weight: 700;
box-shadow: 0 12px 24px -8px rgba(22,163,74,.55);
margin-top: 12px;
}
.pos-taka { color: #0e7490; font-weight: 700; }
.badge-due { background: #fee2e2; color: #991b1b; }
.badge-paid { background: #dcfce7; color: #166534; }
@media (max-width: 900px) {
.pos-wrap { grid-template-columns: 1fr; height: auto; }
.pos-cart { max-height: 50vh; }
}
/* Receipt print styles */
@media print {
.no-print { display: none !important; }
.print-receipt { display: block !important; width: 80mm; margin: 0 auto; }
}
.print-receipt { display: none; }
</style>
</head>
<body>
<nav class="navbar navbar-dark no-print"
style="background: linear-gradient(90deg,#6d28d9,#06b6d4);height:56px;">
<div class="container-fluid">
<span class="navbar-brand fw-bold mb-0 h6">
POS · <?= COMPANY_NAME ?>
</span>
<div class="d-flex align-items-center text-white small">
<a href="?page=Dashboard" class="text-white me-3 text-decoration-none">Dashboard</a>
<a href="?page=Products" class="text-white me-3 text-decoration-none">Products</a>
<span id="userBadge"></span>
<button class="btn btn-outline-light btn-sm ms-2"
onclick="fl365Logout()">Logout</button>
</div>
</div>
</nav>
<div class="pos-wrap no-print">
<!-- LEFT: SEARCH + CART -->
<div class="pos-left">
<div class="pos-search">
<input type="text" class="form-control" id="searchInput"
placeholder="🔍 Scan barcode or type product name..."
autocomplete="off" autofocus>
<div class="pos-results d-none" id="searchResults"></div>
</div>
<div class="pos-cart">
<div id="cartBody"></div>
</div>
</div>
<!-- RIGHT: TOTALS + PAYMENT -->
<div class="pos-right">
<h6>Customer</h6>
<div class="d-flex gap-2 mb-2">
<select class="form-select form-select-sm" id="customerId">
<option value="WALK-IN">Walk-in (Cash only)</option>
</select>
<button class="btn btn-sm btn-outline-primary" type="button"
id="newCustomerBtn">+</button>
</div>
<h6>Payment Method</h6>
<div class="btn-group btn-group-sm mb-3" role="group" id="payMethodGroup">
<input type="radio" class="btn-check" name="pm" id="pm-cash" value="Cash" checked>
<label class="btn btn-outline-primary" for="pm-cash">Cash</label>
<input type="radio" class="btn-check" name="pm" id="pm-bkash" value="bKash">
<label class="btn btn-outline-primary" for="pm-bkash">bKash</label>
<input type="radio" class="btn-check" name="pm" id="pm-nagad" value="Nagad">
<label class="btn btn-outline-primary" for="pm-nagad">Nagad</label>
<input type="radio" class="btn-check" name="pm" id="pm-card" value="Card">
<label class="btn btn-outline-primary" for="pm-card">Card</label>
</div>
<h6>Summary</h6>
<div class="bg-white rounded-3 p-3 mb-3 border">
<div class="d-flex justify-content-between mb-1">
<span>Sub Total</span>
<span id="subTotal">৳ 0.00</span>
</div>
<div class="mb-2">
<label class="form-label small mb-0">Discount</label>
<input type="number" class="form-control form-control-sm"
id="discount" step="0.01" value="0">
</div>
<div class="d-flex justify-content-between pos-total mb-2">
<span>Total</span>
<span class="pos-taka" id="grandTotal">৳ 0.00</span>
</div>
<div class="mb-2">
<label class="form-label small mb-0">Paid</label>
<input type="number" class="form-control"
id="paid" step="0.01" value="0">
</div>
<div class="d-flex justify-content-between fw-bold" id="dueRow">
<span>Due</span>
<span class="text-danger" id="dueAmount">৳ 0.00</span>
</div>
</div>
<button class="pos-btn-save" id="saveBtn">
SAVE & PRINT RECEIPT
</button>
<button class="btn btn-outline-secondary btn-sm w-100 mt-2"
id="clearBtn">Clear Cart</button>
</div>
</div>
<!-- Hidden printable receipt -->
<div class="print-receipt" id="receiptPrint"></div>
<?!= include('JS') ?>
<script>
let cart = [];
let allProducts = [];
let allCustomers = [];
renderUserBadge('userBadge');
Promise.all([
apiCall('getActiveProducts'),
apiCall('getAllCustomers')
]).then(function(r) {
allProducts = r[0];
allCustomers = r[1];
const sel = document.getElementById('customerId');
sel.innerHTML = '<option value="WALK-IN">Walk-in (Cash only)</option>'
+ allCustomers.map(function(c) {
return '<option value="' + c.id + '">'
+ c.name + (c.mobile ? ' · ' + c.mobile : '')
+ '</option>';
}).join('');
});
const searchInput = document.getElementById('searchInput');
const resultsBox = document.getElementById('searchResults');
searchInput.addEventListener('input', function() {
const q = searchInput.value.trim().toLowerCase();
if (q.length < 1) {
resultsBox.classList.add('d-none');
return;
}
const matches = allProducts.filter(function(p) {
return p.name.toLowerCase().indexOf(q) !== -1
|| (p.barcode && p.barcode === q);
}).slice(0, 12);
// Barcode exact match → auto-add to cart
if (matches.length === 1 && matches[0].barcode === q) {
addToCart(matches[0].id);
searchInput.value = '';
resultsBox.classList.add('d-none');
return;
}
resultsBox.innerHTML = matches.map(function(p) {
return '<button type="button" class="list-group-item list-group-item-action" '
+ 'onclick="addToCart(\'' + p.id + '\')">'
+ '<div class="d-flex justify-content-between align-items-center">'
+ '<div><strong>' + p.name + '</strong>'
+ (p.category ? ' <small class="text-muted">' + p.category + '</small>' : '')
+ '</div>'
+ '<div class="text-nowrap">৳ ' + p.salePrice.toFixed(2) + '</div>'
+ '</div>'
+ '</button>';
}).join('') ||
'<div class="list-group-item text-muted small">No products match.</div>';
resultsBox.classList.remove('d-none');
});
window.addToCart = function(id) {
const p = allProducts.find(function(x) { return x.id === id; });
if (!p) return;
const ex = cart.find(function(l) { return l.productId === id; });
if (ex) {
ex.qty += 1;
} else {
cart.push({
productId: id, name: p.name, unit: p.unit,
unitPrice: p.salePrice, qty: 1, discount: 0
});
}
searchInput.value = '';
resultsBox.classList.add('d-none');
searchInput.focus();
renderCart();
};
window.removeItem = function(i) {
cart.splice(i, 1);
renderCart();
};
window.changeQty = function(i, v) {
const q = Number(v);
if (q > 0) { cart[i].qty = q; renderCart(); }
};
window.changePrice = function(i, v) {
const pr = Number(v);
if (pr >= 0) { cart[i].unitPrice = pr; renderCart(); }
};
function renderCart() {
const body = document.getElementById('cartBody');
if (!cart.length) {
body.innerHTML = '<div class="text-center text-muted py-5">'
+ '<div style="font-size:48px;">🛒</div>'
+ '<div class="mt-2">Start typing or scan a barcode to add products.</div></div>';
} else {
body.innerHTML = cart.map(function(l, i) {
const lineTotal = l.qty * l.unitPrice;
return '<div class="pos-cart-row">'
+ '<div class="name">' + l.name
+ '<br><small class="text-muted">' + l.unit + '</small></div>'
+ '<input type="number" class="form-control form-control-sm" min="1"'
+ ' value="' + l.qty + '" onchange="changeQty(' + i + ', this.value)">'
+ '<input type="number" class="form-control form-control-sm" step="0.01"'
+ ' value="' + l.unitPrice + '" onchange="changePrice(' + i + ', this.value)">'
+ '<div class="pos-taka text-end">৳ ' + lineTotal.toFixed(2) + '</div>'
+ '<button class="btn btn-sm btn-outline-danger" onclick="removeItem(' + i + ')">×</button>'
+ '</div>';
}).join('');
}
recalc();
}
function recalc() {
const sub = cart.reduce(function(s, l) { return s + l.qty * l.unitPrice; }, 0);
const disc = Number(document.getElementById('discount').value) || 0;
const total = Math.max(0, sub - disc);
const paid = Number(document.getElementById('paid').value) || 0;
const due = total - paid;
document.getElementById('subTotal').textContent = '৳ ' + sub.toFixed(2);
document.getElementById('grandTotal').textContent = '৳ ' + total.toFixed(2);
document.getElementById('dueAmount').textContent = '৳ ' + Math.max(0, due).toFixed(2);
}
document.getElementById('discount').addEventListener('input', recalc);
document.getElementById('paid').addEventListener(input, recalc);
document.getElementById('clearBtn').addEventListener('click', function() {
if (!cart.length) return;
if (!confirm('Clear the current cart?')) return;
cart = [];
document.getElementById('discount').value = 0;
document.getElementById('paid').value = 0;
renderCart();
});
document.getElementById('newCustomerBtn').addEventListener('click', function() {
const name = prompt('Customer name:');
if (!name) return;
const mobile = prompt('Mobile (optional):') || '';
apiCall('createCustomer', { name, mobile })
.then(function(res) {
allCustomers.push({ id: res.id, name: name, mobile: mobile });
const sel = document.getElementById('customerId');
const opt = document.createElement('option');
opt.value = res.id;
opt.textContent = name + (mobile ? ' · ' + mobile : '');
sel.appendChild(opt);
sel.value = res.id;
})
.catch(function(err) { alert(err.message); });
});
document.getElementById('saveBtn').addEventListener('click', function() {
if (!cart.length) return alert('Cart is empty.');
const customerId = document.getElementById('customerId').value;
const discount = Number(document.getElementById('discount').value) || 0;
const paid = Number(document.getElementById('paid').value) || 0;
const paymentMethod = document.querySelector('input[name="pm"]:checked').value;
const customerName = '';
const items = cart.map(function(l) {
return { productId: l.productId, qty: l.qty, unitPrice: l.unitPrice };
});
document.getElementById('saveBtn').disabled = true;
document.getElementById('saveBtn').textContent = 'Saving...';
apiCall('createSale', {
items: items, customerId: customerId, discount: discount,
paid: paid, paymentMethod: paymentMethod, customerName: customerName
}).then(function(res) {
printReceipt(res);
cart = [];
document.getElementById('discount').value = 0;
document.getElementById('paid').value = 0;
renderCart();
document.getElementById('saveBtn').disabled = false;
document.getElementById('saveBtn').textContent = 'SAVE & PRINT RECEIPT';
searchInput.focus();
}).catch(function(err) {
alert('Error: ' + err.message);
document.getElementById('saveBtn').disabled = false;
document.getElementById('saveBtn').textContent = 'SAVE & PRINT RECEIPT';
});
});
function printReceipt(sale) {
const w = window.open('', '_blank', 'width=380,height=640');
const html =
'<html><head><title>Receipt ' + sale.invoiceNo + '</title>' +
'<style>' +
'body{font-family:ui-monospace,monospace;font-size:13px;padding:10px;max-width:340px;margin:0 auto;}' +
'.c{text-align:center}.b{font-weight:800;font-size:17px}' +
'hr{border:0;border-top:1px dashed #888;margin:8px 0}' +
'.row{display:flex;justify-content:space-between}' +
'.total{border-top:1px solid #333;border-bottom:1px solid #333;padding:4px 0;font-weight:800}' +
'@media print{@page{size:80mm auto;margin:2mm}}' +
'</style></head><body>' +
'<div class="c b">' + '<?= COMPANY_NAME ?>' + '</div>' +
'<div class="c">Mirpur, Dhaka</div>' +
'<div class="c">Mobile: 01XXXXXXXXX</div>' +
'<hr>' +
'<div class="row"><span>Invoice</span><span>' + sale.invoiceNo + '</span></div>' +
'<div class="row"><span>Date</span><span>' + new Date(sale.date).toLocaleString() + '</span></div>' +
'<div class="row"><span>Cashier</span><span>' + (sale.cashier || '') + '</span></div>' +
'<hr>' +
cart.map(function(l) {
return '<div>' + l.name + '</div>'
+ '<div class="row"><span>' + l.qty + ' × ' + l.unitPrice.toFixed(2) + '</span>'
+ '<span>' + (l.qty * l.unitPrice).toFixed(2) + '</span></div>';
}).join('') +
'<hr>' +
'<div class="row"><span>Sub Total</span><span>' + Number(sale.subTotal).toFixed(2) + '</span></div>' +
'<div class="row"><span>Discount</span><span>-' + Number(sale.discount).toFixed(2) + '</span></div>' +
'<div class="row total"><span>TOTAL</span><span>৳ ' + Number(sale.total).toFixed(2) + '</span></div>' +
'<div class="row"><span>Paid (' + sale.paymentMethod + ')</span><span>' + Number(sale.paid).toFixed(2) + '</span></div>' +
(sale.due > 0
? '<div class="row"><span>Due</span><span>' + Number(sale.due).toFixed(2) + '</span></div>'
: '') +
(sale.paid > sale.total
? '<div class="row"><span>Change</span><span>' + (sale.paid - sale.total).toFixed(2) + '</span></div>'
: '') +
'<hr>' +
'<div class="c">ধন্যবাদ! আবার আসবেন।<br>Thank you, please come again.</div>' +
'<div class="c" style="margin-top:8px;font-size:10px;color:#666;">' + sale.invoiceNo + '</div>' +
'<script>window.onload=function(){setTimeout(function(){window.print();},200);}</script>' +
'</body></html>';
w.document.write(html);
w.document.close();
}
</script>
</body>
</html>
⌨️
Keyboard shortcuts for cashiers: We deliberately made the search box autofocus so the cashier's barcode scanner (which types digits and presses Enter) lands directly in the search field. Scanning a known barcode auto-adds it to the cart and re-focuses the search. This means a busy counter can process a sale entirely without touching the mouse.
9
Thermal Receipt Printing
The receipt is what the customer takes home. It matters as much as the sale itself. Here is a preview of what a real 80mm thermal receipt looks like with our design.
DEMO STORE
Mirpur, Dhaka
Mobile: 01XXXXXXXXX
Mobile: 01XXXXXXXXX
InvoiceINV-2026-00042
Date10/09/2026, 6:47 PM
CashierNasrin Akter
চাল ৫ কেজি (Rice 5kg)
2 × 500.001000.00
তেল ২ লিটার (Oil 2L)
1 × 350.00350.00
চিনি ১ কেজি (Sugar 1kg)
1 × 130.00130.00
Sub Total1480.00
Discount-30.00
TOTAL৳ 1450.00
Paid (Cash)1500.00
Change50.00
ধন্যবাদ! আবার আসবেন।
Thank you, please come again.
Thank you, please come again.
INV-2026-00042
Understanding the Print Pipeline
There are three common ways to print a receipt from a web app:
| Method | Works With | Complexity | Our Choice |
|---|---|---|---|
Open a popup window and call window.print() |
Any printer with a driver — thermal, laser, inkjet, PDF | Simple | ✅ Yes |
| Use a browser extension for thermal printers | Specific brands | Medium | Not needed |
| Direct ESC/POS commands over WebUSB / Bluetooth | Raw thermal printers | High | Part 5 (advanced) |
How to Make it Print Correctly on 80mm Paper
The @media print CSS is the key. Here is what matters:
/* 1. Hide everything that shouldn't print. */
@media print {
.no-print { display: none !important; }
body { background: #fff !important; margin: 0; padding: 0; }
}
/* 2. The receipt itself. */
.print-receipt {
display: none; /* hidden on-screen */
font-family: ui-monospace, monospace;
font-size: 12px;
width: 76mm; /* printer is 80mm, minus margins */
margin: 0 auto;
}
/* 3. When actually printing, show the receipt, hide the app. */
@media print {
.print-receipt { display: block !important; }
.pos-wrap { display: none !important; }
}
/* 4. Force a narrow page size for thermal printers. */
@page {
size: 80mm auto; /* width 80mm, height auto */
margin: 2mm;
}
/* 5. Never let the browser add page breaks inside a receipt. */
.print-receipt,
.print-receipt * {
page-break-inside: avoid;
break-inside: avoid;
}
🖨️
If your thermal printer prints on A4-sized paper (i.e., ignores the receipt size), you need to install the manufacturer's Windows driver and set the paper size to "80 x 297mm" or "58 x 297mm" in the printer properties. Most 80mm printers ship with a generic "POS-80" driver that does this. Ask your local supplier if unsure — this is a 2-minute fix.
Bengali on Thermal Printers
Bengali text on thermal printers can be tricky. Most thermal printers have a limited character set and may not render Bengali properly. Two options:
- English-only receipts — safest, always works. Use English product names as the primary label and Bengali as a secondary line only if the printer supports UTF-8.
- Image-based receipts — render the receipt to a canvas and print as an image. This works on any printer but slows down the flow slightly. We cover this in Part 5 as an advanced topic.
For most shops, option 1 is fine. Bengali product names can still be entered and stored — they will just show up as English in the receipt if the printer does not render them.
10
Customers & Dues Management
In Bangladesh, "বাকিতে বিক্রি" (credit sale) is how neighbourhood shops survive. A regular customer takes rice and oil today, pays at month-end. Your ERP must handle this gracefully.
The Dues Model
Customer "Rahim Bhai"
│
├── 02 Sep: Sale INV-2026-00005 Total ৳1,450 Paid ৳1,000 Due ৳450
├── 05 Sep: Sale INV-2026-00011 Total ৳2,300 Paid ৳2,000 Due ৳300
├── 07 Sep: Payment ৳500 ← reduces total due
├── 10 Sep: Sale INV-2026-00028 Total ৳1,200 Paid ৳1,200 Due ৳0
│
▼
Current Due: ৳450 + ৳300 − ৳500 = ৳250
Customers.gs Backend
/**
* Customers.gs
* Customer list, credit dues, and payments.
*
* Sheet: Customers
* Columns: A=CustomerId B=Name C=Mobile D=Address E=OpeningBalance F=CreatedAt
*
* Sheet: CustomerPayments
* Columns: A=PaymentId B=Date C=CustomerId D=Amount E=Method F=Note G=UserId
*/
function getAllCustomers(token) {
validateSession(token);
return readAll('Customers').map(function(c) {
return {
id: c.CustomerId,
name: c.Name,
mobile: c.Mobile || '',
address: c.Address || '',
openingBalance: Number(c.OpeningBalance) || 0
};
});
}
function createCustomer(token, data) {
validateSession(token);
const name = trim(data.name);
if (!name) throw new Error('Customer name is required.');
const id = 'C' + uuid().substring(0, 8).toUpperCase();
appendRow('Customers', [
id, name, trim(data.mobile), trim(data.address),
Number(data.openingBalance) || 0, new Date()
]);
return { success: true, id: id };
}
/**
* Returns the current outstanding due for a customer,
* computed from Sales.Due and CustomerPayments.
*/
function getCustomerDue(token, customerId) {
validateSession(token);
const sales = readAll('Sales');
let totalDue = 0;
sales.forEach(function(s) {
if (s.CustomerId === customerId) {
totalDue += Number(s.Due) || 0;
}
});
const payments = readAll('CustomerPayments');
let totalPaid = 0;
payments.forEach(function(p) {
if (p.CustomerId === customerId) {
totalPaid += Number(p.Amount) || 0;
}
});
// Opening balance adds to the due.
const customers = readAll('Customers');
let openingBalance = 0;
customers.forEach(function(c) {
if (c.CustomerId === customerId) {
openingBalance = Number(c.OpeningBalance) || 0;
}
});
return {
customerId: customerId,
openingBalance: openingBalance,
totalSalesDue: totalDue,
totalPaid: totalPaid,
currentDue: openingBalance + totalDue - totalPaid
};
}
/**
* Records a payment from a customer against their dues.
* Any role can record a payment — cashier receives cash and enters it.
*/
function recordPayment(token, data) {
const session = validateSession(token);
if (!data.customerId) throw new Error('Customer is required.');
const amount = Number(data.amount);
if (!amount || amount <= 0) throw new Error('Amount must be positive.');
const paymentId = 'PMT-' + uuid().substring(0, 8).toUpperCase();
appendRow('CustomerPayments', [
paymentId, new Date(), data.customerId, amount,
data.method || 'Cash', trim(data.note), session.email
]);
logAction(session.email,
'PAYMENT_RECEIVED ' + data.customerId + ' ৳' + amount);
const due = getCustomerDue(token, data.customerId);
return {
success: true,
paymentId: paymentId,
newDue: due.currentDue
};
}
/**
* Returns the full statement for a customer: all sales with due
* plus all payments, sorted by date.
*/
function getCustomerStatement(token, customerId) {
validateSession(token);
const sales = readAll('Sales')
.filter(function(s) { return s.CustomerId === customerId; })
.map(function(s) {
return {
date: s.Date, type: 'SALE',
ref: s.InvoiceNo, amount: Number(s.Total),
paid: Number(s.Paid), due: Number(s.Due)
};
});
const payments = readAll('CustomerPayments')
.filter(function(p) { return p.CustomerId === customerId; })
.map(function(p) {
return {
date: p.Date, type: 'PAYMENT',
ref: p.PaymentId, amount: Number(p.Amount),
method: p.Method, note: p.Note
};
});
const combined = sales.concat(payments)
.sort(function(a, b) { return new Date(a.date) - new Date(b.date); });
const due = getCustomerDue(token, customerId);
return {
customerId: customerId,
entries: combined,
summary: due
};
}
/**
* Returns all customers with outstanding dues > 0.
* Sorted by highest due first.
*/
function getCustomersWithDues(token) {
validateSession(token);
const customers = getAllCustomers(token);
const sales = readAll('Sales');
const payments = readAll('CustomerPayments');
const dueMap = {};
customers.forEach(function(c) {
dueMap[c.id] = c.openingBalance || 0;
});
sales.forEach(function(s) {
if (dueMap[s.CustomerId] !== undefined) {
dueMap[s.CustomerId] += Number(s.Due) || 0;
}
});
payments.forEach(function(p) {
if (dueMap[p.CustomerId] !== undefined) {
dueMap[p.CustomerId] -= Number(p.Amount) || 0;
}
});
return customers
.map(function(c) {
return {
id: c.id, name: c.name, mobile: c.mobile,
due: Math.max(0, dueMap[c.id] || 0)
};
})
.filter(function(c) { return c.due > 0; })
.sort(function(a, b) { return b.due - a.due; });
}Customers UI (Dues Overview)
<!DOCTYPE html>
<html lang="bn">
<head>
<meta charset="UTF-8">
<title>Customers & Dues — <?= COMPANY_NAME ?></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
body { background: #f8fafc; font-family: "Segoe UI", Roboto, "Noto Sans Bengali", sans-serif; }
.due-badge { background: #fee2e2; color: #991b1b; font-weight: 700;
padding: 4px 10px; border-radius: 8px; font-size: 13px; }
.due-badge.zero { background: #dcfce7; color: #166534; }
.due-kpi {
background: linear-gradient(135deg,#f5f3ff,#ecfeff);
border: 1px solid #e0e7ff; border-radius: 14px; padding: 20px;
text-align: center;
}
.due-kpi__label { font-size: 12px; color: #64748b; font-weight: 600;
letter-spacing: .3px; text-transform: uppercase; }
.due-kpi__value { font-size: 26px; font-weight: 800; color: #0e7490;
margin-top: 6px; }
</style>
</head>
<body>
<nav class="navbar navbar-dark" style="background: linear-gradient(90deg,#6d28d9,#06b6d4);">
<div class="container-fluid">
<a class="navbar-brand fw-bold" href="?page=Dashboard"><?= COMPANY_NAME ?> ERP</a>
<div class="d-flex align-items-center text-white">
<span id="userBadge"></span>
<button class="btn btn-outline-light btn-sm ms-3"
onclick="fl365Logout()">Logout</button>
</div>
</div>
</nav>
<div class="container py-4">
<div class="d-flex justify-content-between align-items-center mb-4">
<h4 class="mb-0">Customers & Dues · বাকি ব্যবস্থাপনা</h4>
<button class="btn btn-primary" id="newCustBtn">+ নতুন ক্রেতা</button>
</div>
<div class="row g-3 mb-4">
<div class="col-md-4">
<div class="due-kpi">
<div class="due-kpi__label">Total Customers</div>
<div class="due-kpi__value" id="kpiCustomers">0</div>
</div>
</div>
<div class="col-md-4">
<div class="due-kpi">
<div class="due-kpi__label">Customers With Due</div>
<div class="due-kpi__value" id="kpiWithDue">0</div>
</div>
</div>
<div class="col-md-4">
<div class="due-kpi">
<div class="due-kpi__label">Total Receivable</div>
<div class="due-kpi__value" id="kpiTotal">৳ 0</div>
</div>
</div>
</div>
<div class="card">
<div class="card-body">
<div class="table-responsive">
<table class="table table-hover">
<thead>
<tr>
<th>Customer</th>
<th>Mobile</th>
<th class="text-end">Due</th>
<th style="width:200px;">Actions</th>
</tr>
</thead>
<tbody id="duesBody">
<tr><td colspan="4" class="text-center text-muted py-4">Loading...</td></tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
<!-- Payment Modal -->
<div class="modal fade" id="payModal" tabindex="-1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Record Payment</h5>
<button class="btn-close" data-bs-dismiss="modal"></button>
</div>
<div class="modal-body">
<p id="payFor" class="text-muted mb-3"></p>
<input type="hidden" id="payCustomerId">
<div class="mb-3">
<label class="form-label">Amount (৳)</label>
<input type="number" class="form-control" id="payAmount"
step="0.01">
</div>
<div class="mb-3">
<label class="form-label">Method</label>
<select class="form-select" id="payMethod">
<option>Cash</option>
<option>bKash</option>
<option>Nagad</option>
<option>Card</option>
</select>
</div>
<div class="mb-3">
<label class="form-label">Note</label>
<input type="text" class="form-control" id="payNote">
</div>
</div>
<div class="modal-footer">
<button class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
<button class="btn btn-success" id="savePayBtn">Save Payment</button>
</div>
</div>
</div>
</div>
<?!= include('JS') ?>
<script>
let payModal, dues = [];
renderUserBadge('userBadge');
function loadDues() {
apiCall('getCustomersWithDues')
.then(function(list) {
dues = list;
renderDues();
})
.catch(function(err) { alert(err.message); });
}
function renderDues() {
document.getElementById('kpiCustomers').textContent = dues.length;
document.getElementById('kpiWithDue').textContent = dues.length;
const total = dues.reduce(function(s, d) { return s + d.due; }, 0);
document.getElementById('kpiTotal').textContent = '৳ ' + total.toFixed(2);
const tbody = document.getElementById('duesBody');
if (!dues.length) {
tbody.innerHTML = '<tr><td colspan="4" class="text-center text-muted py-4">🎉 No customers with dues. Great job!</td></tr>';
} else {
tbody.innerHTML = dues.map(function(d) {
return '<tr>'
+ '<td><strong>' + d.name + '</strong></td>'
+ '<td>' + (d.mobile || '—') + '</td>'
+ '<td class="text-end"><span class="due-badge">৳ '
+ d.due.toFixed(2) + '</span></td>'
+ '<td>'
+ '<button class="btn btn-sm btn-success" onclick="openPay(\''
+ d.id + '\',\'' + d.name + '\',' + d.due + ')">'
+ 'Receive Payment</button>'
+ ' <button class="btn btn-sm btn-outline-secondary" onclick="viewStatement(\''
+ d.id + '\')">Statement</button>'
+ '</td>'
+ '</tr>';
}).join('');
}
}
window.openPay = function(id, name, due) {
document.getElementById('payCustomerId').value = id;
document.getElementById('payFor').textContent = 'Receiving payment from ' + name + ' (due ৳' + due.toFixed(2) + ')';
document.getElementById('payAmount').value = due.toFixed(2);
document.getElementById('payNote').value = '';
payModal.show();
};
window.viewStatement = function(id) {
apiCall('getCustomerStatement', id)
.then(function(st) {
const w = window.open('', '_blank');
let html = '<html><head><title>Statement</title>'
+ '<style>body{font-family:sans-serif;padding:24px;max-width:720px;margin:0 auto;} table{width:100%;border-collapse:collapse;font-size:14px;} th,td{padding:8px;border-bottom:1px solid #e2e8f0;text-align:left;} th{background:#f8fafc;} .k{text-align:right;font-weight:700;} h2{margin:0 0 4px;}'
+ '@media print{@page{size:A4;margin:14mm}}</style></head><body>'
+ '<h2>' + '<?= COMPANY_NAME ?>' + '</h2>'
+ '<p>Customer Statement<br>Printed: ' + new Date().toLocaleString() + '</p>'
+ '<hr>'
+ '<table><thead><tr><th>Date</th><th>Type</th><th>Ref</th><th class="k">Amount</th><th class="k">Paid</th><th class="k">Due</th></tr></thead><tbody>'
+ st.entries.map(function(e) {
return '<tr><td>' + new Date(e.date).toLocaleDateString() + '</td>'
+ '<td>' + e.type + '</td>'
+ '<td>' + e.ref + '</td>'
+ '<td class="k">' + Number(e.amount).toFixed(2) + '</td>'
+ '<td class="k">' + Number(e.paid || 0).toFixed(2) + '</td>'
+ '<td class="k">' + Number(e.due || 0).toFixed(2) + '</td></tr>';
}).join('')
+ '</tbody></table>'
+ '<hr><p><strong>Current Due: ৳ ''
+ Number(st.summary.currentDue).toFixed(2) + '</p>'
+ '<script>setTimeout(function(){window.print();},250);</script>'
+ '</body></html>';
w.document.write(html);
w.document.close();
}).catch(function(err) { alert(err.message); });
};
document.getElementById('savePayBtn').addEventListener('click', function() {
const customerId = document.getElementById('payCustomerId').value;
const amount = Number(document.getElementById('payAmount').value);
const method = document.getElementById('payMethod').value;
const note = document.getElementById('payNote').value;
apiCall('recordPayment', { customerId, amount, method, note })
.then(function(res) {
alert('Payment recorded. New due: ৳' + Number(res.newDue).toFixed(2));
payModal.hide();
loadDues();
})
.catch(function(err) { alert(err.message); });
});
document.getElementById('newCustBtn').addEventListener('click', function() {
const name = prompt('Customer name:');
if (!name) return;
const mobile = prompt('Mobile:') || '';
apiCall('createCustomer', { name, mobile })
.then(loadDues)
.catch(function(err) { alert(err.message); });
});
payModal = new bootstrap.Modal(document.getElementById('payModal'));
loadDues();
</script>
</body>
</html>
11
A Full Day at Karim's Grocery
Let's walk through one real day. Every action below is something the shop actually does — and every one of them is a call into the code you have just built.
8:55 AM — Karim Opens the Shop
Karim logs in
Opens the app on his Android phone, logs in as karim@demo.bd. Session token stored. Audit log records LOGIN_SUCCESS role=Admin.
Checks the dashboard
Sees yesterday's sales, today's starting stock. Notes that rice is at 142 bags (from yesterday). Reorder level is 20 — plenty.
Reviews low-stock alerts
Two products flagged: sugar (8 kg left, reorder level 15) and soybean oil (5 pcs left, reorder level 15). Karim decides to call the supplier.
9:15 AM — Purchase Order Arrives
Rahim (Manager) records the GRN
Opens Purchase page. Selects supplier "Rahman Wholesale". Adds 50 kg sugar at ৳110/kg and 40 pcs soybean oil at ৳290/pc. Total: ৳17,100. Pays ৳10,000 by bKash, ৳7,100 due.
GRN is saved
System generates GRN-2026-00043. PurchaseDetails has 2 rows. StockLedger has 2 new PURCHASE entries: sugar +50 → 58, oil +40 → 45. Supplier due increases by ৳7,100.
Cost prices update
Both products' cost prices are set to the latest purchase price (৳110 and ৳290). The next time these products are sold, the profit calculation will use the new cost.
10:30 AM — First Rush
Nasrin (Cashier) logs in
Opens the app on the shop tablet. Logs in. Can see products but not profit reports. Cannot access Purchase or Products pages.
Customer 1 — walk-in, cash
Scans a packet of lentils (barcode 8901234567894). Price ৳160 auto-fills. Clicks "Save & Print". Sale saved with invoice INV-2026-00058, paid by cash. Receipt prints on 80mm thermal printer.
Customer 2 — Rahim Bhai, credit
Buys rice and oil totalling ৳1,450. Says "খাতায় লিখেন" (put it on my tab). Nasrin selects Rahim Bhai from the customer dropdown, enters paid ৳0. System allows because customer is selected (not WALK-IN). Due ৳1,450 is added to his account.
Stock decreases automatically
Rice: 142 → 140. Oil: 45 → 44. Lentils: 30 → 29. Every ledger row's Balance matches what's physically on the shelf.
12:45 PM — A Customer Complains About Prices
Nasrin sees wrong price on screen
One product shows ৳130 but the shelf tag says ৳140. She calls Karim over.
Karim updates the product
Opens Products page (he's Admin, so he sees it). Finds the product, clicks Edit, changes SalePrice to ৳140. Saves. Audit log records PRODUCT_UPDATED P....
Nasrin's next sale uses the new price
She refreshes the POS (or just adds the product again — the frontend reloads the product list on page refresh). New price ৳140 is used. Any sales already made with ৳130 stay as they were — no retroactive change. Correct behaviour.
3:00 PM — Shift Change
Nasrin logs out
Session token removed from cache. Audit log records LOGOUT.
Fatema logs in for the evening shift
Fresh session. Her sales from this point on are logged under her email, not Nasrin's. When Karim checks the audit log at night, he can distinguish who did what.
Karim reviews the day
Checks dashboard: today's sales ৳38,400, dues collected ৳2,100, purchases ৳17,100, low-stock items 3. All computed from the sheets.
8:30 PM — Rahim Bhai Pays His Due
Rahim Bhai walks in with ৳1,000
"আমার বাকিটা শোধ করতে চাই।" (I want to clear part of my due.)
Fatema records the payment
Opens Customers page. Finds Rahim Bhai (due ৳1,450 + previous ৳250 = ৳1,700). Clicks "Receive Payment". Enters ৳1,000, method Cash. Saves.
New due: ৳700
CustomerPayments gets a new row with ৳1,000. Current due recalculated: ৳1,700 − ৳1,000 = ৳700. Audit log: PAYMENT_RECEIVED C... ৳1000.
9:00 PM — Closing the Day
Karim checks today's numbers
Sales Today
৳ 38,400
142 invoices
Payments Received
৳ 3,100
Against previous dues
Purchases Today
৳ 17,100
1 GRN
Total Receivable
৳ 12,450
Across 34 customers
Reviews stock alerts
3 products still below reorder level. Tomorrow's task: another purchase.
Closes the shop
Logs out. Tomorrow: repeat. The ledger, the sales, and the customers are all consistent.
📊
Total cost of running this shop's ERP for the entire day: ৳ 0. All data lives in one Google Sheet inside one Google account. Google's free tier covers this easily.
12
Deep Dive: 5 Real Shop Scenarios
Every shop uses the same code, but they use it in different ways. Here is how the five shops from the start of Part 3 use the exact modules you have just built.
🛒
Karim's Grocery
Weight-based products, high volume, credit-heavy
How they use the system:
- Products: Unit = "kg" for rice, dal, sugar. Unit = "pcs" or "litre" for packaged goods. Barcodes only for factory-packaged items.
- POS: Nasrin types "চাল" — the search returns "চাল ৫ কেজি", "চাল ২৫ কেজি", etc. She picks the right one. Cart quantity is edited directly for custom weights (e.g., 1.5 kg).
- Dues: Heavy. 80% of customers have a credit line. Fatema records customer payments daily — usually in the evening.
- Receipts: Rarely printed — most customers don't want them. Only printed for credit sales as acknowledgement.
- Stock: Low stock alerts drive the morning purchase calls.
Daily numbers: 200 sales, avg ৳280, total ৳56,000/day. 15 new credits, 40 payments.
💊
Rahman Pharmacy
High SKU count, batch-sensitive, mixed cash and institutional billing
How they use the system:
- Products: 1,500 medicines. Names include strength (e.g., "Napa 500mg tablet"). Categories: Antibiotics, Vitamins, Pain Relief, etc.
- Barcodes: Most medicines have manufacturer barcodes. Scanning is standard at the counter.
- Notes field: Used for batch numbers and expiry. Part 4 will extend this to a proper Batch sheet.
- Customers: Two types — cash walk-ins (WALK-IN) and institutional accounts (hospitals, clinics) with 30-day terms.
- Stock: Loose tablets are stocked in bulk. Reorder level per product. Alerts on vitamins and seasonal medicines.
Daily numbers: 150 sales, avg ৳450. 12 institutional invoices per week.
📱
Nasrin Electronics
High ticket, low volume, IMEI tracking, installments
How they use the system:
- Products: Phones and accessories. Each phone model is a separate product. Colour variants are separate products (e.g., "iPhone 15 - Black", "iPhone 15 - Blue").
- Notes: IMEI stored in the product Notes field for phones. When a phone is sold, the receipt carries the IMEI.
- Discounts: Large. A phone may be discounted ৳2,000 from list. Cashier can apply a max discount of ৳500; anything more requires Manager role.
- Installments: Customers pay 20% down, rest over 6–12 months. Each installment is a "customer payment". Current due tracked. Part 4 will add auto-reminders for upcoming installments.
- Payment methods: bKash and Nagad very common. Card for larger purchases. Cash rare above ৳10,000.
Daily numbers: 60 sales, avg ৳3,500. 3 new installment customers per week.
👗
Fatema Boutique
Variants, custom orders, prepayments
How they use the system:
- Products: Each design-size-colour combination is a product. Names like "Kameez-Lotus-Red-M", "Kameez-Lotus-Red-L". Products with only 1–2 units. Sarees are unique — one-off products created per piece.
- Prices: Each product has its own price. Some sarees are ৳15,000+. Sales are infrequent but high-value.
- Custom orders: Customer pays 50% deposit at order. Recorded as a sale with that amount paid, the rest as due. When delivered, the customer pays the balance.
- Receipts: Always printed. Sarees are expensive, customers want a written record.
- Returns: Rare but important. A customer returns an item if size is wrong. Part 4 will add a proper Returns screen; for now the Manager can use Stock Adjust.
Daily numbers: 40 sales, avg ৳1,800. Weekend traffic is 3x weekday.
🍽️
Rahim Restaurant
High concurrency, table numbers, no stock
How they use the system:
- Products: Menu items. No stock tracking (a restaurant cannot easily track "5 kg of rice" used per biryani). Stock ledger is mostly unused. Instead, purchases are recorded as expenses.
- Customers: Tables. Each table has an ID like "TABLE-07". The "customer" dropdown lists tables. When a table pays, the due is recorded. Walk-ins (parcels) use WALK-IN.
- POS: Fast. Peak lunch rush has 30–40 orders per hour. The LockService handles 30 simultaneous writes, which covers 3–4 waiters writing orders at once.
- Receipts: One printed per table at billing time. The receipt serves as the table's final bill.
- Concurrency: During peak lunch (1:00–2:00 PM), three waiters use POS at the same time. LockService queues writes. Average wait: 200ms.
Daily numbers: 300 orders, avg ৳450. Peak hour: 65 orders.
🎯
Same code, five shops. The products differ, prices differ, customer behaviour differs — but the underlying model is identical. That is the payoff of building on a proper schema from Part 1.
✓
Part 3 Completion Checklist
Before moving to Part 4, confirm all of these work:
- Products.gs, Stock.gs, Purchases.gs, Suppliers.gs, Pos.gs, Customers.gs are all saved.
- The Products page loads and shows existing products.
- You can create a new product from the UI and it appears in the sheet.
- Opening stock entered at creation lands in the StockLedger.
- You can search for a product in the POS by name.
- Scanning a barcode adds the product to the cart automatically.
- Saving a sale writes to Sales and SaleDetails.
- Stock balance decreases after a sale.
- Two POS saves in different tabs do not produce duplicate invoice numbers.
- Purchases (GRN) write to Purchases, PurchaseDetails, and StockLedger.
- GRN entries update the CostPrice of purchased products.
- Customers sheet fills as customers are added via POS.
- Payments reduce the customer's outstanding due.
- A printable statement opens for any customer with dues.
- The receipt template prints in the correct size for your thermal printer.
- Bengali text does not break the receipt (even if it shows as boxes).
- Low stock alerts appear for products below their reorder level.
- Stock adjustment writes a correction to the ledger with a reason.
- Void sale works for same-day sales and writes a reverse ledger entry.
- The dashboard shows today's sales, dues, and low-stock count.
🎓
You now have a full POS system. Not a "demo" POS. A real one. Real shops in Bangladesh (and anywhere) can use it to run their business today. Part 4 will add Reports (daily/monthly/profit), PDF Invoices, and a proper Returns workflow.
Q
Knowledge Check — Interactive Quiz
Eight questions covering Part 3. Test your understanding before moving on.
Part 3 Quiz
Correct answers explained instantly.
Score: 0 / 0
?
Frequently Asked Questions
Can two cashiers really bill at the same moment without conflicts?
Yes. Every write to Sales, SaleDetails, and StockLedger happens inside a
LockService.getScriptLock(). Only one execution can hold the lock; the other waits up to 20 seconds. In practice, two cashiers saving at the exact same second will see a delay of 200–500ms — invisible to them.
What happens if a cashier accidentally sells a product that has 0 stock?
The server-side
createSale checks stock levels before writing anything. If any line has insufficient stock, the entire sale is rejected with a clear error message telling the cashier which product and how much is available. Nothing is partially written — it is all-or-nothing.
Can a Cashier see today's total sales?
By default, yes — a Cashier can see today's sales total on the dashboard. They cannot see profit margins or month-to-date figures. If you want to restrict even the daily total, you would add
requireRole(session, ['Admin','Manager']) to the relevant dashboard function. In Part 4 we will build a proper role-aware dashboard.
What if two cashiers enter the same sale twice by mistake?
The system creates two separate invoices — it cannot detect the "same sale" because there is no shared reference between them. The correct fix is a void. An Admin opens the Audit log, finds the duplicate invoice, and calls
voidSale(invoiceNo, 'Duplicate entry'). This creates a reverse ledger entry that restores stock and marks the sale as void (Part 4 will add a Void column to Sales for proper tracking).
How do I handle returns? A customer brings back an item.
Two ways. If the return is the same day, use Void Sale if the whole invoice is returned. If only part is returned, or it is a different day, the Manager records a Stock Adjustment of type INCREASE for the returned quantity (with a note explaining it is a return), and records a negative payment to the customer's account or a cash refund. Part 4 will add a proper Returns screen with a dedicated ledger type.
Can I sell a product that is inactive?
No.
getActiveProducts filters out inactive products before the POS even sees them. If a cashier tries to sell one by manipulating the frontend, createSale would still allow it (because we do not re-check IsActive), but the search would never surface it in the first place. In Part 4 we will add an explicit active check in createSale.
What if the same product is added twice in one sale?
The POS handles this on the client side — if the product already exists in the cart, the quantity is incremented instead of adding a new line. This is cleaner for the cashier and produces a tidy receipt. SaleDetails then has one row per product per invoice.
Can I edit a sale after it is saved?
No. Sales are immutable once written. This is a deliberate design choice — an audit trail is only useful if the past cannot be rewritten. The correct way to "fix" a sale is to void it and create a new, correct one. This mirrors how real POS systems work in every country.
→
What's Coming in Part 4
Part 3 gave you a working POS. Part 4 will turn the numbers into insight and the paper into PDFs.
- Daily, weekly, monthly reports — sales, purchases, expenses, profit
- Profit analysis per product — which products are profitable, which are dragging
- PDF invoices — professional A4 invoices generated from a Google Docs template, saved to Drive
- Bilingual invoice — Bengali + English in a single PDF
- Returns management — proper Returns sheet with its own ledger entries
- Expense tracking — rent, salary, electricity — all in one place
- Profit & Loss statement — auto-generated monthly P&L
- Advanced dashboard — sales chart, top products, cashier performance
- Scheduled email reports — daily summary at 10 PM by Gmail, free
- Cashier performance — who sold what, error rates
📚
Test your POS with real customers before moving on. Even one day of live use will reveal edge cases no tutorial can anticipate — and those edge cases make the best lessons for Part 4.
📖
More Free Resources on FreeLearning365
Pair this tutorial with our other free tools and guides.
CAREER
Job Interview Preparation — Programming, Cloud, Data, ERP & More
Ace IT interviews with structured guides across programming, cloud, data engineering, ERP, SAP and more.
LEARN
Free Online Tutorials & Learning Paths
JavaScript, Angular, Python, SQL, Data Analysis, Cloud, Software Architecture — all free.
TOOLS
100+ Free Online Tools & Utilities
Developer tools, SEO utilities, converters, formatters — no registration required.
AI
World-Class AI Prompt Generator — 40+ Professional Types
Craft high-quality AI prompts for development, business writing, and analytics.
TRAINING
Advance Your IT Career with Professional Training
Structured training paths designed for the Bangladesh IT job market.
EBOOKS
FreeLearning365 eBook Collection
Free downloadable eBooks on programming, tools, and career development.
UTILITY
Free Barcode & Label Generator
Perfect companion for your new POS — generate product barcodes and A4 label sheets instantly.
UTILITY
Free QR Code Generator
Create printable QR codes for products, invoices, or store signage.
BOOTSTRAP
Drag & Drop Form Generator Pro — Bootstrap 5.3/4
Build clean, responsive forms fast — ideal for extending this ERP with custom data-entry screens.
UTILITY
Bangladesh Electricity Bill Calculator 2026
BERC tariff calculator with appliance estimator and printable report.
FINANCE
Bangladesh Income Tax Calculator | NBR Slabs, Rebate
Calculate tax liability with current NBR slabs, rebate and minimum tax rules.
FINANCE
Bangladesh National Pay Scale 2026 Calculator
NPS 2026 salary calculator with basic, grade pay, and allowances.
AI
AI Background Remover Online — Free
Clean product photos for your ERP's catalog in one click.
EDUCATION
Bangladesh's Largest Free Question Bank — BCS, HSC, SSC, JSC, PSC
Complete solutions and question banks for students across Bangladesh.
EDUCATION
EV Class 9-10 All Subjects Guide | SSC Notes, MCQs, CQs
Complete guide for Physics, Chemistry, Biology, Math, ICT and BGS.
EDUCATION
HSC, SSC & Class 6-10 Lecture Sheets PDF Download
400+ completely free lecture sheets, notes and suggestions.
FREELEARNING365.COM
🚀 Complete Free ERP — 5 Part Series
Google Sheets + Apps Script · POS · Inventory · Reports · AI
01 · 🏗️ Foundation
02 · 🔐 Authentication
03 · 🛒 POS & Inventory
04 · 📊 Reports & P&L
05 · 🤖 AI & Go-Live
Follow the complete journey: Foundation → Security → POS → Intelligence → Scale
0 Comments
thanks for your comments!