App Script Authentication, Login UI, Session Tokens & Deployment 100% Free Stack | FreeLearning365.com — Part 2 of 5

September 10, 2026
Authentication, Login UI, Session Tokens & Deployment 100% Free Stack | FreeLearning365.com — Part 2 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 2 of 5 · Authentication & Deployment

Secure Login, Sessions & Deployment for Your Bangladeshi Shop ERP

In Part 1 we built the database. Now we make it alive and protected: real password hashing, six-hour session tokens, role-based access for Admin / Manager / Cashier, an audit trail, and a beautiful Bangla/English login screen — all deployed to the web for free.

HMAC-SHA256 Hashing 6-Hour Sessions 3 Roles Access
★

A Shop Is a Business — Treat Its Data Like Money

Let's start with a story. A grocery shop in Mirpur, Dhaka, has three employees: the owner (Karim), the manager (Rahim), and two cashiers (Nasrin and Fatema). Every day, thousands of taka flow through the POS. Before today, all of that data lived in a spreadsheet that anyone could open, edit, or delete.

That's fine while the business is one person behind one counter. It is not fine the moment a second employee joins. Rahim the manager should be able to run reports but not silently change a sale. Nasrin the cashier should be able to bill customers but not see the day's profit margin. Karim the owner should be able to add or remove staff without giving away his Google password.

That's what authentication gives you — the ability to trust people with parts of the business without trusting them with all of it.

Protect the money

Every sale is recorded with the cashier's email. No more anonymous cash adjustments.

Trust your team

Give the cashier exactly what they need — nothing more. No accidental deletion of the Products sheet.

See what happened

Every login, sale, and edit is logged with a timestamp. If stock doesn't add up, you can trace who did what.

Safe if a phone is lost

Change one password, and the lost device is locked out at its next session expiry.

🇧🇩
Built for Bangladesh. All examples use ৳ Taka, Bangladeshi names, and local scenarios. Roles reflect real shop hierarchies — মালিক (owner/admin), ম্যানেজার (manager), ক্যাশিয়ার (cashier). The UI supports both Bangla and English.
01

What You Will Build in Part 2

Password Hashing

HMAC-SHA256 with a secret key. Even if someone sees the sheet, they cannot read passwords.

6-Hour Session Tokens

Token-based auth using CacheService. Fast, expiring, no cookies needed.

Role-Based Access

Admin, Manager, Cashier — each with a clear permission matrix enforced on the server.

Login UI

Beautiful Bangla/English login page with error handling and loading states.

User Management

Owner-facing UI to add, disable, and change roles of employees.

Web App Deployment

One-click deployment as a web app. Share one URL with all staff.

1

Why Authentication Matters for a Bangladeshi Shop

Most tutorials treat authentication as a checkbox: "log in, get a token, done." That is not how real shops think about it. Real shop owners ask different questions.

Owner's QuestionWhat They Really MeanHow Auth Solves It
"How do I know who sold this?" If a sale is wrong, they want to trace it. Every sale records the cashier's email from the session.
"Can my cashier change prices?" They worry about fake discounts. Cashier role cannot access price editing; only Admin/Manager can.
"What if someone steals my phone?" They want a way to lock the thief out fast. Change password → next request fails → thief is out.
"Can I fire someone without losing their sales history?" They want to disable access, not delete data. Set IsActive = FALSE for the user. History remains intact.
"What if two cashiers share one login?" They want accountability. Each employee gets a unique email and password.

The Three Employees of a Typical Shop

When you walk into a shop in Mirpur, Dhanmondi, or Chattogram, you usually see the same structure:

ADMIN

মালিক — Owner

Full access. Creates users, sets prices, views profit reports, closes the day. Usually 1 person per shop.

MANAGER

ম্যানেজার — Manager

Runs reports, records purchases, manages stock. Cannot add/remove users. Usually 1–2 people.

CASHIER

ক্যাশিয়ার — Cashier

Bills customers, records sales, prints invoices. Cannot see profit, cannot change prices. Usually 2–5 people.

🎯
Design principle: Roles are about actions, not screens. A Cashier is not "the person who sees fewer buttons" — they are "the person who is not allowed to perform admin operations." We enforce this on the server, not just the UI. The UI hides buttons for clarity, but the API always re-checks.

Realistic Threat Model for a Small Shop

You are not a bank. You do not need to defend against nation-state attackers. But you do need to defend against:

  • The curious employee who wants to see today's profit or edit yesterday's sale.
  • The former employee who still has a phone with the app installed.
  • The public Wi-Fi snoop in a shopping mall or restaurant.
  • The shared browser on the shop counter where anyone could hit the back button.
  • The accidental deletion — a cashier opening the Products sheet and deleting a row.

Everything in this Part 2 is designed to defend against these five threats. Nothing is designed to defend against the NSA. If you are a chain of 500 stores, you need SQL Server and a real security audit — that's Part 5.

2

The Complete Login Flow (Step by Step)

Before writing code, walk through the flow. Every arrow in this diagram corresponds to one line of code you will write.

STAGE 1 — USER ARRIVES ───────────────────────────────────────────── User opens the Web App URL on their phone or laptop. │ ▼ Apps Script serves Login.html (doGet → 'Login'). │ ▼ User sees: company name, email field, password field, [লগইন / Login] button. │ STAGE 2 — USER SUBMITS CREDENTIALS ───────────────────────────────────────────── User types: karim@demo.bd / MyShop@2026 │ ▼ Frontend calls: google.script.run.login(email, password) │ ▼ Apps Script login() function runs: 1. Read all rows from Users sheet. 2. Find the row where Email matches. 3. Hash the input password with HMAC-SHA256 + ENCRYPTION_KEY. 4. Compare the hash with the stored hash. 5. If IsActive is FALSE → reject. 6. Generate a new UUID token. 7. Store in CacheService: token → { email, role, name } with TTL = 6 hours (21,600 seconds). 8. Update LastLogin timestamp in the Users sheet. 9. Write to AuditLog: "LOGIN karim@demo.bd". 10. Return { success, token, user: { name, role, companyId } }. STAGE 3 — FRONTEND REMEMBERS ───────────────────────────────────────────── Frontend receives the token. │ ▼ Stores in localStorage: 'fl365_token', 'fl365_user'. │ ▼ Redirects to Dashboard.html. │ STAGE 4 — EVERY SUBSEQUENT REQUEST ───────────────────────────────────────────── User clicks "Save Sale". │ ▼ Frontend calls: apiCall('createSale', saleData) │ ▼ apiCall() attaches the token from localStorage and calls the backend function. │ ▼ Backend runs validateSession(token): 1. Read CacheService entry for token. 2. If missing → throw 'Session expired. Please login again.' 3. If found → parse user info, return it. │ ▼ Only then does the actual business logic run (createSale, createPurchase, etc.). STAGE 5 — SESSION EXPIRES OR USER LOGS OUT ───────────────────────────────────────────── Option A: 6 hours pass → CacheService entry vanishes. Next apiCall fails → UI redirects to login. Option B: User clicks Logout. Frontend calls logout(token). Backend removes the CacheService entry. Frontend clears localStorage. Redirect to login.

Why a Token, Not a Cookie?

Cookies are the traditional way to remember a login. They work beautifully on desktop websites. But they break down in Apps Script for three reasons:

  • Apps Script Web Apps cannot easily set HTTP-only cookies — the platform does not expose raw response headers.
  • Mobile browsers behave differently — third-party cookie blocking is aggressive on iOS and Android. A token in localStorage is simpler.
  • Tokens are explicit — every API call passes the token visibly. Easier to debug, easier to reason about, easier to revoke.
⚠️
Trade-off: localStorage is readable by any JavaScript running on the same page. In a pure Apps Script Web App, that is only your own code — which is fine. If you later host the frontend on GitHub Pages with third-party scripts, review them carefully. Never paste <script src="…"> from an untrusted source into a page that holds a session token.
3

Password Hashing with HMAC-SHA256

Never — ever — store passwords as plain text. Even in a "private" Google Sheet. Here is why.

A Google Sheet is shareable. A Google Sheet has revision history. A Google Sheet can be exported, screenshotted, or accidentally made public by one wrong click on the "Share" button. If a customer or employee uses the same password on your shop app as on their personal email, and you leak it, you have hurt them far more than you hurt yourself.

What Is a Hash?

A hash function takes any input and produces a fixed-length string of characters. The same input always produces the same output. But — and this is the key — you cannot reverse a hash to recover the original input.

Input: "MyShop@2026" │ ▼ HMAC-SHA256 with a secret key Output: "8f3a9b2c1d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a" Input: "MyShop@2027" │ ▼ HMAC-SHA256 with the same key Output: "1a2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2"

Notice that two almost-identical passwords produce completely unrelated hashes. This is called the "avalanche effect" and it is essential — if a hacker changes one character, they cannot guess how the hash will change.

Why HMAC and Not Just SHA-256?

SHA-256 alone is vulnerable to a rainbow table attack. Someone can pre-compute billions of common passwords, hash them, and look up any leaked hash in a giant table. HMAC solves this by combining the password with a secret key that only you possess.

MethodAvailable in Apps Script?Secure Enough?Our Choice
Plain text Yes Never —
MD5 Yes Broken —
SHA-256 Yes Rainbow-table vulnerable —
HMAC-SHA256 Yes Good for small business ✅ This tutorial
bcrypt No (requires Node.js addon) Excellent For SQL migration
Argon2 No Excellent For SQL migration

The Hashing Code

Add this function to Auth.gs. This is the single most important line in the entire authentication system.

Auth.gs — password hashing (paste this first)
/**
 * Auth.gs — Part 1: Password hashing
 * Uses HMAC-SHA256 with a secret key from Script Properties.
 * 
 * WHY THIS IS SAFE:
 *  · Same password + same key = same hash (needed for comparison).
 *  · Different password → completely different hash (avalanche).
 *  · Without ENCRYPTION_KEY, the hash cannot be reproduced by an attacker.
 *  · ENCRYPTION_KEY never leaves Script Properties.
 */

/**
 * Hashes a password with HMAC-SHA256 using ENCRYPTION_KEY.
 * @param {string} password — the plain password to hash
 * @returns {string} lowercase hex string (64 chars)
 */
function hashPassword(password) {
  if (!password) throw new Error('Password is required.');
  if (!ENCRYPTION_KEY) throw new Error('ENCRYPTION_KEY not configured.');
  
  // computeHmacSha256Signature returns a byte array.
  // We convert it to lowercase hex (matching how bcrypt-like
  // systems represent digests).
  const rawHash = Utilities.computeHmacSha256Signature(
    password,
    ENCRYPTION_KEY
  );
  
  return rawHash.map(function(byte) {
    // byte is a signed int8; add 256 to normalise negatives,
    // then pad to two hex chars.
    const unsigned = byte < 0 ? byte + 256 : byte;
    return ('0' + unsigned.toString(16)).slice(-2);
  }).join('');
}

/**
 * Verifies a plain password against a stored hash.
 * @param {string} plain — the password the user typed
 * @param {string} storedHash — the hash from the Users sheet
 * @returns {boolean}
 */
function verifyPassword(plain, storedHash) {
  if (!plain || !storedHash) return false;
  const computed = hashPassword(plain);
  
  // Constant-time comparison — prevents timing attacks.
  // (An attacker measuring response time could otherwise
  // discover how many characters of the hash match.)
  if (computed.length !== storedHash.length) return false;
  
  let diff = 0;
  for (let i = 0; i < computed.length; i++) {
    diff |= computed.charCodeAt(i) ^ storedHash.charCodeAt(i);
  }
  return diff === 0;
}

/**
 * Generates a secure random salt for future use.
 * (Not used in Part 2, but included for extension.)
 */
function generateSalt() {
  return Utilities.getUuid() + Utilities.getUuid();
}

/**
 * Generates a strong random password.
 * Useful for the Admin UI when creating users.
 * @returns {string} — 12-char password with mixed case, digits, symbols
 */
function suggestPassword() {
  const upper = 'ABCDEFGHJKLMNPQRSTUVWXYZ';
  const lower = 'abcdefghijkmnopqrstuvwxyz';
  const digits = '23456789';
  const symbols = '@#$%&*!';
  const all = upper + lower + digits + symbols;
  
  let pwd = '';
  // Guarantee at least one of each class.
  pwd += upper.charAt(Math.floor(Math.random() * upper.length));
  pwd += lower.charAt(Math.floor(Math.random() * lower.length));
  pwd += digits.charAt(Math.floor(Math.random() * digits.length));
  pwd += symbols.charAt(Math.floor(Math.random() * symbols.length));
  
  // Fill the rest with random characters.
  for (let i = 4; i < 12; i++) {
    pwd += all.charAt(Math.floor(Math.random() * all.length));
  }
  
  // Shuffle so the guaranteed characters are not in fixed positions.
  return pwd.split('').sort(function() {
    return Math.random() - 0.5;
  }).join('');
}

Testing the Hash Function

Add a temporary test function, run it, and verify the output. This is how you build confidence in security code — test it before you trust it.

Auth.gs — temporary hash tests
function testHashing() {
  Logger.log('=== Hashing Test ===');
  
  const h1 = hashPassword('MyShop@2026');
  const h2 = hashPassword('MyShop@2026'); // Same input
  const h3 = hashPassword('MyShop@2027'); // Different input
  
  Logger.log('Hash1: ' + h1);
  Logger.log('Hash2: ' + h2);
  Logger.log('Hash3: ' + h3);
  Logger.log('Hash1 === Hash2 (should be true): ' + (h1 === h2));
  Logger.log('Hash1 === Hash3 (should be false): ' + (h1 === h3));
  Logger.log('Length of hash (should be 64): ' + h1.length);
  
  Logger.log('Verify correct pw: ' + verifyPassword('MyShop@2026', h1));
  Logger.log('Verify wrong pw: ' + verifyPassword('WrongPassword', h1));
  
  Logger.log('Suggested password: ' + suggestPassword());
}

/**
 * ONE-TIME SETUP: generates a hash for a known password.
 * Run this to get the hash for seeding the first admin user.
 * 
 * ⚠️ DELETE this function after setup. Never leave it in production.
 */
function generateHashForAdmin() {
  const password = 'Admin@2026'; // change this to your chosen password
  Logger.log('Password: ' + password);
  Logger.log('Hash:     ' + hashPassword(password));
  Logger.log('Copy the hash above into the Users sheet PasswordHash column.');
}

Expected output in the execution log:

Execution log — expected output
=== Hashing Test ===
Hash1: 8f3a9b2c1d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a
Hash2: 8f3a9b2c1d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a
Hash3: 1a2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2
Hash1 === Hash2 (should be true): true
Hash1 === Hash3 (should be false): false
Length of hash (should be 64): 64
Verify correct pw: true
Verify wrong pw: false
🚨
Delete generateHashForAdmin after you use it. Leaving a function that prints hashes to the log is a security risk if anyone else has access to the Apps Script editor. Consider it a one-time setup tool.
4

Session Tokens & CacheService

A session token is a short string that proves "this request came from someone who logged in recently." In our design, it is a UUID stored in CacheService — Google's fast, in-memory key-value store.

Why CacheService and Not PropertiesService?

FeatureCacheServicePropertiesService
SpeedVery fast (in-memory)Slower (persistent)
Auto-expiry✅ Yes (up to 6 hours)❌ No — lives forever
Max entries1,000 per scriptUnlimited
Ideal forSessionsConfiguration
Survives script restart✅ Yes (6 hours max)✅ Forever

Sessions must expire. If a shop's phone is stolen, we want the thief to be locked out after a reasonable window — not six months later. CacheService's built-in TTL (time to live) gives us exactly that: after six hours, the token simply disappears.

Session Lifetime: Why 6 Hours?

We picked 6 hours because it matches the rhythm of a Bangladeshi shop day:

Morning shift: 9 AM – 3 PM

One login at the start of the shift covers the whole shift.

Evening shift: 3 PM – 9 PM

Second login at shift change. Fresh session for fresh cashier.

Overnight

No one logged in — good. Session from yesterday has expired.

Stolen phone

Maximum exposure window: 6 hours. Change password to end it instantly.

🔢
CacheService TTL is in seconds. 6 hours = 6 × 60 × 60 = 21,600 seconds. Google's maximum allowed TTL is 21,600 seconds (exactly 6 hours).
5

Building the Full Auth.gs Module

This is the heart of Part 2. Paste the following functions below the hashing functions you already wrote.

Login

Auth.gs — login()
/**
 * Attempts to log in a user.
 * @param {string} email — user's email
 * @param {string} password — plain password
 * @returns {Object} { token, user: { email, name, role } }
 * @throws {Error} on invalid credentials or inactive account
 */
function login(email, password) {
  email = trim(email).toLowerCase();
  password = trim(password);
  
  if (!email || !password) {
    throw new Error('Email and password are required.');
  }
  
  // --- Rate limiting: max 5 attempts per email per 15 min ---
  const rateKey = 'login_attempts_' + email;
  const cache = CacheService.getScriptCache();
  const attempts = Number(cache.get(rateKey) || 0);
  
  if (attempts >= 5) {
    throw new Error(
      'Too many failed attempts. Please try again in 15 minutes.'
    );
  }
  
  // --- Read the Users sheet ---
  const users = readAll('Users');
  let matchedUser = null;
  
  for (let i = 0; i < users.length; i++) {
    if (trim(users[i].Email).toLowerCase() === email) {
      matchedUser = users[i];
      matchedUser._rowNumber = i + 2; // +1 for header, +1 for 1-based index
      break;
    }
  }
  
  // --- User not found: increment attempts and fail ---
  if (!matchedUser) {
    cache.put(rateKey, String(attempts + 1), 900);
    logAction(email, 'LOGIN_FAILED_NO_USER');
    throw new Error('Invalid email or password.');
  }
  
  // --- Check IsActive ---
  if (matchedUser.IsActive !== true) {
    logAction(email, 'LOGIN_FAILED_INACTIVE');
    throw new Error('This account is disabled. Contact your administrator.');
  }
  
  // --- Verify password ---
  if (!verifyPassword(password, matchedUser.PasswordHash)) {
    cache.put(rateKey, String(attempts + 1), 900);
    logAction(email, 'LOGIN_FAILED_BAD_PASSWORD');
    throw new Error('Invalid email or password.');
  }
  
  // --- Success! Clear rate limit ---
  cache.remove(rateKey);
  
  // --- Generate session token ---
  const token = Utilities.getUuid();
  const session = {
    email: matchedUser.Email,
    name: matchedUser.Name,
    role: matchedUser.Role,
    companyId: COMPANY_ID,
    loginAt: new Date().toISOString()
  };
  
  // --- Store in cache for 6 hours ---
  cache.put('sess_' + token, JSON.stringify(session), 21600);
  
  // --- Update LastLogin timestamp ---
  try {
    sheet('Users')
      .getRange(matchedUser._rowNumber, 7)
      .setValue(new Date());
  } catch (e) {
    console.error('Failed to update LastLogin: ', e);
  }
  
  // --- Audit log ---
  logAction(email, 'LOGIN_SUCCESS role=' + matchedUser.Role);
  
  return {
    token: token,
    user: {
      email: session.email,
      name: session.name,
      role: session.role,
      companyId: session.companyId
    }
  };
}

Session Validation (Used by Every Protected Function)

Auth.gs — validateSession()
/**
 * Validates a session token and returns the user.
 * Called at the top of every protected function.
 * @param {string} token
 * @returns {Object} { email, name, role, companyId }
 * @throws {Error} if token is missing or expired
 */
function validateSession(token) {
  if (!token) {
    throw new Error('No session token provided. Please log in.');
  }
  
  const cache = CacheService.getScriptCache();
  const cached = cache.get('sess_' + token);
  
  if (!cached) {
    throw new Error('Session expired. Please log in again.');
  }
  
  const session = JSON.parse(cached);
  
  // --- Sliding window: refresh TTL on activity ---
  // Every request extends the session by another 6 hours,
  // up to a maximum total from loginAt.
  const loginAt = new Date(session.loginAt).getTime();
  const hoursSince = (Date.now() - loginAt) / (1000 * 60 * 60);
  
  // Hard cap: 12 hours from original login, then force re-login.
  if (hoursSince > 12) {
    cache.remove('sess_' + token);
    throw new Error('Session too old. Please log in again.');
  }
  
  // Refresh TTL (sliding window).
  cache.put('sess_' + token, cached, 21600);
  
  return session;
}

Logout

Auth.gs — logout()
/**
 * Logs a user out by removing their session token from cache.
 * @param {string} token
 */
function logout(token) {
  if (!token) return;
  
  try {
    const session = JSON.parse(
      CacheService.getScriptCache().get('sess_' + token) || '{}'
    );
    if (session.email) {
      logAction(session.email, 'LOGOUT');
    }
  } catch (e) { /* ignore */ }
  
  CacheService.getScriptCache().remove('sess_' + token);
  return { success: true };
}

Change Password (Self-Service)

Auth.gs — changePassword()
/**
 * Allows the currently logged-in user to change their own password.
 * @param {string} token
 * @param {string} oldPassword
 * @param {string} newPassword
 */
function changePassword(token, oldPassword, newPassword) {
  const session = validateSession(token);
  
  if (!newPassword || newPassword.length < 8) {
    throw new Error('New password must be at least 8 characters.');
  }
  
  // Basic password strength checks
  if (!/[A-Z]/.test(newPassword)) {
    throw new Error('Password must contain at least one uppercase letter.');
  }
  if (!/[a-z]/.test(newPassword)) {
    throw new Error('Password must contain at least one lowercase letter.');
  }
  if (!/[0-9]/.test(newPassword)) {
    throw new Error('Password must contain at least one number.');
  }
  
  // Find the user's row
  const s = sheet('Users');
  const values = s.getDataRange().getValues();
  
  for (let i = 1; i < values.length; i++) {
    if (trim(values[i][0]).toLowerCase() === session.email.toLowerCase()) {
      // Verify old password
      if (!verifyPassword(oldPassword, values[i][3])) {
        throw new Error('Current password is incorrect.');
      }
      
      // Update hash in column D (index 3, 1-based col 4)
      s.getRange(i + 1, 4).setValue(hashPassword(newPassword));
      
      logAction(session.email, 'PASSWORD_CHANGED');
      return { success: true, message: 'Password updated successfully.' };
    }
  }
  
  throw new Error('User not found.');
}

Admin: Create User

Auth.gs — createUser()
/**
 * Admin-only: creates a new user.
 * @param {string} token — admin's session token
 * @param {Object} user — { email, name, role, password }
 */
function createUser(token, user) {
  const session = validateSession(token);
  requireRole(session, ['Admin']);
  
  // --- Validate input ---
  const email = trim(user.email).toLowerCase();
  const name = trim(user.name);
  const role = trim(user.role);
  const password = user.password;
  
  if (!email || !name || !role || !password) {
    throw new Error('Email, name, role and password are all required.');
  }
  if (['Admin', 'Manager', 'Cashier'].indexOf(role) === -1) {
    throw new Error('Role must be Admin, Manager, or Cashier.');
  }
  if (password.length < 8) {
    throw new Error('Password must be at least 8 characters.');
  }
  
  // --- Check duplicate email ---
  const users = readAll('Users');
  for (let i = 0; i < users.length; i++) {
    if (trim(users[i].Email).toLowerCase() === email) {
      throw new Error('A user with this email already exists.');
    }
  }
  
  // --- Insert new row ---
  appendRow('Users', [
    email,
    name,
    role,
    hashPassword(password),
    true,               // IsActive
    new Date(),           // CreatedAt
    ''                  // LastLogin (empty)
  ]);
  
  logAction(session.email, 'USER_CREATED ' + email + ' role=' + role);
  
  return { success: true, email: email };
}

/**
 * Admin-only: toggles a user's active status.
 * @param {string} token
 * @param {string} email
 * @param {boolean} isActive
 */
function setUserActive(token, email, isActive) {
  const session = validateSession(token);
  requireRole(session, ['Admin']);
  
  // Safety: prevent admin from disabling themselves
  if (trim(email).toLowerCase() === session.email.toLowerCase()) {
    throw new Error('You cannot disable your own account.');
  }
  
  const s = sheet('Users');
  const values = s.getDataRange().getValues();
  
  for (let i = 1; i < values.length; i++) {
    if (trim(values[i][0]).toLowerCase() === trim(email).toLowerCase()) {
      s.getRange(i + 1, 5).setValue(isActive === true);
      logAction(session.email, (isActive ? 'USER_ENABLED ' : 'USER_DISABLED ') + email);
      return { success: true };
    }
  }
  throw new Error('User not found.');
}

/**
 * Admin-only: lists all users for the management UI.
 * Never returns PasswordHash.
 */
function getAllUsers(token) {
  const session = validateSession(token);
  requireRole(session, ['Admin']);
  
  const users = readAll('Users');
  return users.map(function(u) {
    return {
      email: u.Email,
      name: u.Name,
      role: u.Role,
      isActive: u.IsActive === true,
      createdAt: u.CreatedAt,
      lastLogin: u.LastLogin
    };
  });
}

/**
 * Admin-only: resets another user's password.
 * Returns the new password ONCE for the admin to share with the user.
 */
function resetUserPassword(token, email) {
  const session = validateSession(token);
  requireRole(session, ['Admin']);
  
  const newPassword = suggestPassword();
  const s = sheet('Users');
  const values = s.getDataRange().getValues();
  
  for (let i = 1; i < values.length; i++) {
    if (trim(values[i][0]).toLowerCase() === trim(email).toLowerCase()) {
      s.getRange(i + 1, 4).setValue(hashPassword(newPassword));
      logAction(session.email, 'PASSWORD_RESET_FOR ' + email);
      return { success: true, newPassword: newPassword };
    }
  }
  throw new Error('User not found.');
}

Permission Helper — requireRole()

Auth.gs — requireRole()
/**
 * Throws if the session's role is not in the allowed list.
 * Called at the top of every privileged function.
 * @param {Object} session — from validateSession()
 * @param {string[]} allowedRoles — e.g. ['Admin', 'Manager']
 */
function requireRole(session, allowedRoles) {
  if (!session || !session.role) {
    throw new Error('No active session.');
  }
  if (allowedRoles.indexOf(session.role) === -1) {
    throw new Error(
      'Permission denied. Required role: ' + allowedRoles.join(' or ') +
      '. Your role: ' + session.role
    );
  }
}

Seeding the First Admin User

Before you can log in, you need one Admin user in the Users sheet. Here is how:

Open the Users sheet

Navigate to the Users tab in your ERP_Database spreadsheet.

Generate the password hash

In the Apps Script editor, open Auth.gs, change the password inside generateHashForAdmin to your chosen owner password (e.g. Karim@Mirpur2026), then run it. Copy the long hex hash from the log.

Add a row to the Users sheet

Fill in: owner@demo.bd, Karim Uddin, Admin, the copied hash, TRUE, today's date, and leave LastLogin blank.

Delete the hash generator

Remove generateHashForAdmin from the code. It is a one-time tool.

🛑
Do not commit the password anywhere. Not to GitHub. Not to WhatsApp. Not to your notebook. Store it in a password manager (Bitwarden free tier, or Google Password Manager). Only the owner should know it.
6

Role-Based Access Control

Now that sessions work, we need to decide what each role can do. Here is the complete permission matrix for our ERP.

Action Admin Manager Cashier
Create a sale (POS) ✅ Yes ✅ Yes ✅ Yes
View today's sales ✅ Yes ✅ Yes ✅ Yes (own only)
View profit reports ✅ Yes ✅ Yes ❌ No
Add / edit products ✅ Yes ✅ Yes ❌ No
Change product prices ✅ Yes ✅ Yes ❌ No
Record purchases (GRN) ✅ Yes ✅ Yes ❌ No
Record expenses ✅ Yes ✅ Yes ❌ No
View all user data ✅ Yes ✅ Limited ❌ No
Add / remove users ✅ Yes ❌ No ❌ No
Change company settings ✅ Yes ❌ No ❌ No
View audit log ✅ Yes ✅ Yes ❌ No
Delete a sale ✅ Yes (rare) ❌ No ❌ No

How to Enforce Roles in Code

Every function that touches sensitive data starts with two lines:

The two-line guard pattern (used in every sensitive function)
function someSensitiveOperation(token, data) {
  // Line 1: verify the user is logged in
  const session = validateSession(token);
  
  // Line 2: verify the user has the right role
  requireRole(session, ['Admin', 'Manager']);
  
  // Now safe to proceed.
  // session.email, session.role, session.name are all available.
  return doTheThing(data, session);
}
🛡️
Never trust the frontend. Even if the UI hides a button, the API must re-check. A determined user can open browser DevTools and call any backend function directly. If the backend does not enforce roles, neither does your system.

Practical Example: Cashier Sale vs Manager Price Change

Here is what a real sale looks like from the backend's point of view. Notice how a Cashier can call createSale() but cannot call updateProduct().

Pos.gs — createSale (any logged-in user)
function createSale(token, saleData) {
  const session = validateSession(token);
  requireRole(session, ['Admin', 'Manager', 'Cashier']);
  
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(15000);
    // ... sale logic ...
    return { success: true, invoiceNo: invoiceNo };
  } finally {
    lock.releaseLock();
  }
}
Stock.gs — updateProduct (Admin / Manager only)
function updateProduct(token, productId, updates) {
  const session = validateSession(token);
  requireRole(session, ['Admin', 'Manager']); // ← Cashier blocked here
  
  // A Cashier calling this from DevTools would receive:
  // "Permission denied. Required role: Admin or Manager. Your role: Cashier."
  
  // ... update logic ...
}
7

The Login Screen (Bangla / English)

Here is what the login screen will look like. We designed it to feel both modern and familiar — a Bangladeshi shopkeeper who uses Facebook on their phone will instantly understand it.

The Full HTML File

In the Apps Script editor, add a new HTML file: File → New → HTML file. Name it Login (Apps Script will add .html automatically). Paste the following.

Login.html — Bangla / English login screen
<!DOCTYPE html>
<html lang="bn">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Login — Demo Store ERP</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    body {
      margin: 0;
      min-height: 100vh;
      display: flex;
      align-items: center;
      justify-content: center;
      padding: 20px;
      font-family: "Segoe UI", system-ui, Roboto, "Noto Sans Bengali", sans-serif;
      background:
        radial-gradient(800px 400px at 10% 0%, rgba(139,92,246,.35), transparent 60%),
        radial-gradient(700px 400px at 100% 100%, rgba(6,182,212,.28), transparent 60%),
        linear-gradient(140deg,#1e1b4b 0%,#4c1d95 45%,#0e7490 100%);
      color: #0f172a;
    }
    .login-card {
      width: 100%; max-width: 420px;
      background: #fff; border-radius: 20px;
      box-shadow: 0 24px 48px -16px rgba(0,0,0,.35);
      padding: 34px 28px 28px;
      position: relative;
    }
    .login-logo {
      width: 60px; height: 60px; margin: 0 auto 14px;
      border-radius: 16px;
      background: linear-gradient(135deg,#6d28d9,#06b6d4);
      display: grid; place-items: center; color: #fff;
      box-shadow: 0 12px 24px -8px rgba(109,40,217,.55);
    }
    .login-logo svg { width: 28px; height: 28px; }
    .login-title { font-weight: 800; font-size: 21px; text-align: center; margin: 0 0 4px; }
    .login-sub { text-align: center; color: #64748b; font-size: 14px; margin: 0 0 22px; }
    .form-label { font-weight: 700; font-size: 13px; color: #334155; margin-bottom: 6px; letter-spacing: .3px; }
    .form-control { padding: 12px 14px; border-radius: 10px; font-size: 15px; }
    .form-control:focus { border-color: #8b5cf6; box-shadow: 0 0 0 3px rgba(139,92,246,.15); }
    .btn-login {
      width: 100%; padding: 13px 18px; border-radius: 10px; border: 0;
      background: linear-gradient(135deg,#6d28d9,#06b6d4);
      color: #fff; font-weight: 700; font-size: 15.5px;
      box-shadow: 0 12px 24px -8px rgba(109,40,217,.55);
      transition: transform .2s ease;
    }
    .btn-login:hover:not(:disabled) { transform: translateY(-2px); }
    .btn-login:disabled { opacity: .7; cursor: wait; }
    .error-box {
      background: #fef2f2; border: 1px solid #fecaca;
      color: #991b1b; padding: 12px 14px; border-radius: 10px;
      font-size: 14px; margin-bottom: 14px; display: none;
    }
    .error-box.show { display: block; }
    .lang-toggle {
      position: absolute; top: 16px; right: 16px;
      background: #f1f5f9; border: 1px solid #e2e8f0;
      border-radius: 999px; padding: 4px 12px;
      font-size: 12px; font-weight: 700; color: #475569;
      cursor: pointer;
    }
    .lang-toggle:hover { background: #e2e8f0; }
    .footer-note {
      text-align: center; margin-top: 18px;
      font-size: 12.5px; color: #64748b; line-height: 1.5;
    }
    .company-tag {
      display: inline-block;
      background: #f5f3ff; color: #6d28d9;
      padding: 3px 10px; border-radius: 999px;
      font-size: 12px; font-weight: 700;
      margin-bottom: 8px;
    }
    .spinner {
      display: inline-block;
      width: 16px; height: 16px;
      border: 2px solid rgba(255,255,255,.4);
      border-top-color: #fff;
      border-radius: 50%;
      animation: spin .7s linear infinite;
      vertical-align: -3px;
      margin-right: 6px;
    }
    @keyframes spin { to { transform: rotate(360deg); } }
  
</style>
</head>
<body>

  <div class="login-card">
    <button class="lang-toggle" id="langBtn">বাংলা / EN</button>

    <div class="login-logo">
      <svg viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2">
        <rect x="3" y="11" width="18" height="10" rx="2"/>
        <path d="M7 11V7a5 5 0 0 1 10 0v4"/>
      </svg>
    </div>

    <div style="text-align:center;">
      <span class="company-tag"><?= COMPANY_NAME ?></span>
    </div>
    <h1 class="login-title" id="titleText">স্বাগতম</h1>
    <p class="login-sub" id="subText">আপনার অ্যাকাউন্টে লগইন করুন</p>

    <div class="error-box" id="errorBox"></div>

    <form id="loginForm">
      <div class="mb-3">
        <label class="form-label" id="lblEmail">ইমেইল</label>
        <input type="email" class="form-control" id="email"
               autocomplete="username" required autofocus>
      </div>
      <div class="mb-3">
        <label class="form-label" id="lblPassword">পাসওয়ার্ড</label>
        <input type="password" class="form-control" id="password"
               autocomplete="current-password" required>
      </div>
      <button type="submit" class="btn-login" id="loginBtn">
        <span id="btnText">লগইন</span>
      </button>
    </form>

    <p class="footer-note" id="footerText">
      আপনার পাসওয়ার্ড কারও সাথে শেয়ার করবেন না।</p>
  </div>

  <script>
    // --- Bilingual strings ---
    const STRINGS = {
      bn: {
        title: 'স্বাগতম',
        sub: 'আপনার অ্যাকাউন্টে লগইন করুন',
        email: 'ইমেইল',
        password: 'পাসওয়ার্ড',
        loginBtn: 'লগইন',
        loggingIn: 'অপেক্ষা করুন...',
        footer: 'আপনার পাসওয়ার্ড কারও সাথে শেয়ার করবেন না।',
        error: 'ইমেইল বা পাসওয়ার্ড ভুল হয়েছে।'
      },
      en: {
        title: 'Welcome back',
        sub: 'Sign in to your account',
        email: 'Email',
        password: 'Password',
        loginBtn: 'Login',
        loggingIn: 'Signing in...',
        footer: 'Do not share your password with anyone.',
        error: 'Invalid email or password.'
      }
    };

    let currentLang = 'bn';

    function applyLang(lang) {
      currentLang = lang;
      const s = STRINGS[lang];
      document.getElementById('titleText').textContent = s.title;
      document.getElementById('subText').textContent = s.sub;
      document.getElementById('lblEmail').textContent = s.email;
      document.getElementById('lblPassword').textContent = s.password;
      document.getElementById('btnText').textContent = s.loginBtn;
      document.getElementById('footerText').textContent = s.footer;
    }

    document.getElementById('langBtn').addEventListener('click', function() {
      applyLang(currentLang === 'bn' ? 'en' : 'bn');
    });

    // --- Show errors ---
    function showError(msg) {
      const box = document.getElementById('errorBox');
      box.textContent = msg;
      box.classList.add('show');
    }
    function hideError() {
      document.getElementById('errorBox').classList.remove('show');
    }

    // --- Submit handler ---
    document.getElementById('loginForm').addEventListener('submit', function(e) {
      e.preventDefault();
      hideError();

      const email = document.getElementById('email').value.trim();
      const password = document.getElementById('password').value;

      if (!email || !password) {
        showError(STRINGS[currentLang].error);
        return;
      }

      const btn = document.getElementById('loginBtn');
      const btnText = document.getElementById('btnText');
      btn.disabled = true;
      btnText.innerHTML =
        '<span class="spinner"></span>' + STRINGS[currentLang].loggingIn;

      google.script.run
        .withSuccessHandler(function(res) {
          if (res && res.success && res.data) {
            localStorage.setItem('fl365_token', res.data.token);
            localStorage.setItem('fl365_user', JSON.stringify(res.data.user));
            window.location.href = '?page=Dashboard';
          } else {
            showError(res.error || STRINGS[currentLang].error);
            btn.disabled = false;
            btnText.textContent = STRINGS[currentLang].loginBtn;
          }
        })
        .withFailureHandler(function(err) {
          showError(err.message || STRINGS[currentLang].error);
          btn.disabled = false;
          btnText.textContent = STRINGS[currentLang].loginBtn;
        })
        .login(email, password);
    });
  </script>
</body>
</html>
🌐
Why bilingual? A cashier in Narayanganj may read Bangla more comfortably than English, but the owner reading reports might prefer English. A single toggle button switches both. Notice we used <?= COMPANY_NAME ?> — that is Apps Script's template syntax, filled in from Script Properties when the page loads.
8

Frontend Session Manager

Every page after login (Dashboard, POS, Stock, Reports) needs to know who is logged in and needs a helper to call the backend with the token attached. That's what this shared JavaScript file does.

In the Apps Script editor, add another HTML file called JS. Paste the following.

JS.html — shared frontend utilities (Part 2 version)
<script>
/* ============================================================
   FreeLearning365 ERP — shared frontend runtime
   Loaded on every page after login.
   ============================================================ */

(function() {
  'use strict';

  // --- 1. Read session from localStorage ---
  const TOKEN = localStorage.getItem('fl365_token');
  const USER_RAW = localStorage.getItem('fl365_user');

  // If no token, kick back to login immediately.
  if (!TOKEN) {
    window.location.href = '?page=Login';
    return;
  }

  let USER = {};
  try { USER = JSON.parse(USER_RAW || '{}'); } catch (e) {}

  // --- 2. Global API wrapper ---
  // Every backend call goes through this. It always sends the token
  // as the first argument, and always returns a Promise.
  window.apiCall = function(funcName) {
    const extraArgs = Array.prototype.slice.call(arguments, 1);
    return new Promise(function(resolve, reject) {
      google.script.run
        .withSuccessHandler(function(res) {
          if (res && res.success) resolve(res.data);
          else reject(new Error((res && res.error) || 'Unknown error'));
        })
        .withFailureHandler(function(err) {
          // If the session expired, send the user back to login.
          if (err && err.message && err.message.indexOf('Session') >= 0) {
            localStorage.removeItem('fl365_token');
            localStorage.removeItem('fl365_user');
            alert('Your session expired. Please log in again.');
            window.location.href = '?page=Login';
            return;
          }
          reject(err);
        })
        [funcName].apply(null, [TOKEN].concat(extraArgs));
    });
  };

  // --- 3. Logout helper ---
  window.fl365Logout = function() {
    if (!confirm('Are you sure you want to log out?')) return;
    google.script.run
      .withSuccessHandler(function() {
        localStorage.removeItem('fl365_token');
        localStorage.removeItem('fl365_user');
        window.location.href = '?page=Login';
      })
      .withFailureHandler(function() {
        localStorage.clear();
        window.location.href = '?page=Login';
      })
      .logout(TOKEN);
  };

  // --- 4. Role helpers ---
  window.hasRole = function(/* role1, role2, ... */) {
    const allowed = Array.prototype.slice.call(arguments);
    return allowed.indexOf(USER.role) !== -1;
  };

  window.isAdmin = function() { return USER.role === 'Admin'; };
  window.isManagerOrAbove = function() {
    return USER.role === 'Admin' || USER.role === 'Manager';
  };

  // --- 5. Show user in navbar ---
  window.renderUserBadge = function(elId) {
    const el = document.getElementById(elId);
    if (!el) return;
    const roleClass = {
      Admin: 'badge-admin',
      Manager: 'badge-manager',
      Cashier: 'badge-cashier'
    }[USER.role] || '';
    el.innerHTML =
      '<span class="user-name">' + (USER.name || USER.email) + '</span> ' +
      '<span class="user-role ' + roleClass + '">' + USER.role + '</span>';
  };

  // --- 6. Auto-logout on 6 hours of inactivity ---
  let lastActivity = Date.now();
  ['click', 'keypress', 'touchstart'].forEach(function(ev) {
    document.addEventListener(ev, function() { lastActivity = Date.now(); }, true);
  });
  setInterval(function() {
    if (Date.now() - lastActivity > 6 * 60 * 60 * 1000) {
      alert('You have been idle for 6 hours. Please log in again.');
      fl365Logout();
    }
  }, 60000);
})();
</script>
📱
Why auto-logout? If a cashier leaves the counter for lunch and forgets to log out, and a customer's child picks up the tablet and starts tapping, the session should not stay open forever. Six hours of inactivity matches the session token expiry and forces a fresh login.
9

User Management UI (Admin Only)

The owner needs a simple screen to add staff, change roles, and disable ex-employees. Here is the complete page.

Add another HTML file called Users. Paste this:

Users.html — Admin user management page
<div class="container py-4">
  <div class="d-flex justify-content-between align-items-center mb-4">
    <h3 class="mb-0">ব্যবহারকারী / Users</h3>
    <button class="btn btn-primary" id="addUserBtn">
      + নতুন ব্যবহারকারী / New User
    </button>
  </div>

  <div class="card">
    <div class="card-body">
      <div class="table-responsive">
        <table class="table table-hover align-middle">
          <thead>
            <tr>
              <th>Name</th>
              <th>Email</th>
              <th>Role</th>
              <th>Status</th>
              <th>Last Login</th>
              <th style="width:140px;">Actions</th>
            </tr>
          </thead>
          <tbody id="userTableBody">
            <tr><td colspan="6" class="text-center text-muted py-4">
              Loading users...
            </td></tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<!-- Add User Modal -->
<div class="modal fade" id="addUserModal" tabindex="-1">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">নতুন ব্যবহারকারী / Add New User</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
      </div>
      <div class="modal-body">
        <div class="alert alert-danger d-none" id="addUserError"></div>
        <div class="mb-3">
          <label class="form-label">Name</label>
          <input type="text" class="form-control" id="newName"
                 placeholder="Karim Uddin">
        </div>
        <div class="mb-3">
          <label class="form-label">Email</label>
          <input type="email" class="form-control" id="newEmail"
                 placeholder="karim@demo.bd">
        </div>
        <div class="mb-3">
          <label class="form-label">Role</label>
          <select class="form-select" id="newRole">
            <option value="Cashier">ক্যাশিয়ার / Cashier</option>
            <option value="Manager">ম্যানেজার / Manager</option>
            <option value="Admin">মালিক / Admin</option>
          </select>
        </div>
        <div class="mb-3">
          <label class="form-label">Password</label>
          <div class="input-group">
            <input type="text" class="form-control" id="newPassword">
            <button class="btn btn-outline-secondary" type="button"
                    id="genPwdBtn">Generate</button>
          </div>
          <small class="text-muted">
            Min 8 characters, one uppercase, one lowercase, one number.
          </small>
        </div>
      </div>
      <div class="modal-footer">
        <button class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
        <button class="btn btn-primary" id="saveUserBtn">Create User</button>
      </div>
    </div>
  </div>
</div>

<script>
  let addUserModal;
  let allUsers = [];

  function roleBadge(role) {
    const map = {
      Admin: 'bg-primary',
      Manager: 'bg-info text-dark',
      Cashier: 'bg-success'
    };
    return '<span class="badge ' + (map[role] || 'bg-secondary') + '">' + role + '</span>';
  }

  function statusBadge(isActive) {
    return isActive
      ? '<span class="badge bg-success">Active</span>'
      : '<span class="badge bg-danger">Disabled</span>';
  }

  function fmtDate(v) {
    if (!v) return '—';
    try { return new Date(v).toLocaleString(); } catch (e) { return '—'; }
  }

  function renderUsers() {
    const tbody = document.getElementById('userTableBody');
    if (!allUsers.length) {
      tbody.innerHTML = '<tr><td colspan="6" class="text-center text-muted py-4">No users yet.</td></tr>';
      return;
    }
    tbody.innerHTML = allUsers.map(function(u) {
      return
        '<tr>' +
        '<td>' + u.name + '</td>' +
        '<td>' + u.email + '</td>' +
        '<td>' + roleBadge(u.role) + '</td>' +
        '<td>' + statusBadge(u.isActive) + '</td>' +
        '<td><small>' + fmtDate(u.lastLogin) + '</small></td>' +
        '<td>' +
          '<button class="btn btn-sm btn-outline-primary me-1" onclick="toggleUser(\'' +
            u.email + '\', ' + (!u.isActive) + ')">' +
            (u.isActive ? 'Disable' : 'Enable') +
          '</button>' +
          '<button class="btn btn-sm btn-outline-warning" onclick="resetPwd(\'' +
            u.email + '\')">Reset</button>' +
        '</td>' +
        '</tr>';
    }).join('');
  }

  function loadUsers() {
    apiCall('getAllUsers')
      .then(function(list) {
        allUsers = list;
        renderUsers();
      })
      .catch(function(err) {
        document.getElementById('userTableBody').innerHTML =
          '<tr><td colspan="6" class="text-danger text-center py-4">' +
          err.message + '</td></tr>';
      });
  }

  window.toggleUser = function(email, makeActive) {
    if (!confirm((makeActive ? 'Enable ' : 'Disable ') + email + '?')) return;
    apiCall('setUserActive', email, makeActive)
      .then(loadUsers)
      .catch(function(err) { alert(err.message); });
  };

  window.resetPwd = function(email) {
    if (!confirm('Generate a new password for ' + email + '?')) return;
    apiCall('resetUserPassword', email)
      .then(function(res) {
        alert(
          'New password for ' + email + ':\n\n' + res.newPassword +
          '\n\nCopy this now. It will not be shown again.'
        );
      })
      .catch(function(err) { alert(err.message); });
  };

  document.getElementById('genPwdBtn').addEventListener('click', function() {
    const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz23456789@#$%';
    let p = '';
    for (let i = 0; i < 12; i++) {
      p += chars.charAt(Math.floor(Math.random() * chars.length));
    }
    document.getElementById('newPassword').value = p;
  });

  document.getElementById('saveUserBtn').addEventListener('click', function() {
    const errBox = document.getElementById('addUserError');
    errBox.classList.add('d-none');
    const payload = {
      name: document.getElementById('newName').value.trim(),
      email: document.getElementById('newEmail').value.trim(),
      role: document.getElementById('newRole').value,
      password: document.getElementById('newPassword').value
    };
    apiCall('createUser', payload)
      .then(function() {
        addUserModal.hide();
        document.getElementById('newName').value = '';
        document.getElementById('newEmail').value = '';
        document.getElementById('newPassword').value = '';
        loadUsers();
      })
      .catch(function(err) {
        errBox.textContent = err.message;
        errBox.classList.remove('d-none');
      });
  });

  // --- Init ---
  if (!isAdmin()) {
    document.querySelector('.container').innerHTML =
      '<div class="alert alert-danger m-4">' +
      'Only the owner (Admin) can manage users.' +
      '</div>';
  } else {
    addUserModal = new bootstrap.Modal(document.getElementById('addUserModal'));
    document.getElementById('addUserBtn').addEventListener('click', function() {
      document.getElementById('newPassword').value = '';
      genPwd();
      addUserModal.show();
    });
    loadUsers();
  }

  function genPwd() {
    document.getElementById('genPwdBtn').click();
  }
</script>
10

Audit Logging

Every privileged action in the ERP writes to the AuditLog sheet. This is not just for security — it is for peace of mind. When the owner asks "Who changed the price of rice on Tuesday?", the answer is one search away.

What We Log

ActionLogged WhenExample Entry
LOGIN_SUCCESSUser logs in successfullyLOGIN_SUCCESS role=Admin
LOGIN_FAILED_NO_USEREmail not foundLOGIN_FAILED_NO_USER
LOGIN_FAILED_INACTIVEUser is disabledLOGIN_FAILED_INACTIVE
LOGIN_FAILED_BAD_PASSWORDWrong passwordLOGIN_FAILED_BAD_PASSWORD
LOGOUTUser logs outLOGOUT
USER_CREATEDNew user addedUSER_CREATED nasrin@demo.bd role=Cashier
USER_DISABLEDUser disabledUSER_DISABLED rahim@demo.bd
PASSWORD_CHANGEDSelf-service password changePASSWORD_CHANGED
PASSWORD_RESET_FORAdmin resets another userPASSWORD_RESET_FOR nasrin@demo.bd
SALE_CREATED_<INV>A sale is savedSALE_CREATED_INV-2026-00123
PRODUCT_UPDATED_<ID>Product editedPRODUCT_UPDATED_P001

Security Note: Never Log Passwords

🚫
Do not log passwords, password hashes, or session tokens. The audit log is a historical record of actions — not a place for secrets. Our logAction() function only stores what the user did, never what they typed.

Reading the Audit Log

Open the AuditLog sheet. You will see rows like:

AuditLog sheet — example contents after a day of use
Timestamp               | Email              | Action
2026-09-10 09:02:14     | karim@demo.bd      | LOGIN_SUCCESS role=Admin
2026-09-10 09:14:33     | nasrin@demo.bd     | LOGIN_SUCCESS role=Cashier
2026-09-10 09:15:02     | nasrin@demo.bd     | SALE_CREATED_INV-2026-00001
2026-09-10 09:22:47     | nasrin@demo.bd     | SALE_CREATED_INV-2026-00002
2026-09-10 12:45:11     | nasrin@demo.bd     | LOGOUT
2026-09-10 14:55:03     | karim@demo.bd      | USER_CREATED fatema@demo.bd role=Cashier
2026-09-10 15:02:18     | unknown@x.com      | LOGIN_FAILED_NO_USER
2026-09-10 15:02:21     | unknown@x.com      | LOGIN_FAILED_NO_USER
2026-09-10 15:02:25     | unknown@x.com      | LOGIN_FAILED_NO_USER

Already that last block of three lines tells a story: someone tried to log in with an email that does not exist. Our rate limiter will lock that out after five attempts.

11

Deploying the Web App

This is the moment your code becomes a real, shareable URL that your staff can open on their phones.

First: Create the WebApp.gs File

Add a script file called WebApp. Paste the following. This file handles page routing and the doGet entry point that Apps Script uses.

WebApp.gs — routing and page serving
/**
 * WebApp.gs
 * The entry point for the Web App. Apps Script calls doGet(e)
 * whenever someone opens the published URL.
 */

function doGet(e) {
  const page = (e && e.parameter && e.parameter.page) || 'Login';
  
  // Whitelist of pages we are willing to serve.
  // Prevents arbitrary template injection via the URL.
  const allowedPages = ['Login', 'Dashboard', 'POS', 'Stock', 'Reports', 'Users'];
  const safePage = allowedPages.indexOf(page) !== -1 ? page : 'Login';
  
  // Build the HTML page from a template file.
  // This lets us use <?= COMPANY_NAME ?> inside the HTML.
  const template = HtmlService.createTemplateFromFile(safePage);
  template.COMPANY_NAME = COMPANY_NAME;
  template.COMPANY_ID = COMPANY_ID;
  template.PAGE = safePage;
  
  return template.evaluate()
    .setTitle(COMPANY_NAME + ' ERP')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

/**
 * Includes another HTML file inline. Called from templates as:
 *     <?!= include('JS') ?>
 * @param {string} filename
 * @returns {string} — the file contents as raw HTML
 */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

Create a Minimal Dashboard Page

We need a page to land on after login. Add another HTML file called Dashboard:

Dashboard.html — placeholder dashboard (full version in Part 3)
<!DOCTYPE html>
<html lang="bn">
<head>
  <meta charset="UTF-8">
  <title>Dashboard — <?= COMPANY_NAME ?></title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    body { background: #f8fafc; font-family: "Segoe UI", Roboto, "Noto Sans Bengali", sans-serif; }
    .navbar-brand { font-weight: 800; }
    .user-role { font-size: 11px; padding: 2px 8px; border-radius: 999px; margin-left: 6px; }
    .badge-admin { background: #ede9fe; color: #5b21b6; }
    .badge-manager { background: #cffafe; color: #0e7490; }
    .badge-cashier { background: #dcfce7; color: #166534; }
    .stat-card {
      background: #fff; border: 1px solid #e2e8f0; border-radius: 14px;
      padding: 20px; transition: all .2s ease;
    }
    .stat-card:hover { box-shadow: 0 10px 24px -8px rgba(15,23,42,.14); }
    .stat-card__label { font-size: 12.5px; color: #64748b; font-weight: 600; letter-spacing: .3px; }
    .stat-card__value { font-size: 26px; font-weight: 800; color: #0f172a; margin-top: 4px; }
    .stat-card__sub { font-size: 12px; color: #94a3b8; margin-top: 4px; }
    .taka { color: #0e7490; }
  </style>
</head>
<body>

  <nav class="navbar navbar-dark" style="background: linear-gradient(90deg,#6d28d9,#06b6d4);">
    <div class="container-fluid">
      <span class="navbar-brand"><?= COMPANY_NAME ?> ERP</span>
      <div class="d-flex align-items-center text-white">
        <span id="userBadge"></span>
        <button class="btn btn-outline-light btn-sm ms-3"
                onclick="fl365Logout()">Logout</button>
      </div>
    </div>
  </nav>

  <div class="container py-4">
    <h3 class="mb-4">স্বাগতম, <span id="welcomeName"></span>!</h3>

    <div class="row g-3">
      <div class="col-md-3">
        <div class="stat-card">
          <div class="stat-card__label">TODAY'S SALES</div>
          <div class="stat-card__value taka">৳ <span id="todaySales">0</span></div>
          <div class="stat-card__sub">From the Sales sheet</div>
        </div>
      </div>
      <div class="col-md-3">
        <div class="stat-card">
          <div class="stat-card__label">TODAY'S DUE</div>
          <div class="stat-card__value taka">৳ <span id="todayDue">0</span></div>
          <div class="stat-card__sub">Pending customer payments</div>
        </div>
      </div>
      <div class="col-md-3">
        <div class="stat-card">
          <div class="stat-card__label">RECEIVABLE</div>
          <div class="stat-card__value taka">৳ <span id="totalReceivable">0</span></div>
          <div class="stat-card__sub">All outstanding dues</div>
        </div>
      </div>
      <div class="col-md-3">
        <div class="stat-card">
          <div class="stat-card__label">LOW STOCK</div>
          <div class="stat-card__value"><span id="lowStock">0</span> items</div>
          <div class="stat-card__sub">Below reorder level</div>
        </div>
      </div>
    </div>

    <div class="alert alert-info mt-4">
      <strong>Coming in Part 3:</strong>
      the POS screen, product catalog, stock ledger and full sales workflow.
    </div>
  </div>

  <?!= include('JS') ?>
  <script>
    // Render the user's name + role
    renderUserBadge('userBadge');
    document.getElementById('welcomeName').textContent = USER.name || USER.email;
    // In Part 3 we will fill these with real data.
  </script>
</body>
</html>

Deploying: Step by Step

Save all files

Press Ctrl + S (or Cmd + S) to save every .gs and .html file. Apps Script autosaves, but a manual save gives confidence.

Click "Deploy" → "New deployment"

Top-right corner of the Apps Script editor.

Select type = Web app

Click the gear icon next to "Select type" and choose Web app.

Description

Type something like v1.0 Part 2 release. This is a label for your own use — you can have many deployments.

Execute as: Me

Choose Me (your-email@gmail.com). This means the script runs under your Google identity and has access to your Sheets.

Who has access

This is the most important setting. Choose Anyone so any employee can access the app without needing a Google account. The password protection is inside the app itself — that is the whole point.

Click Deploy

Apps Script will ask you to authorise the app. Approve it. After a few seconds, you will see a Web app URL that ends with /exec. Copy it.

Test the URL

Paste the URL into a fresh browser window (or an incognito window). You should see the login screen.

Share with staff

Send the URL to your employees. They bookmark it on their phones. That's the whole onboarding.

📱
Pro tip — "Add to Home Screen". On any phone, opening the URL in a browser and tapping "Add to Home Screen" (iOS Safari) or "Add to Home screen" (Android Chrome) turns the Web App into an icon that opens full-screen, like a native app. No app store needed.

Redeploying After Code Changes

When you change code, the URL does not change automatically. You must:

  1. Click Deploy → Manage deployments.
  2. Click the pencil icon ✏️ next to the current deployment.
  3. Change Version to "New version".
  4. Click Deploy.

The URL stays the same. Existing sessions stay valid. Employees do not need to re-bookmark.

12

Testing with a Real Shop Scenario

Let's simulate a full day at Karim's grocery shop in Mirpur. Follow along.

Scenario Setup

PersonEmailRoleResponsibility
Karim Uddin (owner) karim@demo.bd Admin Opens shop, checks yesterday's report
Rahim Ahmed rahim@demo.bd Manager Records purchase from supplier
Nasrin Akter nasrin@demo.bd Cashier Bills customers at the counter
Fatema Begum fatema@demo.bd Cashier Second cashier for the evening shift

Test 1 — Karim Logs In

Steps
1.  Open the Web App URL in Chrome.
2.  Enter:  karim@demo.bd
3.  Enter:  Karim@Mirpur2026
4.  Click "লগইন / Login".

EXPECTED:
 · Page redirects to Dashboard
 · Navbar shows: "Karim Uddin   Admin"
 · Welcome message: "স্বাগতম, Karim Uddin!"
 · In the AuditLog sheet, a new row appears:
   2026-09-10 HH:MM:SS | karim@demo.bd | LOGIN_SUCCESS role=Admin

Test 2 — Karim Adds Nasrin as Cashier

Steps
1.  On the Dashboard, navigate to the Users page.
2.  Click "+ নতুন ব্যবহারকারী / New User".
3.  Fill:
    Name:  Nasrin Akter
    Email: nasrin@demo.bd
    Role:  Cashier
    Password:  (click Generate)
4.  Copy the generated password and share it with Nasrin via WhatsApp.
5.  Click "Create User".

EXPECTED:
 · Modal closes
 · Nasrin appears in the Users table with a green "Cashier" badge
 · AuditLog shows: USER_CREATED nasrin@demo.bd role=Cashier

Test 3 — Nasrin Logs In on Her Phone

Steps
1.  On Nasrin's phone, open the same Web App URL.
2.  Log in with nasrin@demo.bd and the generated password.
3.  Once logged in, try to open the URL again with ?page=Users.

EXPECTED:
 · Login succeeds.
 · The Users page shows a red alert:
   "Only the owner (Admin) can manage users."
 · If she tries to open the browser DevTools console and call
   apiCall('getAllUsers'), the server responds with:
   "Permission denied. Required role: Admin. Your role: Cashier."

Test 4 — Session Expires Correctly

To test session expiry without waiting six hours, temporarily change the cache TTL from 21600 to 60 (one minute) in login(). Log in, wait 61 seconds, then click any button.

Expected after 60 seconds
· A browser alert: "Your session expired. Please log in again."
· Redirect to the Login page.
· The token is removed from localStorage.

Remember to change the TTL back to 21600 when done.

Test 5 — Brute Force Protection

Steps
1.  Open the login page in an incognito window.
2.  Try the email karim@demo.bd with five wrong passwords.

EXPECTED:
 · After the 5th failed attempt, the error becomes:
   "Too many failed attempts. Please try again in 15 minutes."
 · Even the correct password will be rejected for the next 15 minutes.
 · AuditLog shows 5 rows of LOGIN_FAILED_BAD_PASSWORD.
🧪
Why test security? Because security code is the only code where "looks correct" is not good enough. A single mis-ordered if statement can completely disable your authentication. These five tests take 15 minutes and can save your shop thousands of taka in liability.
✓

Part 2 Completion Checklist

Before moving to Part 3, make sure you have done all of these:

  • ENCRYPTION_KEY is set in Script Properties and saved in a password manager.
  • hashPassword() and verifyPassword() are working — verified by running the test.
  • At least one Admin user is in the Users sheet with a hash from generateHashForAdmin.
  • generateHashForAdmin has been deleted from the codebase.
  • The Web App is deployed with Execute as: Me, Access: Anyone.
  • You can log in from an incognito browser window with your admin credentials.
  • After login, the Dashboard page shows your name and role.
  • The AuditLog sheet shows a LOGIN_SUCCESS row for your test login.
  • A test user created as Cashier cannot access the Users page.
  • Calling an Admin-only function from a Cashier session returns "Permission denied."
  • Logout clears the session and redirects to the login page.
  • You have shared the Web App URL with at least one test user on their phone.
৳
Total cost so far: ৳ 0. No hosting, no database, no SSL certificate, no domain. You have a real, working, multi-user, multi-role ERP backend with audit logging and session management — running entirely on free Google infrastructure.
Q

Knowledge Check — Interactive Quiz

Eight questions covering Part 2. Tap an answer for instant feedback.

Part 2 Quiz

No login required. Just knowledge.

Score: 0 / 0
?

Frequently Asked Questions

Is HMAC-SHA256 really secure enough for a real shop?
For a small shop in Bangladesh, yes. HMAC-SHA256 with a long secret key is not breakable by brute force in any realistic timeframe. The bigger risk is not the algorithm but the key — if you write ENCRYPTION_KEY in your notebook, or share it with the cashier, security is gone. Treat the key like the shop's bank PIN.
Can I use Google login instead of email + password?
You could, but you would need each employee to have a Google account and then to grant it permission — that is friction. Email + password is more practical for a shop with 5 staff, where some may not have personal Google accounts.
What if a cashier forgets their password?
The owner (Admin) clicks "Reset" on that user in the Users page. The system generates a fresh 12-character password and shows it once. The owner shares it with the cashier via WhatsApp. The cashier logs in and can then change it to something they remember via the profile page.
Can I keep a cashier logged in forever?
You could increase the cache TTL, but we advise against it. Six hours is the sweet spot: it covers a full shift, and it caps the exposure window if a phone is stolen. Remember that Google's CacheService refuses TTLs above 21,600 seconds (6 hours) anyway.
What if two users share the same email?
The system prevents this. When you try to add a user with an existing email, createUser() throws "A user with this email already exists." Each employee must have a unique email. If they do not have one, use a format like nasrin@demo.bd on the shop's own domain, or use a Gmail alias like yourname+cashier1@gmail.com.
Why not use Google Sheets' built-in sharing instead of a login system?
Because Google Sheets sharing gives all-or-nothing access. If a cashier can open the sheet, they can delete rows, change prices, see profit margins, and even download the whole database. Our login system narrows access to exactly the actions each role needs — and it does not require the employee to own a Google account.
What happens to my users if I redeploy the Web App?
Nothing. Existing sessions stay valid because they live in CacheService, which is independent of the deployment version. Users do not need to log in again after you push a code update.
Do I have to check the AuditLog every day?
No. But you should check it once a week. Look for anything that stands out: failed logins from unrecognised emails, a user disabling others without your permission, or password resets you did not initiate. If everything looks normal, close the sheet and move on.
→

What's Coming in Part 3

Part 2 gave you authenticated, role-aware access. Part 3 turns it into a working point-of-sale system.

  • POS Screen: product search, cart, discount, due calculation — with a beautiful Bangladeshi-style receipt preview.
  • Product Catalog: add, edit, deactivate products without leaving the app.
  • Stock Ledger: proper double-entry IN/OUT with running balance.
  • Purchase Entry (GRN): record stock received from suppliers.
  • Customers & Dues: track who owes the shop money, with a printable statement.
  • Concurrency in Action: how LockService prevents two cashiers from getting the same invoice number.
  • Sample receipts in Bengali with ৳ amounts and shop details.
📚
Complete the checklist in this part before moving on. A robust authentication layer is the foundation of everything else — if it is shaky, every later feature inherits the shakiness.
📖

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!