Conectar ao Google Sheets

Importe a base XLSX no Google Drive, cole o Apps Script abaixo e publique como App da Web. Depois cole a URL aqui.

Código Apps Script

/**
 * Apps Script FINAL - Painel Metas IGA 2026
 * Acoes: googleLogin, logout, list, create, update, delete, listUsers, upsertUser, deleteUser
 * Abas: Metas e Usuarios
 */

const TOKEN = 'Lolinha1!';
const ABA_METAS = 'Metas';
const ABA_USUARIOS = PropertiesService.getScriptProperties().getProperty('USERS_SHEET') || 'Usuarios';
const SESSION_TTL_SECONDS = 21600;

const HEADERS_METAS = [
  'ID','Tipo','Responsável','Envolvidos','Meta','Indicador','Valor Meta','Unidade',
  'Farol 80%','Farol 100%','Farol 120%','Peso %','Prazo','Status','Cumprimento %',
  'Realizado','Evidência/Link','Observações','Relatório Qualitativo','Origem'
];

const HEADERS_USUARIOS = ['Email','Nome','Perfil','Área','Ativo'];

const HEADER_ALIASES = {
  'Tipo': ['Tipo','Categoria','Área','Area','Departamento','Setor','Classificação','Classificacao','Grupo','Tipo de meta','Tipo da meta','Tipo Meta','Tipo/Área','Tipo Area'],
  'Responsável': ['Responsável','Responsavel','Responsáveis','Responsaveis','Responsável principal','Responsavel principal','Responsável da meta','Responsavel da meta','Responsável Meta','Responsavel Meta','Responsável pela meta','Responsavel pela meta','Pessoa responsável','Pessoa responsavel','Líder','Lider','Dono','Owner'],
  'Envolvidos': ['Envolvidos','Participantes','Equipe','Apoio','Colaboradores'],
  'Meta': ['Meta','Descrição da meta','Descricao da meta','Objetivo','Descrição','Descricao'],
  'Cumprimento %': ['Cumprimento %','Cumprimento','Percentual','% cumprimento','Progresso'],
  'Área': ['Área','Area','Setor','Departamento','Responsável','Responsavel']
};

function doGet(e) {
  return jsonResponse_({ ok: true, message: 'API do Painel Metas IGA ativa.' });
}

function doPost(e) {
  try {
    const payload = JSON.parse((e && e.postData && e.postData.contents) || '{}');
    if (String(payload.token || '') !== TOKEN) return jsonResponse_({ ok: false, error: 'Token inválido.' });
    const action = String(payload.action || '').toLowerCase().trim();

    if (action === 'googlelogin') return googleLogin_(payload);
    if (action === 'logout') return logout_(payload);
    if (action === 'list') return listMetas_(payload);
    if (action === 'create') return createMeta_(payload);
    if (action === 'update') return updateMeta_(payload);
    if (action === 'delete') return deleteMeta_(payload);
    if (action === 'listusers') return listUsers_(payload);
    if (action === 'upsertuser') return upsertUser_(payload);
    if (action === 'deleteuser') return deleteUser_(payload);

    return jsonResponse_({ ok: false, error: 'Ação inválida. Use googleLogin, logout, list, create, update, delete, listUsers, upsertUser ou deleteUser.' });
  } catch (err) {
    return jsonResponse_({ ok: false, error: String(err && err.message ? err.message : err) });
  }
}

function googleLogin_(payload) {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('GOOGLE_CLIENT_ID');
  const clientSecret = props.getProperty('GOOGLE_CLIENT_SECRET');
  const origin = props.getProperty('SITE_ORIGIN');
  if (!clientId || !clientSecret || !origin) throw new Error('Login Google ainda não configurado no servidor.');
  if (!payload.code || payload.origin !== origin) throw new Error('Solicitação de login inválida.');
  const tokenResponse = UrlFetchApp.fetch('https://oauth2.googleapis.com/token', {
    method: 'post', muteHttpExceptions: true,
    payload: {code: String(payload.code), client_id: clientId, client_secret: clientSecret,
      redirect_uri: origin, grant_type: 'authorization_code'}
  });
  if (tokenResponse.getResponseCode() !== 200) throw new Error('Não foi possível validar o login Google. Tente entrar novamente.');
  const tokens = JSON.parse(tokenResponse.getContentText());
  if (!tokens.access_token) throw new Error('Resposta de autenticação inválida.');
  const identityResponse = UrlFetchApp.fetch('https://openidconnect.googleapis.com/v1/userinfo', {
    headers: {Authorization: 'Bearer ' + tokens.access_token}, muteHttpExceptions: true
  });
  if (identityResponse.getResponseCode() !== 200) throw new Error('Não foi possível confirmar a conta Google.');
  const identity = JSON.parse(identityResponse.getContentText());
  if (identity.email_verified !== true || !identity.email || !identity.sub) throw new Error('E-mail Google não verificado.');
  const usuario = findActiveUser_(identity.email);
  if (!usuario) throw new Error('Esta conta não tem acesso ativo ao painel. Consulte o administrador.');
  const user = buildUser_(usuario);
  const sessionToken = Utilities.getUuid() + Utilities.getUuid();
  CacheService.getScriptCache().put('google_session_' + sessionToken, JSON.stringify({email: user.Email}), SESSION_TTL_SECONDS);
  return jsonResponse_({ok: true, sessionToken, user});
}

function findActiveUser_(email) {
  return getRowsAsObjects_(ABA_USUARIOS).map(canonUser_).find(u =>
    String(u.Email).trim().toLowerCase() === String(email).trim().toLowerCase() && normalizar_(u.Ativo) === 'sim');
}

function logout_(payload) {
  const token = String(payload.sessionToken || '');
  if (token) CacheService.getScriptCache().remove('google_session_' + token);
  return jsonResponse_({ok: true});
}

function listMetas_(payload) {
  ensureMetasHeaders_();
  const user = getUserFromPayload_(payload);
  if (!user) return jsonResponse_({ ok: false, error: 'Faça login para carregar as metas.' });

  let metas = getRowsAsObjects_(ABA_METAS).map(canonMeta_);
  const perfil = normalizar_(user.Perfil || user.perfil || 'leitor');
  if (perfil !== 'admin') {
    const email = normalizar_(user.Email || user.email || '');
    const nome = normalizar_(user.Nome || user.nome || '');

    metas = metas.filter(meta => {
      const tipo = normalizar_(meta.Tipo || '');
      const responsavel = normalizar_(meta['Responsável'] || '');

      // Regra para usuários não-admin/leitor/editor:
      // 1) vê somente metas em que é o responsável;
      // 2) também vê metas do tipo Global;
      // 3) não vê metas apenas por estar em Envolvidos.
      const ehGlobal = tipo === 'global' || tipo.indexOf('global') >= 0;
      const ehResponsavel =
        (nome && responsavel.indexOf(nome) >= 0) ||
        (email && responsavel.indexOf(email) >= 0);

      return ehGlobal || ehResponsavel;
    });
  }
  return jsonResponse_({ ok: true, rows: metas, data: metas, user });
}

function createMeta_(payload) {
  ensureMetasHeaders_();
  const user = getUserFromPayload_(payload);
  if (!user) return jsonResponse_({ ok: false, error: 'Faça login para criar metas.' });
  if (!canEdit_(user)) return jsonResponse_({ ok: false, error: 'Seu perfil não permite criar metas.' });
  const meta = canonMeta_(payload.record || payload.data || payload.meta || {});
  if (!meta.ID) meta.ID = 'META-' + new Date().getTime();
  const sheet = getSheet_(ABA_METAS);
  const headers = getHeaders_(sheet);
  sheet.appendRow(headers.map(h => meta[h] !== undefined ? meta[h] : ''));
  return jsonResponse_({ ok: true, record: meta, data: meta });
}

function updateMeta_(payload) {
  ensureMetasHeaders_();
  const user = getUserFromPayload_(payload);
  if (!user) return jsonResponse_({ ok: false, error: 'Faça login para atualizar metas.' });
  if (!canEdit_(user)) return jsonResponse_({ ok: false, error: 'Seu perfil não permite editar metas.' });
  const meta = canonMeta_(payload.record || payload.data || payload.meta || {});
  const id = String(payload.id || meta.ID || '').trim();
  if (!id) return jsonResponse_({ ok: false, error: 'ID obrigatório para atualizar.' });
  const sheet = getSheet_(ABA_METAS);
  const headers = getHeaders_(sheet);
  const values = sheet.getDataRange().getValues();
  const idIndex = findHeaderIndex_(headers, 'ID');
  for (let r = 1; r < values.length; r++) {
    if (String(values[r][idIndex]).trim() === id) {
      headers.forEach((h, i) => {
        const key = canonicalHeader_(h, HEADERS_METAS);
        if (meta[key] !== undefined) sheet.getRange(r + 1, i + 1).setValue(meta[key]);
      });
      return jsonResponse_({ ok: true, record: meta, data: meta });
    }
  }
  return jsonResponse_({ ok: false, error: 'Meta não encontrada.' });
}

function deleteMeta_(payload) {
  ensureMetasHeaders_();
  const user = getUserFromPayload_(payload);
  if (!user) return jsonResponse_({ ok: false, error: 'Faça login para excluir metas.' });
  if (normalizar_(user.Perfil || user.perfil) !== 'admin') return jsonResponse_({ ok: false, error: 'Somente admin pode excluir metas.' });
  const meta = canonMeta_(payload.record || payload.data || payload.meta || {});
  const id = String(payload.id || meta.ID || '').trim();
  if (!id) return jsonResponse_({ ok: false, error: 'ID obrigatório para excluir.' });
  const sheet = getSheet_(ABA_METAS);
  const headers = getHeaders_(sheet);
  const values = sheet.getDataRange().getValues();
  const idIndex = findHeaderIndex_(headers, 'ID');
  for (let r = 1; r < values.length; r++) {
    if (String(values[r][idIndex]).trim() === id) {
      sheet.deleteRow(r + 1);
      return jsonResponse_({ ok: true });
    }
  }
  return jsonResponse_({ ok: false, error: 'Meta não encontrada.' });
}

function listUsers_(payload) {
  ensureUsuariosHeaders_();
  const user = getUserFromPayload_(payload);
  if (!isAdmin_(user)) return jsonResponse_({ ok: false, error: 'Somente admin pode gerenciar usuários.' });
  const users = getRowsAsObjects_(ABA_USUARIOS).map(canonUser_);
  return jsonResponse_({ ok: true, users, rows: users });
}

function upsertUser_(payload) {
  ensureUsuariosHeaders_();
  const user = getUserFromPayload_(payload);
  if (!isAdmin_(user)) return jsonResponse_({ ok: false, error: 'Somente admin pode salvar usuários.' });
  const u = canonUser_(payload.user || payload.record || {});
  if (!u.Email) return jsonResponse_({ ok: false, error: 'Email obrigatório.' });
  if (!u.Perfil) u.Perfil = 'leitor';
  if (!u.Ativo) u.Ativo = 'SIM';

  const sheet = getSheet_(ABA_USUARIOS);
  const headers = getHeaders_(sheet);
  const values = sheet.getDataRange().getValues();
  const emailIndex = findHeaderIndex_(headers, 'Email');
  let rowIndex = -1;
  for (let r = 1; r < values.length; r++) {
    if (normalizar_(values[r][emailIndex]) === normalizar_(u.Email)) { rowIndex = r + 1; break; }
  }
  if (rowIndex < 0) {
    sheet.appendRow(headers.map(h => u[canonicalHeader_(h, HEADERS_USUARIOS)] || ''));
  } else {
    headers.forEach((h, i) => {
      const key = canonicalHeader_(h, HEADERS_USUARIOS);
      if (u[key] !== undefined) sheet.getRange(rowIndex, i + 1).setValue(u[key]);
    });
  }
  return jsonResponse_({ ok: true, user: u });
}

function deleteUser_(payload) {
  ensureUsuariosHeaders_();
  const user = getUserFromPayload_(payload);
  if (!isAdmin_(user)) return jsonResponse_({ ok: false, error: 'Somente admin pode excluir usuários.' });
  const email = String(payload.email || (payload.user && payload.user.Email) || '').trim();
  if (!email) return jsonResponse_({ ok: false, error: 'Email obrigatório.' });
  const sheet = getSheet_(ABA_USUARIOS);
  const headers = getHeaders_(sheet);
  const values = sheet.getDataRange().getValues();
  const emailIndex = findHeaderIndex_(headers, 'Email');
  for (let r = 1; r < values.length; r++) {
    if (normalizar_(values[r][emailIndex]) === normalizar_(email)) {
      sheet.deleteRow(r + 1);
      return jsonResponse_({ ok: true });
    }
  }
  return jsonResponse_({ ok: false, error: 'Usuário não encontrado.' });
}

function getUserFromPayload_(payload) {
  const sessionToken = String(payload.sessionToken || '').trim();
  if (!sessionToken) return null;
  const raw = CacheService.getScriptCache().get('google_session_' + sessionToken);
  if (!raw) return null;
  const usuario = findActiveUser_(JSON.parse(raw).email);
  return usuario ? buildUser_(usuario) : null;
}

function canEdit_(user) {
  const perfil = normalizar_(user && (user.Perfil || user.perfil || ''));
  return perfil === 'admin' || perfil === 'editor';
}
function isAdmin_(user) { return normalizar_(user && (user.Perfil || user.perfil || '')) === 'admin'; }

function buildUser_(usuario) {
  const email = String(usuario.Email || '').trim();
  const nome = String(usuario.Nome || '').trim();
  const perfil = normalizar_(usuario.Perfil || 'leitor');
  const area = String(usuario['Área'] || usuario.Area || '').trim();
  return { Email: email, Nome: nome, Perfil: perfil, Área: area, Area: area, email, nome, perfil, area };
}

function canonMeta_(obj) {
  const out = {};
  HEADERS_METAS.forEach(h => out[h] = getAny_(obj, h));
  return out;
}
function canonUser_(obj) {
  return {
    Email: String(getAny_(obj, 'Email') || '').trim(),
    Nome: String(getAny_(obj, 'Nome') || '').trim(),
    Perfil: String(getAny_(obj, 'Perfil') || '').trim().toLowerCase(),
    'Área': String(getAny_(obj, 'Área') || getAny_(obj, 'Area') || '').trim(),
    Ativo: String(getAny_(obj, 'Ativo') || '').trim().toUpperCase()
  };
}
function getAny_(obj, target) {
  if (!obj) return '';
  const wanted = normalizarHeader_(target);
  const aliases = (HEADER_ALIASES[target] || [target]).map(normalizarHeader_);
  for (const k in obj) {
    const nk = normalizarHeader_(k);
    if (nk === wanted || aliases.indexOf(nk) >= 0) return obj[k];
  }
  return '';
}
function canonicalHeader_(header, validHeaders) {
  const n = normalizarHeader_(header);
  const found = validHeaders.find(h => normalizarHeader_(h) === n);
  if (found) return found;
  for (const canonical of validHeaders) {
    const aliases = HEADER_ALIASES[canonical] || [canonical];
    if (aliases.some(a => normalizarHeader_(a) === n)) return canonical;
  }
  return String(header || '').trim();
}
function normalizarHeader_(v) {
  return String(v || '').trim().toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g, '').replace(/[^a-z0-9]/g, '');
}
function findHeaderIndex_(headers, target) {
  const idx = headers.findIndex(h => normalizarHeader_(h) === normalizarHeader_(target));
  if (idx < 0) throw new Error('Coluna não encontrada: ' + target);
  return idx;
}

function ensureMetasHeaders_() { ensureHeaders_(ABA_METAS, HEADERS_METAS); }
function ensureUsuariosHeaders_() { ensureHeaders_(ABA_USUARIOS, HEADERS_USUARIOS); }
function ensureHeaders_(sheetName, required) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastCol = sheet.getLastColumn();
  const current = lastCol ? sheet.getRange(1,1,1,lastCol).getValues()[0].map(x => String(x).trim()) : [];
  if (!current.length || current.every(x => !x)) {
    sheet.getRange(1,1,1,required.length).setValues([required]);
    return;
  }

  // Corrige espaços antes/depois dos títulos e padroniza os títulos conhecidos.
  const normalizedRow = current.map(h => canonicalHeader_(h, required));
  sheet.getRange(1,1,1,normalizedRow.length).setValues([normalizedRow]);

  required.forEach(h => {
    if (!normalizedRow.some(c => canonicalHeader_(c, required) === h)) {
      sheet.getRange(1, sheet.getLastColumn() + 1).setValue(h);
      normalizedRow.push(h);
    }
  });
}

function getRowsAsObjects_(sheetName) {
  const sheet = getSheet_(sheetName);
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];
  const valid = sheetName === ABA_USUARIOS ? HEADERS_USUARIOS : HEADERS_METAS;
  const headers = values[0].map(h => canonicalHeader_(h, valid));
  return values.slice(1).filter(row => row.some(cell => cell !== '')).map(row => {
    const obj = {};
    headers.forEach((h, i) => {
      const key = h;
      const val = row[i];
      // Quando existem colunas duplicadas, exemplo "Tipo" e "Tipo " ou uma coluna nova vazia
      // criada em versões anteriores, não deixe a coluna vazia sobrescrever a coluna preenchida.
      if (obj[key] === undefined || obj[key] === '') {
        obj[key] = val;
      } else if (val !== '' && val !== null && val !== undefined) {
        // Mantém o primeiro valor preenchido; só substitui se o anterior estava vazio.
        obj[key] = obj[key];
      }
    });
    return obj;
  });
}
function getHeaders_(sheet) {
  const lastCol = sheet.getLastColumn();
  if (lastCol < 1) return [];
  const valid = sheet.getName() === ABA_USUARIOS ? HEADERS_USUARIOS : HEADERS_METAS;
  return sheet.getRange(1, 1, 1, lastCol).getValues()[0].map(h => canonicalHeader_(h, valid));
}
function getSheet_(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(name);
  if (!sheet) throw new Error('Aba não encontrada: ' + name);
  return sheet;
}
function getOrCreateSheet_(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  return ss.getSheetByName(name) || ss.insertSheet(name);
}
function normalizar_(v) {
  return String(v || '').trim().toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g, '');
}
function jsonResponse_(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}