Vetri Madani | Financial Training & Events

Admin Workspace

Manage Events & Leads

Google Sheets Connection

⬤ Not Connected

⚡ Why every new device needed reconnecting before:
The URL was saved in localStorage — which is per-browser, per-device. A new phone = empty localStorage = no Sheets connection = public visitors can't see events or submit leads.

✅ The permanent fix (already built into this file):
Open this HTML file in any code editor, find this line near the top of the <script> tag:
const HARDCODED_SHEETS_URL = ''; Paste your Apps Script URL between the single quotes, save the file, commit and push to GitHub.
That's it. Every visitor — on any device, anywhere in the world — will automatically connect. No admin setup required.

Or paste here to test in this browser right now (temporary — won't affect other devices):

Setup Guide — 3 Steps (~5 min)

Create a new Google Sheet at sheets.new. Name it Vetri Madani.

Create five tabs at the bottom:

• Rename Sheet1 → Leads — add these headers in Row 1:

ID	Name	Email	Phone	Event	Occupation	Source	IC Number	IC File	Employment Status	Company Name	Declaration	Declaration File	Status	Notes	Timestamp

• Add a second tab, name it Events — add these headers in Row 1:

ID	Title	Category	Date	Venue	Seats	Status	Link	Image	Video	Description	Brochure	Declaration

• Add a third tab, name it EventImages — add headers in Row 1: imageKey imageData (this stores uploaded event photos)
• Add a fourth tab, name it SiteContent — add these headers in Row 1:

data value

• Add a fourth tab, name it Reviews — same headers:

data value

• Add a fifth tab, name it FooterLinks — same headers:

data value

Open Apps Script. In your Sheet click Extensions → Apps Script. Delete all existing code, paste the script below, click Save (Ctrl+S).

// ── Vetri Madani — Apps Script v4 ──────────────────────
// Handles both Events and Leads sheets.
// Sheet must have tabs named exactly: "Leads" and "Events"

const SS = SpreadsheetApp.getActiveSpreadsheet();

function doPost(e) {
  try {
    const payload = JSON.parse(e.postData.contents);
    const action  = payload.action;

    if (action === 'addLead')       return addLead(payload.data);
    if (action === 'addEvent')      return saveEvent(payload.data, false);
    if (action === 'editEvent')     return saveEvent(payload.data, true);
    if (action === 'deleteEvent')   return deleteEvent(payload.id);
    if (action === 'saveImage')     return saveEventImage(payload.imageKey, payload.imageData);
    if (action === 'savePdf')       return saveEventPdf(payload.pdfKey, payload.pdfData, payload.pdfName);
    if (action === 'saveIcFile')    return saveLeadFile(payload.fileKey, payload.fileData, payload.fileName);
    if (action === 'saveContent')   return saveKeyValue('SiteContent', payload.data);
    if (action === 'saveReviews')   return saveKeyValue('Reviews', payload.data);
    if (action === 'saveFooter')    return saveKeyValue('FooterLinks', payload.data);

    return jsonOut({success:false, error:'Unknown action: ' + action});
  } catch(err) {
    return jsonOut({success:false, error:err.message});
  }
}

function doGet(e) {
  try {
    const type = (e.parameter.type || 'leads');
    if (type === 'events') {
      const evRows = getSheetRows('Events');
      evRows.forEach(ev => {
        if (ev.image && ev.image.startsWith('img_')) {
          ev.image = getEventImage(ev.image) || ev.image;
        }
        // brochure and declaration are stored as keys; keep keys — client fetches on demand
      });
      return jsonOut({success:true, events: evRows});
    }
    if (type === 'pdf') {
      const key  = e.parameter.key || '';
      const data = getEventPdf(key);
      if (!data) return jsonOut({success:false, error:'PDF not found'});
      return jsonOut({success:true, pdfData: data.data, pdfName: data.name});
    }
    if (type === 'siteContent') return jsonOut({success:true, data: getKeyValueSheet('SiteContent')});
    if (type === 'reviews')     return jsonOut({success:true, data: getKeyValueSheet('Reviews')});
    if (type === 'footerLinks') return jsonOut({success:true, data: getKeyValueSheet('FooterLinks')});
    return jsonOut({success:true, leads: getSheetRows('Leads')});
  } catch(err) {
    return jsonOut({success:false, error:err.message});
  }
}

// ── EVENT IMAGES (stored separately to keep Event rows small) ────
function saveEventImage(imageKey, imageData) {
  let sheet = SS.getSheetByName('EventImages');
  if (!sheet) {
    sheet = SS.insertSheet('EventImages');
    sheet.appendRow(['imageKey', 'imageData']);
  }
  // Upsert: update existing key or append new row
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    if (rows[i][0] === imageKey) {
      sheet.getRange(i + 1, 2).setValue(imageData);
      return jsonOut({success:true, imageKey});
    }
  }
  sheet.appendRow([imageKey, imageData]);
  return jsonOut({success:true, imageKey});
}

function getEventImage(imageKey) {
  const sheet = SS.getSheetByName('EventImages');
  if (!sheet) return null;
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    if (rows[i][0] === imageKey) return rows[i][1] || null;
  }
  return null;
}

// ── PDF FILES (brochures, declaration forms, IC copies) ──────────
function saveEventPdf(pdfKey, pdfData, pdfName) {
  let sheet = SS.getSheetByName('EventFiles');
  if (!sheet) {
    sheet = SS.insertSheet('EventFiles');
    sheet.appendRow(['pdfKey', 'pdfName', 'pdfData']);
  }
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    if (rows[i][0] === pdfKey) {
      sheet.getRange(i+1, 2, 1, 2).setValues([[pdfName, pdfData]]);
      return jsonOut({success:true, pdfKey});
    }
  }
  sheet.appendRow([pdfKey, pdfName||'', pdfData||'']);
  return jsonOut({success:true, pdfKey});
}

function getEventPdf(pdfKey) {
  const sheet = SS.getSheetByName('EventFiles');
  if (!sheet) return null;
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    if (rows[i][0] === pdfKey) return {name: rows[i][1]||'', data: rows[i][2]||''};
  }
  return null;
}

function saveLeadFile(fileKey, fileData, fileName) {
  // Reuse EventFiles sheet for lead IC uploads
  return saveEventPdf(fileKey, fileData, fileName);
}

// ── KEY-VALUE STORE (for SiteContent, Reviews, FooterLinks) ──────
function saveKeyValue(sheetName, jsonString) {
  let sheet = SS.getSheetByName(sheetName);
  if (!sheet) { sheet = SS.insertSheet(sheetName); sheet.appendRow(['data']); }
  // Store entire JSON in cell B2 with key "data" in A2
  const data = sheet.getDataRange().getValues();
  if (data.length < 2) {
    sheet.appendRow(['data', jsonString]);
  } else {
    sheet.getRange(2, 1).setValue('data');
    sheet.getRange(2, 2).setValue(jsonString);
  }
  return jsonOut({success:true});
}

function getKeyValueSheet(sheetName) {
  const sheet = SS.getSheetByName(sheetName);
  if (!sheet) return null;
  const data = sheet.getDataRange().getValues();
  if (data.length < 2) return null;
  return data[1][1] || null; // B2
}

// ── LEADS ────────────────────────────────────────────────
function addLead(d) {
  const sheet = SS.getSheetByName('Leads');
  sheet.appendRow([
    Date.now().toString(), d.name||'', d.email||'', d.phone||'',
    d.event||'', d.occupation||'', d.source||'', d.ic_number||'',
    d.ic_file||'', d.employment_status||'', d.company_name||'',
    d.declaration_agreed||'', d.declaration_file||'', 'new', '',
    new Date().toLocaleString('en-MY',{timeZone:'Asia/Kuala_Lumpur'})
  ]);
  return jsonOut({success:true});
}

// ── EVENTS ───────────────────────────────────────────────
function saveEvent(d, isEdit) {
  const sheet = SS.getSheetByName('Events');
  if (isEdit) {
    // Find row by ID and update it
    const data = sheet.getDataRange().getValues();
    for (let i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(d.id)) {
        sheet.getRange(i+1, 1, 1, 11).setValues([[
          d.id, d.title||'', d.category||'', d.date||'',
          d.venue||'', d.seats||'', d.status||'open',
          d.link||'', d.image||'', d.video||'', d.description||''
        ]]);
        return jsonOut({success:true});
      }
    }
    return jsonOut({success:false, error:'Event not found'});
  } else {
    // New event — prepend at top (insert row 2, push others down)
    const id = Date.now().toString();
    sheet.insertRowAfter(1);
    sheet.getRange(2, 1, 1, 11).setValues([[
      id, d.title||'', d.category||'', d.date||'',
      d.venue||'', d.seats||'', d.status||'open',
      d.link||'', d.image||'', d.video||'', d.description||''
    ]]);
    return jsonOut({success:true, id});
  }
}

function deleteEvent(id) {
  const sheet = SS.getSheetByName('Events');
  const data  = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (String(data[i][0]) === String(id)) {
      sheet.deleteRow(i + 1);
      return jsonOut({success:true});
    }
  }
  return jsonOut({success:false, error:'Event not found'});
}

// ── HELPERS ──────────────────────────────────────────────
function getSheetRows(sheetName) {
  const sheet = SS.getSheetByName(sheetName);
  if (!sheet) return [];
  const rows  = sheet.getDataRange().getValues();
  if (rows.length < 2) return [];
  // Normalise: lowercase + replace spaces with underscores so JS dot-access works
  // e.g. "IC File" -> "ic_file", "Employment Status" -> "employment_status"
  const headers = rows[0].map(h => h.toString().toLowerCase().trim().replace(/\s+/g, '_'));
  return rows.slice(1).map(row => {
    const obj = {};
    headers.forEach((h, i) => obj[h] = row[i] !== undefined ? row[i].toString() : '');
    return obj;
  }).filter(r => r.id || r.title || r.name); // skip blank rows
}

function jsonOut(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

Deploy as Web App. Click Deploy → New deployment:

• Type: Web app • Execute as: Me • Access: Anyone

Click Deploy, authorise when asked, then copy the Web App URL.

Then — open this HTML file in your editor and find:

const HARDCODED_SHEETS_URL = '';

Paste your Web App URL between the quotes, save, commit & push to GitHub. The page will now connect on every device automatically — no admin setup needed on any phone, tablet, or computer.

⚠ Every time you edit the Apps Script code you must create a New deployment — redeploying to an existing one does not update the live script.

What Works After Setup

✅ Events tab — Add, edit, delete events from any device. Changes appear live on the public page for all visitors within seconds.
✅ Leads tab — Every registration POSTs to your Leads sheet in real time.
✅ Public visitors — Events section loads fresh from Sheets on every page load — no caching, no stale data.
✅ Registration form — Event dropdown always reflects the current live events list from Sheets.
✅ Event image upload — Photos are compressed in-browser, uploaded separately to the EventImages sheet, then referenced by events — works globally.
✅ Content tab — Edit hero text, images, stats, registration section copy — all stored in Google Sheets, visible globally.
✅ Reviews tab — Add/edit customer testimonials shown in the Programme Overview section.
✅ Footer tab — Update social media handles, WhatsApp numbers and email links without any code.

Add New Event
Saving to Sheets…

Event Title *

Module / Category

Date

Venue

Max Seats

Status

Banner Image

Click to choose or drag & drop
JPG · PNG · WebP · GIF — auto-compressed to ≤150 KB

Image is compressed in your browser, then stored in Google Sheets alongside the event — visible globally to everyone.

Event Brochure (PDF — opens when user clicks More Details)

Click to choose or drag & drop a PDF
Max recommended: 2 MB

Trainee Declaration Form (PDF — users can download & fill before submitting)

Click to choose or drag & drop a PDF

YouTube Embed URL (optional)

Description

Live Events from Google Sheets

Connect Sheets to manage events here.

Total Leads

New

Contacted

Converted

Connecting to Google Sheets…

Lead	Contact	Event Interest	Source	Status	Registered
Loading leads…

—

Page Copywriting & Images
Saving…

ℹ How it works: Edit any text or image URL below and click Save Content to Sheets. Changes appear live on the page for all visitors worldwide — no developer needed.

Hero Section

Hero Badge Text

Hero Image URL

Hero Title (use | for line break, *text* for italic gold)

Hero Subtitle

Stat 1 Number

Stat 1 Label

Stat 2 Number

Stat 2 Label

Stat 3 Number

Stat 3 Label

Registration Section

Section Label

Section Title

Section Subtitle

Trust Point 1

Trust Point 2

Trust Point 3

Programme Overview Section

Section Label

Section Title

Section Subtitle

Customer Reviews — Programme Overview Section

The first review in the list is displayed live on the page. Add more for future use. Changes are visible to everyone after saving.

Footer Links & Contact Details

Edit the footer columns. For each link add a Label and a URL. Use https://wa.me/60XXXXXXXXX for WhatsApp, mailto:you@email.com for email, and full URLs for social media pages.

—

Total Registrations

All leads who have filled the Register Interest form.

—

With Uploaded Files

Registrations that include IC copies or signed declaration forms.

—

Registered Today

New registrations received today (Malaysia time).

—

Active Events

Events currently listed on the public page.

Export Registrations (CSV)

Download all lead / registration data as a spreadsheet-ready CSV file. Includes all fields: name, email, phone, event, IC number, employment status, company, declaration status, timestamps and more. Filter before exporting.

Filter by Event Filter by Status From Date To Date Has Uploaded Files

Name Email Phone Event Status IC Files

Load leads to preview

Download Uploaded Files

Retrieve any file uploaded by a registrant — IC copies, signed declaration forms — or any brochure/declaration PDF you uploaded for events. Files are fetched directly from Google Sheets.

Registrant Files (IC & Declaration Uploads)

No leads with uploaded files yet.

Bulk Download All Files

Downloads every uploaded file one by one. Files are saved individually to your browser's Downloads folder. Large batches may take a moment.

Event Brochures & Declaration Forms

Download any brochure or declaration PDF you have uploaded for each event.

No event files uploaded yet.

Build Wealth.
Plan Boldly.
Live Freely.

Structured Events

Expert Facilitators

Actionable Outcomes

Community Network

Featured Events

Take the First Step Toward Financial Freedom

Register Interest

Registration Received!

Comprehensive Financial Education

Wealth Fundamentals

Income Acceleration

Smart Investing

Protection Planning

Retirement Planning

Manage Events & Leads

Google Sheets Connection

Setup Guide — 3 Steps (~5 min)

What Works After Setup

Add New Event
Saving to Sheets…

Live Events from Google Sheets

Page Copywriting & Images
Saving…

Customer Reviews — Programme Overview Section

Footer Links & Contact Details

Export Registrations (CSV)

Download Uploaded Files

Registrant Files (IC & Declaration Uploads)

Bulk Download All Files

Event Brochures & Declaration Forms

Build Wealth.Plan Boldly.Live Freely.

Structured Events

Expert Facilitators

Actionable Outcomes

Community Network

Featured Events

Take the First Step Toward Financial Freedom

Register Interest

Registration Received!

Comprehensive Financial Education

Wealth Fundamentals

Income Acceleration

Smart Investing

Protection Planning

Retirement Planning

Manage Events & Leads

Google Sheets Connection

Setup Guide — 3 Steps (~5 min)

What Works After Setup

Add New Event Saving to Sheets…

Live Events from Google Sheets

Page Copywriting & Images Saving…

Customer Reviews — Programme Overview Section

Footer Links & Contact Details

Export Registrations (CSV)

Download Uploaded Files

Registrant Files (IC & Declaration Uploads)

Bulk Download All Files

Event Brochures & Declaration Forms

Admin Login

Build Wealth.
Plan Boldly.
Live Freely.

Add New Event
Saving to Sheets…

Page Copywriting & Images
Saving…