/** * Nucleous CRM — backend sheet connector (v3) * * SETUP * 1) Open your spreadsheet → Extensions → Apps Script * 2) Paste this entire file → Save * 3) Run setupNucleousBackend once (from the editor Run menu) * → Creates a sheet "Nucleous Leads" with full CRM columns * → Imports / maps existing rows from other tabs into it * 4) Deploy → New deployment → Web app * Execute as: Me | Who has access: Anyone * 5) Copy the /exec URL into Nucleous → Manual connection * * doGet → exports all leads as JSON (CRM import) * doPost → upserts lead changes from the CRM (status, notes, etc.) */ var DATASET_ID = 'manual_dataset'; var BACKEND_SHEET = 'Nucleous Leads'; /** Full CRM column layout (order matters) */ var CRM_HEADERS = [ 'Lead ID', 'Name', 'Email', 'Phone', 'Company', 'Industry', 'Status', 'Priority', 'Progress', 'Assigned To', 'Source', 'Location', 'Pin', 'Service', 'Gender', 'Notes', 'Activity Log', 'Documentation', 'Date Added', 'Last Updated', 'Hot', 'Deal Value', 'Custom JSON' ]; var STATUS_OPTIONS = ['New', 'Contacted', 'Qualified', 'Proposal', 'Negotiation', 'Sold', 'Lost', 'On Hold', 'Archived']; var PRIORITY_OPTIONS = ['Low', 'Medium', 'High', 'Urgent']; /* ───────── Web entry points ───────── */ function doGet(e) { e = e || {}; var p = e.parameter || {}; var action = String(p.action || p.fn || 'exportLeads'); try { ensureBackendSheet_(); if (action === 'ping' || action === 'health') { return json_({ ok: true, service: 'nucleous-backend-v3', sheet: BACKEND_SHEET, datasetId: DATASET_ID }); } if (action === 'setup') { var r = setupNucleousBackend(); return json_(r); } if (action === 'listSheets') { return json_(listSheets_()); } return json_(exportLeads_()); } catch (err) { return json_({ ok: false, error: String(err) }); } } function doPost(e) { try { ensureBackendSheet_(); var body = {}; if (e && e.postData && e.postData.contents) { body = JSON.parse(e.postData.contents); } var action = String((body && body.action) || (body && body.fn) || 'upsertLeads'); if (action === 'upsertLeads' || action === 'syncFromCrm' || action === 'push') { return json_(upsertFromCrm_(body)); } if (action === 'setup') { return json_(setupNucleousBackend()); } return json_({ ok: true }); } catch (err) { return json_({ ok: false, error: String(err) }); } } /** Run once from the script editor */ function setupNucleousBackend() { var ss = SpreadsheetApp.getActiveSpreadsheet(); if (!ss) throw new Error('Open this project from the spreadsheet'); var backend = ensureBackendSheet_(); var imported = importExistingTabsIntoBackend_(ss, backend); return { ok: true, sheet: BACKEND_SHEET, imported: imported, message: 'Nucleous Leads is ready. Deploy as Web App (Anyone) and paste the /exec URL in Nucleous.' }; } function testExport() { ensureBackendSheet_(); var pack = exportLeads_(); Logger.log('count=' + pack.count); Logger.log(JSON.stringify((pack.rows || []).slice(0, 2))); } /* ───────── Backend sheet ───────── */ function ensureBackendSheet_() { var ss = SpreadsheetApp.getActiveSpreadsheet(); if (!ss) throw new Error('Bind script to a spreadsheet (Extensions > Apps Script from the file)'); var sheet = ss.getSheetByName(BACKEND_SHEET); if (!sheet) { sheet = ss.insertSheet(BACKEND_SHEET); } // Ensure header row var lastCol = Math.max(sheet.getLastColumn(), CRM_HEADERS.length); var existing = sheet.getRange(1, 1, 1, CRM_HEADERS.length).getValues()[0]; var needsHeader = !existing[0] || String(existing[0]).toLowerCase().indexOf('lead') < 0; if (needsHeader || sheet.getLastRow() === 0) { sheet.getRange(1, 1, 1, CRM_HEADERS.length).setValues([CRM_HEADERS]); sheet.getRange(1, 1, 1, CRM_HEADERS.length).setFontWeight('bold'); sheet.setFrozenRows(1); } else { // Fill any missing header cells for (var c = 0; c < CRM_HEADERS.length; c++) { if (!existing[c]) sheet.getRange(1, c + 1).setValue(CRM_HEADERS[c]); } } return sheet; } function listSheets_() { var ss = SpreadsheetApp.getActiveSpreadsheet(); return { ok: true, spreadsheetId: ss.getId(), spreadsheetName: ss.getName(), sheets: ss.getSheets().map(function(s) { return { name: s.getName(), id: s.getSheetId(), rows: s.getLastRow(), cols: s.getLastColumn() }; }) }; } /* ───────── Export ───────── */ function exportLeads_() { var sheet = ensureBackendSheet_(); var values = sheet.getDataRange().getValues(); if (!values || values.length < 2) { return { ok: true, datasetId: DATASET_ID, sheet: BACKEND_SHEET, headers: CRM_HEADERS, count: 0, rows: [] }; } var headers = values[0].map(function(h){ return String(h || '').trim(); }); var rows = []; for (var i = 1; i < values.length; i++) { var row = values[i]; if (isBlank_(row)) continue; var obj = rowToLead_(headers, row, i + 1); rows.push(obj); } // Fresh first rows.sort(function(a, b) { return new Date(b.addedAt || 0) - new Date(a.addedAt || 0); }); return { ok: true, datasetId: DATASET_ID, sheet: BACKEND_SHEET, headers: headers, count: rows.length, rows: rows }; } function rowToLead_(headers, row, row1) { function col(name) { var want = String(name).toLowerCase(); for (var i = 0; i < headers.length; i++) { if (String(headers[i]).toLowerCase() === want) return cellStr_(row[i]); } // fuzzy for (var j = 0; j < headers.length; j++) { if (String(headers[j]).toLowerCase().indexOf(want) >= 0) return cellStr_(row[j]); } return ''; } var columns = {}; for (var c = 0; c < headers.length; c++) { if (headers[c]) columns[headers[c]] = cellStr_(row[c]); } var id = col('Lead ID') || ('row_' + row1); var status = col('Status') || 'New'; if (STATUS_OPTIONS.indexOf(status) < 0) { // keep custom status text } return { id: id, name: col('Name'), email: col('Email'), phone: col('Phone'), company: col('Company'), industry: col('Industry') || col('Service'), status: status, priority: col('Priority') || 'Medium', progress: col('Progress'), assignedTo: col('Assigned To'), source: col('Source') || 'connected-sheet', location: col('Location'), pin: col('Pin'), service: col('Service'), gender: col('Gender'), notes: col('Notes'), activityLog: col('Activity Log'), documentation: col('Documentation'), addedAt: col('Date Added') || new Date().toISOString(), updatedAt: col('Last Updated'), hot: String(col('Hot')).toLowerCase() === 'true' || col('Hot') === '1' || col('Hot') === 'Yes', deal: col('Deal Value'), columns: columns, _row: row1, _sheet: BACKEND_SHEET }; } /* ───────── Import existing messy tabs → Nucleous Leads ───────── */ function importExistingTabsIntoBackend_(ss, backend) { var tabs = ss.getSheets(); var imported = 0; var existingIds = {}; var bValues = backend.getDataRange().getValues(); for (var r = 1; r < bValues.length; r++) { var id = String(bValues[r][0] || ''); if (id) existingIds[id] = true; } tabs.forEach(function(tab) { if (tab.getName() === BACKEND_SHEET) return; if (tab.getLastRow() < 2) return; var values = tab.getDataRange().getValues(); var det = detectHeader_(values); var headers = det.headers; var roles = classifyHeaders_(headers); for (var i = det.startRow; i < values.length; i++) { var cells = values[i]; if (isBlank_(cells)) continue; var lead = mapMessyRow_(headers, cells, roles, tab.getName(), i + 1); if (!lead.name && !lead.email && !lead.phone) continue; if (existingIds[lead.id]) continue; appendLeadRow_(backend, lead); existingIds[lead.id] = true; imported++; } }); return imported; } function appendLeadRow_(sheet, lead) { var row = [ lead.id || '', lead.name || '', lead.email || '', lead.phone || '', lead.company || '', lead.industry || '', lead.status || 'New', lead.priority || 'Medium', lead.progress || '', lead.assignedTo || '', lead.source || 'connected-sheet', lead.location || '', lead.pin || '', lead.service || '', lead.gender || '', lead.notes || '', lead.activityLog || '', lead.documentation || '', lead.addedAt || new Date().toISOString(), lead.updatedAt || new Date().toISOString(), lead.hot ? 'Yes' : '', lead.deal || '', lead.customJson || '' ]; sheet.appendRow(row); } /* ───────── Upsert from CRM ───────── */ function upsertFromCrm_(body) { var sheet = ensureBackendSheet_(); var list = (body && body.rows) || []; if (!list.length && body && body.lead) list = [body.lead]; if (!list.length) return { ok: true, updated: 0 }; var values = sheet.getDataRange().getValues(); var idCol = 0; var updated = 0; for (var i = 0; i < list.length; i++) { var lead = normalizeCrmLead_(list[i]); var row1 = findRowByIdOrContact_(values, lead); if (row1 < 0) { appendLeadRow_(sheet, lead); values = sheet.getDataRange().getValues(); updated++; } else { writeLeadRow_(sheet, row1, lead); updated++; } } return { ok: true, updated: updated }; } function normalizeCrmLead_(l) { l = l || {}; var notes = l.notes || ''; var activity = l.activityLog || ''; if (!activity && Array.isArray(l.activities)) { activity = l.activities.map(function(a) { return [a.at || a.date || '', a.type || '', a.text || a.note || a.message || ''].join(' ').trim(); }).filter(Boolean).join(' || '); } var docs = l.documentation || ''; if (!docs && Array.isArray(l.documents)) { docs = l.documents.map(function(d){ return d.name || d.url || JSON.stringify(d); }).join(' || '); } return { id: l.id || l.leadId || ('crm_' + Date.now()), name: l.name || '', email: l.email || '', phone: l.phone || '', company: l.company || '', industry: l.industry || '', status: l.status || 'New', priority: l.priority || 'Medium', progress: l.progress || l.stage || '', assignedTo: l.assignedTo || l.assignedEmail || '', source: l.source || 'crm', location: l.location || '', pin: l.pin || '', service: l.service || '', gender: l.gender || '', notes: notes, activityLog: activity, documentation: docs, addedAt: l.addedAt || '', updatedAt: new Date().toISOString(), hot: !!l.hot, deal: l.deal || l.dealValue || '', customJson: l.columns ? JSON.stringify(l.columns) : (l.custom ? JSON.stringify(l.custom) : '') }; } function findRowByIdOrContact_(values, lead) { var id = String(lead.id || ''); var phone = String(lead.phone || '').replace(/\D/g, ''); var email = String(lead.email || '').toLowerCase(); for (var i = 1; i < values.length; i++) { var rid = String(values[i][0] || ''); if (id && rid === id) return i + 1; var rPhone = String(values[i][3] || '').replace(/\D/g, ''); var rEmail = String(values[i][2] || '').toLowerCase(); if (phone && rPhone && (rPhone === phone || rPhone.slice(-10) === phone.slice(-10))) return i + 1; if (email && rEmail && rEmail === email) return i + 1; } return -1; } function writeLeadRow_(sheet, row1, lead) { var row = [ lead.id || '', lead.name || '', lead.email || '', lead.phone || '', lead.company || '', lead.industry || '', lead.status || 'New', lead.priority || 'Medium', lead.progress || '', lead.assignedTo || '', lead.source || '', lead.location || '', lead.pin || '', lead.service || '', lead.gender || '', lead.notes || '', lead.activityLog || '', lead.documentation || '', lead.addedAt || '', lead.updatedAt || new Date().toISOString(), lead.hot ? 'Yes' : '', lead.deal || '', lead.customJson || '' ]; sheet.getRange(row1, 1, row1, row.length).setValues([row]); } /* ───────── Messy sheet helpers ───────── */ function detectHeader_(values) { var bestIdx = 0, bestScore = -1; var maxScan = Math.min(8, values.length); for (var r = 0; r < maxScan; r++) { var row = values[r], score = 0, nonEmpty = 0; for (var c = 0; c < row.length; c++) { var cell = String(row[c] == null ? '' : row[c]).trim(); if (!cell) continue; nonEmpty++; if (/name|email|phone|mobile|company|status|note|service|city|location/i.test(cell)) score += 4; if (/^[A-Za-z0-9 _#./()%-]{1,40}$/.test(cell)) score += 1; } score += Math.min(nonEmpty, 10); if (score > bestScore) { bestScore = score; bestIdx = r; } } var headerRow = values[bestIdx] || []; var headers = [], used = {}; for (var c2 = 0; c2 < headerRow.length; c2++) { var h = String(headerRow[c2] == null ? '' : headerRow[c2]).trim(); if (!h) h = 'Column ' + (c2 + 1); var base = h, n = 2; while (used[h]) { h = base + ' (' + n + ')'; n++; } used[h] = true; headers.push(h); } return { headers: headers, startRow: bestIdx + 1 }; } function norm_(s) { return String(s || '').toLowerCase().replace(/[^a-z0-9]/g, ''); } function classifyHeaders_(headers) { var roles = { name: -1, email: -1, phone: -1, company: -1, category: -1, location: -1, pin: -1, service: -1, gender: -1, notes: -1, status: -1, date: -1 }; var map = { name: ['name','fullname','contact','customer','client'], email: ['email','mail','emailid'], phone: ['phone','mobile','whatsapp','contactnumber','number','tel'], company: ['company','organisation','organization','agency'], category: ['category','industry','sector'], location: ['location','city','area','address'], pin: ['pin','pincode','zip'], service: ['service','servicerequired','requirement'], gender: ['gender'], notes: ['notes','note','comment','remark','message'], status: ['status','stage'], date: ['date','created','added'] }; for (var i = 0; i < headers.length; i++) { var nk = norm_(headers[i]); for (var role in map) { if (roles[role] >= 0) continue; map[role].forEach(function(a) { if (roles[role] < 0 && (nk === a || nk.indexOf(a) >= 0)) roles[role] = i; }); } } return roles; } function mapMessyRow_(headers, cells, roles, sheetName, row1) { function at(idx) { return idx >= 0 ? cellStr_(cells[idx]) : ''; } var blob = cells.map(cellStr_).join(' | '); var phones = extractPhones_(blob); var emails = extractEmails_(blob); var name = at(roles.name); var email = at(roles.email) || (emails[0] || ''); var phone = at(roles.phone) || (phones[0] || ''); if (!name) { for (var i = 0; i < cells.length; i++) { var s = cellStr_(cells[i]); if (!s || extractEmails_(s).length || (extractPhones_(s).length && s.replace(/\D/g,'').length >= 8)) continue; name = s; break; } } var extra = []; if (emails.length > 1) extra.push('Other emails: ' + emails.slice(1).join(', ')); if (phones.length > 1) extra.push('Other phones: ' + phones.slice(1).join(', ')); var notes = at(roles.notes); if (extra.length) notes = (notes ? notes + ' | ' : '') + extra.join(' | '); var columns = {}; for (var c = 0; c < headers.length; c++) columns[headers[c]] = cellStr_(cells[c]); return { id: 'import_' + sheetName.replace(/\W+/g,'_') + '_' + row1, name: name || email || phone || ('Lead ' + row1), email: email, phone: phone, company: at(roles.company), industry: at(roles.category) || at(roles.service), status: at(roles.status) || 'New', priority: 'Medium', progress: '', assignedTo: '', source: sheetName, location: at(roles.location), pin: at(roles.pin), service: at(roles.service), gender: at(roles.gender), notes: notes, activityLog: '', documentation: '', addedAt: at(roles.date) || new Date().toISOString(), updatedAt: new Date().toISOString(), hot: false, deal: '', customJson: JSON.stringify(columns) }; } function extractPhones_(text) { var found = [], s = String(text || ''); var re = /(\+?\d{1,4}[\s\-.]?)?(?:\(?\d{2,5}\)?[\s\-.]?)?\d{3,5}[\s\-.]?\d{3,5}(?:[\s\-.]?\d{2,5})?/g; var m; while ((m = re.exec(s)) !== null) { var raw = m[0].trim(); var d = raw.replace(/\D/g, ''); if (d.length >= 8 && d.length <= 15 && found.indexOf(raw) < 0) found.push(raw); } return found; } function extractEmails_(text) { var found = [], s = String(text || ''); var re = /[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}/g; var m; while ((m = re.exec(s)) !== null) { var em = m[0].toLowerCase(); if (found.indexOf(em) < 0) found.push(em); } return found; } function cellStr_(v) { if (v === null || typeof v === 'undefined' || v === '') return ''; if (Object.prototype.toString.call(v) === '[object Date]') { return Utilities.formatDate(v, Session.getScriptTimeZone() || 'GMT', "yyyy-MM-dd'T'HH:mm:ss"); } return String(v).trim(); } function isBlank_(row) { for (var i = 0; i < (row || []).length; i++) { if (cellStr_(row[i])) return false; } return true; } function json_(o) { return ContentService.createTextOutput(JSON.stringify(o)).setMimeType(ContentService.MimeType.JSON); } function onOpen() { try { SpreadsheetApp.getUi().createMenu('Nucleous') .addItem('Setup backend sheet', 'setupNucleousBackend') .addItem('Test export', 'testExport') .addToUi(); } catch (e) {} }