Do dado da Meta à decisão, em três camadas.

/**
 * ADS 360 · Atualização automática da planilha com dados da Meta
 * -----------------------------------------------------------------
 * Preenche as abas Base_Conta, Base_Campanhas e Base_Diario do modelo Ads 360
 * a partir da API de Marketing da Meta. Só escreve nas colunas de dado (azuis)
 * e replica as fórmulas (pretas) para as linhas novas.
 *
 * Instalação: veja o playbook em /playbook.
 * Segredos (token e conta) ficam em "Propriedades do script", não nas células.
 */

var API = 'https://graph.facebook.com/v21.0/';
var TZ = 'America/Sao_Paulo';
var HEADER_ROW = 4;
var FIRST_ROW = 5;

var IN_CONTA = ['Mês', 'Início', 'Fim', 'Situação', 'Investimento', 'Impressões', 'Alcance', 'Cliques no link', 'Compras', 'ROAS Meta'];
var IN_CAMPS = ['Mês', 'Campanha', 'Objetivo', 'Investimento', 'Impressões', 'Alcance', 'Cliques no link', 'Compras', 'ROAS Meta'];
var IN_DIARIO = ['Data', 'Investimento', 'Impressões', 'Cliques no link', 'Compras', 'ROAS Meta'];

var OBJETIVOS = {
  OUTCOME_SALES: 'Vendas', CONVERSIONS: 'Vendas', PRODUCT_CATALOG_SALES: 'Vendas',
  OUTCOME_TRAFFIC: 'Tráfego', LINK_CLICKS: 'Tráfego',
  OUTCOME_ENGAGEMENT: 'Engajamento', POST_ENGAGEMENT: 'Engajamento', PAGE_LIKES: 'Engajamento', VIDEO_VIEWS: 'Engajamento',
  OUTCOME_AWARENESS: 'Reconhecimento', REACH: 'Reconhecimento', BRAND_AWARENESS: 'Reconhecimento',
  OUTCOME_LEADS: 'Leads', LEAD_GENERATION: 'Leads',
  OUTCOME_APP_PROMOTION: 'App', APP_INSTALLS: 'App', MESSAGES: 'Mensagens'
};

/* ============================ MENU ============================ */

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Ads 360')
    .addItem('Atualizar agora', 'atualizarTudo')
    .addSeparator()
    .addItem('1. Configurar conexão com a Meta', 'configurar')
    .addItem('2. Ativar atualização diária (00:01)', 'ativarDiario')
    .addItem('Desativar atualização diária', 'desativarDiario')
    .addToUi();
}

function configurar() {
  var ui = SpreadsheetApp.getUi();
  var props = PropertiesService.getScriptProperties();

  var a = ui.prompt('Conta de anúncios', 'Cole o ID da conta de anúncios (só números, sem "act_"):', ui.ButtonSet.OK_CANCEL);
  if (a.getSelectedButton() !== ui.Button.OK) return;
  var conta = a.getResponseText().replace(/\D/g, '');
  if (!conta) { ui.alert('ID inválido. Use só os números da conta.'); return; }

  var t = ui.prompt('Token da Meta', 'Cole o token de acesso (permissão ads_read). Ele fica guardado só nas propriedades do script.', ui.ButtonSet.OK_CANCEL);
  if (t.getSelectedButton() !== ui.Button.OK) return;
  var token = t.getResponseText().trim();
  if (token.length < 20) { ui.alert('Token inválido.'); return; }

  var d = ui.prompt('Data inicial', 'A partir de que dia buscar o histórico? Formato AAAA-MM-DD. Deixe em branco para os últimos 4 meses.', ui.ButtonSet.OK_CANCEL);
  if (d.getSelectedButton() !== ui.Button.OK) return;
  var inicio = d.getResponseText().trim();
  if (inicio && !/^\d{4}-\d{2}-\d{2}$/.test(inicio)) { ui.alert('Data inválida. Use AAAA-MM-DD.'); return; }

  props.setProperties({ ACCOUNT_ID: conta, META_TOKEN: token, START_DATE: inicio });
  ui.alert('Conexão salva. Agora use Ads 360 › Atualizar agora para testar.');
}

/* ======================== ATUALIZAÇÃO ======================== */

/** Execução manual (menu). Mostra o resultado na tela. */
function atualizarTudo() {
  var ui = SpreadsheetApp.getUi();
  try {
    var r = executar_();
    ui.alert('Atualizado', r.meses + ' meses, ' + r.camps + ' linhas de campanha e ' + r.dias + ' dias gravados.', ui.ButtonSet.OK);
  } catch (e) {
    ui.alert('Não consegui atualizar', String(e.message || e), ui.ButtonSet.OK);
  }
}

/** Alvo do gatilho diário. Sem tela; avisa o dono por e-mail se falhar. */
function atualizarAgendado() {
  try {
    executar_();
  } catch (e) {
    MailApp.sendEmail(Session.getEffectiveUser().getEmail(), 'Ads 360: a atualização diária falhou', String(e.message || e));
    throw e;
  }
}

function executar_() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) throw new Error('Outra atualização está em andamento. Tente de novo em instantes.');
  try {
    var cfg = lerConfig_();
    var hoje = Utilities.formatDate(new Date(), TZ, 'yyyy-MM-dd');
    var desde = cfg.start || primeiroDiaMesesAtras_(hoje, 3);

    var contaRaw = insights_(cfg, { level: 'account', time_increment: 'monthly' }, desde, hoje);
    var campRaw = insights_(cfg, { level: 'campaign', time_increment: 'monthly' }, desde, hoje);
    var diaRaw = insights_(cfg, { level: 'account', time_increment: '1' }, desde, hoje);
    var objetivos = objetivosPorCampanha_(cfg);

    var contaRows = montarConta_(contaRaw);
    var campRows = montarCampanhas_(campRaw, objetivos);
    var diaRows = montarDiario_(diaRaw);

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var stamp = 'Atualizado automaticamente em ' + Utilities.formatDate(new Date(), TZ, 'dd/MM/yyyy HH:mm');
    gravar_(ss, 'Base_Conta', IN_CONTA, contaRows, stamp);
    gravar_(ss, 'Base_Campanhas', IN_CAMPS, campRows, stamp);
    gravar_(ss, 'Base_Diario', IN_DIARIO, diaRows, stamp);
    SpreadsheetApp.flush();
    return { meses: contaRows.length, camps: campRows.length, dias: diaRows.length };
  } finally {
    lock.releaseLock();
  }
}

function ativarDiario() {
  var ui = SpreadsheetApp.getUi();
  desativar_();
  ScriptApp.newTrigger('atualizarAgendado').timeBased().everyDays(1).atHour(0).nearMinute(1).inTimezone(TZ).create();
  ui.alert('Atualização diária ativada',
    'A planilha será atualizada todos os dias por volta de 00:01 (horário de Brasília). O Google executa dentro de uma janela de cerca de 15 minutos em torno desse horário.',
    ui.ButtonSet.OK);
}

function desativarDiario() {
  var n = desativar_();
  SpreadsheetApp.getUi().alert(n ? 'Atualização diária desativada.' : 'Não havia atualização diária ativa.');
}

function desativar_() {
  var n = 0;
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'atualizarAgendado') { ScriptApp.deleteTrigger(t); n++; }
  });
  return n;
}

/* ===================== TRANSFORMAÇÕES (puras) ===================== */

function num_(v) {
  var n = parseFloat(v);
  return isNaN(n) ? 0 : n;
}

/** Valor do evento de compra em actions ou purchase_roas. */
function valorCompra_(arr) {
  if (!arr) return 0;
  var tipos = ['omni_purchase', 'purchase'];
  for (var t = 0; t < tipos.length; t++) {
    for (var i = 0; i < arr.length; i++) {
      if (arr[i].action_type === tipos[t]) return num_(arr[i].value);
    }
  }
  return 0;
}

function lerInsight_(r) {
  return {
    inv: num_(r.spend),
    imp: num_(r.impressions),
    alc: num_(r.reach),
    cli: num_(r.inline_link_clicks),
    compras: valorCompra_(r.actions),
    roas: valorCompra_(r.purchase_roas)
  };
}

function dt_(s) {
  return new Date(parseInt(s.slice(0, 4), 10), parseInt(s.slice(5, 7), 10) - 1, parseInt(s.slice(8, 10), 10));
}

function primeiroDiaMesesAtras_(hojeStr, meses) {
  var d = dt_(hojeStr);
  var x = new Date(d.getFullYear(), d.getMonth() - meses, 1);
  return x.getFullYear() + '-' + ('0' + (x.getMonth() + 1)).slice(-2) + '-01';
}

function montarConta_(raw) {
  return raw.map(function (r) {
    var m = lerInsight_(r);
    var ini = dt_(r.date_start), fim = dt_(r.date_stop);
    var fimDoMes = new Date(ini.getFullYear(), ini.getMonth() + 1, 0);
    return {
      'Mês': new Date(ini.getFullYear(), ini.getMonth(), 1), 'Início': ini, 'Fim': fim,
      'Situação': fim.getTime() < fimDoMes.getTime() ? 'Parcial' : 'Fechado',
      'Investimento': m.inv, 'Impressões': m.imp, 'Alcance': m.alc, 'Cliques no link': m.cli,
      'Compras': m.compras, 'ROAS Meta': m.roas
    };
  }).filter(function (l) { return l['Investimento'] > 0; })
    .sort(function (a, b) { return a['Mês'] - b['Mês']; });
}

function montarCampanhas_(raw, objetivos) {
  return raw.map(function (r) {
    var m = lerInsight_(r);
    var ini = dt_(r.date_start);
    var bruto = objetivos[r.campaign_id];
    return {
      'Mês': new Date(ini.getFullYear(), ini.getMonth(), 1), 'Campanha': r.campaign_name || '',
      'Objetivo': OBJETIVOS[bruto] || bruto || 'Outros',
      'Investimento': m.inv, 'Impressões': m.imp, 'Alcance': m.alc, 'Cliques no link': m.cli,
      'Compras': m.compras, 'ROAS Meta': m.roas
    };
  }).filter(function (l) { return l['Investimento'] > 0; })
    .sort(function (a, b) { return (a['Mês'] - b['Mês']) || (b['Investimento'] - a['Investimento']); });
}

function montarDiario_(raw) {
  return raw.map(function (r) {
    var m = lerInsight_(r);
    return {
      'Data': dt_(r.date_start), 'Investimento': m.inv, 'Impressões': m.imp, 'Cliques no link': m.cli,
      'Compras': m.compras, 'ROAS Meta': m.roas
    };
  }).filter(function (l) { return l['Investimento'] > 0 || l['Compras'] > 0; })
    .sort(function (a, b) { return a['Data'] - b['Data']; });
}

/* ========================= API DA META ========================= */

function lerConfig_() {
  var p = PropertiesService.getScriptProperties().getProperties();
  if (!p.ACCOUNT_ID || !p.META_TOKEN) throw new Error('Conexão não configurada. Use Ads 360 › 1. Configurar conexão com a Meta.');
  return { account: p.ACCOUNT_ID, token: p.META_TOKEN, start: p.START_DATE || '' };
}

function qs_(o) {
  return Object.keys(o).map(function (k) { return encodeURIComponent(k) + '=' + encodeURIComponent(o[k]); }).join('&');
}

function get_(url, token) {
  for (var tentativa = 0; tentativa < 4; tentativa++) {
    var res = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token }, muteHttpExceptions: true });
    var j;
    try { j = JSON.parse(res.getContentText()); } catch (e) { throw new Error('Resposta inesperada da Meta (HTTP ' + res.getResponseCode() + ').'); }
    if (!j.error) return j;
    var limite = [4, 17, 32, 613].indexOf(j.error.code) >= 0;
    if (limite && tentativa < 3) { Utilities.sleep(20000 * (tentativa + 1)); continue; }
    if (j.error.code === 190) throw new Error('O token da Meta expirou ou é inválido. Gere um novo e use Configurar conexão.');
    throw new Error('Meta: ' + j.error.message);
  }
}

function paginar_(url, token) {
  var out = [];
  while (url) {
    var j = get_(url, token);
    out = out.concat(j.data || []);
    url = j.paging && j.paging.next ? j.paging.next : null;
  }
  return out;
}

function insights_(cfg, extra, desde, ate) {
  var p = {
    fields: 'spend,impressions,reach,inline_link_clicks,actions,purchase_roas' + (extra.level === 'campaign' ? ',campaign_id,campaign_name' : ''),
    time_range: JSON.stringify({ since: desde, until: ate }),
    limit: 500
  };
  Object.keys(extra).forEach(function (k) { p[k] = extra[k]; });
  return paginar_(API + 'act_' + cfg.account + '/insights?' + qs_(p), cfg.token);
}

function objetivosPorCampanha_(cfg) {
  var lista = paginar_(API + 'act_' + cfg.account + '/campaigns?' + qs_({ fields: 'id,objective', limit: 500 }), cfg.token);
  var mapa = {};
  lista.forEach(function (c) { mapa[c.id] = c.objective; });
  return mapa;
}

/* ======================== GRAVAÇÃO NA ABA ======================== */

function gravar_(ss, nome, entradas, linhas, stamp) {
  var sh = ss.getSheetByName(nome);
  if (!sh) throw new Error('A aba "' + nome + '" não existe. Use o modelo padrão do Ads 360.');
  var lastCol = sh.getLastColumn();
  var headers = sh.getRange(HEADER_ROW, 1, 1, lastCol).getValues()[0].map(function (h) { return String(h).trim(); });
  var idx = {};
  headers.forEach(function (h, i) { if (h) idx[h] = i + 1; });
  entradas.forEach(function (h) {
    if (!idx[h]) throw new Error('A aba "' + nome + '" não tem a coluna "' + h + '". Não renomeie as colunas do modelo.');
  });

  var n = linhas.length;
  var lastRow = Math.max(sh.getLastRow(), FIRST_ROW);
  var antigas = lastRow - FIRST_ROW + 1;
  var formulaCols = [];
  var formulas = sh.getRange(FIRST_ROW, 1, 1, lastCol).getFormulas()[0];
  headers.forEach(function (h, i) {
    if (h && entradas.indexOf(h) < 0 && formulas[i]) formulaCols.push(i + 1);
  });

  // limpa os dados antigos das colunas de entrada (mantém formatação)
  entradas.forEach(function (h) { sh.getRange(FIRST_ROW, idx[h], antigas, 1).clearContent(); });
  if (n === 0) { sh.getRange(3, 1).setValue(stamp + ' · sem dados no período'); return; }

  // formata e grava as colunas de entrada
  entradas.forEach(function (h) {
    var col = idx[h];
    if (n > 1) sh.getRange(FIRST_ROW, col).copyTo(sh.getRange(FIRST_ROW + 1, col, n - 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
    var vals = linhas.map(function (l) { return [l[h] === undefined ? '' : l[h]]; });
    sh.getRange(FIRST_ROW, col, n, 1).setValues(vals);
  });

  // replica as fórmulas e apaga as sobras
  formulaCols.forEach(function (c) {
    if (n > 1) sh.getRange(FIRST_ROW, c).copyTo(sh.getRange(FIRST_ROW + 1, c, n - 1, 1));
    var sobra = lastRow - (FIRST_ROW + n - 1);
    if (sobra > 0) sh.getRange(FIRST_ROW + n, c, sobra, 1).clearContent();
  });
  sh.getRange(3, 1).setValue(stamp);
}