Build a Multi-Company ERP & POS with Google Sheets + Apps Script
A complete, beginner-to-deployment tutorial — zero hosting cost, zero paid database, real business ready. New architecture: Each company gets its own Google account — perfect data isolation, zero risk of cross-tenant leaks. By the end of this 5-part series you will have a working system with login, POS, stock ledger, invoices, and multi-company support — all built on tools you already have.
Why This Series Exists
Most "free" business software isn't really free. It hides behind trial limits, user caps, or a ₹/$ per month paywall. Small shops in Bangladesh — a pharmacy in Mirpur, a grocery in Sylhet, a boutique in Chattogram — deserve better. They need a system that just works, on a phone, in Bangla or English, without a monthly bill.
Over the next five parts, you'll build exactly that. You will learn real-world software patterns — authentication, session tokens, concurrency locks, multi-tenant data isolation, ledgers, invoice generation — the same patterns used inside enterprise ERPs, but implemented on tools you already have.
What You Will Build (Across All 5 Parts)
Multi-Company Login
Token-based auth that isolates each shop's data completely. One login screen, many companies.
POS / Sale Screen
Search products, add to cart, apply discount, auto-calculate due, save invoice.
Stock Ledger
Never just "Stock" — a proper ledger with IN/OUT/Balance rows for auditability.
PDF Invoices
Template-driven invoices saved to Drive — no paid PDF library needed.
Dashboard & Reports
Today's sales, receivables, low stock — generated straight from your Sheets data.
AI-Ready Architecture
Structured ledger rows = perfect training data. Add a natural-language assistant later with zero refactor.
Why Google Sheets as a Database?
Before we write any code, it's worth answering the question every experienced developer asks first: "Aren't spreadsheets the wrong tool for this?"
The honest answer is: it depends on scale. For a small shop doing 20–200 transactions per day, a spreadsheet is not a compromise — it's actually a sensible choice. Here's why.
✅ Zero Cost
No server, no database license, no cloud bill. You need a Google account. That's it.
✅ Instant Setup
Your "database admin tool" is a spreadsheet you already know how to use. Non-technical staff can inspect data directly.
✅ Built-in Backup & Version History
Google gives you revision history for free — a feature enterprise DBAs charge thousands for.
✅ Everywhere Access
Works from any browser, any phone, any OS. No VPN, no local install.
✅ Perfect for MVP
Launch in a day, learn from real users, then decide what to upgrade. No premature engineering.
✅ Trivial Migration Path
The schema we design in Part 1 maps 1:1 onto SQL Server tables. Copy-paste your thoughts, not your data.
When Google Sheets Is the Wrong Choice
To be responsible teachers, we must tell you where the wall is. Google Sheets starts to hurt when you hit any of these:
| Signal | Threshold (approx.) | What breaks first |
|---|---|---|
| Concurrent writers | 30+ users saving at the same moment | Apps Script LockService timeouts |
| Rows in a single sheet | ~50,000 – 100,000 | Read/write latency grows noticeably |
| Apps Script execution | 6 minutes per call | Heavy report generation fails |
| Daily triggers | 90 minutes total | Scheduled jobs get cut off |
| Companies in one Master Sheet | ~100 companies | Master lookups slow down |
apiCall() wrapper, migrating to SQL Server later becomes a one-file change — literally.
The New Multi-Company Architecture: One Company = One Google Account
Why This Change Matters
In the previous design, all companies' data lived under one Google account (yours). A filter bug, a bad query, or a misconfigured permission could — in theory — expose one company's data to another. With the new architecture, that risk disappears entirely.
Here is how it works:
How Company Routing Works
The frontend needs to know which company a user belongs to, so it can call the correct Apps Script backend. We solve this with a simple company registry — a small JSON file or a configuration object.
/**
* Company Registry
* Maps a company ID to its Apps Script Web App URL.
* When a user logs in, we look up their CompanyId here
* and route all API calls to the correct backend.
*/
const COMPANY_REGISTRY = {
'C-DEMO-001': {
name: 'Demo Store',
backendUrl: 'https://script.google.com/macros/s/AKfycb.../exec'
},
'C-DEMO-002': {
name: 'Rahman Pharmacy',
backendUrl: 'https://script.google.com/macros/s/BKfycb.../exec'
}
// Add more companies here as they onboard
};
/**
* Returns the backend URL for a given company.
* Throws if the company is not registered.
*/
function getBackendUrl(companyId) {
const company = COMPANY_REGISTRY[companyId];
if (!company) {
throw new Error('Company not registered: ' + companyId);
}
return company.backendUrl;
}Advantages of the New Architecture
| Aspect | Old Architecture | New Architecture |
|---|---|---|
| Data isolation | Logical (filter by CompanyId) | Physical — separate Google accounts |
| Security risk | Code bug could leak data | Zero risk — accounts cannot see each other |
| Storage quota | Shared 15 GB across all companies | 15 GB per company |
| Apps Script quota | Shared daily limits | Independent limits per company |
| Onboarding | You create a spreadsheet per company | Company owner creates their own account |
| Migration to SQL | Migrate all at once | Migrate per company independently |
Trade-offs to Be Aware Of
- You cannot run cross-company reports from a single dashboard. If you need to see "total sales across all companies," you would build a separate reporting system that pulls from each company's API.
- Onboarding is slightly more involved — each company must set up its own Google account and Apps Script project. We provide a setup guide (this Part 1) that they follow.
- Updating code across companies requires re-deploying each company's Apps Script. We mitigate this by keeping the backend logic identical and versioned in a shared GitHub repository.
Understanding the Google Ecosystem
Before we dive into code, let's build a mental model of every Google service you'll use. This is not trivia — understanding why each piece exists will make the rest of this series feel obvious.
Gmail
Your identity in the Google ecosystem. Every Google account starts here. The email address is your login name across all Google services.
Google Drive
Cloud file storage. 15 GB free with every account. Stores your spreadsheets, document templates, PDF invoices, and backups.
Google Docs
Word processor used as an invoice template. You design a document once with placeholders, and Apps Script fills it with real data.
Google Sheets
Spreadsheet used as your database. Each sheet (tab) becomes a table. Rows become records. Columns become fields.
Apps Script
Serverless JavaScript runtime. Your backend API. It reads/writes Sheets, generates Docs, sends Gmail, and serves HTML.
Google Cloud Console
Advanced API management. Used for OAuth consent screens, API keys, and enabling advanced services like Drive API v3.
How the Pieces Fit Together
What "Serverless" Really Means
When you write Apps Script, you never think about servers. There is no machine to provision, no operating system to patch, no uptime to monitor. Google runs your code when a user triggers it, then shuts it down. You pay nothing for idle time because there is no idle time.
This is the same model as AWS Lambda or Azure Functions — except Apps Script is free for consumer accounts and comes pre-authenticated with your Google identity.
let, const, arrow functions, template literals, promises, destructuring, async/await — works in Apps Script today.
Setting Up Your Google Account
If you already have a Gmail account, you can use it for development. But for a real business, we strongly recommend creating a dedicated Google account. Here is why and how.
Why a Dedicated Account?
- Separation of concerns: Your personal email and your business data live in different accounts. If one is compromised, the other is safe.
- Cleaner sharing: When you share the ERP with employees, you share the business account — not your personal one.
- Easier transfer: If you sell the business, you transfer the Google account. No personal data leaks.
- Professional appearance: Invoices and emails come from a business-sounding address.
Step-by-Step: Creating a Business Google Account
Open the Google Account creation page
Go to accounts.google.com/signup in your browser. You can also click "Create account" from the Gmail login page.
Choose "For work or my business"
Google will ask whether the account is for personal use or work/business. Choose For work or my business. This unlocks slightly different defaults (though the free tier is the same).
Enter your name and desired email address
Use a name that represents the business, e.g., Rahman Store and rahmanstore.erp@gmail.com. If the address is taken, try variations like rahmanstore.bd or rahmanstore2026.
Create a strong password
Use at least 12 characters with a mix of letters, numbers, and symbols. Store it in a password manager. Never reuse a password from another service.
Add a recovery phone number and email
This is critical. If you lose access to the account, recovery is your only way back in. Use a phone number you control and a secondary email address.
Enable 2-Step Verification
After creating the account, go to myaccount.google.com → Security → 2-Step Verification and turn it on. Use the Google Authenticator app or SMS. This prevents 99% of account takeover attempts.
Free Tier Limits (What You Get for Free)
| Service | Free Limit | What It Means for Your ERP |
|---|---|---|
| Google Drive storage | 15 GB (shared across Drive, Gmail, Photos) | Enough for thousands of PDF invoices and spreadsheet data. Text data uses almost no space. |
| Google Sheets | 10 million cells per spreadsheet | Way more than a small shop will ever need. A million sales records is ~5 million cells. |
| Apps Script execution | 6 minutes per execution | Enough for any single POS transaction or report generation. |
| Apps Script daily trigger | 90 minutes total per day | Enough for scheduled reports and backups. |
| Gmail sending | 100 recipients per day | Enough for sending invoices to customers. |
| UrlFetchApp calls | 20,000 per day | Enough for any external API integration. |
Deep Dive: Google Apps Script
Apps Script is the engine room of your ERP. Before you write a single line, understand what it is, what it can do, and — equally important — what it cannot do.
What Is Apps Script?
Google Apps Script is a serverless JavaScript runtime that lives inside Google's infrastructure. It was originally created in 2009 as a way to automate Google Sheets. Today it is a full-fledged application platform that can:
- Serve HTML web apps accessible from any browser
- Read and write Google Sheets, Docs, Slides, and Forms
- Send and receive Gmail messages
- Create, move, and delete Google Drive files
- Call external REST APIs via
UrlFetchApp - Run on time-based triggers (cron jobs) or event triggers (form submissions, sheet edits)
- Integrate with Google Cloud services like BigQuery, Vertex AI, and Maps
The V8 Runtime
Since 2020, Apps Script runs on the V8 JavaScript engine — the same engine that powers Chrome and Node.js[reference:0]. This means you get:
- Modern syntax:
let,const, arrow functions, template literals, destructuring, spread operators - ES6+ features: Classes, promises, generators,
Map,Set,Symbol - Async patterns:
async/await(though Apps Script services are synchronous — more on this below) - Better performance: V8 is significantly faster than the old Rhino runtime
import and export statements in Apps Script. All .gs files in a project share a global scope. This is why we use plain function declarations and a flat file structure. It is a limitation, but a manageable one.
The Execution Model
Understanding how Apps Script executes is critical for writing efficient code. Here is the lifecycle of a typical request:
CacheService, PropertiesService, or a Sheet. This is why we use session tokens (Part 2).
Apps Script Quotas and Limits (2026 Reference)
Every free Google account has hard limits on Apps Script usage. Exceeding them throws errors like "Service invoked too many times" or "Exceeded maximum execution time"[reference:1]. Here is the complete reference for consumer (Gmail) accounts:
| Quota | Free (Gmail) Limit | Mitigation Strategy |
|---|---|---|
| Script runtime per execution | 6 minutes | Process in batches; use continuation triggers for long jobs |
| Daily trigger runtime | 90 minutes total | Schedule heavy jobs at night; combine operations |
| UrlFetchApp calls per day | 20,000 | Cache external data; poll less frequently |
| Email recipients per day | 100 | Batch emails; use Google Chat for alerts |
| Simultaneous executions | ~30 | Use LockService; design for queueing |
| Script project size | 50 MB | Keep code lean; store large data in Sheets/Drive |
| Triggers per project | 20 per user per script | Audit triggers; delete orphans |
| CacheService entries | 1,000 per script | Clean expired tokens; use short TTLs |
Triggers: How Apps Script Runs Automatically
There are two kinds of triggers in Apps Script:
| Trigger Type | How It Fires | Use Case in Our ERP |
|---|---|---|
| Simple triggers | Automatically on edit, open, install, or form submit | onOpen(e) to add a custom menu to the spreadsheet |
| Installable triggers | Created programmatically or via the Triggers UI | Time-driven trigger for daily backup; onEdit for stock alerts |
| Time-driven triggers | Run every N minutes/hours/days | Nightly report generation; low-stock email alerts |
For our ERP, we will primarily use time-driven triggers for scheduled tasks and installable onEdit triggers for reactive logic. Simple triggers are useful for spreadsheet menus but cannot access external services without authorisation[reference:2].
LockService: Preventing Race Conditions
Imagine two cashiers clicking "Save Sale" at the exact same millisecond. Both scripts read the last row of the Sales sheet, both get the same invoice number, both write their rows — and now you have two different sales with the same invoice number. This is a race condition, and it is one of the most common bugs in multi-user spreadsheet apps.
LockService solves this[reference:3]. It creates a mutex (mutual exclusion) around a block of code. Only one execution can hold the lock at a time. Everyone else waits in line.
function createSale(token, saleData) {
const user = validateSession(token);
const lock = LockService.getScriptLock();
try {
// Wait up to 15 seconds for other writers to finish
lock.waitLock(15000);
// === CRITICAL SECTION ===
// Only ONE execution can be here at a time.
// Read last invoice number, increment, write.
// Without this lock, two cashiers could get the same number.
const invoiceNo = generateInvoiceNo(companySs);
salesSheet.appendRow([...]);
// ... write sale details, update stock ledger ...
return { success: true, invoiceNo: invoiceNo };
} finally {
// ALWAYS release the lock, even if an error occurs
lock.releaseLock();
}
}try/finally with locks. If you acquire a lock and then an error occurs without releasing it, every subsequent request will hang until the lock times out. The finally block guarantees the lock is released no matter what happens.
CacheService: Fast Temporary Storage
CacheService is a key-value store that lives for a short time (up to 6 hours). It is perfect for session tokens, temporary calculations, and reducing repeated Sheet reads.
// Store a session token for 6 hours
const cache = CacheService.getScriptCache();
cache.put(token, JSON.stringify(userData), 21600);
// Retrieve it
const cached = cache.get(token);
if (cached) {
const user = JSON.parse(cached);
// User is authenticated
}
// Remove it on logout
cache.remove(token);PropertiesService: Persistent Configuration
PropertiesService stores key-value pairs that persist indefinitely. Use it for configuration that should not be hardcoded: spreadsheet IDs, API keys, feature flags.
// In Apps Script editor: Project Settings → Script Properties
// Add: MASTER_SHEET_ID = 1aBcD...
// Add: ENCRYPTION_KEY = your-long-random-string
// Read them in code
const sheetId = PropertiesService
.getScriptProperties()
.getProperty('MASTER_SHEET_ID');The Apps Script File Structure
A well-organised Apps Script project looks like this:
Every .gs file shares the same global scope. You can call uuid() from Auth.gs even though it is defined in Utils.gs. This is different from Node.js modules, but it is how Apps Script works.
Setting Up the Spreadsheet Database
In the new architecture, each company has its own Google account and therefore its own spreadsheet. There is no "Master Spreadsheet" anymore — the company spreadsheet is the database.
Step-by-Step: Creating the Company Spreadsheet
Create a new Google Sheet
Log in to the company's Google account. Go to sheets.new in your browser. Rename the spreadsheet to ERP_Database.
Create the required tabs
Rename the default tab to Products. Then add the following tabs by clicking the + button at the bottom:
SalesSaleDetailsStockLedgerCustomersSuppliersPurchasesPurchaseDetailsExpensesUsersSettings
Add header rows to every sheet
Type the exact headers listed in the next section. Capitalisation matters — the code reads them by column index, but you will thank yourself later for consistency.
Freeze the header row
In every sheet: View → Freeze → 1 row. This prevents accidental data overwrites and makes long files readable.
Copy the Spreadsheet ID
From the URL docs.google.com/spreadsheets/d/THIS_PART/edit — copy the long ID between /d/ and /edit. You will paste this into Script Properties in the next section.
Sheet-by-Sheet Schema
This is the complete database schema. Every column, every data type, every purpose.
| # | Header | Type | Notes |
|---|---|---|---|
| A | ProductId | string | UUID, generated at creation |
| B | Name | string | Product display name |
| C | Category | string | Free-text category |
| D | Unit | string | pcs / kg / litre |
| E | CostPrice | number | Purchase price |
| F | SalePrice | number | Selling price |
| G | IsActive | boolean | Soft-disable without deleting |
| H | ReorderLevel | number | For low-stock alerts |
| I | CreatedAt | datetime | When the product was added |
| J | Barcode | string | Optional barcode number |
| # | Header | Type | Notes |
|---|---|---|---|
| A | InvoiceNo | string | e.g. INV-2026-00001 |
| B | Date | datetime | Server time of sale |
| C | CustomerId | string | "WALK-IN" for cash sales |
| D | SubTotal | number | Before discount |
| E | Discount | number | Absolute amount |
| F | Total | number | SubTotal − Discount |
| G | Paid | number | Amount received now |
| H | Due | number | Total − Paid |
| I | UserId | string | Email of cashier |
| J | PaymentMethod | string | Cash / bKash / Card |
| # | Header | Type | Notes |
|---|---|---|---|
| A | InvoiceNo | string | FK to Sales |
| B | ProductId | string | FK to Products |
| C | Qty | number | Sold quantity |
| D | UnitPrice | number | Price at the time of sale |
| E | LineTotal | number | Qty × UnitPrice |
| F | Discount | number | Per-line discount (optional) |
| # | Header | Type | Notes |
|---|---|---|---|
| A | Date | datetime | When the movement happened |
| B | ProductId | string | Which product |
| C | Type | string | PURCHASE / SALE / ADJUST / RETURN |
| D | RefNo | string | Invoice or GRN number |
| E | QtyIn | number | Positive for stock coming in |
| F | QtyOut | number | Positive for stock going out |
| G | Balance | number | Running balance after this row |
| H | Note | string | Reason or reference |
| # | Header | Type | Notes |
|---|---|---|---|
| A | CustomerId | string | UUID |
| B | Name | string | Full name |
| C | Mobile | string | For SMS later |
| D | Address | string | Free text |
| E | OpeningBalance | number | Due carried forward |
| F | CreatedAt | datetime | When the customer was added |
| # | Header | Type | Notes |
|---|---|---|---|
| A | SupplierId | string | UUID |
| B | Name | string | Supplier name |
| C | Mobile | string | Contact number |
| D | Address | string | Free text |
| E | OpeningBalance | number | Amount payable |
| # | Header | Type | Notes |
|---|---|---|---|
| A | GRNNo | string | Goods Received Note number |
| B | Date | datetime | When the purchase was received |
| C | SupplierId | string | FK to Suppliers |
| D | Total | number | Total purchase amount |
| E | Paid | number | Amount paid |
| F | Due | number | Total − Paid |
| G | UserId | string | Who recorded the purchase |
| # | Header | Type | Notes |
|---|---|---|---|
| A | Date | datetime | When incurred |
| B | Category | string | Rent / Electricity / Salary |
| C | Amount | number | Positive number |
| D | Note | string | Free text |
| E | UserId | string | Who recorded the expense |
| # | Header | Type | Notes |
|---|---|---|---|
| A | string | Login identifier | |
| B | Name | string | Display name |
| C | Role | string | Admin / Cashier / Manager |
| D | PasswordHash | string | HMAC-SHA256 hex |
| E | IsActive | boolean | Suspend without deleting |
| F | CreatedAt | datetime | Audit trail |
| G | LastLogin | datetime | Last successful login |
| # | Header | Type | Notes |
|---|---|---|---|
| A | Key | string | Setting name |
| B | Value | string | Setting value |
| C | Description | string | What this setting does |
Sample Data to Insert
Before we write code, add some sample data so you can test. Copy these rows into the corresponding sheets.
ProductId | Name | Category | Unit | CostPrice | SalePrice | IsActive | ReorderLevel | CreatedAt | Barcode
P001 | Rice 5kg | Grocery | bag | 420 | 500 | TRUE | 20 | 2026-09-10 10:00:00| 8901234567890
P002 | Soybean Oil 2L | Grocery | pcs | 290 | 350 | TRUE | 15 | 2026-09-10 10:00:00| 8901234567891
P003 | Sugar 1kg | Grocery | kg | 110 | 130 | TRUE | 30 | 2026-09-10 10:00:00| 8901234567892
P004 | Flour 2kg | Grocery | bag | 95 | 120 | TRUE | 25 | 2026-09-10 10:00:00| 8901234567893
P005 | Lentils 1kg | Grocery | kg | 130 | 160 | TRUE | 20 | 2026-09-10 10:00:00| 8901234567894Email | Name | Role | PasswordHash | IsActive | CreatedAt | LastLogin
owner@store.com | Owner | Admin | (see Part 2) | TRUE | 2026-09-10 10:00:00|
cashier1@store.com | Karim Uddin | Cashier | (see Part 2) | TRUE | 2026-09-10 10:00:00|
manager@store.com | Rahim Ahmed | Manager | (see Part 2) | TRUE | 2026-09-10 10:00:00| In Part 2, we will generate the actual password hashes. For now, leave the PasswordHash column empty.
First Code — Config.gs & Utils.gs
Now it's time to write real code. Open the Apps Script editor from your company spreadsheet: Extensions → Apps Script.
Setting Up Script Properties
Before writing code, configure your environment. In the Apps Script editor:
- Click the ⚙️ Project Settings icon in the left sidebar.
- Scroll to Script Properties.
- Add the following properties:
| Property | Value | Purpose |
|---|---|---|
SHEET_ID | (paste your spreadsheet ID) | Identifies the database file |
ENCRYPTION_KEY | (a long random string) | For password hashing |
COMPANY_ID | C-DEMO-001 | Your company identifier |
COMPANY_NAME | Demo Store | Display name |
random.org/strings or with a simple Apps Script function: Utilities.getUuid() + Utilities.getUuid(). Store it in a password manager. If you lose it, you cannot regenerate the same password hashes.
Config.gs — The Foundation
Open Code.gs and rename it to Config.gs. Paste the following code.
/**
* Config.gs
* Central access to script properties and spreadsheet handles.
* Never hardcode IDs. Never hardcode keys.
*
* ARCHITECTURE: One company = one Google account = one spreadsheet.
* This file runs inside that company's Apps Script project.
*/
// --- Read from Script Properties (never hardcode these) ---
const SHEET_ID = PropertiesService
.getScriptProperties()
.getProperty('SHEET_ID');
const ENCRYPTION_KEY = PropertiesService
.getScriptProperties()
.getProperty('ENCRYPTION_KEY');
const COMPANY_ID = PropertiesService
.getScriptProperties()
.getProperty('COMPANY_ID');
const COMPANY_NAME = PropertiesService
.getScriptProperties()
.getProperty('COMPANY_NAME');
// --- Validate configuration on load ---
if (!SHEET_ID) throw new Error('SHEET_ID is not set in Script Properties.');
if (!ENCRYPTION_KEY) throw new Error('ENCRYPTION_KEY is not set in Script Properties.');
/**
* Returns a handle to the company's spreadsheet.
* @returns {GoogleAppsScript.Spreadsheet.Spreadsheet}
*/
function getSpreadsheet() {
return SpreadsheetApp.openById(SHEET_ID);
}
/**
* Returns the first sheet with the given name, or throws.
* A tiny helper that saves a lot of null-checks.
* @param {string} name
* @returns {GoogleAppsScript.Spreadsheet.Sheet}
*/
function sheet(name) {
const s = getSpreadsheet().getSheetByName(name);
if (!s) throw new Error('Missing sheet: ' + name);
return s;
}
/**
* Reads all rows from a sheet as an array of objects.
* Assumes row 1 contains headers.
* @param {string} sheetName
* @returns {Array<Object>}
*/
function readAll(sheetName) {
const s = sheet(sheetName);
const values = s.getDataRange().getValues();
if (values.length < 2) return [];
const headers = values[0];
const rows = [];
for (let i = 1; i < values.length; i++) {
const obj = {};
for (let j = 0; j < headers.length; j++) {
obj[headers[j]] = values[i][j];
}
rows.push(obj);
}
return rows;
}
/**
* Appends a single row to a sheet.
* @param {string} sheetName
* @param {Array} row
*/
function appendRow(sheetName, row) {
sheet(sheetName).appendRow(row);
}Utils.gs — Reusable Helpers
Create a new file called Utils.gs (click the + next to "Files" and choose "Script"). Paste this code.
/**
* Utils.gs
* Reusable helpers used across the entire backend.
*/
/**
* Generates a URL-safe unique ID.
* @returns {string}
*/
function uuid() {
return Utilities.getUuid().replace(/-/g, '').substring(0, 12);
}
/**
* Writes a batch of rows starting at the bottom of a sheet.
* Much faster than calling appendRow in a loop.
* @param {GoogleAppsScript.Spreadsheet.Sheet} s
* @param {Array<Array>} rows
*/
function appendRows(s, rows) {
if (!rows || rows.length === 0) return;
s.getRange(s.getLastRow() + 1, 1, rows.length, rows[0].length)
.setValues(rows);
}
/**
* Trims a string safely.
* @param {*} v
* @returns {string}
*/
function trim(v) {
return (v == null ? '' : String(v)).trim();
}
/**
* Formats a number to 2 decimals (for currency-ish output).
* @param {number} n
* @returns {string}
*/
function money(n) {
return Number(n || 0).toFixed(2);
}
/**
* Generates a sequential invoice number.
* Reads the Sales sheet to find the last invoice number.
* MUST be called inside a LockService lock.
* @returns {string}
*/
function nextInvoiceNo() {
const s = sheet('Sales');
const lastRow = s.getLastRow();
const year = new Date().getFullYear();
if (lastRow <= 1) return 'INV-' + year + '-00001';
const lastInvoice = s.getRange(lastRow, 1).getValue();
const parts = String(lastInvoice).split('-');
const lastNum = parseInt(parts[2], 10) || 0;
const nextNum = (lastNum + 1).toString().padStart(5, '0');
return 'INV-' + year + '-' + nextNum;
}
/**
* Wraps a handler so the frontend always gets a consistent
* { success, data, error } shape.
* @param {Function} fn
* @returns {Function}
*/
function safe(fn) {
return function () {
try {
const data = fn.apply(null, arguments);
return { success: true, data: data };
} catch (e) {
console.error(e);
return { success: false, error: e.message };
}
};
}
/**
* Logs an action for auditing.
* @param {string} email
* @param {string} action
*/
function logAction(email, action) {
try {
appendRow('AuditLog', [new Date(), email, action]);
} catch (e) {
console.error('Audit log failed:', e);
}
}safe()? Frontend code should never have to worry about whether the server threw a JavaScript Error or returned undefined. A consistent envelope — { success, data } | { success, error } — makes every .then() handler trivial to write.
Creating the AuditLog Sheet
The logAction() function writes to an AuditLog sheet. If you have not created it yet, add a new tab called AuditLog with these headers:
| # | Header | Purpose |
|---|---|---|
| A | Timestamp | When the action happened |
| B | Which user | |
| C | Action | e.g. LOGIN, SALE_CREATED_INV-2026-00001 |
Testing Your Setup
Before moving on, prove that everything works. Add a temporary test function to Config.gs.
function testConfig() {
Logger.log('=== Configuration Test ===');
Logger.log('Company: ' + COMPANY_NAME + ' (' + COMPANY_ID + ')');
Logger.log('Spreadsheet: ' + getSpreadsheet().getName());
Logger.log('Sheets found:');
const sheets = getSpreadsheet().getSheets();
sheets.forEach(function(s) {
Logger.log(' - ' + s.getName() + ' (' + s.getLastRow() + ' rows)');
});
}
function testReadProducts() {
Logger.log('=== Reading Products ===');
const products = readAll('Products');
Logger.log('Found ' + products.length + ' products:');
products.forEach(function(p) {
Logger.log(' ' + p.ProductId + ': ' + p.Name + ' — ৳' + p.SalePrice);
});
}
function testUtils() {
Logger.log('=== Utils Test ===');
Logger.log('UUID: ' + uuid());
Logger.log('Money: ' + money(1234.5));
Logger.log('Next invoice: ' + nextInvoiceNo());
Logger.log('Trim: "' + trim(' hello ') + '"');
}Save the file (Ctrl+S / Cmd+S). Then:
- Select
testConfigfrom the function dropdown at the top. - Click Run.
- Google will ask for authorisation. Click Review permissions → choose your account → Advanced → Go to [project name] (unsafe) → Allow. This is normal for your own scripts.
- Open View → Execution log.
You should see something like:
=== Configuration Test ===
Company: Demo Store (C-DEMO-001)
Spreadsheet: ERP_Database
Sheets found:
- Products (6 rows)
- Sales (1 row)
- SaleDetails (1 row)
- StockLedger (1 row)
- Customers (1 row)
- Suppliers (1 row)
- Purchases (1 row)
- PurchaseDetails (1 row)
- Expenses (1 row)
- Users (4 rows)
- Settings (1 row)
- AuditLog (1 row)Then run testReadProducts and testUtils. You should see your five sample products and a generated invoice number like INV-2026-00001.
Troubleshooting Common Issues
| Error | Cause | Fix |
|---|---|---|
Missing sheet: Products |
Sheet name is misspelled or does not exist | Check the exact tab name. Apps Script is case-sensitive. |
SHEET_ID is not set |
Script Properties not configured | Go to Project Settings → Script Properties and add SHEET_ID. |
You do not have permission to call openById |
You are not authorised yet | Run the function again and complete the authorisation flow. |
Cannot read property 'length' of undefined |
A sheet has no headers or is empty | Ensure every sheet has headers in row 1. |
Exceeded maximum execution time |
A loop is too large or an API call is hanging | Check your network; reduce batch size; add logging to find the bottleneck. |
Knowledge Check — Interactive Quiz
Eight quick questions. The questions and answers are stored as a JavaScript array — a pattern you will reuse later for product catalogs, FAQ schemas, and AI prompt libraries.
Part 1 Quiz
Tap an answer to check it instantly. Your score updates as you go.
Frequently Asked Questions
LockService.getScriptLock(), which serialises concurrent writers. We cover this in detail in Part 3. Google allows up to ~30 simultaneous Apps Script executions, which comfortably covers small and mid-sized shops.
apiCall() wrapper, migrating means pointing it at a new URL — an ASP.NET Core Web API, a Node.js service, anything. Your database schema (designed in Part 1) becomes a set of SQL tables with almost no changes.
.gs files in a project share a single global scope. This is a limitation of the platform. We work around it with plain function declarations and a flat, well-organised file structure.
clasp (Command Line Apps Script Projects) allow you to script this: store the code in a GitHub repository, then run a loop that deploys to each company's script ID. We cover this in Part 5.
What's Coming in Part 2
Part 1 gave you the database and the empty backend. Part 2 turns it into a living, login-protected multi-user system.
- Password hashing with HMAC-SHA256 (no bcrypt library needed)
- Session tokens stored in
CacheService - The complete
Auth.gsmodule —login(),logout(),validateSession() - An HTML login page wired to the backend
- Role-based access (Admin / Cashier / Manager)
- Audit logging on every privileged action
- A user-management UI for shop owners to add staff
- Deploying the web app and sharing the URL with employees
More Free Resources on FreeLearning365
This tutorial is part of a growing library. Here are hand-picked resources that pair well with the skills you are building right now.
 | FreeLearning365.com — Part 1 of 5){kind=link}
0 Comments
thanks for your comments!