Code.gs

/**
 * 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', 'user1@demo.com', '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', 'user2@demo.com', '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', 'user3@demo.com', '03001000003', '1995-11-08', '',          'Business Analyst',   'Finance',    'Analyst',          '3-5 years',  'Referral',          'Technology, Support',   '',                       '', ''],
    [4, '2026-04-03T09:33:41Z', 'User 4', 'user4@demo.com', '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', 'user5@demo.com', '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.';
}