Filtragem (SQL)

GoogleViz

O projeto utiliza a API de Visualização do Google (Google Viz) para ler e filtrar dados em massa de uma planilha do Google usando SQL. É muito mais rápido do que iterar nas linhas da planilha usando SpreadsheetApp e filtrando com Javascript/Apps Script. Quando isso foi implementado, foi obtido uma melhora na performance de 92x em relação à implementação que iterava em ranges obtidas via SpreadsheetApp. Isso é extremamente valioso em um ambiente restrito de recursos como o Google Apps Script (ver problemas). Na escala atual da planilha, é impossível obter os dados de todos os processos sem essa técnica.

A função mais importante é filtrarGoogleViz() definida em filtragem.ts. Através dela, podemos fazer buscas na planilha com SQL e iterar nestas linhas, segundo o exemplo “Um exemplo de uso de filtrarGoogleViz”.

Um exemplo de uso de filtrarGoogleViz

export function obterEquipe(
  numero: number,
  setor: string,
  encarregado: string
): ColaboradorRelatorio[] {
  const linhasBuscadas = filtrarGoogleViz(
    EQUIPE,
    new VizSelectQueryBuilder()
      .or(
        (b) => b.equals(COLUNA_EQUIPE_STATUS, "PRESENTE"),
        (b) =>
          b
            .notEquals(COLUNA_EQUIPE_STATUS, "PRESENTE")
            .notNull(COLUNA_EQUIPE_SUBSTITUICOES)
      )
      .notNullOrEquals(COLUNA_EQUIPE_TIME, numero)
      .notNullOrEquals(COLUNA_EQUIPE_AREA, setor)
      .notNullOrEquals(COLUNA_EQUIPE_ENCARREGADO, encarregado)
  );
  const linhas = linhasBuscadas.table?.rows?.map((linha) => linha.c);
  if (!linhas) return [];
  const valores = linhas.map((linha) => linha?.map((val) => val?.v ?? null));
  return valores
    .map((colaborador) => {
      // Corpo do que fazer com esses colaboradores aqui
    })
    .map(resolverSubstitutos);
}

Aviso

Atente-se às colunas sendo manipuladas nos seus dados, especialmente a coluna final. Em todos os casos do momento de escrita desta documentação, todos os intervalos iniciais são “A” porque os números das colunas definidos nas constantes são relativos à primeira coluna (ver coordinates.ts).

A função filtrarGoogleViz tenta obter automaticamente a coluna final do intervalo usando o VizSelectQueryBuilder, mas às vezes é preciso usar colunas além das fornecidas na query SQL. Nestes casos, É preciso passar diretamente a maior coluna para filtrarGoogleViz.

Query Builder

Para reduzir (se não eliminar) erros de digitação nas queries SQL, foi implementada uma classe VizSelectQueryBuilder cujo propósito é produzir o texto final que virará a a query SQL a ser enviada à API do Google Viz. Deste modo, podemos abstrair a tarefa de gerar SQL sintáticamente válido sem ter que lidar com strings grandes de difícil leitura, balanceamento de parênteses, aspas e outros caracteres de natureza similar, ou conversão para notação A1 usando numParaCol().

Ela funciona encadeiando uma série de filtros/statements lógicos (implementados como funções) com AND. É possível, como visto anteriormente, encadear o builder recursivamente com OR. O usuário passa a or() uma sequência de subbuilders ou funções que produzem subbuilders que são por si também VizSelectQueryBuilders, ele irá avaliar o texto destes builders e encadear o resultado final com ORs. O exemplo Um exemplo de uso de filtrarGoogleViz produz a seguinte query:

Query em Equipe (A-Q): SELECT * WHERE ((E = "PRESENTE") OR (E != "PRESENTE" AND I IS NOT NULL)) AND M = 2 AND Q = "PINTURA" AND L = "JOÃO SILVA" (retornou 5 linhas)

A API é feita para ser flexível. Funções podem ser usadas de mais de uma maneira. Por exemplo: A função equals() automaticamente converte algo como equals(COLUNA_STATUS, "Execução Concluída", "Cancelada/Tramitado") para ("A" = "Execução Concluída" OR "A" = "Cancelada/Tramitado"). O oposto, aplicando o teorema de De Morgan na expressão booleana, vale para notEquals(). Funções que usam colunas aceitam as coordenadas brutas (números) ou a notação A1 (strings) das colunas.

Nota

É possível usar o operador IN do SQL para substituir a lógica booleana na verificação de valores iguais ou não iguais. Isso fica como uma possível refatoração a ser feita futuramente.