/**

* Developed by Rameez Scripts

* WhatsApp: https://wa.me/923224083545 (For Custom Projects)

* YouTube: https://www.youtube.com/@rameezimdad (Subscribe for more!)

*/

const SHEETS = { SETTINGS: 'Settings', RESPONSES: 'Responses', CONFIG: 'Config' };

// Settings cols: 0=id, 1=step, 2=step_label, 3=name, 4=label, 5=type, 6=options, 7=required, 8=placeholder, 9=order, 10=active, 11=depends_on

const SC = { ID:0, STEP:1, STEP_LABEL:2, NAME:3, LABEL:4, TYPE:5, OPTIONS:6, REQUIRED:7, PLACEHOLDER:8, ORDER:9, ACTIVE:10, DEPENDS_ON:11 };

function doGet(e) {

return HtmlService.createHtmlOutputFromFile('index')

.setTitle('Multi-Step Form')

.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)

.addMetaTag('viewport', 'width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no');

}

function getSheet(name) {

return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);

}

function getSheetData(name) {

const sh = getSheet(name);

if (!sh || sh.getLastRow() < 2) return [];

return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();

}

function ts() { return new Date().toISOString(); }

function getNextId(name) {

const sh = getSheet(name);

if (sh.getLastRow() < 2) return 1;

const ids = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues().flat().filter(v => v !== '');

return ids.length ? Math.max(...ids) + 1 : 1;

}

function getConfig() {

const sh = getSheet(SHEETS.CONFIG);

if (!sh || sh.getLastRow() < 2) return {};

return sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues()

.reduce((m, r) => { if (r[0]) m[String(r[0]).trim()] = String(r[1]).trim(); return m; }, {});

}

function getUploadFolder() {

// folder named after the spreadsheet — auto

const name = SpreadsheetApp.getActiveSpreadsheet().getName();

const it = DriveApp.getFoldersByName(name);

return it.hasNext() ? it.next() : DriveApp.createFolder(name);

}

function uploadFile(base64, name, mime) {

try {

const bytes = Utilities.base64Decode(base64);

const blob = Utilities.newBlob(bytes, mime, name);

const folder = getUploadFolder();

const file = folder.createFile(blob);

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

const id = file.getId();

// images → lh3 CDN, other files → drive view link

const isImage = mime.startsWith('image/');

const url = isImage

? 'https://lh3.google.com/u/0/d/' + id

: 'https://drive.google.com/file/d/' + id + '/view';

return { success: true, data: { url, fileId: id, name } };

} catch(e) {

return { success: false, message: e.message };

}

}

function getFormConfig() {

try {

const rows = getSheetData(SHEETS.SETTINGS);

const active = rows.filter(r => String(r[SC.ACTIVE]).toLowerCase() === 'yes');

active.sort((a, b) => a[SC.STEP] - b[SC.STEP] || a[SC.ORDER] - b[SC.ORDER]);

const stepMap = {};

active.forEach(r => {

const s = r[SC.STEP];

if (!stepMap[s]) stepMap[s] = { num: s, label: r[SC.STEP_LABEL], fields: [] };

const rawOpts = r[SC.OPTIONS] ? String(r[SC.OPTIONS]).trim() : '';

const isDep = String(r[SC.TYPE]) === 'dependent_select';

stepMap[s].fields.push({

id: r[SC.ID],

name: r[SC.NAME],

label: r[SC.LABEL],

type: r[SC.TYPE],

options: (!isDep && rawOpts) ? rawOpts.split(',').map(o => o.trim()).filter(Boolean) : [],

rawOptions: rawOpts,

required: String(r[SC.REQUIRED]).toLowerCase() === 'yes',

placeholder: r[SC.PLACEHOLDER] || '',

dependsOn: r[SC.DEPENDS_ON] ? String(r[SC.DEPENDS_ON]).trim() : ''

});

});

const steps = Object.values(stepMap).sort((a, b) => a.num - b.num);

const appConfig = getConfig();

return { success: true, data: { steps, appConfig } };

} catch(e) {

return { success: false, message: e.message };

}

}

function checkDuplicate(email, fieldName) {

try {

const sh = getSheet(SHEETS.RESPONSES);

if (!sh || sh.getLastRow() < 2) return { success: true, data: { exists: false } };

const headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];

const col = headers.indexOf(fieldName);

if (col === -1) return { success: true, data: { exists: false } };

const vals = sh.getRange(2, col + 1, sh.getLastRow() - 1, 1).getValues().flat();

const exists = vals.some(v => String(v).toLowerCase().trim() === String(email).toLowerCase().trim());

return { success: true, data: { exists } };

} catch(e) {

return { success: false, message: e.message };

}

}

function sendEmails(formData, id, fieldDefs, cfg) {

try {

const submitterEmail = String(formData['email'] || '').trim();

const adminEmail = String(cfg['admin_email'] || '').trim();

if (!submitterEmail && !adminEmail) return;

const rows = fieldDefs.map(f => {

let val = formData[f.name];

if (Array.isArray(val)) val = val.join(', ');

val = (val !== undefined && val !== null && val !== '') ? String(val) : '—';

if (val.startsWith('https://lh3.google.com') || val.startsWith('https://drive.google.com')) val = '[Uploaded File]';

return `<tr><td style="padding:8px 12px;font-weight:600;color:#555;border-bottom:1px solid #f0f0f0;white-space:nowrap;font-size:13px">${f.label}</td><td style="padding:8px 12px;color:#222;border-bottom:1px solid #f0f0f0;font-size:13px">${val}</td></tr>`;

}).join('');

const tableHtml = `<table style="width:100%;border-collapse:collapse">${rows}</table>`;

const wrap = (intro, footer) => `

<div style="font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;max-width:620px;margin:0 auto;background:#f5f5f5;padding:20px">

<div style="background:#001f3f;padding:20px 28px;border-radius:6px 6px 0 0">

<h2 style="color:white;margin:0;font-size:18px;font-weight:700">Form Submission — Ref #${id}</h2>

</div>

<div style="background:white;padding:24px 28px;border-radius:0 0 6px 6px;border:1px solid #e0e0e0">

${intro}

<div style="background:#f8f9fa;border-radius:4px;border:1px solid #e0e0e0;overflow:hidden;margin:16px 0">${tableHtml}</div>

${footer}

</div>

</div>`;

const emailRe = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

if (submitterEmail && emailRe.test(submitterEmail)) {

MailApp.sendEmail({

to: submitterEmail,

subject: `Submission Confirmed — Ref #${id}`,

htmlBody: wrap(

`<p style="color:#555;margin:0 0 4px;font-size:14px">Thank you for your submission! Here's a copy of your response:</p>`,

`<p style="color:#aaa;font-size:12px;margin:16px 0 0">Please keep this email for your records.</p>`

)

});

}

if (adminEmail && emailRe.test(adminEmail)) {

MailApp.sendEmail({

to: adminEmail,

subject: `New Form Submission — Ref #${id}`,

htmlBody: wrap(

`<p style="color:#555;margin:0 0 4px;font-size:14px">A new form submission was received. Reference ID: <strong>#${id}</strong></p>`,

`<p style="color:#aaa;font-size:12px;margin:16px 0 0">Automated admin notification.</p>`

)

});

}

} catch(e) {

Logger.log('sendEmails err: ' + e.message); // non-fatal

}

}

function submitForm(formData) {

try {

const rows = getSheetData(SHEETS.SETTINGS);

const active = rows

.filter(r => String(r[SC.ACTIVE]).toLowerCase() === 'yes')

.sort((a, b) => a[SC.STEP] - b[SC.STEP] || a[SC.ORDER] - b[SC.ORDER]);

const sh = getSheet(SHEETS.RESPONSES);

const fieldNames = active.map(r => r[SC.NAME]);

const fieldDefs = active.map(r => ({ name: r[SC.NAME], label: r[SC.LABEL] }));

if (sh.getLastRow() === 0) {

sh.appendRow(['response_id', 'submitted_at', ...fieldNames]);

sh.getRange(1, 1, 1, fieldNames.length + 2).setFontWeight('bold').setBackground('#001f3f').setFontColor('white');

} else {

const existing = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];

fieldNames.forEach(fn => {

if (!existing.includes(fn)) {

sh.getRange(1, existing.length + 1).setValue(fn).setFontWeight('bold').setBackground('#001f3f').setFontColor('white');

existing.push(fn);

}

});

}

const headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];

const lock = LockService.getScriptLock();

lock.waitLock(10000);

let id;

try {

id = getNextId(SHEETS.RESPONSES);

const row = new Array(headers.length).fill('');

row[0] = id;

row[1] = ts();

fieldNames.forEach(fn => {

const idx = headers.indexOf(fn);

if (idx === -1) return;

const val = formData[fn];

row[idx] = Array.isArray(val) ? val.join(', ') : (val !== undefined ? String(val) : '');

});

sh.appendRow(row);

} finally {

lock.releaseLock();

}

const cfg = getConfig();

sendEmails(formData, id, fieldDefs, cfg);

return { success: true, message: 'Form submitted successfully!', data: { id } };

} catch(e) {

return { success: false, message: e.message };

}

}

function getResponses() {

try {

const sh = getSheet(SHEETS.RESPONSES);

if (!sh || sh.getLastRow() < 1) return { success: true, data: { headers: [], rows: [] } };

const headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];

if (sh.getLastRow() < 2) return { success: true, data: { headers, rows: [] } };

const vals = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();

const rows = vals.map(r => {

const obj = {};

headers.forEach((h, i) => { obj[h] = r[i] instanceof Date ? r[i].toISOString() : r[i]; });

return obj;

}).reverse();

return { success: true, data: { headers, rows } };

} catch(e) {

return { success: false, message: e.message };

}

}

// run once in Apps Script editor — wipes everything and seeds demo data

function setupDemoData() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const tmp = ss.insertSheet('_tmp');

ss.getSheets().forEach(s => { if (s.getName() !== '_tmp') ss.deleteSheet(s); });

// ── Config sheet ──

let sh = ss.insertSheet(SHEETS.CONFIG);

const cfgH = ['key', 'value'];

sh.appendRow(cfgH);

sh.getRange(1, 1, 1, 2).setFontWeight('bold').setBackground('#001f3f').setFontColor('white');

sh.setFrozenRows(1);

sh.setColumnWidth(1, 200);

sh.setColumnWidth(2, 420);

[

['admin_email', ''], // fill in your email to receive admin alerts

['success_title', 'Thank You!'],

['success_message', 'Your response has been recorded. We will be in touch shortly.'],

].forEach(r => sh.appendRow(r));

// ── Settings sheet ──

sh = ss.insertSheet(SHEETS.SETTINGS);

const h = ['field_id','step','step_label','field_name','field_label','field_type','options','required','placeholder','order','active','depends_on'];

sh.appendRow(h);

sh.getRange(1, 1, 1, h.length).setFontWeight('bold').setBackground('#001f3f').setFontColor('white');

sh.setFrozenRows(1);

sh.setColumnWidths(1, 2, 60);

sh.setColumnWidth(3, 160);

sh.setColumnWidth(4, 160);

sh.setColumnWidth(5, 180);

sh.setColumnWidth(6, 120);

sh.setColumnWidth(7, 400);

sh.setColumnWidth(8, 80);

sh.setColumnWidth(9, 200);

sh.setColumnWidths(10, 2, 70);

sh.setColumnWidth(12, 120);

const DEP_OPTS = 'Technology:Developer,DevOps Engineer,Data Scientist|Healthcare:Doctor,Nurse,Administrator|Finance:Analyst,Accountant,Financial Advisor|Education:Teacher,Principal,Coordinator|Retail:Store Manager,Sales Associate,Merchandiser|Manufacturing:Engineer,Technician,Supervisor|Other:Manager,Analyst,Consultant';

const fields = [

// id, step, step_label, name, label, type, options, required, placeholder, order, active, depends_on

[1, 1, 'Personal Info', 'full_name', 'Full Name', 'text', '', 'Yes', 'Enter your full name', 1, 'Yes', ''],

[2, 1, 'Personal Info', 'email', 'Email Address', 'email', '', 'Yes', 'Enter your email', 2, 'Yes', ''],

[3, 1, 'Personal Info', 'phone', 'Phone Number', 'tel', '', 'Yes', 'Enter your phone number', 3, 'Yes', ''],

[4, 1, 'Personal Info', 'dob', 'Date of Birth', 'date', '', 'No', '', 4, 'Yes', ''],

[5, 2, 'Professional Details', 'company', 'Company / Organization', 'text', '', 'No', 'Company name', 1, 'Yes', ''],

[6, 2, 'Professional Details', 'job_title', 'Job Title', 'text', '', 'No', 'Your job title', 2, 'Yes', ''],

[7, 2, 'Professional Details', 'industry', 'Industry', 'select', 'Technology,Healthcare,Finance,Education,Retail,Manufacturing,Other', 'Yes', 'Select industry', 3, 'Yes', ''],

[8, 2, 'Professional Details', 'industry_role', 'Role in Industry', 'dependent_select', DEP_OPTS, 'No', 'Select your role...', 4, 'Yes', 'industry'],

[9, 2, 'Professional Details', 'experience', 'Years of Experience', 'select', 'Less than 1,1-3 years,3-5 years,5-10 years,10+ years', 'Yes', 'Select experience', 5, 'Yes', ''],

[10, 3, 'About You', 'referral', 'How did you hear about us?', 'radio', 'Google Search,Social Media,Referral,Event / Conference,Other', 'Yes', '', 1, 'Yes', ''],

[11, 3, 'About You', 'interests', 'Areas of Interest', 'checkbox', 'Sales,Marketing,Technology,Support,Operations,HR', 'No', '', 2, 'Yes', ''],

[12, 3, 'About You', 'notes', 'Additional Notes', 'textarea', '', 'No', 'Anything else to share...', 3, 'Yes', ''],

[13, 4, 'Documents', 'profile_photo', 'Profile Photo', 'image', '', 'No', '', 1, 'Yes', ''],

[14, 4, 'Documents', 'resume', 'Resume / CV', 'file', '', 'No', '', 2, 'Yes', ''],

];

fields.forEach(f => sh.appendRow(f));

fields.forEach((_, i) => {

if (i % 2 === 1) sh.getRange(i + 2, 1, 1, h.length).setBackground('#f0f4f8');

});

// ── Responses sheet ──

const respHeaders = ['response_id','submitted_at','full_name','email','phone','dob','company','job_title','industry','industry_role','experience','referral','interests','notes','profile_photo','resume'];

sh = ss.insertSheet(SHEETS.RESPONSES);

sh.appendRow(respHeaders);

sh.getRange(1, 1, 1, respHeaders.length).setFontWeight('bold').setBackground('#001f3f').setFontColor('white');

sh.setFrozenRows(1);

sh.setColumnWidth(1, 100);

sh.setColumnWidth(2, 200);

sh.setColumnWidth(3, 160);

sh.setColumnWidth(4, 200);

sh.setColumnWidth(5, 140);

sh.setColumnWidth(6, 110);

sh.setColumnWidths(7, 2, 160);

sh.setColumnWidths(9, 2, 140);

sh.setColumnWidth(11, 120);

sh.setColumnWidth(12, 180);

sh.setColumnWidth(13, 220);

sh.setColumnWidth(14, 240);

sh.setColumnWidths(15, 2, 260);

// demo responses — numbered placeholders only (YouTube-safe)

const demo = [

[1, '2026-03-10T08:22:14Z', 'User 1', '[email protected]', '03001000001', '1992-03-15', 'Company 1', 'Product Manager', 'Technology', 'Developer', '5-10 years', 'Google Search', 'Sales, Marketing', 'Sample note for User 1', '', ''],

[2, '2026-03-18T11:45:30Z', 'User 2', '[email protected]', '03001000002', '1988-07-22', 'Company 2', 'Operations Lead', 'Healthcare', 'Nurse', '10+ years', 'Social Media', 'Operations, HR', 'Sample note for User 2', '', ''],

[3, '2026-03-27T14:09:55Z', 'User 3', '[email protected]', '03001000003', '1995-11-08', '', 'Business Analyst', 'Finance', 'Analyst', '3-5 years', 'Referral', 'Technology, Support', '', '', ''],

[4, '2026-04-03T09:33:41Z', 'User 4', '[email protected]', '03001000004', '1993-01-30', 'Company 3', 'Curriculum Designer','Education', 'Teacher', '1-3 years', 'Event / Conference','Marketing, Technology', 'Sample note for User 4', '', ''],

[5, '2026-04-09T16:51:07Z', 'User 5', '[email protected]', '03001000005', '1997-06-14', 'Company 4', 'Sales Executive', 'Retail', 'Store Manager', '1-3 years', 'Google Search', 'Sales, Operations', 'Sample note for User 5', '', ''],

];

demo.forEach(r => sh.appendRow(r));

demo.forEach((_, i) => {

if (i % 2 === 1) sh.getRange(i + 2, 1, 1, respHeaders.length).setBackground('#f0f4f8');

});

ss.deleteSheet(tmp);

SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(sh);

return 'Setup complete — Config + 4-step Settings (dependent_select) + 5 demo responses seeded.';

}