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
Exports, Backups, AI Assistant, Migration & Going Live
This is where your ERP stops being "a project" and becomes a business asset. We add CSV/Excel exports, automated backups, multi-branch support, an AI assistant that answers questions in natural language, WhatsApp integration, bulk import, the SQL Server migration path, and the twenty-point go-live checklist that has launched real shops.
CSV & Excel exports
AI assistant built-in
Go-live checklist
★
Four Parts Ago, You Wrote Your First Line of Code
Let's pause for a moment. In Part 1 you created a blank Google Sheet and typed your first function getSpreadsheet(). In Part 2 you built a login screen. In Part 3 a cashier started ringing up sales. In Part 4 the owner started getting reports at 10 PM. Now, in Part 5, we finish the job.
By the end of this part, you will have:
- Every report downloadable as CSV or Excel — so an accountant can open it in their own tools.
- A nightly backup of your entire database — so a single mistake never destroys your business.
- Multi-branch support — one shop today, five shops next year, no re-architecture.
- An AI assistant — "Show me last month's top product" answered in plain language.
- A path to SQL Server, ASP.NET Core and Azure — so this scales from 1 shop to 100.
- A 20-point go-live checklist that has launched real businesses.
🏁
This is the finale. It's the largest Part in the series — not because it's more code, but because it's the moment everything connects. Take your time. Read it once. Then go back and do it.
01
What You Will Build in Part 5
CSV & Excel Exports
Download any report as CSV, or generate Excel files from any sheet in one click.
Automated Backups
Nightly full copy of the database, kept for 30 days rolling plus monthly archives.
Multi-Branch
Add a BranchId column, and every sale, purchase and stock row knows where it belongs.
AI Assistant
Ask questions in plain Bengali or English. The assistant writes the query, runs it, and answers.
WhatsApp & SMS
Send invoice confirmations and reminders through WhatsApp Cloud API — free tier.
Bulk Import
Upload a CSV of 500 products and have them all created in seconds.
1
CSV & Excel Exports
There are moments when a shop needs its data outside the ERP: an accountant filing taxes, an owner applying for a loan, or a manager building a custom spreadsheet. Exports make that easy.
Two Export Formats
| Format | When to use | Pros | Cons |
|---|---|---|---|
| CSV | Simple data, imports into any tool | Universal, tiny, opens anywhere | No formatting, no formulas |
| Excel (.xlsx) | Reports with multiple sheets, formatting | Professional, formulas, styles | Larger files, needs a converter |
Export.gs — Server-Side Export Helpers
/**
* Export.gs
* Provides CSV downloads (raw text returned to the frontend)
* and XLSX downloads (converted via Drive's export URL).
*/
/**
* Exports any report's data as a CSV string.
* @param {string} token
* @param {string} reportType — 'sales', 'products', 'expenses', 'profit', 'customers'
* @param {Object} options — { from, to } date range for time-bound reports
* @returns {Object} { success, filename, content }
*/
function exportReportCsv(token, reportType, options) {
validateSession(token);
options = options || {};
let rows = [];
let filename = 'export';
switch (reportType) {
case 'sales':
rows = buildCsvSales(token, options.from, options.to);
filename = 'sales_' + (options.from || 'all') + '_to_' + (options.to || 'now');
break;
case 'products':
rows = buildCsvProducts(token);
filename = 'products_' + today();
break;
case 'expenses':
rows = buildCsvExpenses(token, options.from, options.to);
filename = 'expenses_' + (options.from || 'all');
break;
case 'profit':
rows = buildCsvProfit(token, options.from, options.to);
filename = 'profit_' + (options.from || 'all');
break;
case 'customers':
rows = buildCsvCustomers(token);
filename = 'customers_' + today();
break;
case 'stock':
rows = buildCsvStock(token);
filename = 'stock_' + today();
break;
default:
throw new Error('Unknown report type: ' + reportType);
}
const session = validateSession(token);
logAction(session.email, 'EXPORT_CSV ' + reportType);
return {
success: true,
filename: filename + '.csv',
content: toCsv(rows)
};
}
/**
* Converts a 2D array to a CSV string, handling quotes and commas.
*/
function toCsv(rows) {
return rows.map(function(r) {
return r.map(function(cell) {
const s = String(cell == null ? '' : cell);
// Quote fields that contain a comma, quote, or newline
if (/[",\n]/.test(s)) {
return '"' + s.replace(/"/g, '""') + '"';
}
return s;
}).join(',');
}).join('\r\n');
}
// ---------- CSV builders ----------
function buildCsvSales(token, from, to) {
const sales = getSales(token, from, to);
const rows = [[
'InvoiceNo', 'Date', 'CustomerId',
'SubTotal', 'Discount', 'Total',
'Paid', 'Due', 'PaymentMethod', 'Cashier'
]];
sales.forEach(function(s) {
rows.push([
s.invoiceNo, fmtDate(s.date), s.customerId,
s.subTotal, s.discount, s.total, s.paid, s.due, s.paymentMethod, s.userId
]);
});
return rows;
}
function buildCsvProducts(token) {
const products = getAllProducts(token);
const rows = [[
'ProductId', 'Name', 'Category', 'Unit',
'CostPrice', 'SalePrice', 'IsActive',
'ReorderLevel', 'Barcode', 'Notes'
]];
products.forEach(function(p) {
rows.push([
p.id, p.name, p.category, p.unit, p.costPrice, p.salePrice,
p.isActive ? 'TRUE' : 'FALSE', p.reorderLevel, p.barcode, p.notes
]);
});
return rows;
}
function buildCsvExpenses(token, from, to) {
const expenses = getExpenses(token, from, to);
const rows = [['Date', 'Category', 'Amount', 'Method', 'Note', 'UserId']];
expenses.forEach(function(e) {
rows.push([fmtDate(e.date), e.category, e.amount, e.method, e.note, e.userId]);
});
return rows;
}
function buildCsvProfit(token, from, to) {
const rows = getProductProfitReport(token, from, to);
const out = [[
'ProductId', 'Name', 'Unit',
'QtySold', 'Revenue', 'COGS',
'GrossProfit', 'MarginPercent'
]];
rows.forEach(function(r) {
out.push([
r.productId, r.name, r.unit, r.qty, r.revenue, r.cost,
r.grossProfit, r.margin.toFixed(2)
]);
});
return out;
}
function buildCsvCustomers(token) {
const dues = getCustomersWithDues(token);
const all = getAllCustomers(token);
const dueMap = {};
dues.forEach(function(d) { dueMap[d.id] = d.due; });
const rows = [['CustomerId', 'Name', 'Mobile', 'Address', 'OpeningBalance', 'CurrentDue']];
all.forEach(function(c) {
rows.push([c.id, c.name, c.mobile, c.address, c.openingBalance, dueMap[c.id] || 0]);
});
return rows;
}
function buildCsvStock(token) {
validateSession(token);
const products = getAllProducts(token);
const ledger = readAll('StockLedger');
const stockMap = {};
ledger.forEach(function(l) { stockMap[l.ProductId] = Number(l.Balance) || 0; });
const rows = [[
'ProductId', 'Name', 'Category', 'Unit',
'CurrentStock', 'ReorderLevel',
'CostPrice', 'SalePrice', 'StockValueAtCost'
]];
products.forEach(function(p) {
const stock = stockMap[p.id] || 0;
rows.push([
p.id, p.name, p.category, p.unit, stock, p.reorderLevel,
p.costPrice, p.salePrice, (stock * p.costPrice).toFixed(2)
]);
});
return rows;
}
/**
* Exports a full Google Sheet tab as XLSX.
* Creates a temporary spreadsheet with the tab's data, converts to XLSX,
* then trashes the temporary file.
* @param {string} token
* @param {string} sheetName — 'Products', 'Sales', etc.
* @returns {Object} { success, fileUrl, fileId }
*/
function exportSheetAsXlsx(token, sheetName) {
validateSession(token);
const source = sheet(sheetName);
const values = source.getDataRange().getValues();
// Create a temp spreadsheet with this data
const temp = SpreadsheetApp.create('Export_' + sheetName + '_' + today());
const tempSheet = temp.getSheets()[0];
tempSheet.setName(sheetName);
if (values.length > 0 && values[0].length > 0) {
tempSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
// Bold the header row
tempSheet.getRange(1, 1, 1, values[0].length).setFontWeight('bold').setBackground('#ede9fe');
tempSheet.setFrozenRows(1);
// Auto-size columns
for (let c = 1; c <= values[0].length; c++) {
tempSheet.autoResizeColumn(c);
}
}
// Export as XLSX
const file = DriveApp.getFileById(temp.getId());
const xlsxUrl = 'https://docs.google.com/spreadsheets/d/' + file.getId() +
'/export?format=xlsx';
// Trash the temp spreadsheet — we hand the URL to the client instead
try { file.setTrashed(true); } catch (e) {}
return {
success: true,
fileUrl: xlsxUrl,
sheetName: sheetName,
rowCount: values.length
};
}
function fmtDate(d) {
if (!d) return '';
return Utilities.formatDate(new Date(d), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');
}
function today() {
return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
}Frontend Export Helper
Add these utility functions to your shared JS.html so any page can trigger an export:
/**
* Downloads a CSV file generated by the server.
* @param {string} reportType — 'sales', 'products', etc.
* @param {Object} options — { from, to }
*/
window.downloadCsv = function(reportType, options) {
apiCall('exportReportCsv', reportType, options || {})
.then(function(res) {
const blob = new Blob([res.content], { type: 'text/csv;charset=utf-8;' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = res.filename;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
})
.catch(function(err) { alert('Export failed: ' + err.message); });
};
/**
* Opens an XLSX export of a full sheet in a new tab.
*/
window.downloadXlsx = function(sheetName) {
apiCall('exportSheetAsXlsx', sheetName)
.then(function(res) {
window.open(res.fileUrl, '_blank');
})
.catch(function(err) { alert('XLSX export failed: ' + err.message); });
};Sample CSV Output — Sales
InvoiceNo,Date,CustomerId,SubTotal,Discount,Total,Paid,Due,PaymentMethod,Cashier
INV-2026-00042,2026-09-10 18:47,C-Rahim,1480.00,30.00,1450.00,1500.00,0.00,Cash,nasrin@demo.bd
INV-2026-00043,2026-09-10 19:02,WALK-IN,320.00,0.00,320.00,320.00,0.00,Cash,nasrin@demo.bd
INV-2026-00044,2026-09-10 19:15,C-Fatema,2200.00,100.00,2100.00,0.00,2100.00,Due,fatema@demo.bd
📊
Why Excel via Drive? There is no free XLSX library available in Apps Script. Google Sheets is already an Excel-compatible spreadsheet, so we create a temporary Sheet, then use Drive's built-in export URL (
/export?format=xlsx) to deliver an XLSX download. Zero dependencies, zero cost, perfect compatibility.
2
Automated Nightly Backups
A shop's spreadsheet is a business asset. Losing it — through accidental deletion, a Google account issue, or a corrupted sheet — could end the business. A daily automated backup is not optional.
Backup Strategy
Every night at 2:00 AM
│
▼
Copy the entire spreadsheet
· Filename: ERP_Backup_2026-09-10
· Location: Drive/ERP_Backups/
│
▼
Retention rules
· Keep last 30 daily backups
· On the 1st of each month, archive a copy to ERP_Backups/Monthly/
· Keep 12 monthly archives
· Delete older backups automatically
│
▼
Weekly integrity check
· Verify the latest backup opens
· Count rows in key sheets
· Email the owner the backup status
/**
* Backup.gs
* Daily automated backup of the entire company spreadsheet.
* Retains 30 daily + 12 monthly copies in Drive.
*
* Setup: run installBackupTrigger() once.
*/
const BACKUP_FOLDER_NAME = 'ERP_Backups';
const BACKUP_DAILY_LIMIT = 30;
const BACKUP_MONTHLY_LIMIT = 12;
function installBackupTrigger() {
// Remove existing backup triggers
ScriptApp.getProjectTriggers().forEach(function(t) {
if (t.getHandlerFunction() === 'runDailyBackup') {
ScriptApp.deleteTrigger(t);
}
});
// Daily at 2:00 AM shop time
ScriptApp.newTrigger('runDailyBackup')
.timeBased()
.atHour(2)
.nearMinute(0)
.everyDays(1)
.create();
Logger.log('Daily backup trigger installed at 2 AM.');
}
/**
* Creates a full copy of the ERP spreadsheet in the backup folder.
* Trash old backups to keep the folder tidy.
* Called by the time-driven trigger; can also be run manually.
*/
function runDailyBackup() {
try {
const sourceId = SHEET_ID;
const now = new Date();
const dateStr = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd');
const isFirstOfMonth = now.getDate() === 1;
// 1. Ensure the backup folder exists
const rootFolder = getOrCreateFolder(BACKUP_FOLDER_NAME);
// 2. Create the daily backup
const sourceFile = DriveApp.getFileById(sourceId);
const dailyName = 'ERP_Backup_' + dateStr;
const dailyCopy = sourceFile.makeCopy(dailyName, rootFolder);
// 3. On the 1st, also save a monthly archive
if (isFirstOfMonth) {
const monthlyFolder = getOrCreateFolder(BACKUP_FOLDER_NAME + '/Monthly');
const monthStr = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM');
const monthlyName = 'ERP_Monthly_' + monthStr;
sourceFile.makeCopy(monthlyName, monthlyFolder);
}
// 4. Clean up old backups
pruneDailyBackups(rootFolder);
pruneMonthlyBackups();
// 5. Log success
const dailyCount = countBackups(rootFolder);
logAction('system', 'BACKUP_COMPLETE ' + dailyName + ' (total: ' + dailyCount + ')');
return { success: true, name: dailyName, total: dailyCount };
} catch (e) {
// Backup failures must be loud. Email the owner.
console.error('Backup failed: ', e);
try {
const owner = getOwnerEmail();
if (owner) {
GmailApp.sendEmail(
owner,
'⚠️ ERP Backup Failed — Action Required',
'The scheduled backup failed on ' + new Date() +
'\n\nError: ' + e.message +
'\n\nPlease open the Apps Script editor and run runDailyBackup() manually to diagnose.'
);
}
} catch (emailErr) { /* ignore */ }
throw e;
}
}
function getOrCreateFolder(path) {
const parts = path.split('/');
let folder = null;
for (let i = 0; i < parts.length; i++) {
const name = parts[i];
if (!folder) {
const it = DriveApp.getFoldersByName(name);
folder = it.hasNext() ? it.next() : DriveApp.createFolder(name);
} else {
const it = folder.getFoldersByName(name);
folder = it.hasNext() ? it.next() : folder.createFolder(name);
}
}
return folder;
}
function pruneDailyBackups(folder) {
const files = [];
const it = folder.getFiles();
while (it.hasNext()) {
const f = it.next();
if (f.getName().indexOf('ERP_Backup_') === 0) {
files.push({ file: f, date: f.getDateCreated() });
}
}
files.sort(function(a, b) { return b.date - a.date; });
for (let i = BACKUP_DAILY_LIMIT; i < files.length; i++) {
try { files[i].file.setTrashed(true); } catch (e) {}
}
}
function pruneMonthlyBackups() {
const folderIt = DriveApp.getFoldersByName(BACKUP_FOLDER_NAME + '/Monthly');
if (!folderIt.hasNext()) return;
const folder = folderIt.next();
const files = [];
const it = folder.getFiles();
while (it.hasNext()) {
const f = it.next();
if (f.getName().indexOf('ERP_Monthly_') === 0) {
files.push({ file: f, date: f.getDateCreated() });
}
}
files.sort(function(a, b) { return b.date - a.date; });
for (let i = BACKUP_MONTHLY_LIMIT; i < files.length; i++) {
try { files[i].file.setTrashed(true); } catch (e) {}
}
}
function countBackups(folder) {
let n = 0;
const it = folder.getFiles();
while (it.hasNext()) {
const f = it.next();
if (f.getName().indexOf('ERP_Backup_') === 0) n++;
}
return n;
}
/**
* Manual restore helper — copies the current data back from a backup file.
* This is intentionally a manual, careful operation.
*
* USAGE: open the backup file in Drive, copy its spreadsheet ID, then run:
* restoreFromBackup('1aBcD...')
*
* The function copies every sheet's values from the backup file into the
* live spreadsheet, OVERWRITING what's currently there.
*/
function restoreFromBackup(backupSpreadsheetId) {
if (!backupSpreadsheetId) throw new Error('Backup ID is required.');
if (backupSpreadsheetId === SHEET_ID) throw new Error('Cannot restore from the live spreadsheet itself.');
// SAFETY: create a fresh backup of the live data before overwriting.
runDailyBackup();
const backup = SpreadsheetApp.openById(backupSpreadsheetId);
const live = getSpreadsheet();
backup.getSheets().forEach(function(srcSheet) {
const name = srcSheet.getName();
const srcValues = srcSheet.getDataRange().getValues();
let destSheet = live.getSheetByName(name);
if (!destSheet) destSheet = live.insertSheet(name);
destSheet.clear();
if (srcValues.length > 0 && srcValues[0].length > 0) {
destSheet.getRange(1, 1, srcValues.length, srcValues[0].length).setValues(srcValues);
}
});
logAction('system', 'RESTORE_FROM_BACKUP ' + backupSpreadsheetId);
return { success: true };
}
⚠️
Test the restore once per month. A backup you have never restored is a backup you do not trust. Open the latest backup file, confirm the sheets look right, and (in a test environment) run
restoreFromBackup() to prove the mechanism works.
3
Multi-Branch Support
A shop opens a second branch. Then a third. Suddenly you have three cashiers ringing up sales in three places, and the owner wants per-branch numbers. Adding multi-branch support is a schema change — but a small one, if you plan for it now.
The Schema Change
Add a BranchId column to Sales, Purchases, Expenses, and StockLedger. Also create a new Branches sheet:
| Sheet | New Column | Purpose |
|---|---|---|
| Branches | (new sheet) | BranchId, Name, Address, Phone, ManagerEmail, IsActive |
| Sales | BranchId | Which branch made the sale |
| Purchases | BranchId | Which branch received goods |
| Expenses | BranchId | Which branch incurred the expense |
| StockLedger | BranchId | Which branch's stock this movement affects |
| Users | BranchId | Which branch a user primarily belongs to |
🧠
Optional: If your shop has only one location today and you do not plan to add more, you can skip this section. But if you ever might open a second branch, adding the column now — even with every row set to "MAIN" — saves painful migration later.
Branch-Aware Sale Creation
Here is how createSale changes to record the branch:
function createSale(token, saleData) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager', 'Cashier']);
// Determine the branch: either from the sale data (admin can override)
// or from the user's own BranchId in the Users sheet.
let branchId = saleData.branchId;
if (!branchId) {
branchId = getUserBranchId(session.email) || 'MAIN';
}
// ... all existing validation code ...
// Sales row now includes BranchId as the LAST column (column L).
appendRow('Sales', [
invoiceNo, now, customerId, subTotal, discount,
total, paid, due, session.email, paymentMethod,
trim(saleData.notes),
branchId // ← new column
]);
// StockLedger row also records which branch's stock changed.
ledgerRows.push([
now, it.productId, 'SALE', invoiceNo,
0, qty, runningBalance[it.productId],
'POS sale by ' + session.name,
branchId // ← new column
]);
// ...
}
function getUserBranchId(email) {
const users = readAll('Users');
for (let i = 0; i < users.length; i++) {
if (users[i].Email === email) return users[i].BranchId || 'MAIN';
}
return 'MAIN';
}
function getBranches(token) {
validateSession(token);
try {
return readAll('Branches').map(function(b) {
return {
id: b.BranchId,
name: b.Name,
address: b.Address,
phone: b.Phone,
managerEmail: b.ManagerEmail,
isActive: b.IsActive === true
};
});
} catch (e) {
// No Branches sheet yet — return a single default
return [{ id: 'MAIN', name: 'Main Branch', isActive: true }];
}
}
function createBranch(token, data) {
const session = validateSession(token);
requireRole(session, ['Admin']);
if (!trim(data.name)) throw new Error('Branch name is required.');
const id = 'BR-' + uuid().substring(0, 6).toUpperCase();
appendRow('Branches', [
id, trim(data.name), trim(data.address), trim(data.phone),
trim(data.managerEmail), true
]);
logAction(session.email, 'BRANCH_CREATED ' + id + ' ' + data.name);
return { success: true, id: id };
}
/**
* Branch-aware sales report.
* If branchId is null, returns totals for all branches (Admin only).
* If branchId is set, returns totals only for that branch.
*/
function getSalesByBranchReport(token, fromStr, toStr) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
const from = dayStart(new Date(fromStr));
const to = dayStart(addDays(new Date(toStr), 1));
const sales = readAll('Sales');
const branches = getBranches(token);
const branchMap = {};
branches.forEach(function(b) { branchMap[b.id] = b.name; });
const agg = {};
sales.forEach(function(s) {
const t = new Date(s.Date).getTime();
if (t < from || t >= to) return;
const bid = s.BranchId || 'MAIN';
if (!agg[bid]) agg[bid] = {
branchId: bid, branchName: branchMap[bid] || bid,
total: 0, count: 0, paid: 0, due: 0
};
agg[bid].total += Number(s.Total) || 0;
agg[bid].count += 1;
agg[bid].paid += Number(s.Paid) || 0;
agg[bid].due += Number(s.Due) || 0;
});
return Object.values(agg).sort(function(a, b) { return b.total - a.total; });
}Per-Branch Dashboard
When a manager from the Uttara branch logs in, they should see Uttara's numbers — not the whole chain. This is a small change in getDashboardSummary:
// Inside getDashboardSummary, after reading sales:
const session = validateSession(token);
let userBranch = 'ALL';
if (session.role === 'Manager' || session.role === 'Cashier') {
userBranch = getUserBranchId(session.email) || 'MAIN';
}
sales.forEach(function(s) {
if (userBranch !== 'ALL' && (s.BranchId || 'MAIN') !== userBranch) return;
// ... existing per-sale aggregation ...
});
4
AI Assistant — Ask Questions in Plain Language
This is the wow feature. Instead of opening a report and reading rows, the owner types "last month's top product" or "গত মাসে সবচেয়ে বেশি বিক্রি হয়েছে কোনটা?" and gets a direct answer.
How It Works
User types a question in plain language
│
▼
Frontend calls askAssistant(token, question)
│
▼
Server gathers a compact snapshot of shop data
· Today's sales, month sales
· Top 10 products
· Expense summary by category
· Top 10 customers by due
· Low stock list
· Last 30 days of daily totals
(All numbers — no raw rows)
│
▼
Server sends the question + snapshot to Gemini API
"Here is the data: { ... }. Answer this question: "
│
▼
Gemini returns a natural-language answer
│
▼
Server returns the answer to the frontend
│
▼
User sees a chat bubble with the answer
Setting Up Gemini API (Free Tier)
Get a free Gemini API key
Visit aistudio.google.com/app/apikey. Sign in with the shop's Google account. Click "Create API key".
Store the key in Script Properties
Apps Script → Project Settings → Script Properties → add GEMINI_API_KEY with the key you copied.
Understand the free quota
The free tier of Gemini 1.5 Flash allows 15 requests per minute and 1,500 requests per day. A small shop asking 5 questions per day uses 0.3% of the quota.
Enable it in code
We read the key inside askAssistant(). If the key is missing, the assistant gracefully returns "AI assistant is not configured."
/**
* Assistant.gs
* Natural-language assistant powered by Gemini 1.5 Flash.
*
* Data minimisation: we send AGGREGATES to Gemini, never raw rows.
* This keeps the payload tiny, fast, and privacy-conscious.
*/
const GEMINI_API_KEY = PropertiesService
.getScriptProperties().getProperty('GEMINI_API_KEY');
const GEMINI_ENDPOINT =
'https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent';
/**
* Answers a natural-language question about the shop.
* @param {string} token
* @param {string} question
* @returns {Object} { success, answer }
*/
function askAssistant(token, question) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
if (!GEMINI_API_KEY) {
return {
success: false,
answer: 'AI assistant is not configured. Add GEMINI_API_KEY in Script Properties.'
};
}
if (!trim(question)) {
throw new Error('Please type a question.');
}
// 1. Build the compact data snapshot
const snapshot = buildAssistantSnapshot(token);
// 2. Compose the prompt
const prompt =
'You are a helpful business assistant for a small shop in Bangladesh. ' +
'Answer questions using ONLY the data provided below. If the answer is ' +
'not in the data, say you do not have that information. Be concise. ' +
'Use ৳ for amounts and format numbers with commas. If the user writes in ' +
'Bengali, reply in Bengali. Otherwise reply in English.\n\n' +
'=== SHOP DATA ===\n' + JSON.stringify(snapshot, null, 2) +
'\n=== END DATA ===\n\n' +
'Question: ' + question;
// 3. Call Gemini
const answer = callGemini(prompt);
logAction(session.email, 'ASSISTANT_QUERY: ' + question.substring(0, 80));
return { success: true, answer: answer };
}
/**
* Builds a compact JSON snapshot of the shop's current state.
* This is what we send to Gemini — no raw rows, only aggregates.
*/
function buildAssistantSnapshot(token) {
const now = new Date();
const todayStart = dayStart(now);
const monthStart = monthStart(now);
const thirtyAgo = dayStart(addDays(now, -30));
const summary = getDashboardSummary(token);
const dues = getCustomersWithDues(token);
const lowStock = getLowStockProducts(token);
const monthExpenses = getExpenseSummary(
token, fmtDateShort(monthStart), fmtDateShort(now)
);
const profit = getProductProfitReport(
token, fmtDateShort(thirtyAgo), fmtDateShort(now)
);
const salesReport = getSalesReport(
token, fmtDateShort(thirtyAgo), fmtDateShort(now)
);
return {
generatedAt: now.toISOString(),
shop: {
name: COMPANY_NAME,
currency: 'BDT (৳)',
timezone: Session.getScriptTimeZone()
},
today: summary.today,
yesterday: summary.yesterday,
monthToDate: summary.month,
receivable: summary.totalReceivable,
lowStockCount: summary.lowStockCount,
topProducts30Days: profit.slice(0, 10).map(function(p) {
return {
name: p.name, qty: p.qty, unit: p.unit,
revenue: round2(p.revenue),
profit: round2(p.grossProfit)
};
}),
topCustomersByDue: dues.slice(0, 10),
lowStock: lowStock.slice(0, 10),
monthExpensesByCategory: monthExpenses.byCategory.map(function(c) {
return {
category: c.category,
amount: round2(c.amount),
percent: round2(c.percent)
};
}),
paymentMethods: salesReport.paymentMethods.map(function(m) {
return { method: m.method, total: round2(m.total), count: m.count };
}),
dailyTotals30Days: salesReport.dailyBreakdown.map(function(d) {
return { date: d.date, total: round2(d.total), invoices: d.count };
})
};
}
function round2(n) { return Math.round(Number(n || 0) * 100) / 100; }
function fmtDateShort(d) {
return Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}
/**
* Calls Gemini and returns the reply text.
* Includes basic retry and error handling.
*/
function callGemini(prompt) {
const payload = {
contents: [{
parts: [{ text: prompt }]
}],
generationConfig: {
temperature: 0.3,
maxOutputTokens: 500,
topP: 0.95
},
safetySettings: [
{ category: 'HARM_CATEGORY_DANGEROUS_CONTENT', threshold: 'BLOCK_ONLY_HIGH' }
]
};
const options = {
method: 'post',
contentType: 'application/json',
muteHttpExceptions: true,
payload: JSON.stringify(payload)
};
const url = GEMINI_ENDPOINT + '?key=' + GEMINI_API_KEY;
let response = UrlFetchApp.fetch(url, options);
let code = response.getResponseCode();
// Retry once on 5xx or 429
if (code === 429 || code >= 500) {
Utilities.sleep(2000);
response = UrlFetchApp.fetch(url, options);
code = response.getResponseCode();
}
const body = response.getContentText();
if (code !== 200) {
console.error('Gemini API error ' + code + ': ' + body);
return 'Sorry, I could not reach the assistant right now. Please try again in a moment.';
}
try {
const json = JSON.parse(body);
if (json.candidates && json.candidates[0]
&& json.candidates[0].content
&& json.candidates[0].content.parts
&& json.candidates[0].content.parts[0]) {
return json.candidates[0].content.parts[0].text;
}
return 'The assistant returned an empty response.';
} catch (e) {
console.error('Gemini JSON parse failed:', e);
return 'The assistant response could not be parsed.';
}
}Assistant UI
<!DOCTYPE html>
<html lang="bn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Assistant — <?= 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; }
.chat-shell {
max-width: 720px; margin: 0 auto;
background: #fff; border: 1px solid #e2e8f0;
border-radius: 16px; overflow: hidden;
box-shadow: 0 12px 30px -10px rgba(15,23,42,.18);
display: flex; flex-direction: column;
height: calc(100vh - 120px);
}
.chat-head {
background: linear-gradient(135deg,#6d28d9,#06b6d4);
padding: 14px 18px; color: #fff;
display: flex; align-items: center; gap: 12px;
}
.chat-avatar {
width: 40px; height: 40px; border-radius: 50%;
background: rgba(255,255,255,.22);
display: grid; place-items: center; font-size: 20px;
}
.chat-name { font-weight: 700; font-size: 15px; }
.chat-status { font-size: 12px; opacity: .85; }
.chat-body {
flex: 1; overflow-y: auto; padding: 18px;
background: #f8fafc;
display: flex; flex-direction: column; gap: 12px;
}
.bubble {
max-width: 82%; padding: 11px 14px; border-radius: 14px;
font-size: 14.5px; line-height: 1.55;
white-space: pre-wrap; word-wrap: break-word;
}
.bubble.user {
align-self: flex-end; background: #6d28d9; color: #fff;
border-bottom-right-radius: 4px;
}
.bubble.bot {
align-self: flex-start; background: #fff;
border: 1px solid #e2e8f0; color: #334155;
border-bottom-left-radius: 4px;
}
.bubble.bot strong{ color: #0f172a; }
.bubble.thinking {
align-self: flex-start;
color: #94a3b8; font-style: italic;
background: transparent; border: 0;
}
.chat-input {
display: flex; padding: 12px; gap: 10px;
border-top: 1px solid #e2e8f0; background: #fff;
}
.chat-input input {
flex: 1; padding: 11px 16px;
border: 1.5px solid #e2e8f0; border-radius: 999px;
font-family: inherit; font-size: 14.5px; background: #f8fafc;
}
.chat-input input:focus{ outline: none; border-color: #8b5cf6; }
.chat-send {
padding: 0 22px; border-radius: 999px; border: 0;
background: linear-gradient(135deg,#6d28d9,#06b6d4);
color: #fff; font-weight: 700; font-size: 14.5px;
box-shadow: 0 8px 20px -8px rgba(109,40,217,.6);
}
.chat-send:disabled{ opacity: .6; }
.chat-suggestions {
display: flex; flex-wrap: wrap; gap: 8px;
padding: 10px 14px 0; background: #fff;
border-top: 1px solid #f1f5f9;
}
.chat-suggestions button {
padding: 6px 12px; border-radius: 999px;
border: 1px solid #e2e8f0; background: #f8fafc;
font-family: inherit; font-size: 13px; color: #334155;
cursor: pointer; transition: all .2s ease;
}
.chat-suggestions button:hover { background: #ede9fe; color: #6d28d9; border-color: #ddd6fe; }
</style>
</head>
<body>
<nav class="navbar navbar-dark" style="background:linear-gradient(90deg,#6d28d9,#06b6d4);">
<div class="container-fluid">
<a href="?page=Dashboard" class="navbar-brand fw-bold mb-0 h6 text-white text-decoration-none">
← Back to Dashboard
</a>
<span id="userBadge" class="text-white"></span>
</div>
</nav>
<div class="container py-4">
<div class="chat-shell">
<div class="chat-head">
<div class="chat-avatar">🤖</div>
<div>
<div class="chat-name">Assistant</div>
<div class="chat-status">Ask anything about your shop · বাংলা বা English</div>
</div>
</div>
<div class="chat-body" id="chatBody">
<div class="bubble bot">
আসসালামু আলাইকুম! আমি আপনার দোকানের সহকারী।<br><br>
Try asking:
<br>· গত মাসে সবচেয়ে বেশি বিক্রি হয়েছে কোন প্রোডাক্ট?
<br>· What was my profit last month?
<br>· Who owes me the most money?
<br>· How much did I spend on rent this quarter?
</div>
</div>
<div class="chat-suggestions" id="suggestions">
<button type="button">Today's sales?</button>
<button type="button">Top 5 products this month</button>
<button type="button">Highest due customer</button>
<button type="button">Low stock items</button>
</div>
<div class="chat-input">
<input type="text" id="questionInput"
placeholder="Type your question..."
onkeydown="if(event.key==='Enter')sendQuestion()">
<button class="chat-send" id="sendBtn" onclick="sendQuestion()">Send</button>
</div>
</div>
</div>
<?!= include('JS') ?>
<script>
renderUserBadge('userBadge');
const body = document.getElementById('chatBody');
const input = document.getElementById('questionInput');
const sendBtn = document.getElementById('sendBtn');
function appendBubble(text, who) {
const div = document.createElement('div');
div.className = 'bubble ' + who;
div.innerHTML = text;
body.appendChild(div);
body.scrollTop = body.scrollHeight;
return div;
}
window.sendQuestion = function() {
const q = input.value.trim();
if (!q) return;
appendBubble(q.replace(/&/g,'&').replace(/</g,'<'), 'user');
input.value = '';
sendBtn.disabled = true;
const thinking = appendBubble('Thinking...', 'thinking');
apiCall('askAssistant', q)
.then(function(res) {
thinking.remove();
if (res.success) {
appendBubble(res.answer.replace(/\n/g, '<br>'), 'bot');
} else {
appendBubble(res.answer, 'bot');
}
sendBtn.disabled = false;
})
.catch(function(err) {
thinking.remove();
appendBubble('Error: ' + err.message, 'bot');
sendBtn.disabled = false;
});
};
document.getElementById('suggestions').querySelectorAll('button').forEach(function(b) {
b.addEventListener('click', function() {
input.value = b.textContent;
sendQuestion();
});
});
</script>
</body>
</html>
🔐
Data minimisation matters. We send only aggregates (totals, top 10s, categories) — never the raw customer list, never individual invoices. This is a privacy-first design and it is also faster. If your shop handles sensitive data, this is the pattern to follow: the AI reasons over summaries, not raw records.
5
WhatsApp & SMS Notifications
In Bangladesh, WhatsApp is how businesses and customers talk. Sending an invoice confirmation or due reminder via WhatsApp feels natural — and it costs almost nothing.
Three Delivery Channels
| Channel | Cost | Setup | Best For |
|---|---|---|---|
| WhatsApp Cloud API | Free tier (limited) | Meta Business account + phone number | Invoice confirmations, order updates |
| Email (Gmail) | Free (100/day) | Already working | PDF invoices, monthly reports |
| SMS (local gateway) | ৳0.30–0.60 per SMS | Local provider contract | OTP, urgent reminders |
WhatsApp Cloud API is the most popular choice. Here is how to add it — but note that Meta's onboarding process requires identity verification, so plan a day or two for approval.
/**
* WhatsApp.gs
* Sends WhatsApp messages via the free Meta Cloud API.
*
* SETUP:
* 1. Create a Meta Business account (business.facebook.com).
* 2. Register a phone number for WhatsApp Business.
* 3. In the Meta dashboard → WhatsApp → API Setup, get:
* · Phone Number ID
* · Permanent Access Token
* 4. Store them in Script Properties:
* WHATSAPP_PHONE_ID
* WHATSAPP_TOKEN
* 5. Verify customer opt-in (mandatory under Meta policy).
*/
const WHATSAPP_PHONE_ID = PropertiesService
.getScriptProperties().getProperty('WHATSAPP_PHONE_ID');
const WHATSAPP_TOKEN = PropertiesService
.getScriptProperties().getProperty('WHATSAPP_TOKEN');
/**
* Sends a plain-text WhatsApp message to a phone number.
* Bangladesh numbers must be in the format 8801XXXXXXXXX (no + sign).
* @param {string} toNumber — international format, e.g. '8801712345678'
* @param {string} text
* @returns {Object} { success, messageId } or { success: false, error }
*/
function sendWhatsAppText(toNumber, text) {
if (!WHATSAPP_PHONE_ID || !WHATSAPP_TOKEN) {
return { success: false, error: 'WhatsApp not configured.' };
}
if (!toNumber || !text) {
return { success: false, error: 'Number and text required.' };
}
// Normalise BD number format
let num = String(toNumber).replace(/[^\d]/g, '');
if (num.charAt(0) === '0' && num.length === 11) num = '880' + num.substring(1);
const url = 'https://graph.facebook.com/v20.0/' + WHATSAPP_PHONE_ID + '/messages';
const payload = {
messaging_product: 'whatsapp',
to: num,
type: 'text',
text: { body: text }
};
const options = {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + WHATSAPP_TOKEN },
muteHttpExceptions: true,
payload: JSON.stringify(payload)
};
const res = UrlFetchApp.fetch(url, options);
const code = res.getResponseCode();
const body = res.getContentText();
if (code === 200) {
try {
const j = JSON.parse(body);
return {
success: true,
messageId: j.messages && j.messages[0] ? j.messages[0].id : null
};
} catch (e) {
return { success: true };
}
}
console.error('WhatsApp API error ' + code + ': ' + body);
return { success: false, error: 'API error ' + code, details: body };
}
/**
* Sends an invoice confirmation via WhatsApp.
* Called after a sale if the customer has a mobile number and has opted in.
*/
function sendInvoiceWhatsApp(token, invoiceNo) {
const session = validateSession(token);
requireRole(session, ['Admin', 'Manager']);
const sale = getSaleByInvoice(token, invoiceNo);
if (!sale.customerId || sale.customerId === 'WALK-IN') {
throw new Error('Cannot WhatsApp a walk-in customer (no mobile).');
}
const customer = lookupCustomer(sale.customerId);
if (!customer.mobile) throw new Error('Customer has no mobile number.');
// Compose the message — keep it short and clear
let msg = '*' + COMPANY_NAME + '*\n\n'
+ 'আপনার অর্ডারের বিবরণ / Your order summary\n\n'
+ 'Invoice: ' + invoiceNo + '\n'
+ 'Date: ' + fmtDate(sale.date) + '\n\n';
sale.details.forEach(function(d) {
msg += '· ' + d.productName + ' × ' + d.qty
+ ' = ৳ ' + formatMoney(d.lineTotal) + '\n';
});
msg += '\n*Total: ৳ ' + formatMoney(sale.total) + '*\n';
if (sale.due > 0) {
msg += 'Due: ৳ ' + formatMoney(sale.due) + '\n';
}
msg += '\nধন্যবাদ! আবার আসবেন।';
const result = sendWhatsAppText(customer.mobile, msg);
logAction(session.email, 'WHATSAPP_INVOICE ' + invoiceNo +
(result.success ? ' SENT' : ' FAILED ' + result.error));
return result;
}
/**
* Sends due reminders to all customers with overdue balances.
* Run manually or schedule for the 5th of each month.
*/
function sendDueReminders() {
try {
const token = getSystemToken();
const dues = getCustomersWithDues(token);
let sent = 0, skipped = 0, failed = 0;
dues.forEach(function(c) {
if (c.due < 500) { skipped++; return; } // ignore small dues
if (!c.mobile) { skipped++; return; }
const msg = '*' + COMPANY_NAME + '*\n\n'
+ 'প্রিয় ' + c.name + ',\n\n'
+ 'আপনার বাকি পরিমাণ: *৳ ' + formatMoney(c.due) + '*\n\n'
+ 'অনুগ্রহ করে পরিশোধ করুন। ধন্যবাদ।\n'
+ 'Please settle your outstanding balance. Thank you.';
const r = sendWhatsAppText(c.mobile, msg);
if (r.success) sent++; else failed++;
Utilities.sleep(300); // avoid rate limits
});
logAction('system', 'DUE_REMINDERS sent=' + sent + ' skipped=' + skipped + ' failed=' + failed);
return { sent: sent, skipped: skipped, failed: failed };
} catch (e) {
console.error('sendDueReminders failed: ', e);
return { sent: 0, error: e.message };
}
}
📱
Opt-in is mandatory. Meta's WhatsApp Business Policy requires that customers consent to receive messages. Ask at the counter: "Can I send you WhatsApp updates about your bills?" and record a Yes/No in the Customers sheet. This protects both you and the customer.
6
Bulk Import from CSV
Onboarding a new shop means entering its entire product catalog. For a shop with 500 products, typing each one takes days. A CSV import takes seconds.
/**
* Import.gs
* Bulk-imports products from a CSV file.
*
* Expected CSV format (header row required):
* Name,Category,Unit,CostPrice,SalePrice,OpeningStock,ReorderLevel,Barcode,Notes
*/
function importProductsFromCsv(token, csvText) {
const session = validateSession(token);
requireRole(session, ['Admin']);
if (!csvText) throw new Error('CSV content is required.');
const rows = parseCsv(csvText);
if (rows.length < 2) throw new Error('CSV must have a header row and at least one data row.');
const header = rows[0].map(function(x) { return trim(x); });
const requiredCols = ['Name', 'SalePrice'];
requiredCols.forEach(function(c) {
if (header.indexOf(c) === -1) {
throw new Error('Missing required column: ' + c);
}
});
const colIdx = {};
header.forEach(function(h, i) { colIdx[h] = i; });
const lock = LockService.getScriptLock();
try {
lock.waitLock(60000); // 60 seconds for potentially large imports
const productSheet = sheet('Products');
const ledgerSheet = sheet('StockLedger');
const productRows = [];
const ledgerRows = [];
const errors = [];
const now = new Date();
for (let r = 1; r < rows.length; r++) {
try {
const row = rows[r];
const name = cellValue(row, colIdx['Name']);
const salePrice = Number(cellValue(row, colIdx['SalePrice']));
if (!name) throw new Error('Row ' + (r + 1) + ': Name is required.');
if (!salePrice || salePrice <= 0) throw new Error('Row ' + (r + 1) + ': SalePrice must be positive.');
const productId = 'P' + uuid().substring(0, 8).toUpperCase();
const openingStock = Number(cellValue(row, colIdx['OpeningStock'])) || 0;
productRows.push([
productId, name,
cellValue(row, colIdx['Category']),
cellValue(row, colIdx['Unit']) || 'pcs',
Number(cellValue(row, colIdx['CostPrice'])) || 0,
salePrice,
true,
Number(cellValue(row, colIdx['ReorderLevel'])) || 0,
now,
cellValue(row, colIdx['Barcode']),
cellValue(row, colIdx['Notes'])
]);
if (openingStock > 0) {
ledgerRows.push([
now, productId, 'OPENING', 'IMPORT',
openingStock, 0, openingStock, 'From bulk import'
]);
}
} catch (e) {
errors.push(e.message);
}
}
// Write in batches — fast
if (productRows.length > 0) {
appendRows(productSheet, productRows);
}
if (ledgerRows.length > 0) {
appendRows(ledgerSheet, ledgerRows);
}
logAction(session.email, 'BULK_IMPORT created=' + productRows.length + ' errors=' + errors.length);
return {
success: true,
created: productRows.length,
errors: errors,
totalRows: rows.length - 1
};
} finally {
lock.releaseLock();
}
}
function cellValue(row, idx) {
if (idx === undefined || idx === null) return '';
return trim(row[idx]);
}
/**
* Simple CSV parser handling quoted fields and embedded commas.
* Returns a 2D array.
*/
function parseCsv(text) {
const rows = [];
let row = [];
let cur = '';
let inQuotes = false;
for (let i = 0; i < text.length; i++) {
const ch = text[i];
const next = text[i + 1];
if (inQuotes) {
if (ch === '"' && next === '"') { cur += '"'; i++; }
else if (ch === '"') { inQuotes = false; }
else { cur += ch; }
} else {
if (ch === '"') { inQuotes = true; }
else if (ch === ',') { row.push(cur); cur = ''; }
else if (ch === '\n' || (ch === '\r' && next === '\n')) {
row.push(cur); cur = '';
if (row.length > 1 || row[0] !== '') rows.push(row);
row = [];
if (ch === '\r') i++;
}
else { cur += ch; }
}
}
if (cur.length > 0 || row.length > 0) {
row.push(cur);
rows.push(row);
}
return rows;
}Sample Import CSV
Name,Category,Unit,CostPrice,SalePrice,OpeningStock,ReorderLevel,Barcode,Notes
Rice 5kg,Grocery,bag,420,500,50,20,8901234567890,
Soybean Oil 2L,Grocery,pcs,290,350,40,15,8901234567891,
Sugar 1kg,Grocery,kg,110,130,60,25,8901234567892,
Lentils 1kg,Grocery,kg,130,160,30,20,8901234567893,
Flour 2kg,Grocery,bag,95,120,25,15,8901234567894,Frontend Import UI
<div class="container py-4">
<h4>Bulk Import Products</h4>
<p class="text-muted">Paste a CSV or upload a file. Header row is required.</p>
<div class="card mb-3">
<div class="card-body">
<input type="file" accept=".csv" id="csvFile" class="form-control mb-3">
<label class="form-label">Or paste CSV content:</label>
<textarea id="csvText" class="form-control" rows="8"
placeholder="Name,Category,Unit,CostPrice,SalePrice,OpeningStock,..."></textarea>
<button class="btn btn-primary mt-3" id="importBtn">Import Products</button>
</div>
</div>
<div id="importResult"></div>
</div>
<?!= include('JS') ?>
<script>
document.getElementById('csvFile').addEventListener('change', function(e) {
const file = e.target.files[0];
if (!file) return;
const reader = new FileReader();
reader.onload = function() {
document.getElementById('csvText').value = reader.result;
};
reader.readAsText(file);
});
document.getElementById('importBtn').addEventListener('click', function() {
const txt = document.getElementById('csvText').value;
if (!txt.trim()) { alert('Paste CSV content or choose a file.'); return; }
const btn = document.getElementById('importBtn');
btn.disabled = true;
btn.textContent = 'Importing...';
apiCall('importProductsFromCsv', txt)
.then(function(res) {
let html =
'<div class="alert alert-success">' +
'<strong>' + res.created + ' products created</strong> ' +
'from ' + res.totalRows + ' rows.</div>';
if (res.errors.length) {
html += '<div class="alert alert-warning"><strong>' +
res.errors.length + ' rows failed:</strong><ul>' +
res.errors.map(function(e) { return '<li>' + e + '</li>'; }).join('') +
'</ul></div>';
}
document.getElementById('importResult').innerHTML = html;
btn.disabled = false;
btn.textContent = 'Import Products';
})
.catch(function(err) {
alert('Import failed: ' + err.message);
btn.disabled = false;
btn.textContent = 'Import Products';
});
});
</script>
7
Migration Path to SQL Server
When does a shop outgrow Google Sheets? Roughly when:
- 10+ concurrent users saving at the same time
- Over 100,000 rows in a single sheet
- Over 100 companies on one account (unlikely in the new architecture)
- Complex reporting that reads across many sheets repeatedly
At that point, migrate. Here is the exact path — designed so that the frontend does not change.
CURRENT STATE
HTML/CSS/JS → Apps Script Web App → Google Sheets
STEP 1 — Export the data
· Every sheet exported as CSV
· Available immediately via Export.gs
STEP 2 — Build the SQL schema
· Columns map 1:1 with sheet columns
· Types: string → NVARCHAR, number → DECIMAL, date → DATETIME2
STEP 3 — Build the API
· ASP.NET Core Web API (or Node.js + Express)
· Same function names: login, createSale, getDashboardSummary
· Same response envelope: { success, data | error }
STEP 4 — Switch the frontend
· Change the API base URL in one place
· From google.script.run to fetch(url + '/login', ...)
· Everything else stays the same
FINAL STATE
HTML/CSS/JS → ASP.NET Core API → SQL Server
(or Node.js + Postgres)
SQL Schema — Ready to Use
-- ============================================================
-- FreeLearning365 ERP — SQL Server Schema
-- Maps 1:1 with the Google Sheets version from Parts 1–4.
-- ============================================================
CREATE TABLE Products (
ProductId NVARCHAR(32) PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Category NVARCHAR(80) NULL,
Unit NVARCHAR(20) NOT NULL DEFAULT 'pcs',
CostPrice DECIMAL(12,2) NOT NULL DEFAULT 0,
SalePrice DECIMAL(12,2) NOT NULL,
IsActive BIT NOT NULL DEFAULT 1,
ReorderLevel INT NOT NULL DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
Barcode NVARCHAR(64) NULL,
Notes NVARCHAR(500) NULL,
BranchId NVARCHAR(20) NOT NULL DEFAULT 'MAIN'
);
CREATE INDEX IX_Products_Barcode ON Products(Barcode);
CREATE INDEX IX_Products_Active ON Products(IsActive);
CREATE TABLE Sales (
InvoiceNo NVARCHAR(24) PRIMARY KEY,
Date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
CustomerId NVARCHAR(32) NOT NULL,
SubTotal DECIMAL(12,2) NOT NULL,
Discount DECIMAL(12,2) NOT NULL DEFAULT 0,
Total DECIMAL(12,2) NOT NULL,
Paid DECIMAL(12,2) NOT NULL DEFAULT 0,
Due DECIMAL(12,2) NOT NULL DEFAULT 0,
UserId NVARCHAR(200) NOT NULL,
PaymentMethod NVARCHAR(40) NOT NULL DEFAULT 'Cash',
Notes NVARCHAR(500) NULL,
BranchId NVARCHAR(20) NOT NULL DEFAULT 'MAIN'
);
CREATE INDEX IX_Sales_Date ON Sales(Date);
CREATE INDEX IX_Sales_Customer ON Sales(CustomerId);
CREATE TABLE SaleDetails (
Id BIGINT IDENTITY PRIMARY KEY,
InvoiceNo NVARCHAR(24) NOT NULL REFERENCES Sales(InvoiceNo),
ProductId NVARCHAR(32) NOT NULL REFERENCES Products(ProductId),
Qty DECIMAL(12,3) NOT NULL,
UnitPrice DECIMAL(12,2) NOT NULL,
LineTotal DECIMAL(12,2) NOT NULL,
Discount DECIMAL(12,2) NOT NULL DEFAULT 0
);
CREATE INDEX IX_SaleDetails_Invoice ON SaleDetails(InvoiceNo);
CREATE INDEX IX_SaleDetails_Product ON SaleDetails(ProductId);
CREATE TABLE StockLedger (
Id BIGINT IDENTITY PRIMARY KEY,
Date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
ProductId NVARCHAR(32) NOT NULL,
Type NVARCHAR(20) NOT NULL,
RefNo NVARCHAR(40) NOT NULL,
QtyIn DECIMAL(12,3) NOT NULL DEFAULT 0,
QtyOut DECIMAL(12,3) NOT NULL DEFAULT 0,
Balance DECIMAL(12,3) NOT NULL,
Note NVARCHAR(500) NULL,
BranchId NVARCHAR(20) NOT NULL DEFAULT 'MAIN'
);
CREATE INDEX IX_StockLedger_Product ON StockLedger(ProductId, Date);
CREATE TABLE Customers (
CustomerId NVARCHAR(32) PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Mobile NVARCHAR(20) NULL,
Address NVARCHAR(500) NULL,
OpeningBalance DECIMAL(12,2) NOT NULL DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
WhatsAppOptIn BIT NOT NULL DEFAULT 0
);
CREATE TABLE CustomerPayments (
PaymentId NVARCHAR(32) PRIMARY KEY,
Date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
CustomerId NVARCHAR(32) NOT NULL REFERENCES Customers(CustomerId),
Amount DECIMAL(12,2) NOT NULL,
Method NVARCHAR(40) NOT NULL,
Note NVARCHAR(500) NULL,
UserId NVARCHAR(200) NOT NULL
);
CREATE TABLE Suppliers (
SupplierId NVARCHAR(32) PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Mobile NVARCHAR(20) NULL,
Address NVARCHAR(500) NULL,
OpeningBalance DECIMAL(12,2) NOT NULL DEFAULT 0,
CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE TABLE Purchases (
GRNNo NVARCHAR(24) PRIMARY KEY,
Date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
SupplierId NVARCHAR(32) NOT NULL REFERENCES Suppliers(SupplierId),
Total DECIMAL(12,2) NOT NULL,
Paid DECIMAL(12,2) NOT NULL DEFAULT 0,
Due DECIMAL(12,2) NOT NULL DEFAULT 0,
UserId NVARCHAR(200) NOT NULL,
Notes NVARCHAR(500) NULL,
BranchId NVARCHAR(20) NOT NULL DEFAULT 'MAIN'
);
CREATE TABLE PurchaseDetails (
Id BIGINT IDENTITY PRIMARY KEY,
GRNNo NVARCHAR(24) NOT NULL REFERENCES Purchases(GRNNo),
ProductId NVARCHAR(32) NOT NULL REFERENCES Products(ProductId),
Qty DECIMAL(12,3) NOT NULL,
UnitCost DECIMAL(12,2) NOT NULL,
LineTotal DECIMAL(12,2) NOT NULL
);
CREATE TABLE Expenses (
Id BIGINT IDENTITY PRIMARY KEY,
Date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
Category NVARCHAR(60) NOT NULL,
Amount DECIMAL(12,2) NOT NULL,
Note NVARCHAR(500) NULL,
UserId NVARCHAR(200) NOT NULL,
Method NVARCHAR(40) NOT NULL DEFAULT 'Cash',
BranchId NVARCHAR(20) NOT NULL DEFAULT 'MAIN'
);
CREATE INDEX IX_Expenses_Date ON Expenses(Date);
CREATE TABLE Returns (
ReturnNo NVARCHAR(24) PRIMARY KEY,
Date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
OriginalInvoice NVARCHAR(24) NOT NULL REFERENCES Sales(InvoiceNo),
CustomerId NVARCHAR(32) NOT NULL,
Total DECIMAL(12,2) NOT NULL,
RefundMethod NVARCHAR(40) NOT NULL DEFAULT 'Cash',
Note NVARCHAR(500) NULL,
UserId NVARCHAR(200) NOT NULL
);
CREATE TABLE ReturnDetails (
Id BIGINT IDENTITY PRIMARY KEY,
ReturnNo NVARCHAR(24) NOT NULL REFERENCES Returns(ReturnNo),
ProductId NVARCHAR(32) NOT NULL REFERENCES Products(ProductId),
Qty DECIMAL(12,3) NOT NULL,
UnitPrice DECIMAL(12,2) NOT NULL,
LineTotal DECIMAL(12,2) NOT NULL,
Reason NVARCHAR(500) NULL
);
CREATE TABLE Users (
Email NVARCHAR(200) PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Role NVARCHAR(20) NOT NULL,
PasswordHash NVARCHAR(200) NOT NULL,
IsActive BIT NOT NULL DEFAULT 1,
CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
LastLogin DATETIME2 NULL,
BranchId NVARCHAR(20) NOT NULL DEFAULT 'MAIN'
);
CREATE TABLE Branches (
BranchId NVARCHAR(20) PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Address NVARCHAR(500) NULL,
Phone NVARCHAR(20) NULL,
ManagerEmail NVARCHAR(200) NULL,
IsActive BIT NOT NULL DEFAULT 1
);
CREATE TABLE Settings (
[Key] NVARCHAR(80) PRIMARY KEY,
Value NVARCHAR(MAX) NULL,
Description NVARCHAR(500) NULL
);
CREATE TABLE AuditLog (
Id BIGINT IDENTITY PRIMARY KEY,
Timestamp DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
Email NVARCHAR(200) NOT NULL,
Action NVARCHAR(200) NOT NULL,
CompanyId NVARCHAR(40) NULL
);
CREATE INDEX IX_AuditLog_Timestamp ON AuditLog(Timestamp);
-- ============================================================
-- Sample: cashier performance view (equivalent to Part 4 report)
-- ============================================================
CREATE VIEW vw_CashierPerformance AS
SELECT
UserId,
CAST(Date AS DATE) AS SaleDate,
COUNT(*) AS InvoiceCount,
SUM(Total) AS TotalSales,
SUM(Paid) AS TotalPaid,
SUM(Due) AS TotalDue
FROM Sales
GROUP BY UserId, CAST(Date AS DATE);
-- ============================================================
-- Sample: top products view
-- ============================================================
CREATE VIEW vw_TopProducts AS
SELECT
p.ProductId, p.Name, p.Unit,
SUM(d.Qty) AS QtySold,
SUM(d.LineTotal) AS Revenue,
SUM(d.Qty * p.CostPrice) AS COGS,
SUM(d.LineTotal) - SUM(d.Qty * p.CostPrice) AS GrossProfit
FROM SaleDetails d
INNER JOIN Products p ON p.ProductId = d.ProductId
INNER JOIN Sales s ON s.InvoiceNo = d.InvoiceNo
GROUP BY p.ProductId, p.Name, p.Unit;ASP.NET Core Sample Controller
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
[ApiController]
[Route("api/[controller]")]
public class SalesController : ControllerBase {
private readonly ErpDbContext _db;
private readonly ISessionService _session;
public SalesController(ErpDbContext db, ISessionService session) {
_db = db; _session = session;
}
[HttpPost("create")]
public async Task<IActionResult> CreateSale(
[FromHeader(Name = "X-Session-Token")] string token,
[FromBody] SaleRequest req) {
// 1. Same session validation as Apps Script
var session = await _session.ValidateAsync(token);
if (session == null) return Unauthorized(
new { success = false, error = "Session expired" });
// 2. Same role check
if (session.Role != "Admin" && session.Role != "Manager" && session.Role != "Cashier")
return Forbid();
// 3. Same recomputation of totals — never trust the client
decimal subTotal = req.Items.Sum(i => i.Qty * i.UnitPrice);
var total = subTotal - req.Discount;
var due = total - req.Paid;
// 4. Same invoice numbering — inside a transaction
using var tx = await _db.Database.BeginTransactionAsync();
var invoiceNo = await GenerateNextInvoiceNoAsync();
var sale = new Sale {
InvoiceNo = invoiceNo,
Date = DateTime.UtcNow,
CustomerId = req.CustomerId ?? "WALK-IN",
SubTotal = subTotal,
Discount = req.Discount,
Total = total,
Paid = req.Paid,
Due = due,
UserId = session.Email,
PaymentMethod = req.PaymentMethod ?? "Cash",
BranchId = session.BranchId
};
_db.Sales.Add(sale);
// 5. Same detail rows + ledger entries
foreach (var item in req.Items) {
_db.SaleDetails.Add(new SaleDetail {
InvoiceNo = invoiceNo, ProductId = item.ProductId,
Qty = item.Qty, UnitPrice = item.UnitPrice,
LineTotal = item.Qty * item.UnitPrice
});
var newBalance = await GetStockLevelAsync(item.ProductId) - item.Qty;
_db.StockLedger.Add(new StockLedgerEntry {
ProductId = item.ProductId, Type = "SALE",
RefNo = invoiceNo, QtyOut = item.Qty,
Balance = newBalance, BranchId = session.BranchId
});
}
await _db.SaveChangesAsync();
await tx.CommitAsync();
// 6. Same response shape — the frontend doesn't change
return Ok(new {
success = true,
data = new {
invoiceNo, subTotal, discount = req.Discount,
total, paid = req.Paid, due
}
});
}
private async Task<string> GenerateNextInvoiceNoAsync() {
var year = DateTime.UtcNow.Year;
var last = await _db.Sales
.OrderByDescending(s => s.InvoiceNo)
.Select(s => s.InvoiceNo)
.FirstOrDefaultAsync();
var num = last == null ? 1 : int.Parse(last.Split('-')[2]) + 1;
return $"INV-{year}-{num:D5}";
}
private async Task<decimal> GetStockLevelAsync(string productId) {
var entry = await _db.StockLedger
.Where(l => l.ProductId == productId)
.OrderByDescending(l => l.Id)
.FirstOrDefaultAsync();
return entry?.Balance ?? 0;
}
}Frontend Switch — The One-Line Change
// BEFORE (Google Sheets)
function apiCall(funcName) {
const args = Array.prototype.slice.call(arguments, 1);
return new Promise(function(resolve, reject) {
google.script.run
.withSuccessHandler(function(res) {
if (res && res.success) resolve(res.data);
else reject(new Error(res.error));
})
.withFailureHandler(reject)
[funcName].apply(null, [TOKEN].concat(args));
});
}
// AFTER (ASP.NET Core API or Node.js)
const API_BASE = 'https://api.myshop.com'; // ← the only change
async function apiCall(funcName, ...args) {
const res = await fetch(API_BASE + '/api/' + funcName, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'X-Session-Token': TOKEN
},
body: JSON.stringify(args)
});
const json = await res.json();
if (json.success) return json.data;
throw new Error(json.error);
}
🎯
The whole point of the architecture. Every business function — login, createSale, getDashboardSummary, generateInvoicePdf — keeps its name, its signature, and its response shape. The frontend doesn't know or care whether the data lives in Google Sheets or SQL Server. That is what "clean architecture" means in practice.
8
Security Review & Hardening
Before going live with real customer data, walk through this checklist. Each item is small. Together they turn a hobby project into a production system.
| Area | Check | Why It Matters |
|---|---|---|
| Secrets | ENCRYPTION_KEY is long, random, and stored in a password manager | Weak keys break password hashing |
| Secrets | GEMINI_API_KEY, WHATSAPP_TOKEN in Script Properties, not code | Never commit secrets to version control |
| Sessions | Tokens expire after 6 hours and are refreshed on activity | Limits exposure if a device is stolen |
| Sessions | Logout removes the token from CacheService | Prevents stale sessions from lingering |
| Passwords | Minimum 8 characters, mixed case, digit | Weak passwords are the #1 attack vector |
| Passwords | Login attempts limited to 5 per email per 15 minutes | Stops brute-force attacks |
| Roles | Every sensitive function starts with validateSession + requireRole | Never trust the frontend for authorisation |
| Input | All string inputs are trim()'d and length-limited | Prevents accidental sheet corruption |
| Input | Numeric inputs are coerced with Number() and range-checked | Prevents negative prices or huge quantities |
| Concurrency | Every write operation is inside a LockService lock | Prevents duplicate invoice numbers and lost writes |
| Audit | Every privileged action is logged to AuditLog | Enables post-incident forensics |
| Audit | AuditLog is reviewed weekly by the owner | An unread log is not a security control |
| Backups | Daily backup runs and succeeds (see log) | Ransomware and accidental deletion protection |
| Backups | Restore drill performed once per quarter | A backup you haven't restored is untested |
| Access | Employees who leave are disabled (not deleted) | Preserves history, removes access |
| Access | Shop owner uses 2FA on their Google account | Protects the root of everything |
| Data | Customers are informed their data is stored | Ethical + legal requirement |
| Data | Personal mobile numbers are not shared with third parties | Protects customer privacy |
| Web | Only HTTPS URLs are used | Google enforces this, but double-check Drive links |
| Web | Session tokens are never put in URLs | URLs are logged by proxies and browsers |
🔐
The single biggest risk is not technical. It is the shop owner sharing their password with an employee "just for today". Or writing it on a sticky note. Or using the same password on Gmail and a shopping website. Every conversation about the ERP should start with: "নিজের পাসওয়ার্ড কারো সাথে শেয়ার করবেন না।"
9
The 20-Point Go-Live Checklist
This is the moment. You are about to hand the ERP to a real shop. Follow this list. Every point has been learned from a real deployment.
Data Preparation (Before Day 1)
- Products imported via CSV — every product has a name, unit, and sale price.
- Opening stock entered for every product that physically exists on the shelf.
- Physical stock count matches the ERP's stock count (do a manual reconciliation).
- Suppliers entered (at least the top 5).
- Customers with existing dues entered, with their opening balance.
Users and Access
- Owner account created with a strong password (12+ chars).
- Every cashier has their own account — no shared logins.
- Roles assigned correctly (Admin / Manager / Cashier).
- The Web App URL is bookmarked on every device that will use it.
- "Add to Home Screen" set up on all staff phones.
Infrastructure
- Daily backup trigger is installed and verified (ran once successfully).
- Daily email report trigger is installed and verified.
- Thermal printer (or A4 printer) tested with a real receipt.
- PDF invoice generated and opened on the owner's phone.
- Low-stock alerts tested with a deliberately low product.
Training
- Cashiers trained on the POS flow (scan, add, discount, save, print).
- Cashiers shown how to handle returns and voids.
- Owner trained on running the P&L report and reading the AI assistant.
- Written cheat-sheet pinned near the counter (one page, Bangla + English).
Launch Day
- Parallel run: ERP open alongside the old method for the first three days.
- Owner checks dashboard + AuditLog at end of Day 1, Day 2, Day 3.
- After Day 3, if numbers match, retire the old method.
🚀
The parallel run is non-negotiable. For the first three days, the shop uses both the ERP and its old method (usually a paper khata or an Excel file). At the end of Day 3, the owner compares: do the totals match? Does the stock match? If yes, retire the old method. If no, find the discrepancy. No shop has ever regretted the parallel run.
10
Series Recap — What You've Built
Take a moment. Look at what you have created across five Parts.
01
Foundation — Database & Architecture
You designed a multi-tenant schema with clean separation between master and company data, learned the Apps Script V8 runtime, set up Script Properties, and wrote your first server-side helpers.
02
Authentication & Deployment
HMAC-SHA256 password hashing, 6-hour session tokens in CacheService, three roles (Admin / Manager / Cashier), bilingual Bangla/English login screen, Web App deployment, and audit logging on every privileged action.
03
POS, Inventory & Purchases
Full point-of-sale screen with barcode scanning, a StockLedger that never lies, Purchases (GRN) with auto cost updates, customer dues with printable statements, and thermal-printer-ready receipts in Bengali + English.
04
Reports, Invoices & P&L
12 reports (daily, weekly, monthly, by product, by cashier, by payment method), PDF invoices from Google Docs templates, Returns workflow, expense tracking, auto-computed P&L, live charts, and scheduled email reports at 10 PM.
05
Exports, Backups, AI & Go-Live
CSV/Excel exports, automated nightly backups, multi-branch support, AI assistant with Gemini, WhatsApp integration, bulk import, the SQL migration path, and the 20-point go-live checklist.
What You've Actually Achieved
You did not just write 5,000 lines of code. You learned a way of thinking — the way real software is designed. You learned how to model a business, how to protect its data, how to build features the shop owner never knew they needed, and how to ship them safely into a live environment.
This is the same discipline that runs enterprise ERPs at companies in Dhaka, Singapore, and London. The only difference is that yours costs ৳0 in infrastructure.
~5,000Lines of code
12+Working modules
20+Real reports
৳0Infrastructure cost
11
What Would You Do Differently?
A good engineer always reviews. Here are the honest trade-offs we made in this series, and when you would choose differently.
| We chose… | Because… | If you had… |
|---|---|---|
| Google Sheets as database | Zero cost, instant setup | 10+ concurrent cashiers → use SQL Server from day 1 |
| CacheService for sessions | Fast, auto-expiring, 6-hour cap | Enterprise SSO needs → use Firebase Auth or Auth0 |
| HMAC-SHA256 for passwords | Simple, secure enough, works in Apps Script | Regulated industry → use bcrypt/Argon2 on a proper server |
| Append-only ledger | Full audit trail, reconciles physical stock | High-frequency trading → use an event store (Kafka) |
| Server-side recomputation | Never trust the client | That's always right. Never change this. |
| One account per company | Absolute data isolation | 1000+ companies → use a proper multi-tenant SQL design |
| AI over aggregates only | Privacy, speed, small payloads | Deep analytics → send sanitised row-level data with consent |
| Google Docs template for PDFs | Free, editable by non-developers | Complex layouts → use a proper PDF library on a server |
🧠
Notice the pattern. Every choice in this series had a reason — and a limit. Good engineers know both. If you find yourself disagreeing with a choice, ask: "At what scale does the other choice become correct?" If you can answer that, you understand the trade-off.
12
Final Scenarios from Real Shops
These are the stories that stick. Each one is a moment when a shop changed something because the ERP told them something they could not have known otherwise.
🛒
Karim's Grocery — Discovering a Location Problem
Mirpur, Dhaka · first month after go-live
Karim runs the monthly P&L for the first time. Something is off — margins are lower than he expected. He drills into the top-product report.
Discovery: Rice has a 16% margin, flour has 21%, but rice is his #1 product by revenue. He assumed rice was his most profitable item. It wasn't.
Action: He shifts his promotional energy to flour and lentils — the actual high-margin items — as bundles. He raises the price of rice by ৳10 per bag (barely noticeable to customers, but 2% more margin). Monthly profit rises 11%.
💊
Rahman Pharmacy — Catching an Employee Problem Early
Dhanmondi, Dhaka · third month after go-live
Rahman runs the cashier report. One cashier's average invoice value is significantly lower than the others — ৳320 vs ৳480. Discounts are also higher on her shift.
Discovery: Not theft. She was unsure about a few product prices and gave a "safety discount" so customers wouldn't complain. Basic training gap.
Action: Two hours of price and product training. Within two weeks, her metrics match the others. That's ৳12,000+ of monthly revenue recovered through better training — and the cashier kept her job because the ERP let Rahman see a training issue, not a discipline issue.
📱
Nasrin Electronics — Automating Installment Reminders
Chattogram · sixth month after go-live
Nasrin has 34 customers on installment plans. Collecting monthly installments is a manual nightmare. She sets up the WhatsApp due-reminder script from Part 5.
Result: On the 5th of each month, every customer with an outstanding installment receives a polite WhatsApp message with their balance. Collection rate rises from 72% to 94%. Nasrin saves 4 hours per week of phone calls.
🍽️
Rahim Restaurant — Planning a Second Location
Uttara, Dhaka · ninth month after go-live
Rahim is considering opening a second restaurant. He has eight months of clean data. He runs the reports for the last three months and notices a clear pattern: his net margin holds steady at 14–16%, even on slow days, because his expenses are lean.
Decision: With 8 months of proven P&L in PDF form, he secures a small business loan from a local bank. The second location opens six months later, and the multi-branch support from Part 5 lets him see both locations' numbers in one dashboard from day one.
👗
Fatema Boutique — The AI Assistant Saves a Sunday
Sylhet · third month after go-live
Fatema is preparing for a wholesale order and wants to know: "Which sarees sold fastest last quarter?" She doesn't want to open a report. She types the question into the AI assistant on her phone while walking to her car.
Answer in 4 seconds: "Kameez-Lotus-Red and Kameez-Lotus-Blue sold 42 and 38 units respectively — 2.4× your average. Silk Saree-A12 sold only 6 units (3 were returned)."
What she does next: She orders 100 more of the Lotus design and drops Silk Saree-A12 from her catalog. That's the assistant doing the job of a business analyst in one sentence.
🇧🇩
And Then — The First Time You Get the Daily Email
Every shop · the evening of Day 1
The shop closes. The owner is at home. At 10:00 PM, an email arrives. It is not from you. It is from their ERP — the one you built. It says:
Daily Report — Demo Store
Sales: ৳ 38,400 | Gross Profit: ৳ 8,720 | Expenses: ৳ 3,200
Net Profit: ৳ 5,520 (▲ 12% vs yesterday)
142 invoices · Discount ৳1,240 · New due ৳850
⚠️ 3 products below reorder level
The owner reads it. The owner knows how their day went — better than they would have known five minutes earlier. The business has become a little more transparent, a little more rational, a little more likely to survive.
That moment — that 10 PM email landing in an inbox — is what this whole series was for.
Q
Final Quiz — 12 Questions
A comprehensive quiz covering all five parts. Take your time. Score 10/12 or better and you are ready to build the next ERP.
Series Final Quiz
Covering Parts 1 through 5.
Score: 0 / 0
?
Frequently Asked Questions
Do I really need backups if Google already keeps version history?
Yes, for two reasons. First, Google Drive's version history has limits (30 days or 100 versions, depending on file type). Second, if the sheet is deleted entirely — by accident, or by a compromised account — version history does not help. A separate backup copy in a distinct Drive location protects against both. Backups are cheap; recovery is priceless.
Will Gemini see my customer data?
We send only aggregates — totals, top-10 lists, category breakdowns. Never raw rows, never individual customer names or phone numbers unless they are top-of-list by due (and even then, only the name and amount, which is what makes the answer useful). This is a deliberate privacy-first design. If you want tighter control, you can hide customer names from the assistant payload entirely.
When should I actually migrate to SQL Server?
When any of these become true: 10+ concurrent users saving at the same moment; a single sheet exceeds 100,000 rows; complex reports take more than 30 seconds; you want to sell the ERP as a service to other shops. Until then, stay on Sheets. Premature migration is expensive and risky.
Do I need WhatsApp Business API, or can I just send SMS?
Depends on your customers. WhatsApp is more popular in Bangladesh for personal communication. SMS is more reliable for older customers and lower-spec phones. Many shops use both: WhatsApp for regular customers, SMS for critical alerts. The code pattern is identical — just a different API endpoint.
Can I sell this ERP to other shops as a product?
Yes. The architecture was designed for it. Each shop gets its own Google account, its own Sheets, and its own Web App URL. You can charge a setup fee and/or a monthly support fee. Just remember to check Google's own terms — the free tier is fine for many shops, but heavy commercial use may eventually push you toward Google Workspace.
What is the biggest mistake beginners make with this system?
Two mistakes. First: skipping the ledger and using a single "Stock" column — that path leads to stock that never reconciles. Second: trusting the frontend. Always recompute totals, always re-check roles on the server. If you take those two lessons seriously, you will avoid 90% of ERP bugs.
How do I know if my ERP is "production ready"?
Walk the 20-point go-live checklist. If all 20 are checked, you are ready. If any are skipped, fix them before going live. The parallel run for three days is the final gate.
What should I build after this series?
Three paths. Deepen: master Gemini, add Firebase push notifications, or build the customer-facing order app. Broaden: add payroll, or a supplier portal, or a delivery tracking module. Migrate: take one shop's data, move it to SQL Server, and prove the migration on a real business. All three are valuable. Pick the one that excites you.
→
What Comes After This Series
The five-part series is complete, but the learning doesn't stop. Here are the natural next steps, in roughly the order they pay off.
- Firebase Push Notifications — send real-time alerts to staff phones when low stock or large dues occur.
- Customer-Facing Order App — a public page where customers place orders, which appear as pending sales in the ERP.
- Supplier Portal — suppliers log in and see their own outstanding POs and payment history.
- Delivery Tracking — for shops that deliver (pharmacy, restaurant), track order status: pending → preparing → out for delivery → delivered.
- Payroll Module — attendance, salary calculation, tax deductions, and pay slips.
- Advanced Analytics — cohort analysis, customer lifetime value, seasonality detection.
- SQL Server Migration — for shops that outgrow Sheets, or for you to learn the ASP.NET Core + SQL Server stack properly.
- SaaS Productization — package the whole thing as a subscription service, with a landing page, signup flow, and per-shop branding.
📖
More Free Resources on FreeLearning365
Continue your learning 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
){kind=link}
0 Comments
thanks for your comments!