Integração Google Sheets
Chave da Service Account (JSON completo) *
Compartilhe a planilha com o e-mail client_email da Service Account como Editor.
Campos a enviar
Cancelar
Salvar e ativar
// ══════════════════════════════════════
// ESTADO
// ══════════════════════════════════════
const USERS = { admin: '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a' };
const state = {
user: null,
page: 'conexoes',
connections: JSON.parse(localStorage.getItem('pb_connections') || '[]'),
pixels: JSON.parse(localStorage.getItem('pb_pixels') || '[]'),
leads: JSON.parse(localStorage.getItem('pb_leads') || '[]'),
conversoes: JSON.parse(localStorage.getItem('pb_conversoes') || '[]'),
sheets: JSON.parse(localStorage.getItem('pb_sheets') || 'null'),
};
function save(k){ localStorage.setItem('pb_'+k, JSON.stringify(state[k])); }
// ══════════════════════════════════════
// AUTH
// ══════════════════════════════════════
async function sha256(str){
const buf = await crypto.subtle.digest('SHA-256', new TextEncoder().encode(str));
return Array.from(new Uint8Array(buf)).map(b=>b.toString(16).padStart(2,'0')).join('');
}
async function doLogin(){
const user = document.getElementById('login-user').value.trim();
const pass = document.getElementById('login-pass').value;
const err = document.getElementById('login-err');
if(!user||!pass){ err.textContent='Preencha usuário e senha.'; return; }
const hash = await sha256(pass);
if(USERS[user] && USERS[user]===hash){
state.user = user;
sessionStorage.setItem('pb_session', user);
document.getElementById('login-screen').style.display='none';
document.getElementById('app').classList.add('visible');
document.getElementById('user-avatar').textContent = user[0].toUpperCase();
document.getElementById('user-display').textContent = user;
loadMockData();
nav('conexoes', document.querySelector('[data-page="conexoes"]'));
} else {
err.textContent='Usuário ou senha inválidos.';
}
}
function doLogout(){
sessionStorage.removeItem('pb_session');
state.user=null;
document.getElementById('login-screen').style.display='flex';
document.getElementById('app').classList.remove('visible');
}
window.addEventListener('DOMContentLoaded',()=>{
const s=sessionStorage.getItem('pb_session');
if(s && USERS[s]){
state.user=s;
document.getElementById('login-screen').style.display='none';
document.getElementById('app').classList.add('visible');
document.getElementById('user-avatar').textContent = s[0].toUpperCase();
document.getElementById('user-display').textContent = s;
loadMockData();
nav('conexoes', document.querySelector('[data-page="conexoes"]'));
}
});
// ══════════════════════════════════════
// MOCK DATA
// ══════════════════════════════════════
function loadMockData(){
if(!state.leads.length){
state.leads=[
{id:1,phone:'5511987654321',name:'Ana Paula Souza',ctwa_clid:'ARAkLkA8rmlFeiCktEJQ-QTwRiyYHAFDLMNDBH0C',fbc:'fb.1.1.1714500000000.ARAkLkA8rml',source_url:'https://fb.com/ads/123',ad_headline:'Promoção Imperdível',timestamp:1714500000,pixel_sent:true,created_at:'2024-05-01T10:23:00Z'},
{id:2,phone:'5521998765432',name:'Carlos Mendes',ctwa_clid:'BRBlLbB9snmGfjDluFKR-RUxSjzZIBGEMOECIH1D',fbc:'fb.1.1.1714503600000.BRBlLbB9snm',source_url:'https://fb.com/ads/456',ad_headline:'Curso Premium',timestamp:1714503600,pixel_sent:true,created_at:'2024-05-01T11:40:00Z'},
{id:3,phone:'5531976543210',name:'Fernanda Lima',ctwa_clid:'CSCmMcC0tomHgkEmvGLS-SVyTk0AJCHFNPFDJi2E',fbc:'fb.1.1.1714510800000.CSCmMcC0tom',source_url:'https://fb.com/ads/789',ad_headline:'Black Friday',timestamp:1714510800,pixel_sent:false,created_at:'2024-05-01T13:00:00Z'},
{id:4,phone:'5541965432109',name:'Roberto Alves',ctwa_clid:null,fbc:null,source_url:null,ad_headline:null,timestamp:1714514400,pixel_sent:false,created_at:'2024-05-01T14:00:00Z'},
]; save('leads');
}
if(!state.conversoes.length){
state.conversoes=[
{id:1,phone:'5511987654321',name:'Ana Paula Souza',value:297,currency:'BRL',product:'Curso Premium',order_id:'ORD-001',sale_at:'2024-05-01T15:00:00Z',pixel_sent:true,ctwa_clid:'ARAkLkA8rmlFeiCktEJQ-QTwRiyYHAFDLMNDBH0C',fbc:'fb.1.1.1714500000000.ARAkLkA8rml'},
{id:2,phone:'5521998765432',name:'Carlos Mendes',value:497,currency:'BRL',product:'Mentoria',order_id:'ORD-002',sale_at:'2024-05-01T16:30:00Z',pixel_sent:false,ctwa_clid:'BRBlLbB9snmGfjDluFKR-RUxSjzZIBGEMOECIH1D',fbc:'fb.1.1.1714503600000.BRBlLbB9snm'},
]; save('conversoes');
}
if(!state.pixels.length){
state.pixels=[{id:1,name:'Pixel Principal',pixel_id:'123456789012345',token:'EAAxxxxx...',crm:'CRM Interno',active:true}];
save('pixels');
}
updateBadges();
}
function updateBadges(){
document.getElementById('badge-leads').textContent = state.leads.length;
document.getElementById('badge-conv').textContent = state.conversoes.length;
}
// ══════════════════════════════════════
// NAVEGAÇÃO
// ══════════════════════════════════════
const TITLES = {conexoes:'Conexões WhatsApp',leads:'Leads',pixels:'Webhooks de Vendas',conversoes:'Conversões',envio:'Envio ao Pixel'};
function nav(page, el){
state.page=page;
document.querySelectorAll('.nav-item').forEach(n=>n.classList.remove('active'));
if(el) el.classList.add('active');
document.getElementById('page-title').textContent=TITLES[page];
document.getElementById('topbar-actions').innerHTML='';
document.getElementById('page-content').innerHTML='';
({conexoes,leads,pixels,conversoes,envio})[page](
document.getElementById('page-content'),
document.getElementById('topbar-actions')
);
}
// ══════════════════════════════════════
// P1 — CONEXÕES
// ══════════════════════════════════════
function conexoes(content,actions){
actions.innerHTML=`
`;
const connected=state.connections.filter(c=>c.status==='connected').length;
content.innerHTML=`
Conectados
${connected}
de ${state.connections.length} cadastrados
Leads hoje
${state.leads.length}
CTWA capturados
${state.leads.filter(l=>l.ctwa_clid).length}
Pixel enviados
${state.leads.filter(l=>l.pixel_sent).length}
${renderConnCards()}
`;
}
function renderConnCards(){
const cards=state.connections.map(c=>`
${c.name}
${mkBadge(c.status)}
${c.phone||'Número não informado'}
${c.qr
?`
`
:`
${c.status==='connected'?'Conectado':'Aguardando QR'}
`}
Reconectar
Remover
`).join('');
return cards+`
Adicionar número
`;
}
function mkBadge(st){
const m={connected:['badge-green','Conectado'],connecting:['badge-amber','Conectando'],waiting_qr:['badge-amber','Aguard. QR'],disconnected:['badge-red','Desconectado']};
const[cls,lb]=m[st]||['badge-gray','—'];
return `
`;
const sheetsStatus = state.sheets ? `
Google Sheets ativo
ID: ${state.sheets.spreadsheet_id} · Aba: ${state.sheets.tab_name} · ${sheetFieldLabels()}
Editar
Desativar
` : '';
content.innerHTML=`
${sheetsStatus}
Nome Telefone CTWA Anúncio Recebido Pixel Sheets ${leadsRows(state.leads)}
${l.name||'—'}
${fmtPhone(l.phone)}
${l.ctwa_clid?`
${l.ad_headline||`— `}
${fmtDate(l.created_at)}
${l.pixel_sent?`
${!state.sheets?`— `:l.sheets_sent?`
Ver dados `).join('');
}
function filterLeads(q){
const f=state.leads.filter(l=>l.name?.toLowerCase().includes(q.toLowerCase())||l.phone?.includes(q.replace(/\D/g,'')));
const tb=document.getElementById('leads-tbody');
if(tb)tb.innerHTML=leadsRows(f);
}
// ── Google Sheets ──────────────────────────────────
const SHEET_FIELDS = [
{id:'sf-phone', key:'phone', label:'Telefone'},
{id:'sf-ctwa', key:'ctwa_clid', label:'ctwa_clid'},
{id:'sf-fbc', key:'fbc', label:'fbc'},
{id:'sf-name', key:'name', label:'Nome'},
{id:'sf-headline',key:'ad_headline',label:'Anúncio'},
{id:'sf-url', key:'source_url', label:'URL Anúncio'},
{id:'sf-date', key:'created_at', label:'Data'},
{id:'sf-pixel', key:'pixel_sent', label:'Status Pixel'},
];
function sheetFieldLabels(){
if(!state.sheets) return '';
return state.sheets.fields.map(k=>SHEET_FIELDS.find(f=>f.key===k)?.label||k).join(', ');
}
function saveSheets(){
const sid = document.getElementById('sh-id').value.trim();
const tab = document.getElementById('sh-tab').value.trim() || 'Leads';
const key = document.getElementById('sh-key').value.trim();
if(!sid || !key){ alert('ID da planilha e chave da Service Account são obrigatórios.'); return; }
let parsed;
try { parsed = JSON.parse(key); } catch { alert('JSON da Service Account inválido. Verifique o formato.'); return; }
if(!parsed.client_email || !parsed.private_key){ alert('JSON incompleto — faltam client_email ou private_key.'); return; }
const fields = SHEET_FIELDS.filter(f=>f.id==='sf-phone'||document.getElementById(f.id)?.checked).map(f=>f.key);
state.sheets = { spreadsheet_id: sid, tab_name: tab, fields, service_account: parsed, active: true };
localStorage.setItem('pb_sheets', JSON.stringify(state.sheets));
// Envia config para o backend salvar
const backend = (localStorage.getItem('pb_backend')||'').replace(/\/$/,'');
if(backend){
fetch(`${backend}/sheets/config`, {
method:'POST',
headers:{'Content-Type':'application/json','x-crm-secret': localStorage.getItem('pb_crm_secret')||''},
body: JSON.stringify(state.sheets)
}).catch(()=>{});
}
closeModal('modal-sheets');
nav('leads', document.querySelector('[data-page="leads"]'));
}
function disableSheets(){
if(!confirm('Desativar sincronização com o Google Sheets?')) return;
state.sheets = null;
localStorage.setItem('pb_sheets', 'null');
nav('leads', document.querySelector('[data-page="leads"]'));
}
// Preenche modal com config existente ao abrir
const _origOpenModal = openModal;
function openModal(id){
if(id==='modal-sheets' && state.sheets){
const s = state.sheets;
setTimeout(()=>{
document.getElementById('sh-id').value = s.spreadsheet_id || '';
document.getElementById('sh-tab').value = s.tab_name || 'Leads';
document.getElementById('sh-key').value = JSON.stringify(s.service_account, null, 2);
SHEET_FIELDS.forEach(f=>{
const el = document.getElementById(f.id);
if(el && !el.disabled) el.checked = s.fields?.includes(f.key) ?? false;
});
}, 50);
}
document.getElementById(id).classList.add('open');
}
function openLeadDrawer(lead){
document.getElementById('drawer-title').textContent=lead.name||lead.phone;
document.getElementById('drawer-body').innerHTML=`
Contato
${dr('Nome completo',lead.name,true)}${dr('Telefone',fmtPhone(lead.phone))}${dr('Recebido em',fmtDate(lead.created_at),true)}
Dados do anúncio CTWA
${dr('ctwa_clid',lead.ctwa_clid||'—')}${dr('fbc gerado',lead.fbc||'—')}
${dr('URL do anúncio',lead.source_url||'—')}${dr('Título',lead.ad_headline||'—',true)}
Rastreamento
${dr('Evento Contact',lead.pixel_sent?'✓ Enviado':'⏳ Pendente',true)}
${dr('Timestamp',lead.timestamp?new Date(lead.timestamp*1000).toLocaleString('pt-BR'):'—',true)}
`;
const backend = (localStorage.getItem('pb_backend')||'https://seu-app.railway.app').replace(/\/$/,'');
const cards = state.pixels.length ? state.pixels.map(p => {
const url = `${backend}/sale-event?wh=${p.token_key}`;
const evts = [p.evt_paid&&'Pagamento aprovado', p.evt_sched&&'Agendado'].filter(Boolean).join(' · ');
return `
URL do Webhook — cole na sua ferramenta de vendas
Criado em
${fmtDate(p.created_at)}
Ver exemplo de payload JSON
${examplePayload()}
`;
}).join('') : `
Nenhum webhook criado.
`;
content.innerHTML=`
Crie um webhook para cada ferramenta de vendas que você usa. Ao confirmar um pagamento ou agendamento, o CRM faz um POST na URL gerada com os dados do cliente. A plataforma localiza o lead pelo telefone, recupera o ctwa_clid, converte em fbc, aplica hash SHA-256 e envia o evento ao Pixel da Meta.
${cards}
`;
}
function examplePayload(){
return `{
"phone": "5511999998888",
"order_id": "ORD-12345",
"value": 297.00,
"currency": "BRL",
"product_name": "Curso Premium",
"status": "paid",
"sale_at": "2024-05-01T14:30:00Z"
}`.replace(//g,'>');
}
function copyUrl(url, btn){
navigator.clipboard.writeText(url).then(()=>{
const orig = btn.textContent;
btn.textContent = 'Copiado!';
btn.style.color = 'var(--green)';
btn.style.borderColor = 'var(--green)';
setTimeout(()=>{ btn.textContent=orig; btn.style.color=''; btn.style.borderColor=''; }, 2000);
});
}
function genToken(){ return Array.from(crypto.getRandomValues(new Uint8Array(18))).map(b=>b.toString(16).padStart(2,'0')).join(''); }
function addPixel(){
const name = document.getElementById('new-pixel-name').value.trim();
const desc = document.getElementById('new-pixel-crm').value.trim();
const paid = document.getElementById('evt-paid').checked;
const sched= document.getElementById('evt-sched').checked;
if(!name){ alert('Informe o nome da integração.'); return; }
if(!paid && !sched){ alert('Selecione ao menos um tipo de evento.'); return; }
state.pixels.push({
id: Date.now(),
name, desc,
evt_paid: paid,
evt_sched: sched,
token_key: genToken(),
active: true,
created_at: new Date().toISOString(),
});
save('pixels');
closeModal('modal-pixel');
document.getElementById('new-pixel-name').value='';
document.getElementById('new-pixel-crm').value='';
document.getElementById('evt-paid').checked=true;
document.getElementById('evt-sched').checked=true;
nav('pixels', document.querySelector('[data-page="pixels"]'));
}
function removePixel(id){
if(!confirm('Remover este webhook? A URL deixará de funcionar imediatamente.'))return;
state.pixels=state.pixels.filter(p=>p.id!==id); save('pixels');
nav('pixels',document.querySelector('[data-page="pixels"]'));
}
// ══════════════════════════════════════
// P4 — CONVERSÕES
// ══════════════════════════════════════
function conversoes(content){
content.innerHTML=`
Nome Telefone Produto Valor fbc Pixel ${convRows()}
Nenhuma conversão registrada.
${c.name}
${fmtPhone(c.phone)}
${c.product}
${c.currency} ${Number(c.value).toLocaleString('pt-BR',{minimumFractionDigits:2})}
${c.fbc?c.fbc.slice(0,22)+'…':'— '} ${c.pixel_sent?`
Ver hash `).join('');
}
async function openConvDrawer(c){
const ph=await sha256(c.phone.replace(/\D/g,''));
const fn=await sha256((c.name||'').split(' ')[0].toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g,''));
const ln=await sha256((c.name||'').split(' ').slice(1).join(' ').toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g,''));
document.getElementById('drawer-title').textContent='Dados com Hash — '+c.name;
document.getElementById('drawer-body').innerHTML=`
Dados da venda
${dr('Produto',c.product,true)}
${dr('Valor',c.currency+' '+Number(c.value).toLocaleString('pt-BR',{minimumFractionDigits:2}),true)}
${dr('Pedido',c.order_id)}${dr('Data',fmtDate(c.sale_at),true)}
fbc — ctwa_clid convertido
${dr('ctwa_clid',c.ctwa_clid||'—')}
fbc gerado:
${c.fbc||'—'}
Hash SHA-256 → enviado ao Pixel
${hrow('ph — telefone',c.phone.replace(/\D/g,''),ph)}
${hrow('fn — primeiro nome',(c.name||'').split(' ')[0].toLowerCase(),fn)}
${hrow('ln — sobrenome',(c.name||'').split(' ').slice(1).join(' ').toLowerCase(),ln)}
fbc — texto puro (sem hash)
${c.fbc||'—'}
✓ fbc, fbp, client_ip e user_agent são enviados sem hash, conforme exige a Meta Conversions API.
`;
openDrawer();
}
function hrow(field,orig,hashed){
return`
${field}
${orig||'—'}
${hashed}
`;
}
// ══════════════════════════════════════
// P5 — ENVIO AO PIXEL
// ══════════════════════════════════════
function envio(content){
const saved=localStorage.getItem('pb_backend')||'';
const sc=state.leads.filter(l=>l.pixel_sent).length;
const sp=state.conversoes.filter(c=>c.pixel_sent).length;
const tc=state.leads.filter(l=>l.ctwa_clid).length;
const tp=state.conversoes.length;
const pend=(tc-sc)+(tp-sp);
content.innerHTML=`
Contact enviados
${sc}
de ${tc} leads com CTWA
Purchase enviados
${sp}
de ${tp} conversões
Pendentes
${pend}
${pend===0?'Tudo em dia':'aguardando envio'}
${progRow('Eventos Contact (leads com CTWA)',sc,tc)}
${progRow('Eventos Purchase (vendas)',sp,tp)}
${pend===0?'Todos os eventos foram enviados ao Pixel com sucesso.':pend+' evento(s) pendente(s) — verifique o log abaixo.'}
Tipo Lead Telefone Status Data
${state.conversoes.map(c=>`
${c.name} ${fmtPhone(c.phone)}
${c.pixel_sent?`
${fmtDate(c.sale_at)} `).join('')}
${state.leads.filter(l=>l.ctwa_clid).map(l=>`
${l.name} ${fmtPhone(l.phone)}
${l.pixel_sent?`
${fmtDate(l.created_at)} `).join('')}
`;
if(saved) checkBackend(saved);
}
function progRow(label,sent,total){
const pct=total>0?Math.round(sent/total*100):100;
return``;
}
async function saveBackend(){
const url=document.getElementById('backend-url-input').value.trim().replace(/\/$/,'');
localStorage.setItem('pb_backend',url);
await checkBackend(url);
}
async function checkBackend(url){
const el=document.getElementById('backend-status');
if(!el)return;
el.innerHTML=``;
try{
const r=await fetch(url+'/health',{signal:AbortSignal.timeout(5000)});
if(r.ok) el.innerHTML=`
Backend conectado e respondendo.
Backend retornou HTTP ${r.status}.
Não foi possível conectar. Verifique a URL e o Railway. ${key} ${val}
`;}
function closeModal(id){document.getElementById(id).classList.remove('open');}
function openDrawer(){document.getElementById('drawer-overlay').classList.add('open');document.getElementById('drawer').classList.add('open');}
function closeDrawer(){document.getElementById('drawer-overlay').classList.remove('open');document.getElementById('drawer').classList.remove('open');}
`
:`