Google App Script Reports · PDF Invoices · Returns · Expenses · P&L · Charts 100% Free Stack | FreeLearning365.com — Part 4 of 5

September 10, 2026
Google App Script Reports · PDF Invoices · Returns · Expenses · P&L · Charts 100% Free Stack | FreeLearning365.com — Part 4 of 5
FREELEARNING365.COM
🚀 Complete Free ERP — 5 Part Series
Google Sheets + Apps Script · POS · Inventory · Reports · AI
Follow the complete journey: Foundation → Security → POS → Intelligence → Scale
Part 4 of 5 · Reports · Invoices · P&L

Reports, PDF Invoices, Returns & P&L for Your Bangladeshi Shop

Your POS now records every sale, purchase and payment. Part 4 turns that raw data into decisions: today's profit, top-selling products, cashier performance, printable A4 invoices, expense tracking, and a live dashboard the owner checks each night. All 100% free.

12+ Reports PDF Invoices free Auto Emails at 10 PM
★

The Difference Between Data and Insight

It's 10 PM. Karim closes his shop in Mirpur and opens his phone. He does not want to scroll through 142 sales rows. He wants answers to three questions:

  1. Did I make a profit today? Not "revenue" — actual profit after cost of goods and expenses.
  2. Which products sold best? So he knows what to reorder tomorrow.
  3. Who owes me money? So he can plan his evening calls to customers with big dues.

Part 4 delivers those answers. Every second you spent building the ledger in Parts 1–3 pays off here, because reports are simply well-organised reads of a well-organised ledger.

Today's Revenue
৳ 38,400
▲ 12% vs yesterday
Today's Profit
৳ 8,720
▲ 18% vs yesterday
Top Product
Rice 5kg
48 bags · ৳4,800
Receivable
৳ 12,450
34 customers
📊
What you'll build in Part 4: 12 reports (daily, weekly, monthly, by product, by cashier, by payment method), professional PDF invoices from Google Docs templates, proper Returns workflow with its own ledger, expense tracking with categories, an auto-computed P&L statement, a live dashboard with charts, and scheduled email reports that hit the owner's inbox at 10 PM — for free.
01

What You Will Build in Part 4

12 Core Reports

Daily, weekly, monthly, by product, by cashier, by payment method — with ৳ totals and comparison to previous period.

PDF Invoices

Google Docs template → filled by Apps Script → exported to PDF → saved to Drive. Bilingual.

Returns Workflow

Dedicated Returns sheet + ledger entries + optional customer due adjustments.

Expense Tracking

Rent, salary, electricity, transport — categories with monthly rollups.

P&L Statement

Profit & Loss auto-generated for any month. Revenue − COGS − Expenses.

Scheduled Emails

Daily 10 PM summary + Monthly 1st-of-month P&L, delivered via Gmail triggers.

1

Report Design Principles

Before writing reports, understand three rules. Break any of them and your reports will be slow, wrong, or unreadable.

Rule 1: Read Once, Compute Many

Apps Script charges a fixed cost per getValues() call regardless of how many rows you read. A report that reads the Sales sheet once and computes 10 metrics in memory is 10 times faster than a report that reads it 10 times.

❌ Slow pattern ✅ Fast pattern ───────────────────── ───────────────────── Read Sales (getDaily) Read Sales once → compute today sales → compute today sales Read Sales (getWeekly) → compute weekly sales → compute weekly sales → compute monthly sales Read Sales (getMonthly) → compute by cashier → compute monthly → compute by product 10 sheet calls 1 sheet call 10× slower 1× cost

Rule 2: Compute on the Server, Render on the Client

Send numbers and dates to the browser — not raw rows. A 5,000-row Sales sheet would take 2+ seconds to serialise and transmit. A pre-aggregated response with 12 numbers takes 30ms.

Rule 3: Always Compare Two Periods

A number alone tells nothing. "Today's sales: ৳38,400" is meaningless. "Today's sales: ৳38,400 (▲ 12% vs yesterday)" tells a story. Every report we build will return the current period and the equivalent previous period — so the UI can show deltas.

🎯
Bangladeshi shop calendar note: The Bangladeshi business week is often Saturday to Friday — with Friday as the quietest day. Our reports will be configurable: a shop can set its "week start" to Saturday or Sunday. Monthly is Gregorian (1st–last day).
2

Sales Reports (Daily / Weekly / Monthly)

Create a new Apps Script file: Reports.gs. Every reporting function reads once and computes aggregates in memory.

Reports.gs — top-level dashboard summary (reads Sales once)
/**
 * Reports.gs — Part 1: Dashboard summary
 * Reads Sales, Expenses, Purchases, StockLedger once and returns
 * a compact object with today + yesterday + month totals.
 */

function getDashboardSummary(token) {
  validateSession(token);
  
  const now = new Date();
  const todayStart = dayStart(now);
  const yesterdayStart = dayStart(addDays(now, -1));
  const monthStart = monthStart(now);
  
  // --- Read everything ONCE ---
  const sales = readAll('Sales');
  const expenses = readAll('Expenses');
  const purchases = readAll('Purchases');
  const ledger = readAll('StockLedger');
  const products = readAll('Products');
  
  // --- Precompute cost map for gross-profit calc ---
  const costMap = {};
  products.forEach(function(p) {
    costMap[p.ProductId] = Number(p.CostPrice) || 0;
  });
  
  // --- Aggregate in a single pass over Sales ---
  let today = emptyPeriod();
  let yesterday = emptyPeriod();
  let month = emptyPeriod();
  
  sales.forEach(function(s) {
    const t = new Date(s.Date).getTime();
    const row = saleToNumbers(s);
    
    if (t >= todayStart) { addToPeriod(today, row); }
    if (t >= yesterdayStart && t < todayStart) { addToPeriod(yesterday, row); }
    if (t >= monthStart) { addToPeriod(month, row); }
  });
  
  // --- Today's gross profit requires sale detail lookup ---
  const details = readAll('SaleDetails');
  let todayCOGS = 0;
  details.forEach(function(d) {
    const sale = findSale(sales, d.InvoiceNo);
    if (!sale) return;
    const t = new Date(sale.Date).getTime();
    if (t >= todayStart) {
      todayCOGS += (Number(d.Qty) || 0) * (costMap[d.ProductId] || 0);
    }
  });
  
  // --- Today's expenses ---
  let todayExpenses = 0;
  expenses.forEach(function(e) {
    const t = new Date(e.Date).getTime();
    if (t >= todayStart) todayExpenses += Number(e.Amount) || 0;
  });
  
  // --- Today's purchases ---
  let todayPurchases = 0;
  purchases.forEach(function(p) {
    const t = new Date(p.Date).getTime();
    if (t >= todayStart) todayPurchases += Number(p.Total) || 0;
  });
  
  // --- Low stock count ---
  const stockMap = {};
  ledger.forEach(function(l) {
    stockMap[l.ProductId] = Number(l.Balance) || 0;
  });
  let lowStock = 0;
  products.forEach(function(p) {
    if (p.IsActive !== true) return;
    const rl = Number(p.ReorderLevel) || 0;
    if (rl > 0 && (stockMap[p.ProductId] || 0) <= rl) lowStock++;
  });
  
  // --- Total receivable across all customers ---
  let receivable = 0;
  sales.forEach(function(s) {
    receivable += Number(s.Due) || 0;
  });
  const customerPayments = readAll('CustomerPayments');
  customerPayments.forEach(function(p) {
    receivable -= Number(p.Amount) || 0;
  });
  
  return {
    today: {
      sales: today.total,
      invoiceCount: today.count,
      grossProfit: today.total - todayCOGS,
      expenses: todayExpenses,
      purchases: todayPurchases,
      netProfit: today.total - todayCOGS - todayExpenses
    },
    yesterday: {
      sales: yesterday.total,
      invoiceCount: yesterday.count
    },
    month: {
      sales: month.total,
      invoiceCount: month.count
    },
    lowStockCount: lowStock,
    totalReceivable: receivable,
    todayDiscount: today.discount,
    todayPaid: today.paid,
    todayDue: today.due,
    generatedAt: new Date()
  };
}

// ---------- Shared aggregation helpers ----------
function emptyPeriod() {
  return { total: 0, count: 0, discount: 0, paid: 0, due: 0 };
}

function saleToNumbers(s) {
  return {
    total: Number(s.Total) || 0,
    discount: Number(s.Discount) || 0,
    paid: Number(s.Paid) || 0,
    due: Number(s.Due) || 0
  };
}

function addToPeriod(period, row) {
  period.total += row.total;
  period.count += 1;
  period.discount += row.discount;
  period.paid += row.paid;
  period.due += row.due;
}

function dayStart(d) {
  const x = new Date(d); x.setHours(0,0,0,0); return x.getTime();
}
function monthStart(d) {
  return new Date(d.getFullYear(), d.getMonth(), 1).getTime();
}
function addDays(d, n) {
  const x = new Date(d); x.setDate(x.getDate() + n); return x;
}
function findSale(sales, inv) {
  for (let i = 0; i < sales.length; i++) {
    if (sales[i].InvoiceNo === inv) return sales[i];
  }
  return null;
}

Range Report — Sales Between Two Dates

Reports.gs — getSalesReport(from, to) for any date range
/**
 * Detailed sales report for any date range.
 * Returns: daily breakdown, invoice count, discount, paid, due,
 *          top products, top cashiers, payment method split.
 */
function getSalesReport(token, fromStr, toStr) {
  validateSession(token);
  const from = dayStart(new Date(fromStr));
  const to = dayStart(addDays(new Date(toStr), 1));
  
  const sales = readAll('Sales');
  const details = readAll('SaleDetails');
  const products = readAll('Products');
  
  // Product name + cost map
  const productInfo = {};
  products.forEach(function(p) {
    productInfo[p.ProductId] = {
      name: p.Name, unit: p.Unit,
      cost: Number(p.CostPrice) || 0
    };
  });
  
  // Filter sales in range once
  const inRangeSales = sales.filter(function(s) {
    const t = new Date(s.Date).getTime();
    return t >= from && t < to;
  });
  
  const invoiceSet = {};
  inRangeSales.forEach(function(s) { invoiceSet[s.InvoiceNo] = true; });
  
  // --- Daily breakdown ---
  const dailyMap = {};
  inRangeSales.forEach(function(s) {
    const day = new Date(s.Date).toISOString().substring(0, 10);
    if (!dailyMap[day]) dailyMap[day] = { date: day, total: 0, count: 0, paid: 0, due: 0 };
    dailyMap[day].total += Number(s.Total) || 0;
    dailyMap[day].count += 1;
    dailyMap[day].paid += Number(s.Paid) || 0;
    dailyMap[day].due += Number(s.Due) || 0;
  });
  
  // --- Top products ---
  const productAgg = {};
  details.forEach(function(d) {
    if (!invoiceSet[d.InvoiceNo]) return;
    if (!productAgg[d.ProductId]) {
      productAgg[d.ProductId] = {
        productId: d.ProductId,
        name: productInfo[d.ProductId] ? productInfo[d.ProductId].name : d.ProductId,
        qty: 0, revenue: 0, cost: 0
      };
    }
    const q = Number(d.Qty) || 0;
    const rev = Number(d.LineTotal) || 0;
    const cost = q * (productInfo[d.ProductId] ? productInfo[d.ProductId].cost : 0);
    productAgg[d.ProductId].qty += q;
    productAgg[d.ProductId].revenue += rev;
    productAgg[d.ProductId].cost += cost;
  });
  const topProducts = Object.values(productAgg)
    .map(function(p) {
      p.profit = p.revenue - p.cost;
      return p;
    })
    .sort(function(a, b) { return b.revenue - a.revenue; })
    .slice(0, 10);
  
  // --- Top cashiers ---
  const cashierAgg = {};
  inRangeSales.forEach(function(s) {
    const email = s.UserId || 'unknown';
    if (!cashierAgg[email]) cashierAgg[email] = { email: email, count: 0, total: 0 };
    cashierAgg[email].count += 1;
    cashierAgg[email].total += Number(s.Total) || 0;
  });
  const topCashiers = Object.values(cashierAgg)
    .sort(function(a, b) { return b.total - a.total; });
  
  // --- Payment method split ---
  const methodAgg = {};
  inRangeSales.forEach(function(s) {
    const m = s.PaymentMethod || 'Cash';
    if (!methodAgg[m]) methodAgg[m] = { method: m, total: 0, count: 0 };
    methodAgg[m].total += Number(s.Total) || 0;
    methodAgg[m].count += 1;
  });
  
  // --- Totals ---
  let total = 0, count = 0, paid = 0, due = 0, discount = 0;
  inRangeSales.forEach(function(s) {
    total += Number(s.Total) || 0;
    paid += Number(s.Paid) || 0;
    due += Number(s.Due) || 0;
    discount += Number(s.Discount) || 0;
    count++;
  });
  
  return {
    from: fromStr,
    to: toStr,
    summary: {
      total: total, invoiceCount: count, paid: paid, due: due, discount: discount,
      avgInvoiceValue: count > 0 ? total / count : 0
    },
    dailyBreakdown: Object.values(dailyMap).sort(function(a, b) {
      return a.date.localeCompare(b.date);
    }),
    topProducts: topProducts,
    topCashiers: topCashiers,
    paymentMethods: Object.values(methodAgg)
  };
}
🧠
Why top 10, not all? A shop with 800 products would produce a useless wall of text. Top 10 answers 90% of "what's selling?" questions. If you need the rest, we will add a "full product report" in Part 5's export feature.

What the Sales Report Looks Like

Weekly Sales — Sep 4 to Sep 10, 2026
Daily Sales
৳31k
শনি
৳36k
রবি
৳43k
সোম
৳39k
মঙ্গল
৳44k
বুধ
৳46k
বৃহস্পতি
৳19k
শুক্র

Notice the pattern: Friday is quiet. That's normal in Bangladesh — it's the weekly holiday. Knowing this lets the owner schedule staff and stock deliveries better.

3

Profit Analysis — by Product, by Cashier

Revenue is vanity. Profit is sanity. Our ERP already stores cost price on every product and user email on every sale — we just need to join them.

Gross Profit on a Sale = Sale Price − Cost Price (per unit) × Quantity − Line Discount Gross Profit for a Period = Σ (Sale Price − Cost Price) × Qty − All Discounts Net Profit for a Period = Gross Profit − Operating Expenses (Rent, Salary, Electricity, Transport, etc.) Margin % = (Gross Profit ÷ Revenue) × 100
Reports.gs — getProductProfitReport(from, to)
/**
 * Product-level profit report.
 * For every product sold in the range, computes:
 *  · Qty sold
 *  · Revenue (sum of LineTotal)
 *  · Cost of goods sold (qty × cost price)
 *  · Gross profit and margin %
 * Sorted by profit descending (the winners first, losers last).
 */
function getProductProfitReport(token, fromStr, toStr) {
  validateSession(token);
  const from = dayStart(new Date(fromStr));
  const to = dayStart(addDays(new Date(toStr), 1));
  
  const sales = readAll('Sales');
  const details = readAll('SaleDetails');
  const products = readAll('Products');
  
  const inRangeInvoices = {};
  sales.forEach(function(s) {
    const t = new Date(s.Date).getTime();
    if (t >= from && t < to) inRangeInvoices[s.InvoiceNo] = true;
  });
  
  const productMap = {};
  products.forEach(function(p) {
    productMap[p.ProductId] = {
      name: p.Name, unit: p.Unit,
      cost: Number(p.CostPrice) || 0,
      sale: Number(p.SalePrice) || 0
    };
  });
  
  const agg = {};
  details.forEach(function(d) {
    if (!inRangeInvoices[d.InvoiceNo]) return;
    const pid = d.ProductId;
    if (!agg[pid]) {
      agg[pid] = {
        productId: pid,
        name: productMap[pid] ? productMap[pid].name : pid,
        unit: productMap[pid] ? productMap[pid].unit : '',
        qty: 0, revenue: 0, cost: 0, discount: 0
      };
    }
    const q = Number(d.Qty) || 0;
    agg[pid].qty += q;
    agg[pid].revenue += Number(d.LineTotal) || 0;
    agg[pid].cost += q * (productMap[pid] ? productMap[pid].cost : 0);
    agg[pid].discount += Number(d.Discount) || 0;
  });
  
  return Object.values(agg).map(function(p) {
    p.grossProfit = p.revenue - p.cost;
    p.margin = p.revenue > 0 ? (p.grossProfit / p.revenue) * 100 : 0;
    return p;
  }).sort(function(a, b) { return b.grossProfit - a.grossProfit; });
}

/**
 * Cashier performance report.
 * Useful for identifying top sellers, training needs,
 * or simply validating who worked which day.
 */
function getCashierReport(token, fromStr, toStr) {
  validateSession(token);
  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 users = readAll('Users');
  
  const nameMap = {};
  users.forEach(function(u) { nameMap[u.Email] = u.Name || u.Email; });
  
  const agg = {};
  sales.forEach(function(s) {
    const t = new Date(s.Date).getTime();
    if (t < from || t >= to) return;
    const email = s.UserId || 'unknown';
    if (!agg[email]) {
      agg[email] = {
        email: email, name: nameMap[email] || email,
        invoiceCount: 0, total: 0, paid: 0,
        due: 0, discount: 0
      };
    }
    agg[email].invoiceCount += 1;
    agg[email].total += Number(s.Total) || 0;
    agg[email].paid += Number(s.Paid) || 0;
    agg[email].due += Number(s.Due) || 0;
    agg[email].discount += Number(s.Discount) || 0;
  });
  
  return Object.values(agg).map(function(a) {
    a.avgInvoiceValue = a.invoiceCount > 0 ? a.total / a.invoiceCount : 0;
    return a;
  }).sort(function(a, b) { return b.total - a.total; });
}

/**
 * Returns products that never sold in the range.
 * Very useful for identifying dead stock.
 */
function getDeadStockReport(token, fromStr, toStr) {
  validateSession(token);
  const from = dayStart(new Date(fromStr));
  const to = dayStart(addDays(new Date(toStr), 1));
  
  const sales = readAll('Sales');
  const details = readAll('SaleDetails');
  const products = readAll('Products');
  const ledger = readAll('StockLedger');
  
  const inRangeInvoices = {};
  sales.forEach(function(s) {
    const t = new Date(s.Date).getTime();
    if (t >= from && t < to) inRangeInvoices[s.InvoiceNo] = true;
  });
  
  const sold = {};
  details.forEach(function(d) {
    if (inRangeInvoices[d.InvoiceNo]) sold[d.ProductId] = true;
  });
  
  const stockMap = {};
  ledger.forEach(function(l) {
    stockMap[l.ProductId] = Number(l.Balance) || 0;
  });
  
  return products
    .filter(function(p) {
      return p.IsActive === true && !sold[p.ProductId] && (stockMap[p.ProductId] || 0) > 0;
    })
    .map(function(p) {
      return {
        productId: p.ProductId, name: p.Name,
        stock: stockMap[p.ProductId] || 0,
        unit: p.Unit,
        costValue: (stockMap[p.ProductId] || 0) * (Number(p.CostPrice) || 0)
      };
    })
    .sort(function(a, b) { return b.costValue - a.costValue; });
}

Sample Output — Product Profit Report

Product Qty Sold Revenue COGS Gross Profit Margin
Rice 5kg148 bag৳74,000৳62,160৳11,84016.0%
Soybean Oil 2L92 pcs৳32,200৳26,680৳5,52017.1%
Sugar 1kg210 kg৳27,300৳23,100৳4,20015.4%
Lentils 1kg88 kg৳14,080৳11,440৳2,64018.8%
Flour 2kg76 bag৳9,120৳7,220৳1,90020.8%

Now Karim knows: flour has the best margin (20.8%), rice has the biggest absolute profit but a low margin. He might raise the price of rice slightly, or push flour as an upsell.

4

Expense Tracking

A shop's true profit requires subtracting every taka spent. Rent, salaries, electricity bills, transport, repairs, internet — all must be recorded. Otherwise "profit" is a lie.

Common Expense Categories in Bangladesh

🏠 দোকান ভাড়া / Rent 💼 বেতন / Salary ⚡ বিদ্যুৎ / Electricity 🌐 ইন্টারনেট / Internet 🚚 পরিবহন / Transport 🔧 মেরামত / Repair 🧾 সরকারি ফি / Govt Fee 📦 মোড়ক / Packaging 🎁 মার্কেটিং / Marketing ☕ চা-নাস্তা / Refreshments
Expenses.gs — full expense module
/**
 * Expenses.gs
 * Sheet: Expenses
 * Columns: A=Date B=Category C=Amount D=Note E=UserId F=Method
 */

const DEFAULT_EXPENSE_CATEGORIES = [
  'Rent', 'Salary', 'Electricity', 'Water',
  'Internet', 'Transport', 'Repair', 'Govt Fee',
  'Packaging', 'Marketing', 'Refreshment', 'Other'
];

function getExpenseCategories(token) {
  validateSession(token);
  // Read custom categories from Settings sheet if present.
  try {
    const settings = readAll('Settings');
    for (let i = 0; i < settings.length; i++) {
      if (settings[i].Key === 'ExpenseCategories' && settings[i].Value) {
        return settings[i].Value.split(',').map(function(x) { return x.trim(); }).filter(function(x) { return x; });
      }
    }
  } catch (e) { /* Settings sheet may not exist */ }
  return DEFAULT_EXPENSE_CATEGORIES;
}

function createExpense(token, data) {
  const session = validateSession(token);
  requireRole(session, ['Admin', 'Manager']);
  
  const amount = Number(data.amount);
  if (!amount || amount <= 0) throw new Error('Amount must be positive.');
  if (!data.category) throw new Error('Category is required.');
  
  appendRow('Expenses', [
    data.date ? new Date(data.date) : new Date(),
    trim(data.category),
    amount,
    trim(data.note),
    session.email,
    trim(data.method) || 'Cash'
  ]);
  
  logAction(session.email,
    'EXPENSE_ADDED ' + data.category + ' ৳' + amount);
  
  return { success: true };
}

function getExpenses(token, fromStr, toStr, category) {
  validateSession(token);
  const from = fromStr ? dayStart(new Date(fromStr)) : 0;
  const to = toStr ? dayStart(addDays(new Date(toStr), 1)) : Date.now();
  
  return readAll('Expenses')
    .filter(function(e) {
      const t = new Date(e.Date).getTime();
      if (t < from || t >= to) return false;
      if (category && e.Category !== category) return false;
      return true;
    })
    .map(function(e) {
      return {
        date: e.Date,
        category: e.Category,
        amount: Number(e.Amount),
        note: e.Note || '',
        userId: e.UserId,
        method: e.Method || 'Cash'
      };
    })
    .sort(function(a, b) { return new Date(b.date) - new Date(a.date); });
}

/**
 * Grouped expense summary by category.
 * Returns: { total, byCategory: [{category, amount, count, percent}], count }
 */
function getExpenseSummary(token, fromStr, toStr) {
  validateSession(token);
  const list = getExpenses(token, fromStr, toStr);
  
  const total = list.reduce(function(s, e) { return s + e.amount; }, 0);
  const byCat = {};
  list.forEach(function(e) {
    if (!byCat[e.category]) byCat[e.category] = { category: e.category, amount: 0, count: 0 };
    byCat[e.category].amount += e.amount;
    byCat[e.category].count += 1;
  });
  
  return {
    total: total,
    count: list.length,
    byCategory: Object.values(byCat)
      .map(function(c) {
        c.percent = total > 0 ? (c.amount / total) * 100 : 0;
        return c;
      })
      .sort(function(a, b) { return b.amount - a.amount; })
  };
}

Sample Monthly Expense Breakdown

September 2026 Expenses · ৳ 47,200
৳18k
Rent
৳13k
Salary
৳7.5k
Elec
৳4.5k
Trans
৳2.8k
Other
💡
Salary handling: If you have 5 employees earning ৳13,000 each = ৳65,000/month salary, don't record it in one shot. Record each employee's salary separately on payday. This gives a clean audit trail if you ever need to prove payment. Use the Note field for the employee name.
5

Profit & Loss Statement

The P&L is the single most important report a shop owner can look at. It answers: "Did I actually make money this month, or did I just handle a lot of cash?"

DEMO STORE — Profit & Loss Period: 1 Sep 2026 → 30 Sep 2026 REVENUE Gross Sales ...................... ৳ 985,400 Less: Discounts .................. −৳ 12,300 Less: Sales Returns .............. −৳ 8,400 Net Revenue ...................... ৳ 964,700 COST OF GOODS SOLD (COGS) Opening Stock at Cost ............ ৳ 85,000 + Purchases ...................... ৳ 720,000 − Closing Stock at Cost .......... −৳ 95,000 COGS ............................. ৳ 710,000 GROSS PROFIT ..................... ৳ 254,700 (26.4%) OPERATING EXPENSES Rent ............................. ৳ 18,000 Salaries ......................... ৳ 65,000 Electricity ...................... ৳ 7,500 Internet ......................... ৳ 2,000 Transport ........................ ৳ 4,500 Repairs .......................... ৳ 1,800 Packaging ........................ ৳ 2,400 Marketing ........................ ৳ 1,500 Other ............................ ৳ 1,200 Total Expenses ................... ৳ 103,900 NET PROFIT ....................... ৳ 150,800 (15.6%)
Reports.gs — getProfitAndLoss(year, month)
/**
 * Profit & Loss statement for a given month.
 * Uses opening/closing stock value (at cost) instead of
 * purchase-minus-sales, which is more accurate for shops
 * that carry inventory across months.
 */
function getProfitAndLoss(token, year, month) {
  validateSession(token);
  const session = validateSession(token);
  requireRole(session, ['Admin', 'Manager']);
  
  const periodStart = new Date(year, month - 1, 1);
  const periodEnd = new Date(year, month, 1); // exclusive
  const startMs = periodStart.getTime();
  const endMs = periodEnd.getTime();
  
  const sales = readAll('Sales');
  const details = readAll('SaleDetails');
  const purchases = readAll('Purchases');
  const expenses = readAll('Expenses');
  const ledger = readAll('StockLedger');
  const products = readAll('Products');
  
  const costMap = {};
  products.forEach(function(p) {
    costMap[p.ProductId] = Number(p.CostPrice) || 0;
  });
  
  // --- 1. Revenue: sum of sales in period ---
  let grossSales = 0;
  let totalDiscount = 0;
  const inPeriod = {};
  sales.forEach(function(s) {
    const t = new Date(s.Date).getTime();
    if (t >= startMs && t < endMs) {
      inPeriod[s.InvoiceNo] = true;
      grossSales += (Number(s.SubTotal) || 0);
      totalDiscount += (Number(s.Discount) || 0);
    }
  });
  
  // --- 2. Returns in period (negative revenue) ---
  let returnsTotal = 0;
  try {
    const returns = readAll('Returns');
    returns.forEach(function(r) {
      const t = new Date(r.Date).getTime();
      if (t >= startMs && t < endMs) {
        returnsTotal += Number(r.Total) || 0;
      }
    });
  } catch (e) { /* Returns sheet may not exist yet */ }
  
  const netRevenue = grossSales - totalDiscount - returnsTotal;
  
  // --- 3. COGS: opening + purchases − closing ---
  const openingValue = computeStockValue(ledger, costMap, startMs);
  const closingValue = computeStockValue(ledger, costMap, endMs);
  
  let purchasesInPeriod = 0;
  purchases.forEach(function(p) {
    const t = new Date(p.Date).getTime();
    if (t >= startMs && t < endMs) {
      purchasesInPeriod += Number(p.Total) || 0;
    }
  });
  
  const cogs = openingValue + purchasesInPeriod - closingValue;
  const grossProfit = netRevenue - cogs;
  
  // --- 4. Operating expenses ---
  const expenseByCat = {};
  let totalExpenses = 0;
  expenses.forEach(function(e) {
    const t = new Date(e.Date).getTime();
    if (t >= startMs && t < endMs) {
      const c = e.Category || 'Other';
      if (!expenseByCat[c]) expenseByCat[c] = 0;
      expenseByCat[c] += Number(e.Amount) || 0;
      totalExpenses += Number(e.Amount) || 0;
    }
  });
  
  const netProfit = grossProfit - totalExpenses;
  
  return {
    period: {
      year: year, month: month,
      from: periodStart, to: addDays(periodEnd, -1),
      label: monthName(month) + ' ' + year
    },
    revenue: {
      grossSales: grossSales,
      discount: totalDiscount,
      returns: returnsTotal,
      net: netRevenue
    },
    cogs: {
      openingStock: openingValue,
      purchases: purchasesInPeriod,
      closingStock: closingValue,
      total: cogs
    },
    grossProfit: grossProfit,
    grossMargin: netRevenue > 0 ? (grossProfit / netRevenue) * 100 : 0,
    expenses: {
      byCategory: Object.keys(expenseByCat).map(function(c) {
        return { category: c, amount: expenseByCat[c] };
      }).sort(function(a, b) { return b.amount - a.amount; }),
      total: totalExpenses
    },
    netProfit: netProfit,
    netMargin: netRevenue > 0 ? (netProfit / netRevenue) * 100 : 0
  };
}

/**
 * Computes the value of stock (at cost) as of a specific time.
 * Walks the ledger, tracking the balance of every product up to
 * the cutoff time, then multiplies each balance by the current
 * cost price.
 * 
 * NOTE: Uses CURRENT cost price, which is a simplification. For
 * a fully accurate historical valuation, you'd store cost at time
 * of purchase in the ledger. In practice, for a small shop where
 * cost prices change slowly, current cost is accurate enough.
 */
function computeStockValue(ledger, costMap, cutoffMs) {
  const balances = {};
  ledger.forEach(function(l) {
    const t = new Date(l.Date).getTime();
    if (t >= cutoffMs) return;
    balances[l.ProductId] = Number(l.Balance) || 0;
  });
  let total = 0;
  Object.keys(balances).forEach(function(pid) {
    total += balances[pid] * (costMap[pid] || 0);
  });
  return total;
}

function monthName(m) {
  const names = ['January','February','March','April','May','June',
    'July','August','September','October','November','December'];
  return names[m - 1] || '';
}

Bilingual P&L Output

Item বাংলা Amount (৳)
Gross Salesমোট বিক্রয়985,400.00
Less: Discountsবিয়োগ: ছাড়−12,300.00
Less: Returnsবিয়োগ: ফেরত−8,400.00
Net Revenueনিট আয়964,700.00
COGSপণ্যের ক্রয়মূল্য−710,000.00
Gross Profitমোট মুনাফা254,700.00
Total Expensesমোট খরচ−103,900.00
Net Profitনিট মুনাফা150,800.00
Net Marginনিট মার্জিন15.6%
⚠️
The stock valuation caveat: We use the current cost price for both opening and closing stock. If you bought rice at ৳400 last month and re-bought at ৳450 this month, opening stock is slightly understated. For a small shop, this error is small. For a proper accounting system, you would store the cost at the time of each purchase. Part 5 will offer this as an advanced option.
6

Returns Workflow

Returns are a fact of business. A customer buys 5 kg of rice, comes back saying it smells old. A boutique customer returns a saree because the colour isn't right. A pharmacy customer returns unused medicine. Your ERP must handle these cleanly.

🔴 Why Returns Need Their Own Sheet

You cannot simply "delete" a sale or "reduce" a stock number. Both would destroy the audit trail. A Return is a new event that references the original sale and reverses part or all of it.

Returns.gs Backend

Returns.gs — full returns module
/**
 * Returns.gs
 * Customer returns against previous invoices.
 * 
 * Sheet: Returns
 * Columns: A=ReturnNo B=Date C=OriginalInvoice D=CustomerId
 *          E=Total F=RefundMethod G=Note H=UserId
 * 
 * Sheet: ReturnDetails
 * Columns: A=ReturnNo B=ProductId C=Qty D=UnitPrice E=LineTotal F=Reason
 */

function createReturn(token, data) {
  const session = validateSession(token);
  requireRole(session, ['Admin', 'Manager']);
  
  if (!data.originalInvoice) throw new Error('Original invoice is required.');
  if (!data.items || !data.items.length) throw new Error('Return must have at least one item.');
  if (!data.reason) throw new Error('Reason is required.');
  
  // Look up the original sale
  const original = getSaleByInvoice(token, data.originalInvoice);
  const originalDetailMap = {};
  original.details.forEach(function(d) { originalDetailMap[d.productId] = d; });
  
  // Validate each returned item against the original
  let returnTotal = 0;
  data.items.forEach(function(it) {
    const orig = originalDetailMap[it.productId];
    if (!orig) throw new Error('Product not part of the original sale: ' + it.productId);
    if (Number(it.qty) > orig.qty) {
      throw new Error('Cannot return more than sold. Product: ' + orig.productName +
        ', sold: ' + orig.qty + ', returning: ' + it.qty);
    }
    returnTotal += (Number(it.qty) || 0) * orig.unitPrice;
  });
  
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(20000);
    
    const returnNo = nextReturnNo();
    const now = new Date();
    
    // 1. Write Return header
    appendRow('Returns', [
      returnNo, now, data.originalInvoice, original.customerId,
      returnTotal, data.refundMethod || 'Cash',
      trim(data.reason), session.email
    ]);
    
    // 2. Write detail lines
    const detailRows = [];
    data.items.forEach(function(it) {
      const orig = originalDetailMap[it.productId];
      const q = Number(it.qty);
      detailRows.push([returnNo, it.productId, q, orig.unitPrice, q * orig.unitPrice, trim(it.reason)]);
    });
    appendRows(sheet('ReturnDetails'), detailRows);
    
    // 3. Write ledger entries (positive qty back into stock)
    const ledgerSheet = sheet('StockLedger');
    const ledgerData = ledgerSheet.getDataRange().getValues();
    const ledgerRows = [];
    
    data.items.forEach(function(it) {
      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;
        }
      }
      const q = Number(it.qty);
      ledgerRows.push([
        now, it.productId, 'RETURN', returnNo,
        q, 0, current + q,
        'Return against ' + data.originalInvoice + ' · ' + data.reason
      ]);
    });
    appendRows(ledgerSheet, ledgerRows);
    
    // 4. Adjust customer due (negative adjustment, since we are crediting them)
    if (original.customerId !== 'WALK-IN' && data.refundMethod === 'Due Adjust') {
      appendRow('CustomerPayments', [
        'RET-' + uuid().substring(0, 8).toUpperCase(),
        now, original.customerId, returnTotal,
        'Return Credit', 'Credit from ' + returnNo, session.email
      ]);
    }
    
    logAction(session.email,
      'RETURN_CREATED ' + returnNo + ' total=৳' + returnTotal + ' against ' + data.originalInvoice);
    
    return {
      success: true,
      returnNo: returnNo,
      total: returnTotal,
      lineCount: data.items.length
    };
  } finally {
    lock.releaseLock();
  }
}

function nextReturnNo() {
  const s = sheet('Returns');
  const lastRow = s.getLastRow();
  const year = new Date().getFullYear();
  if (lastRow <= 1) return 'RET-' + year + '-00001';
  const last = String(s.getRange(lastRow, 1).getValue());
  const n = parseInt(last.split('-')[2], 10) || 0;
  return 'RET-' + year + '-' + (n + 1).toString().padStart(5, '0');
}

function getReturns(token, fromStr, toStr) {
  validateSession(token);
  const from = fromStr ? dayStart(new Date(fromStr)) : 0;
  const to = toStr ? dayStart(addDays(new Date(toStr), 1)) : Date.now();
  
  return readAll('Returns')
    .filter(function(r) {
      const t = new Date(r.Date).getTime();
      return t >= from && t < to;
    })
    .map(function(r) {
      return {
        returnNo: r.ReturnNo,
        date: r.Date,
        originalInvoice: r.OriginalInvoice,
        customerId: r.CustomerId,
        total: Number(r.Total) || 0,
        refundMethod: r.RefundMethod,
        note: r.Note,
        userId: r.UserId
      };
    })
    .sort(function(a, b) { return new Date(b.date) - new Date(a.date); });
}
🎯
The four-point return: A proper return does four things at once — writes a header, writes detail lines, restores stock via the ledger, and optionally adjusts the customer's due. All under one lock, so nothing is half-done.
7

Google Docs Invoice Template Setup

We will use Google Docs to design a beautiful invoice once, add placeholders, and let Apps Script fill it in every time a sale happens. The result is a professional A4 PDF invoice, free forever.

Step 1: Create the Template Document

Open Google Docs

In the shop's Google account, go to docs.new. Rename it Invoice_Template.

Design the header

Top of the page: big shop name, address, phone. Right-aligned: "INVOICE" and a small "TAX INVOICE" label. Add a horizontal line.

Add the two-party block

Two-column layout with "Billed To" (customer name, address, mobile) on the left and invoice metadata (invoice number, date, cashier) on the right.

Insert the items table

Insert a table with 5 columns: #, Product, Qty, Unit Price, Line Total. Add a placeholder row at the top of the body: {{ITEMS_START}} … {{ITEMS_END}}. We'll replace this whole block programmatically.

Add the totals block

Right-aligned small table below: Subtotal, Discount, Grand Total, Paid, Due. Use placeholders {{SUB_TOTAL}}, {{DISCOUNT}}, etc.

Add the footer

Thank-you line in Bengali + English: "ধন্যবাদ! আবার আসবেন · Thank you, please come again." Plus a signature line for the owner.

Note the Template ID

From the URL docs.google.com/document/d/THIS_ID/edit, copy the ID. Save it in Script Properties as INVOICE_TEMPLATE_ID.

Step 2: Set the Script Property

In the Apps Script editor, click ⚙️ Project Settings → Script Properties → Add:

PropertyValuePurpose
INVOICE_TEMPLATE_ID (paste the Docs ID) Which document to clone for each invoice
INVOICE_FOLDER_ID (optional folder ID) Where to save generated PDFs. Create a Drive folder "Invoices" and copy its ID.

Step 3: Preview the Result

Here is what a filled invoice looks like:

DEMO STORE Mirpur, Dhaka 1216 · Mobile: 01XXXXXXXXX
INVOICE INV-2026-00042
Date: 10 Sep 2026
BILLED TO Rahim Bhai
House 42, Road 5, Mirpur 10
Dhaka · Mobile: 01711-XXXXXX
INVOICE DETAILS Cashier: Nasrin Akter
Payment: Cash
Terms: Immediate
# Product Qty Price Total
1Rice 5kg2 bag৳ 500.00৳ 1,000.00
2Soybean Oil 2L1 pcs৳ 350.00৳ 350.00
3Sugar 1kg1 kg৳ 130.00৳ 130.00
Subtotal৳ 1,480.00
Discount৳ -30.00
Grand Total৳ 1,450.00
Paid (Cash)৳ 1,500.00
Change৳ 50.00
ধন্যবাদ! আবার আসবেন · Thank you, please come again.
Invoice INV-2026-00042 · Generated automatically
8

PDF Invoice Generation Code

Add a new Apps Script file: Invoice.gs. This is where the magic happens.

Invoice.gs — generate PDF invoice from template
/**
 * Invoice.gs
 * Generates a professional A4 PDF invoice for any sale,
 * by cloning the Invoice_Template Google Doc, replacing
 * placeholders, and exporting to PDF via Drive.
 */

const INVOICE_TEMPLATE_ID = PropertiesService
  .getScriptProperties().getProperty('INVOICE_TEMPLATE_ID');
const INVOICE_FOLDER_ID = PropertiesService
  .getScriptProperties().getProperty('INVOICE_FOLDER_ID');

/**
 * Generates a PDF invoice for a given sale.
 * @param {string} token — session token
 * @param {string} invoiceNo — the sale's invoice number
 * @returns {Object} { success, pdfUrl, pdfId }
 */
function generateInvoicePdf(token, invoiceNo) {
  validateSession(token);
  
  if (!INVOICE_TEMPLATE_ID) {
    throw new Error('INVOICE_TEMPLATE_ID is not configured in Script Properties.');
  }
  
  const sale = getSaleByInvoice(token, invoiceNo);
  const customer = lookupCustomer(sale.customerId);
  const shop = getShopInfo();
  
  // 1. Clone the template
  const template = DriveApp.getFileById(INVOICE_TEMPLATE_ID);
  const fileName = 'Invoice_' + invoiceNo + '_' +
    Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd_HHmm');
  const copy = template.makeCopy(fileName, getOrCreateInvoiceFolder());
  
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();
  
  // 2. Simple placeholder replacement
  const dateStr = Utilities.formatDate(
    new Date(sale.date),
    Session.getScriptTimeZone(),
    'dd MMM yyyy, HH:mm'
  );
  
  body.replaceText('{{SHOP_NAME}}', shop.name);
  body.replaceText('{{SHOP_ADDRESS}}', shop.address);
  body.replaceText('{{SHOP_PHONE}}', shop.phone);
  body.replaceText('{{INVOICE_NO}}', sale.invoiceNo);
  body.replaceText('{{INVOICE_DATE}}', dateStr);
  body.replaceText('{{CASHIER}}', sale.userId || '');
  body.replaceText('{{CUSTOMER_NAME}}', customer.name || 'Walk-in Customer');
  body.replaceText('{{CUSTOMER_ADDRESS}}', customer.address || '');
  body.replaceText('{{CUSTOMER_MOBILE}}', customer.mobile || '');
  body.replaceText('{{PAYMENT_METHOD}}', sale.paymentMethod || 'Cash');
  
  body.replaceText('{{SUB_TOTAL}}', formatMoney(sale.subTotal));
  body.replaceText('{{DISCOUNT}}', formatMoney(sale.discount));
  body.replaceText('{{GRAND_TOTAL}}', formatMoney(sale.total));
  body.replaceText('{{PAID}}', formatMoney(sale.paid));
  body.replaceText('{{DUE}}', formatMoney(sale.due));
  
  // 3. Find the items table and fill it
  const tables = body.getTables();
  if (tables.length === 0) {
    throw new Error('Invoice template has no table. Add an items table first.');
  }
  
  // We assume the FIRST table is the items table.
  const itemsTable = tables[0];
  
  // The template's table should have a header row and ONE example row
  // with placeholders {{ITEM_NAME}}, {{ITEM_QTY}}, etc.
  // We replace the example row with real rows.
  
  // Find the template row index — should be row 1 (row 0 is header)
  const templateRowIdx = 1;
  const templateRow = itemsTable.getRow(templateRowIdx);
  
  // Copy cell attributes from the template row
  const cellCount = templateRow.getNumCells();
  
  // Insert real rows BEFORE the template row, then remove the template row.
  const startIdx = templateRowIdx;
  sale.details.forEach(function(d, i) {
    const newRow = itemsTable.insertTableRow(startIdx + i);
    const cells = newRow.getNumCells();
    if (cells >= 5) {
      newRow.getCell(0).setText(String(i + 1));
      newRow.getCell(1).setText(d.productName);
      newRow.getCell(2).setText(String(d.qty) + ' ' + (d.unit || ''));
      newRow.getCell(3).setText('৳ ' + formatMoney(d.unitPrice));
      newRow.getCell(4).setText('৳ ' + formatMoney(d.lineTotal));
    }
  });
  
  // Remove the template placeholder row
  itemsTable.removeRow(startIdx + sale.details.length);
  
  // 4. Save and export as PDF
  doc.saveAndClose();
  
  const pdfBlob = copy.getAs('application/pdf').setName(fileName + '.pdf');
  const pdfFile = getOrCreateInvoiceFolder().createFile(pdfBlob);
  
  // 5. Optionally delete the intermediate Doc
  try { copy.setTrashed(true); } catch (e) { /* ignore */ }
  
  // 6. Log
  const session = validateSession(token);
  logAction(session.email, 'INVOICE_PDF_GENERATED ' + invoiceNo);
  
  return {
    success: true,
    invoiceNo: invoiceNo,
    pdfId: pdfFile.getId(),
    pdfUrl: pdfFile.getUrl(),
    downloadUrl: 'https://drive.google.com/uc?export=download&id=' + pdfFile.getId()
  };
}

/**
 * Returns or creates the folder where invoices are saved.
 */
function getOrCreateInvoiceFolder() {
  if (INVOICE_FOLDER_ID) {
    try {
      return DriveApp.getFolderById(INVOICE_FOLDER_ID);
    } catch (e) { /* fall through */ }
  }
  // Create on demand
  const it = DriveApp.getFoldersByName('ERP_Invoices');
  return it.hasNext() ? it.next() : DriveApp.createFolder('ERP_Invoices');
}

/**
 * Returns shop info for the invoice header.
 * Priority: Settings sheet → Script Properties → fallback.
 */
function getShopInfo() {
  const info = {
    name: COMPANY_NAME || 'My Shop',
    address: '',
    phone: ''
  };
  try {
    const settings = readAll('Settings');
    settings.forEach(function(s) {
      if (s.Key === 'ShopName') info.name = s.Value;
      if (s.Key === 'ShopAddress') info.address = s.Value;
      if (s.Key === 'ShopPhone') info.phone = s.Value;
    });
  } catch (e) { /* Settings sheet optional */ }
  return info;
}

/**
 * Looks up a customer row (or returns a walk-in placeholder).
 */
function lookupCustomer(customerId) {
  if (!customerId || customerId === 'WALK-IN') {
    return { name: 'Walk-in Customer', address: '', mobile: '' };
  }
  const all = readAll('Customers');
  for (let i = 0; i < all.length; i++) {
    if (all[i].CustomerId === customerId) {
      return {
        name: all[i].Name,
        address: all[i].Address || '',
        mobile: all[i].Mobile || ''
      };
    }
  }
  return { name: customerId, address: '', mobile: '' };
}

/**
 * Formats a number with thousands separators and 2 decimals.
 */
function formatMoney(n) {
  const num = Number(n || 0);
  const parts = num.toFixed(2).split('.');
  parts[0] = parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, ',');
  return parts.join('.');
}

Setting Up Your Settings Sheet

The invoice header needs your shop's real name, address and phone. Create a Settings sheet (if you haven't already) with these rows:

KeyValueDescription
ShopNameDemo StoreDisplayed on the invoice header
ShopAddressMirpur 10, Dhaka 1216Displayed under the name
ShopPhone01XXXXXXXXXContact number
ShopEmailowner@demo.bdFor emails and receipts
ExpenseCategoriesRent,Salary,Electricity,Transport,OtherComma-separated list
VATNumber(optional) BIN numberDisplayed on invoice if set

Triggering Invoice Generation from the POS

After a sale is saved, add a "Print PDF Invoice" button in the POS. The button calls generateInvoicePdf(token, invoiceNo) and opens the returned pdfUrl in a new tab.

Add this snippet to Pos.html (after a successful sale)
// Inside the .then() after createSale succeeds, replace printReceipt(res):
apiCall('generateInvoicePdf', res.invoiceNo)
  .then(function(pdf) {
    // Open the PDF in a new tab — user can print or email it.
    window.open(pdf.pdfUrl, '_blank');
  })
  .catch(function(err) {
    console.error('PDF generation failed:', err);
    // Fallback to the thermal receipt
    printReceipt(res);
  });
📎
Email the invoice: After generating the PDF, call GmailApp.sendEmail(customer.email, 'Your Invoice ' + invoiceNo, 'Please find your invoice attached.', { attachments: [pdfBlob] }) — free, instant, professional. Free accounts can send 100 emails per day, which is plenty for a shop.
9

Live Dashboard with Charts

A good dashboard tells the owner three things at a glance: are sales up, is profit healthy, is anything broken. We'll build that with Bootstrap + a lightweight chart library (Chart.js) — no server, no paid tools.

Dashboard.html — full advanced dashboard
<!DOCTYPE html>
<html lang="bn">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <title>Dashboard — <?= COMPANY_NAME ?></title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
  <style>
    body { background:#f8fafc; font-family: "Segoe UI", Roboto, "Noto Sans Bengali", sans-serif; }
    .dash-card {
      background:#fff; border:1px solid #e2e8f0; border-radius:14px;
      padding:18px; box-shadow: 0 1px 2px rgba(15,23,42,.06);
      transition: all .25s ease;
    }
    .dash-card:hover { box-shadow: 0 10px 24px -8px rgba(15,23,42,.14); }
    .dash-card .label {
      font-size: 12px; color:#64748b; font-weight:700;
      letter-spacing:.4px; text-transform:uppercase;
    }
    .dash-card .value { font-size: 26px; font-weight:800; color:#0f172a; margin-top:6px; }
    .dash-card .value.taka { color:#0e7490; }
    .dash-card .value.green { color:#16a34a; }
    .dash-card .value.red { color:#dc2626; }
    .dash-card .delta { font-size:12px; margin-top:4px; }
    .dash-card .delta.up { color:#16a34a; }
    .dash-card .delta.down { color:#dc2626; }
    .chart-card {
      background:#fff; border:1px solid #e2e8f0; border-radius:14px;
      padding:18px; margin-bottom: 20px;
    }
    .chart-card h6 { font-size:14px; margin:0 0 12px; color:#0f172a; font-weight:700; }
    .dash-nav a {
      color:#fff !important; text-decoration:none; font-size:14px;
      padding: 6px 12px; border-radius: 8px;
      transition: all .2s ease;
    }
    .dash-nav a:hover { background: rgba(255,255,255,.15); }
  </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 mb-0 h6"><?= COMPANY_NAME ?> ERP</span>
      <div class="d-flex align-items-center dash-nav">
        <a href="?page=POS">POS</a>
        <a href="?page=Products">Products</a>
        <a href="?page=Customers">Customers</a>
        <a href="?page=Reports">Reports</a>
        <span id="userBadge" class="ms-3"></span>
        <button class="btn btn-outline-light btn-sm ms-2" onclick="fl365Logout()">Logout</button>
      </div>
    </div>
  </nav>

  <div class="container py-4">
    <div class="d-flex justify-content-between align-items-center mb-3">
      <h4 class="mb-0">Dashboard</h4>
      <small class="text-muted" id="genAt"></small>
    </div>

    <!-- KPI ROW -->
    <div class="row g-3 mb-3">
      <div class="col-6 col-md-3">
        <div class="dash-card">
          <div class="label">Today's Sales</div>
          <div class="value taka" id="kpiTodaySales">৳ 0</div>
          <div class="delta" id="kpiTodayDelta"></div>
        </div>
      </div>
      <div class="col-6 col-md-3">
        <div class="dash-card">
          <div class="label">Today's Profit</div>
          <div class="value green" id="kpiTodayProfit">৳ 0</div>
          <div class="delta">Gross · after discount</div>
        </div>
      </div>
      <div class="col-6 col-md-3">
        <div class="dash-card">
          <div class="label">Month Sales</div>
          <div class="value taka" id="kpiMonthSales">৳ 0</div>
          <div class="delta">Month-to-date</div>
        </div>
      </div>
      <div class="col-6 col-md-3">
        <div class="dash-card">
          <div class="label">Receivable</div>
          <div class="value red" id="kpiReceivable">৳ 0</div>
          <div class="delta">Across all customers</div>
        </div>
      </div>
    </div>

    <!-- CHARTS -->
    <div class="row g-3">
      <div class="col-md-8">
        <div class="chart-card">
          <h6>Last 7 Days — Sales & Profit</h6>
          <canvas id="chartWeekly" height="110"></canvas>
        </div>
      </div>
      <div class="col-md-4">
        <div class="chart-card">
          <h6>Payment Methods · This Month</h6>
          <canvas id="chartPayment" height="200"></canvas>
        </div>
      </div>
    </div>

    <div class="row g-3">
      <div class="col-md-7">
        <div class="chart-card">
          <h6>Top 5 Products · This Month</h6>
          <canvas id="chartTopProducts" height="120"></canvas>
        </div>
      </div>
      <div class="col-md-5">
        <div class="chart-card">
          <h6>Low Stock Alerts</h6>
          <div id="lowStockList">
            <div class="text-muted small">Loading...</div>
          </div>
        </div>
      </div>
    </div>
  </div>

  <?!= include('JS') ?>
  <script>
    renderUserBadge('userBadge');
    const fmt = function(n) {
      return '৳ ' + Number(n || 0).toLocaleString('en-BD', { maximumFractionDigits: 0 });
    };
    const fmt2 = function(n) {
      return Number(n || 0).toLocaleString('en-BD', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
    };
    const taka = function(n) { return '৳ ' + fmt2(n); };

    // --- 1. Load the summary KPIs ---
    apiCall('getDashboardSummary')
      .then(function(d) {
        document.getElementById('kpiTodaySales').textContent = taka(d.today.sales);
        document.getElementById('kpiTodayProfit').textContent = taka(d.today.grossProfit);
        document.getElementById('kpiMonthSales').textContent = taka(d.month.sales);
        document.getElementById('kpiReceivable').textContent = taka(d.totalReceivable);
        document.getElementById('genAt').textContent =
          'Last updated: ' + new Date(d.generatedAt).toLocaleTimeString();

        if (d.yesterday.sales > 0) {
          const pct = ((d.today.sales - d.yesterday.sales) / d.yesterday.sales) * 100;
          const el = document.getElementById('kpiTodayDelta');
          el.textContent = (pct >= 0 ? '▲ ' : '▼ ') + Math.abs(pct).toFixed(1) + '% vs yesterday';
          el.className = 'delta ' + (pct >= 0 ? 'up' : 'down');
        }
      });

    // --- 2. Weekly chart (last 7 days) ---
    const now = new Date();
    const sevenAgo = new Date(now.getTime() - 6 * 86400000);
    const iso = function(d) { return d.toISOString().substring(0,10); };

    apiCall('getSalesReport', iso(sevenAgo), iso(now))
      .then(function(r) {
        const labels = r.dailyBreakdown.map(function(d) {
          return new Date(d.date).toLocaleDateString('en-GB', { day:'2-digit', month:'short' });
        });
        const salesData = r.dailyBreakdown.map(function(d) { return d.total; });
        new Chart(document.getElementById('chartWeekly'), {
          type: 'line',
          data: {
            labels: labels,
            datasets: [{
              label: 'Sales (৳)', data: salesData,
              borderColor: '#6d28d9', backgroundColor: 'rgba(109,40,217,.12)',
              fill: true, tension: 0.35, borderWidth: 3,
              pointBackgroundColor: '#6d28d9', pointRadius: 4
            }]
          },
          options: {
            plugins: { legend: { display: false } },
            scales: { y: { beginAtZero: true, ticks: { callback: function(v){ return '৳'+v; } } } }
          }
        });
      });

    // --- 3. Payment method pie (this month) ---
    const monthStart = new Date(now.getFullYear(), now.getMonth(), 1);
    apiCall('getSalesReport', iso(monthStart), iso(now))
      .then(function(r) {
        const labels = r.paymentMethods.map(function(m){ return m.method; });
        const values = r.paymentMethods.map(function(m){ return m.total; });
        new Chart(document.getElementById('chartPayment'), {
          type: 'doughnut',
          data: {
            labels: labels,
            datasets: [{
              data: values,
              backgroundColor: ['#6d28d9','#06b6d4','#f59e0b','#10b981','#ef4444']
            }]
          },
          options: { plugins: { legend: { position: 'bottom', labels: { boxWidth: 12 } } } }
        });
      });

    // --- 4. Top products bar ---
    apiCall('getProductProfitReport', iso(monthStart), iso(now))
      .then(function(rows) {
        const top = rows.slice(0, 5);
        new Chart(document.getElementById('chartTopProducts'), {
          type: 'bar',
          data: {
            labels: top.map(function(p){ return p.name; }),
            datasets: [
              { label: 'Revenue', data: top.map(function(p){ return p.revenue; }),
                backgroundColor: '#6d28d9', borderRadius: 6 },
              { label: 'Profit', data: top.map(function(p){ return p.grossProfit; }),
                backgroundColor: '#10b981', borderRadius: 6 }
            ]
          },
          options: {
            plugins: { legend: { position: 'bottom' } },
            scales: { y: { beginAtZero: true, ticks: { callback: function(v){ return '৳'+v; } } } }
          }
        });
      });

    // --- 5. Low stock list ---
    apiCall('getLowStockProducts')
      .then(function(list) {
        const el = document.getElementById('lowStockList');
        if (!list.length) {
          el.innerHTML = '<div class="text-muted small">All products are above reorder level. 🎉</div>';
          return;
        }
        el.innerHTML = list.slice(0, 8).map(function(p) {
          return '<div class="d-flex justify-content-between align-items-center py-1 border-bottom">'
            + '<span class="small">' + p.name + '</span>'
            + '<span class="badge bg-danger">' + p.stock + ' ' + p.unit + '</span>'
            + '</div>';
        }).join('');
      });
  </script>
</body>
</html>
📈
Why Chart.js and not Google Charts? Both are free. Chart.js is smaller, more modern, mobile-friendly, and looks better out of the box on a phone. Google Charts is fine but heavier. For a shop owner checking her phone at 10 PM, Chart.js wins.
10

Scheduled Triggers & Email Reports

The best report is the one you don't have to open. We will set up automatic emails: a daily summary at 10 PM, and a monthly P&L on the 1st of each month. All free via Apps Script time-driven triggers.

Triggers.gs — scheduled email reports
/**
 * Triggers.gs
 * Time-driven functions that run automatically.
 * 
 * Setup (ONE TIME):
 *   1. Run installTriggers() once from the Apps Script editor.
 *   2. Approve permissions.
 *   3. You're done. Emails start arriving on schedule.
 */

function installTriggers() {
  // Remove any existing triggers from this function
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function(t) {
    const fn = t.getHandlerFunction();
    if (fn === 'sendDailyReport' || fn === 'sendMonthlyPL') {
      ScriptApp.deleteTrigger(t);
    }
  });
  
  // Daily at 10:00 PM shop time
  ScriptApp.newTrigger('sendDailyReport')
    .timeBased()
    .atHour(22)
    .nearMinute(0)
    .everyDays(1)
    .create();
  
  // Monthly P&L on the 1st at 8:00 AM
  ScriptApp.newTrigger('sendMonthlyPL')
    .timeBased()
    .onMonthDay(1)
    .atHour(8)
    .create();
  
  Logger.log('Triggers installed: Daily 10 PM report, Monthly 1st 8 AM P&L.');
}

/**
 * Fires at 10 PM every day.
 * Sends a beautifully formatted HTML email to the owner
 * with today's sales, profit, expenses and low-stock alerts.
 */
function sendDailyReport() {
  try {
    const summary = computeDailySummary();
    const ownerEmail = getOwnerEmail();
    if (!ownerEmail) {
      Logger.log('No owner email configured. Add Settings: OwnerEmail.');
      return;
    }
    
    const html = buildDailyEmailHtml(summary);
    GmailApp.sendEmail(
      ownerEmail,
      '📊 Daily Report — ' + COMPANY_NAME + ' — ' +
        Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd MMM yyyy'),
      'Your daily report for today. Open in an HTML-capable email client.',
      { htmlBody: html }
    );
    
    logAction('system', 'DAILY_REPORT_SENT to ' + ownerEmail);
  } catch (e) {
    console.error('sendDailyReport failed: ', e);
  }
}

/**
 * Fires on the 1st of every month.
 * Sends last month's full P&L as an HTML email.
 */
function sendMonthlyPL() {
  try {
    const now = new Date();
    let year = now.getFullYear();
    let month = now.getMonth(); // 0-indexed: current month; we want previous
    if (month === 0) { month = 12; year--; }
    
    const token = getSystemToken();
    const pl = getProfitAndLoss(token, year, month);
    const ownerEmail = getOwnerEmail();
    if (!ownerEmail) return;
    
    const html = buildPLHtml(pl);
    GmailApp.sendEmail(
      ownerEmail,
      '📈 P&L Report — ' + pl.period.label + ' — ' + COMPANY_NAME,
      'Last month P&L. Open in an HTML-capable email client.',
      { htmlBody: html }
    );
    
    logAction('system', 'MONTHLY_PL_SENT for ' + pl.period.label);
  } catch (e) {
    console.error('sendMonthlyPL failed: ', e);
  }
}

/**
 * Generates a short-lived internal session for scheduled jobs.
 * These functions run without a real user, but need a token to call
 * other functions that validate sessions.
 */
function getSystemToken() {
  const token = 'sys_' + Utilities.getUuid();
  const session = {
    email: 'system@internal',
    name: 'System',
    role: 'Admin', // system jobs need full access
    companyId: COMPANY_ID,
    loginAt: new Date().toISOString()
  };
  CacheService.getScriptCache().put('sess_' + token, JSON.stringify(session), 300);
  return token;
}

/**
 * Reads owner email from Settings sheet.
 */
function getOwnerEmail() {
  try {
    const settings = readAll('Settings');
    for (let i = 0; i < settings.length; i++) {
      if (settings[i].Key === 'OwnerEmail') return settings[i].Value;
    }
  } catch (e) { /* ignore */ }
  // Fallback to the first Admin user's email
  try {
    const users = readAll('Users');
    for (let i = 0; i < users.length; i++) {
      if (users[i].Role === 'Admin') return users[i].Email;
    }
  } catch (e) { /* ignore */ }
  return null;
}

/**
 * Computes summary numbers for the daily email.
 * Does NOT require a session (called directly by the trigger).
 */
function computeDailySummary() {
  const now = new Date();
  const todayStart = dayStart(now);
  const yesterdayStart = dayStart(addDays(now, -1));
  
  const sales = readAll('Sales');
  const expenses = readAll('Expenses');
  const products = readAll('Products');
  const ledger = readAll('StockLedger');
  
  const costMap = {};
  products.forEach(function(p) { costMap[p.ProductId] = Number(p.CostPrice) || 0; });
  
  let todaySales = 0, yesterdaySales = 0, todayCount = 0;
  let todayDiscount = 0, todayDue = 0;
  sales.forEach(function(s) {
    const t = new Date(s.Date).getTime();
    if (t >= todayStart) {
      todaySales += Number(s.Total) || 0;
      todayDiscount += Number(s.Discount) || 0;
      todayDue += Number(s.Due) || 0;
      todayCount++;
    }
    if (t >= yesterdayStart && t < todayStart) {
      yesterdaySales += Number(s.Total) || 0;
    }
  });
  
  // Compute today's COGS from SaleDetails
  const details = readAll('SaleDetails');
  const todaysInvoices = {};
  sales.forEach(function(s) {
    if (new Date(s.Date).getTime() >= todayStart) todaysInvoices[s.InvoiceNo] = true;
  });
  let todayCOGS = 0;
  details.forEach(function(d) {
    if (todaysInvoices[d.InvoiceNo]) {
      todayCOGS += (Number(d.Qty) || 0) * (costMap[d.ProductId] || 0);
    }
  });
  
  let todayExpenses = 0;
  expenses.forEach(function(e) {
    if (new Date(e.Date).getTime() >= todayStart) {
      todayExpenses += Number(e.Amount) || 0;
    }
  });
  
  // Low stock
  const stockMap = {};
  ledger.forEach(function(l) { stockMap[l.ProductId] = Number(l.Balance) || 0; });
  const lowStock = products.filter(function(p) {
    return p.IsActive === true && Number(p.ReorderLevel) > 0
      && (stockMap[p.ProductId] || 0) <= Number(p.ReorderLevel);
  }).map(function(p) {
    return { name: p.Name, stock: stockMap[p.ProductId] || 0, unit: p.Unit };
  });
  
  const grossProfit = todaySales - todayCOGS;
  const netProfit = grossProfit - todayExpenses;
  
  return {
    date: now,
    todaySales: todaySales,
    yesterdaySales: yesterdaySales,
    invoiceCount: todayCount,
    discount: todayDiscount,
    due: todayDue,
    cogs: todayCOGS,
    grossProfit: grossProfit,
    expenses: todayExpenses,
    netProfit: netProfit,
    lowStock: lowStock
  };
}

/**
 * Builds a beautiful HTML email with KPIs and alerts.
 */
function buildDailyEmailHtml(s) {
  const delta = s.yesterdaySales > 0
    ? ((s.todaySales - s.yesterdaySales) / s.yesterdaySales) * 100 : 0;
  const deltaStr = s.yesterdaySales > 0
    ? (delta >= 0 ? '▲ ' : '▼ ') + Math.abs(delta).toFixed(1) + '% vs yesterday' : '';
  const deltaColor = delta >= 0 ? '#16a34a' : '#dc2626';
  
  let lowStockHtml = '';
  if (s.lowStock.length) {
    lowStockHtml = '<h3 style="color:#dc2626;font-size:15px;margin:20px 0 8px;">⚠️ Low Stock Alerts</h3>'
      + '<table style="width:100%;border-collapse:collapse;font-size:13px;">'
      + s.lowStock.slice(0, 8).map(function(p) {
          return '<tr><td style="padding:6px;border-bottom:1px solid #e2e8f0;">'
            + p.name + '</td><td style="padding:6px;border-bottom:1px solid #e2e8f0;text-align:right;color:#dc2626;font-weight:700;">'
            + p.stock + ' ' + p.unit + '</td></tr>';
        }).join('')
      + '</table>';
  }
  
  return
    '<div style="font-family:Segoe UI,Roboto,sans-serif;max-width:640px;margin:0 auto;background:#fff;border-radius:12px;overflow:hidden;border:1px solid #e2e8f0;">'
    + '<div style="background:linear-gradient(135deg,#6d28d9,#06b6d4);padding:22px 24px;color:#fff;">'
    + '<div style="font-size:12px;opacity:.9;letter-spacing:.4px;">' + COMPANY_NAME + '</div>'
    + '<div style="font-size:22px;font-weight:800;margin-top:4px;">Daily Report</div>'
    + '<div style="font-size:13px;opacity:.9;margin-top:4px;">'
    + Utilities.formatDate(s.date, Session.getScriptTimeZone(), 'EEEE, dd MMMM yyyy') + '</div>'
    + '</div>'
    + '<div style="padding:22px 24px;">'
    + '<table style="width:100%;border-collapse:separate;border-spacing:8px;"><tr>'
    + '<td style="background:#f5f3ff;padding:12px;border-radius:10px;text-align:center;">'
    + '<div style="font-size:11px;color:#64748b;font-weight:700;">SALES</div>'
    + '<div style="font-size:20px;font-weight:800;color:#0e7490;">৳ ' + formatMoney(s.todaySales) + '</div>'
    + '<div style="font-size:11px;color:' + deltaColor + ';">' + deltaStr + '</div>'
    + '</td>'
    + '<td style="background:#ecfdf5;padding:12px;border-radius:10px;text-align:center;">'
    + '<div style="font-size:11px;color:#64748b;font-weight:700;">GROSS PROFIT</div>'
    + '<div style="font-size:20px;font-weight:800;color:#16a34a;">৳ ' + formatMoney(s.grossProfit) + '</div>'
    + '<div style="font-size:11px;color:#64748b;">before expenses</div>'
    + '</td>'
    + '<td style="background:#fef3c7;padding:12px;border-radius:10px;text-align:center;">'
    + '<div style="font-size:11px;color:#64748b;font-weight:700;">EXPENSES</div>'
    + '<div style="font-size:20px;font-weight:800;color:#92400e;">৳ ' + formatMoney(s.expenses) + '</div>'
    + '<div style="font-size:11px;color:#64748b;">today</div>'
    + '</td>'
    + '</tr></table>'
    + '<div style="background:#0f172a;color:#fff;padding:14px 18px;border-radius:10px;margin-top:10px;display:flex;justify-content:space-between;">'
    + '<span style="font-weight:700;">NET PROFIT TODAY</span>'
    + '<span style="font-weight:800;font-size:18px;color:#4ade80;">৳ ' + formatMoney(s.netProfit) + '</span>'
    + '</div>'
    + '<div style="margin-top:18px;font-size:13px;color:#475569;">'
    + '<strong>' + s.invoiceCount + '</strong> invoices · Discount ৳ ' + formatMoney(s.discount) + ' · New due ৳ ' + formatMoney(s.due)
    + '</div>'
    + lowStockHtml
    + '<div style="margin-top:24px;padding-top:16px;border-top:1px dashed #cbd5e1;font-size:11.5px;color:#64748b;text-align:center;">'
    + 'Generated automatically by ' + COMPANY_NAME + ' ERP</div>'
    + '</div></div>';
}

/**
 * Builds the monthly P&L email HTML.
 */
function buildPLHtml(pl) {
  const expRows = pl.expenses.byCategory.map(function(c) {
    return '<tr><td style="padding:6px 10px;border-bottom:1px solid #e2e8f0;">'
      + c.category + '</td><td style="padding:6px 10px;border-bottom:1px solid #e2e8f0;text-align:right;">৳ '
      + formatMoney(c.amount) + '</td></tr>';
  }).join('');
  
  return
    '<div style="font-family:Segoe UI,Roboto,sans-serif;max-width:640px;margin:0 auto;background:#fff;border-radius:12px;overflow:hidden;border:1px solid #e2e8f0;">'
    + '<div style="background:linear-gradient(135deg,#6d28d9,#06b6d4);padding:22px 24px;color:#fff;">'
    + '<div style="font-size:12px;opacity:.9;">' + COMPANY_NAME + '</div>'
    + '<div style="font-size:22px;font-weight:800;margin-top:4px;">Profit & Loss — ' + pl.period.label + '</div>'
    + '</div>'
    + '<div style="padding:22px 24px;">'
    + '<table style="width:100%;border-collapse:collapse;font-size:14px;">'
    + '<tr><td style="padding:8px 10px;">Gross Sales</td><td style="padding:8px 10px;text-align:right;">৳ ' + formatMoney(pl.revenue.grossSales) + '</td></tr>'
    + '<tr><td style="padding:8px 10px;">Discounts</td><td style="padding:8px 10px;text-align:right;color:#dc2626;">−৳ ' + formatMoney(pl.revenue.discount) + '</td></tr>'
    + '<tr><td style="padding:8px 10px;">Returns</td><td style="padding:8px 10px;text-align:right;color:#dc2626;">−৳ ' + formatMoney(pl.revenue.returns) + '</td></tr>'
    + '<tr style="background:#f8fafc;font-weight:800;"><td style="padding:10px;">Net Revenue</td><td style="padding:10px;text-align:right;">৳ ' + formatMoney(pl.revenue.net) + '</td></tr>'
    + '<tr><td style="padding:8px 10px;">COGS</td><td style="padding:8px 10px;text-align:right;color:#dc2626;">−৳ ' + formatMoney(pl.cogs.total) + '</td></tr>'
    + '<tr style="background:#f0fdf4;font-weight:800;color:#166534;"><td style="padding:10px;">Gross Profit</td><td style="padding:10px;text-align:right;">৳ ' + formatMoney(pl.grossProfit) + '</td></tr>'
    + '<tr><td colspan="2" style="padding:12px 10px 6px;font-weight:700;color:#475569;font-size:12px;">OPERATING EXPENSES</td></tr>'
    + expRows
    + '<tr style="background:#f1f5f9;font-weight:700;"><td style="padding:10px;">Total Expenses</td><td style="padding:10px;text-align:right;">৳ ' + formatMoney(pl.expenses.total) + '</td></tr>'
    + '</table>'
    + '<div style="background:#0f172a;color:#fff;padding:16px 20px;border-radius:10px;margin-top:16px;display:flex;justify-content:space-between;align-items:center;">'
    + '<span style="font-weight:700;">NET PROFIT</span>'
    + '<span style="font-weight:800;font-size:22px;color:#4ade80;">৳ ' + formatMoney(pl.netProfit) + '</span>'
    + '</div>'
    + '<div style="font-size:12.5px;color:#64748b;margin-top:8px;">Net margin: '
    + pl.netMargin.toFixed(1) + '%</div>'
    + '</div></div>';
}
⚠️
Install triggers ONCE. After pasting the code, run installTriggers() from the Apps Script editor a single time. It will create both the daily and monthly triggers. You can verify them under the Triggers section (clock icon) in the left sidebar. If you redeploy the Web App, triggers persist — but if you install them twice, delete the duplicates.
11

Real Usage Scenarios

Reports and invoices only matter when they change how a shop operates. Here are six real scenarios from Bangladeshi shops using exactly the modules in this Part 4.

🛒

Karim Discovers a Dead Product Line

Mirpur grocery · using getDeadStockReport

Karim runs the dead stock report for the last 30 days. It shows "Organic Honey 500g" — 24 jars, cost ৳380 each = ৳9,120 tied up. Never sold once.

Action taken:

  • Puts honey on a 30% promotional discount
  • Places it at the front counter (impulse buy)
  • Sells out in 3 weeks, freeing ৳9,120 of working capital
💊

Rahman Pharmacy Sends Monthly Hospital Invoices

Dhanmondi · using generateInvoicePdf + GmailApp

Rahman supplies medicines to three nearby clinics on 30-day terms. On the 1st of each month, a script pulls every sale to those clinics from the last month and emails a single PDF invoice consolidating all their purchases.

Result: The clinic's accounts payable team receives a clean invoice on time. Payment cycles shortened from 45 days to 32 days. That's ৳85,000 arriving 13 days earlier every month.

📱

Nasrin Electronics Identifies a Losing Product

Chattogram · using getProductProfitReport

Nasrin runs the product profit report. Every phone model looks fine. But the "Screen Protector - Universal" shows: sold 62 units at ৳50 each = ৳3,100 revenue, cost ৳35 each = ৳2,170 → profit ৳930. But her staff spent 3 hours applying them, and 8 cracked during application (free replacements).

Action taken: She raises the price to ৳80, and switches to tempered glass protectors with fewer application failures. Margin jumps from 30% to 55%.

👗

Fatema Boutique Catches a Saree Return Pattern

Sylhet · using Returns + getReturns

Fatema notices a pattern in her Returns sheet. Three out of ten "Silk Saree - A12" pieces were returned with the note "color different from photo". She investigates.

Root cause: The product photos were taken under warm yellow light; the actual sarees are cooler toned. She re-photographs under neutral daylight, updates the images, and returns drop from 30% to 8%.

🍽️

Rahim Restaurant Uses the 10 PM Email Report

Uttara · using sendDailyReport trigger

Rahim closes the restaurant at 11 PM. By the time he reaches home, his inbox has the daily report. Last week, three consecutive days showed net profit dropping while sales stayed flat.

What he found: The chicken supplier had raised prices by 12% without notice. His COGS was eating profit. He renegotiated with the supplier the next morning, recovering ৳18,000 in monthly profit.

📊

The Owner Prepares for BIDA Loan Application

Any shop · using getProfitAndLoss + PDF export

A shop owner applies for a small business loan from BRAC Bank. The bank asks for 12 months of P&L statements. Because the ERP has been running for a year, she generates 12 P&L PDFs in an afternoon — proof of consistent profit, verifiable expenses, and clean monthly breakdowns.

Without the ERP: she would have spent weeks reconstructing this from cash receipts. With it: 20 minutes.

💡
The bigger pattern: Every scenario above was a moment when the owner saw something they couldn't have seen in a raw spreadsheet. A report is not just data — it is the ability to make a decision. That is the real value of a working ERP.
✓

Part 4 Completion Checklist

Before moving to Part 5, confirm these work:

  • Reports.gs, Expenses.gs, Returns.gs, Invoice.gs, Triggers.gs all saved.
  • The Settings sheet contains ShopName, ShopAddress, ShopPhone, OwnerEmail.
  • Dashboard loads with today's sales, profit, month sales, receivable.
  • Weekly sales chart renders correctly.
  • Payment method pie shows this month's split.
  • Top 5 products bar chart displays profit alongside revenue.
  • Low stock list appears on the dashboard.
  • You can add an expense from the UI and see it on the summary.
  • Expense summary groups by category with percentages.
  • Returns screen works: pick original invoice, select qty, save.
  • Returned quantities restore stock (check StockLedger).
  • Return refunds reduce customer due if "Due Adjust" selected.
  • P&L statement generates for last month with correct numbers.
  • Your Google Docs template has placeholders and a proper table.
  • INVOICE_TEMPLATE_ID is set in Script Properties.
  • You can generate a PDF invoice for any sale and open it in Drive.
  • The invoice PDF has Bengali + English text (if printer supports it).
  • Triggers are installed (verify in the Triggers panel).
  • You received a test daily report email (run sendDailyReport manually).
  • The email renders correctly on your phone and desktop.
🎉
You now have a complete, professional ERP. POS, inventory, purchases, customers, dues, reports, invoices, expenses, returns, P&L, scheduled emails — all in one system, entirely free. This is the level of software that many shops in Bangladesh pay ৳15,000–40,000/month for. Yours runs on a Google account.
Q

Knowledge Check — Interactive Quiz

Eight questions covering Part 4. Test your understanding of reports and invoices.

Part 4 Quiz

Covering reports, invoices, returns, P&L and emails.

Score: 0 / 0
?

Frequently Asked Questions

Do Google Docs PDF invoices count against my Drive storage?
Yes — each generated PDF is a file in Drive, counting toward your 15 GB. A typical invoice PDF is 30–80 KB. A shop generating 200 invoices per month uses about 10 MB — meaning 15 GB lasts decades. If you want to keep storage minimal, add a monthly cleanup trigger that trashes invoices older than 2 years.
Can I email invoices directly to customers?
Yes. After generating the PDF, call GmailApp.sendEmail(customerEmail, subject, body, { attachments: [pdfBlob] }). Free Gmail accounts can send 100 emails per day — plenty for a shop. Add a "customer email" column to the Customers sheet to enable this automatically.
How accurate is the P&L calculation?
For a small shop, accurate enough for decision-making. The two simplifications are: (1) current cost price is used for historical stock valuation, and (2) it does not account for supplier dues that are unpaid (though they are recorded). For a formal tax filing, hand the P&L to your accountant — they will fine-tune it. For day-to-day decisions, it is more than enough.
What if a return happens 3 months after the sale?
Our createReturn does not restrict by date, but you must use the original invoice number. The return is recorded with today's date (not the original sale date), and the current-month P&L takes the hit. This is standard accounting practice — the return belongs to the period when it actually occurred.
Why not just use Google Sheets' built-in charts?
Sheets charts update on the spreadsheet, but they are (a) not visible inside your Web App, (b) not interactive on mobile, and (c) require manual setup. Chart.js renders inside your app, is fully interactive, works beautifully on phones, and reads live data from your backend. Both have their place — Sheets charts for ad-hoc spreadsheet analysis, Chart.js for the owner-facing dashboard.
Do scheduled reports eat into my Apps Script daily quota?
Yes, but minimally. The daily report takes about 2–5 seconds of execution time. The monthly P&L takes 3–8 seconds. Across 30 days, that is under 2 minutes per month — far below the 90-minute daily quota for trigger execution. You could run 20 such reports per day and still have room.
Can I export reports to Excel/CSV?
Yes — Part 5 will add a CSV export button on every report. For now, you can always open the underlying Google Sheet and use File → Download → CSV to export any raw data. The Sheets themselves are the source of truth and portable by design.
What happens if a scheduled trigger fails?
Google sends the script owner (that is, you) an email saying "Trigger failed". The catch block in sendDailyReport prevents partial state. If a failure happens due to a transient Google issue, the next day's run will still work. If it fails repeatedly, check the Apps Script execution log for the error message.
→

What's Coming in Part 5

Part 4 gave you reports and invoices. Part 5 turns the entire system into a real business asset.

  • CSV & Excel exports — download any report or product list for offline use
  • Backup automation — daily full-database backup to a separate Drive folder
  • Multi-branch support — extend the schema to track sales by branch
  • Advanced AI assistant — natural-language queries like "Show me last month's top 3 products"
  • Migration path to SQL Server — what changes and what stays the same
  • WhatsApp & SMS integration — customer notifications via free-tier APIs
  • Bulk imports — onboarding a shop with 500 products in one CSV upload
  • Sales analytics dashboard v2 — YoY comparison, seasonal trends, customer segmentation
  • Password-less login — one-time-code via email for even simpler cashier onboarding
  • Production hardening checklist — the 20 things to verify before going live with a real shop
📚
Before Part 5, run Part 4 with real data for at least a week. Watch the numbers. Notice which reports you actually open and which you skip. Then tell Part 5 what matters most.
📖

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
Follow the complete journey: Foundation → Security → POS → Intelligence → Scale
Tags:

Post a Comment

0 Comments

thanks for your comments!