Total Clients
0
Active accounts
Total USD
$0
All clients combined
Total NGN
₦0
All clients combined
Transactions
0
Total posted
📋 Recent Transactions
| Date | Client ID | Client | Description | Type | Ccy | Amount | Balance After |
|---|---|---|---|---|---|---|---|
| No transactions yet | |||||||
💼 Client Balances
👥
No clients yet
👥 All Clients
👤
No clients yet
Click Add New Client
💳 New Transaction Entry
Transaction Type
💰DEPOSIT
💸PAYMENT
🔄CONVERSION
🧾EXPENSE
Client & Date
Amount & Currency
Alert Channels
👁 Live Balance
Select a client
📧 Email Preview
Fill details to preview
📱 WhatsApp Preview
Fill details to preview
📋
Select a client above
📤 Send Weekly Summary
📋 Alert Log
📭
No alerts sent yet
👤 You Are Currently Logged In As
👥 Team Members
🔐 Roles
👑 Admin
Full access: add clients, all transactions, alerts, manage staff.
📝 Staff
Post transactions, view statements. Cannot add clients or manage staff.
👁️ Viewer
Read-only. View statements and balances only.
📋 All Actions
| Time | Staff | Action | Client | Detail | Status |
|---|---|---|---|---|---|
| Loading... | |||||
🎯 What You Will Have When Done
- Live web app on your own URL (phone & desktop)
- Real Gmail alerts on every transaction posted
- WhatsApp messages to clients via Twilio
- Permanent database in Google Sheets (no data loss)
- Staff logins with role-based access
- Auto weekly balance summary every Sunday
🛠️ Tech Stack
Google Sheets Database — FREE
Google Apps Script Backend — FREE
Gmail API Email alerts — FREE
Twilio WhatsApp — ~$0.005/msg
Netlify Web hosting — FREE
GitHub Code storage — FREE
STEP 1
📁 Set Up Google Sheet Database
Go to sheets.google.com → Create a new spreadsheet → Name it Neemat Motors DB
Create 4 sheet tabs with these exact column headers:
SHEET: CLIENTS
clientId | name | email | phone | whatsapp | city | notes | balanceNGN | balanceUSD | createdAt
SHEET: TRANSACTIONS
txId | clientId | date | type | currency | amount | narration | vehicle | rate | usdAmount | ngnDebit | balanceNGN | balanceUSD | createdAt
SHEET: ALERTS_LOG
logId | clientId | date | alertType | channel | status
SHEET: STAFF
staffId | name | email | role | phone | createdAt
Copy the Spreadsheet ID from the URL (long string between /d/ and /edit). You will need it in Step 2.
STEP 2
📧 Set Up Gmail via Google Apps Script (FREE)
- In your Google Sheet: click
Extensions→Apps Script - Delete all existing code and paste the script below
- Replace
YOUR_SPREADSHEET_IDwith your actual ID from Step 1 - Click 💾 Save, then click
Runonce and grant Gmail permission when asked - Click
Deploy→New Deployment→ Type: Web App - Set Who has access to
Anyone→ Deploy → Copy the Web App URL
const SHEET_ID = 'YOUR_SPREADSHEET_ID';
const ss = SpreadsheetApp.openById(SHEET_ID);
function doPost(e) {
const d = JSON.parse(e.postData.contents);
if(d.action==='addClient') return ok(addClient(d));
if(d.action==='postTransaction') return ok(postTransaction(d));
if(d.action==='getClients') return ok(getClients());
if(d.action==='sendWeekly') return ok(sendWeeklyAll(d));
return ok({error:'Unknown action'});
}
function ok(obj){ return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON); }
function addClient(d) {
const id = 'NM' + String(Date.now()).slice(-6);
ss.getSheetByName('CLIENTS').appendRow([
id, d.name, d.email, d.phone, d.whatsapp,
d.city||'', d.notes||'', d.balanceNGN||0, d.balanceUSD||0, new Date()]);
return { success:true, clientId:id };
}
function postTransaction(d) {
const id = 'TX' + String(Date.now()).slice(-8);
ss.getSheetByName('TRANSACTIONS').appendRow([
id, d.clientId, d.date, d.type, d.currency,
d.amount, d.narration, d.vehicle||'', d.rate||'',
d.usdAmount||'', d.ngnDebit||'', d.balanceNGN, d.balanceUSD, new Date()]);
updateBalance(d.clientId, d.balanceNGN, d.balanceUSD);
if(d.sendEmail) sendTxEmail(d);
return { success:true, txId:id };
}
function updateBalance(clientId, ngn, usd) {
const sh = ss.getSheetByName('CLIENTS');
const rows = sh.getDataRange().getValues();
for(let i=1;i<rows.length;i++){
if(rows[i][0]===clientId){
sh.getRange(i+1,8).setValue(ngn);
sh.getRange(i+1,9).setValue(usd);
break;
}
}
}
function sendTxEmail(d) {
const isD = d.type==='payment'||d.type==='expense';
const sign = isD ? '-' : '+';
const color = isD ? '#EF4444' : '#22C55E';
const amt = d.currency==='USD' ? '$'+Number(d.amount).toFixed(2) : '₦'+Number(d.amount).toLocaleString();
const html = buildEmailHTML(d, sign+amt, isD?'🔴 DEBIT':'🟢 CREDIT', color);
GmailApp.sendEmail(d.clientEmail, '🚗 Neemat Motors - Transaction Alert', '', {htmlBody:html});
}
function buildEmailHTML(d, amtStr, indicator, color) {
return `<div style="font-family:Arial;max-width:500px;margin:0 auto">
<div style="background:#0A5C36;padding:16px 20px;border-radius:10px 10px 0 0">
<h2 style="color:#fff;margin:0;font-size:17px">🚗 Neemat Motors</h2>
<p style="color:rgba(255,255,255,0.65);margin:3px 0 0;font-size:11px">Transaction Alert | Kaduna, Nigeria | +1-240-288-0001</p>
</div>
<div style="background:#fff;padding:18px;border:1px solid #e5e7eb;border-radius:0 0 10px 10px">
<p>Dear <strong>${d.clientName}</strong> <span style="background:#f0fdf4;border:1px solid #86efac;border-radius:4px;padding:1px 7px;font-size:11px;font-weight:700;color:#16a34a">${d.clientId}</span></p>
<p style="color:#555;margin:10px 0;font-size:13px">A transaction has been posted to your account:</p>
<table style="width:100%;border-collapse:collapse;font-size:12px">
<tr style="background:#f9fafb"><td style="padding:8px 12px;color:#666;border-bottom:1px solid #eee">Client ID</td><td style="padding:8px 12px;font-weight:700;border-bottom:1px solid #eee;color:#0A5C36">${d.clientId}</td></tr>
<tr><td style="padding:8px 12px;color:#666;border-bottom:1px solid #eee">Date</td><td style="padding:8px 12px;font-weight:700;border-bottom:1px solid #eee">${d.date}</td></tr>
<tr style="background:#f9fafb"><td style="padding:8px 12px;color:#666;border-bottom:1px solid #eee">Description</td><td style="padding:8px 12px;font-weight:700;border-bottom:1px solid #eee">${d.narration}</td></tr>
<tr><td style="padding:8px 12px;color:#666;border-bottom:1px solid #eee">Amount</td><td style="padding:8px 12px;font-weight:700;font-size:16px;color:${color};border-bottom:1px solid #eee">${amtStr}</td></tr>
<tr style="background:#f9fafb"><td style="padding:8px 12px;color:#666;border-bottom:1px solid #eee">Indicator</td><td style="padding:8px 12px;font-weight:700;border-bottom:1px solid #eee">${indicator}</td></tr>
<tr><td style="padding:8px 12px;color:#666;border-bottom:1px solid #eee">USD Balance</td><td style="padding:8px 12px;font-weight:700;color:#0A5C36;border-bottom:1px solid #eee">$${Number(d.balanceUSD).toFixed(2)}</td></tr>
<tr style="background:#f9fafb"><td style="padding:8px 12px;color:#666">NGN Balance</td><td style="padding:8px 12px;font-weight:700;color:#0A5C36">₦${Number(d.balanceNGN).toLocaleString()}</td></tr>
</table>
<p style="font-size:10px;color:#888;border-top:2px solid #0A5C36;padding-top:9px;margin-top:13px">
Automated alert | Neemat Motors | Kaduna, Nigeria | +1-240-288-0001
</p>
</div></div>`;
}
function getClients() {
const rows = ss.getSheetByName('CLIENTS').getDataRange().getValues();
const headers = rows[0];
return rows.slice(1).map(r => {
const obj = {};
headers.forEach((h,i) => obj[h]=r[i]);
return obj;
});
}
function sendWeeklyAll(d) {
const rows = ss.getSheetByName('CLIENTS').getDataRange().getValues().slice(1);
rows.forEach(r => {
if(d.clientId && d.clientId!==r[0]) return;
GmailApp.sendEmail(r[2], '📊 Neemat Motors - Weekly Balance', '', {
htmlBody: weeklyEmailHTML(r[0],r[1],r[8],r[7],d.customMsg||'')
});
});
return {success:true};
}
function weeklyEmailHTML(id,name,usd,ngn,msg){
return `<div style="font-family:Arial;max-width:500px;margin:0 auto">
<div style="background:#0A5C36;padding:16px;border-radius:10px 10px 0 0">
<h2 style="color:#fff;margin:0">🚗 Neemat Motors</h2>
<p style="color:rgba(255,255,255,0.65);margin:3px 0 0;font-size:11px">Weekly Balance Summary</p>
</div>
<div style="background:#fff;padding:18px;border:1px solid #e5e7eb;border-radius:0 0 10px 10px">
<p>Dear <strong>${name}</strong> <span style="background:#fef9c3;border:1px solid #fde047;border-radius:4px;padding:1px 7px;font-size:11px;font-weight:700;color:#854d0e">${id}</span></p>
<p style="font-size:13px;color:#555;margin:10px 0">Your account balance as of ${new Date().toLocaleDateString('en-GB',{day:'numeric',month:'long',year:'numeric'})}:</p>
<table style="width:100%;border-collapse:separate;border-spacing:8px">
<tr>
<td style="background:#f0fdf4;border:2px solid #86efac;border-radius:8px;padding:14px;text-align:center">
<div style="font-size:10px;color:#666;text-transform:uppercase">USD Balance</div>
<div style="font-size:24px;font-weight:800;color:#16a34a">$${Number(usd).toFixed(2)}</div>
</td>
<td style="background:#fefce8;border:2px solid #fde047;border-radius:8px;padding:14px;text-align:center">
<div style="font-size:10px;color:#666;text-transform:uppercase">NGN Balance</div>
<div style="font-size:24px;font-weight:800;color:#b45309">₦${Number(ngn).toLocaleString()}</div>
</td>
</tr>
</table>
${msg?'<p style="background:#f9fafb;padding:10px;border-radius:6px;font-size:12px;margin-top:10px">'+msg+'</p>':''}
<p style="font-size:10px;color:#888;border-top:2px solid #0A5C36;padding-top:9px;margin-top:13px">
Neemat Motors | Kaduna, Nigeria | +1-240-288-0001
</p>
</div></div>`;
}
// Run this manually or via Trigger for weekly auto-send
function autoWeeklySummary() { sendWeeklyAll({}); }
STEP 3
📱 Set Up WhatsApp via Twilio
- Go to twilio.com → Sign up free
- Go to Messaging → Try it out → Send a WhatsApp message to set up the sandbox
- Each client sends
join [word]to Twilio sandbox number to opt in (one-time) - Get your
Account SIDandAuth Tokenfrom the Twilio Console - Add this function to your Apps Script from Step 2:
function sendWhatsApp(toPhone, message) {
const SID = 'YOUR_TWILIO_ACCOUNT_SID';
const TOKEN = 'YOUR_TWILIO_AUTH_TOKEN';
const FROM = 'whatsapp:+14155238886'; // sandbox number
UrlFetchApp.fetch(
'https://api.twilio.com/2010-04-01/Accounts/'+SID+'/Messages.json',
{ method:'post',
payload:{ From:FROM, To:'whatsapp:'+toPhone, Body:message },
headers:{ Authorization:'Basic '+Utilities.base64Encode(SID+':'+TOKEN) }
}
);
}
// WhatsApp message template:
function buildWAMsg(clientId, clientName, txType, narration, amount, currency, balUSD, balNGN, date) {
const isDebit = txType==='payment' || txType==='expense';
const indicator = isDebit ? '🔴 DEBIT' : '🟢 CREDIT';
const amt = currency==='USD' ? '$'+Number(amount).toFixed(2) : '₦'+Number(amount).toLocaleString();
return `🚗 *NEEMAT MOTORS*
Transaction Alert
👤 *${clientName}*
ID: \`${clientId}\`
📅 ${date}
📝 ${narration}
💰 Amount: *${amt}*
${indicator}
💵 USD Bal: *$${Number(balUSD).toFixed(2)}*
💸 NGN Bal: *₦${Number(balNGN).toLocaleString()}*
For queries: +1-240-288-0001
_Neemat Motors | Kaduna, Nigeria_`;
}
For production (remove sandbox opt-in), upgrade to WhatsApp Business API in Twilio. ~$10/month for a dedicated number. Well worth it for professional use.
STEP 4
🌐 Host Online Free (Netlify)
- Go to github.com → Create account → New repository:
neemat-motors - Upload
neemat-motors.htmlto the repository - Go to netlify.com → Sign up → Connect GitHub → Select repo → Deploy
- You get a free URL like
neemat-motors.netlify.appimmediately - Optional: Buy a domain like
app.neeatmotors.com(~$10/year on Namecheap) and connect it in Netlify
✅ Bookmark the URL on all staff phones. It works perfectly as a mobile web app.
STEP 5
🔗 Connect Frontend to Google Apps Script
Add this to the HTML file so data saves to Google Sheets instead of browser memory:
// Add near top of your script tag:
const GAS_URL = 'https://script.google.com/macros/s/AKfycbweTcJj5JKDirOUlkjysod8LEcXaQKcpYhLBv5HMlLkE1vZ_4l1vUbZ6i4i9MtTu5zDNw/exec';
// Replace the addClient() function body with:
async function addClient() {
// ... validation ...
const result = await fetch(GAS_URL, {
method:'POST', body: JSON.stringify({ action:'addClient', ...formData })
}).then(r => r.json());
if(result.success) { /* show success, refresh list */ }
}
// Replace postTransaction() with server call:
async function postTx() {
// ... validation ...
const result = await fetch(GAS_URL, {
method:'POST', body: JSON.stringify({ action:'postTransaction', ...txData })
}).then(r => r.json());
if(result.success) showToast('Posted! TX ID: ' + result.txId);
}
STEP 6
⏰ Auto Weekly Alerts Every Sunday (Google Trigger)
- In Google Apps Script, click the ⏰ Triggers icon (left sidebar clock icon)
- Click + Add Trigger (bottom right)
- Function:
autoWeeklySummary| Event:Time-driven| Type:Week timer| Day:Every Sunday| Time:8am–9am - Click Save — done! Every client gets weekly balance emails automatically every Sunday
COST
💰 Monthly Cost Summary
COMPONENT COST/MONTH
-----------------------------------------
Google Sheets + Apps Script FREE
Gmail alerts (100/day limit) FREE
Netlify hosting FREE
GitHub FREE
-----------------------------------------
WhatsApp sandbox (testing) FREE
WhatsApp Business (production) ~$10.00
Twilio messages (500/month) ~$2.50
Custom domain (optional) ~$1.00
-----------------------------------------
Email only (full system): $0.00 FREE
Email + WhatsApp (production): ~$13.50/month
HELP
📞 Troubleshooting & Support
- Gmail not sending: Re-run Apps Script and re-grant Gmail permissions
- CORS error: Make sure Web App "Who has access" is set to
Anyone - WhatsApp not received: Client must send the join code first (sandbox mode)
- Data lost on refresh: Steps 4–5 (Google Sheets connection) not yet done — data is in browser memory only
- Need a developer: Post on Upwork.com — search "Google Apps Script" — budget $50–$150 for full setup
- WhatsApp Business API alternative: Try WATI.io (easier setup, ~$49/month) or Interakt for Nigeria