Série GAS #3 – Criando um Dashboard

Este texto faz parte da série Google Apps Script – Spreadsheet. Nos tutoriais anteriores detalhamos:

CGAS #1 – Como mudar a aba da planilha usando script do Google
GAS #2 – Como automatizar o envio de e-mails pelo Google Spreadsheet

**

O Painel de controle, também conhecido como Dashboard, é cada vez mais difundido na gestão empresarial, utilizado em variadas áreas das empresas. Na gestão de marketing e comercial temos o Google Analytics, permite que possamos acompanhar as métricas e avaliar o ROI (Retorno Sobre Investimento). No mercado financeiro como Home Broker que permite acompanhar os preços e as oscilações das ações, indicadores financeiros, entre outros dados.

A avaliação de desempenho otimizada é uma das maiores vantagens de se usar um Dashboard. A gestão da empresa visível para todos é essencial para a que evolução da equipe seja contínua. A objetividade e dinamização na atualização dos dados faz com que a tomada de decisão seja rápida, por exemplo, quando houver uma queda fora da curva de algum indicador, caso em que a gestão facilmente visualizará o outlier no painel e assim poderá agir rapidamente.

Neste post vamos construir um Dashboard que apresentará a informação de três formas diferentes: 1) por tabela; 2) por gráfico de coluna e gráfico de linha; 3) e filtros para facilitar na busca pela informação.

Existem formas de programar um Dashboard alimentando os dados manualmente, mas a mudança e atualização do painel será lenta pois, neste caso, a cada alteração seria necessário acessar o código e atualizar os números um por um. No nosso caso, teremos uma forma mais dinâmica de atualização do Painel que será alimentado usando informações de uma planilha de nossa escolha, sendo assim a cada alteração no cenário o Dashboard atualizará automaticamente, sem a necessidade de um input manual de dados.

Construindo o Dashboard

1) Abra o editor de Scripts.

2) Usaremos uma ‘function’ genérica ‘doGet’. A presença dessa função faz com que esse script possa ser executado através de requests, o que será usado mais pra frente. Nas duas primeiras linhas de código ativamos a planilha e o intervalo onde os dados serão coletados.

Importante: a ‘Id’ da planilha será a da aba cujos dados serão usados para o dashboard, e pode ser encontrada na URL do navegador quando a aba é acessada, conforme destaque abaixo:

3) Construiremos os filtros que serão usados no dashboard, conforme dados da planilha.

** para não termos problemas de funcionalidade deixe a tabela a ser utilizada no código na primeira aba da planilha, mesmo que oculta.

Os dados serão coletados a partir da coluna B (ou 1, no quando referenciada via código).

• • Coluna 1 (B) = ano (Year) ;

• • Coluna 2 (C) = Categoria (Category);

• Coluna 3 (D) = Faturamento (Value R$ // Revenue)

No script, dentro da função ‘doGet’ criada anteriormente, adicionamos as seguintes linhas de código:

Cada linha consiste na criação de um filtro, baseado nas colunas citadas anteriormente. Repare que o método ‘setFilterColumnIndex’ recebe um valor numérico equivalente à coluna em questão (B = 1, C = 2, etc).

4) No próximo passo vamos construir a tabela que aparecerá no dashboard:

Neste caso estamos utilizando gráficos de coluna e de linha, mas há também outros tipos disponíveis.

5) Definimos agora a variável ‘dashboard’, que receberá um os dados do intervalo e as variáveis criadas anteriormente no código.

6) O último passo será a configuração final do dashboard, assim como sua orientação e espaçamento (seu design).

Após terminar salve o código.

Código completo:

function doGet() {
   var ss = SpreadsheetApp.openById('1NVUxb_cMETPR250IKJ1USyVdtvUv_w7T9dM1oDoFZqI'); // ativar planilha com Id
   var data = ss.getDataRange(); // Intervalo de dados a ser coletado
   
   var revenueFilter = Charts.newNumberRangeFilter().setFilterColumnIndex(3).build();  // criação de um filtro faturamento
   var categoryFilter = Charts.newCategoryFilter().setFilterColumnIndex(2).build(); // criação de um filtro categoria
   var yearFilter = Charts.newStringFilter().setFilterColumnIndex(1).build(); // criação de um filtro ano
 
   var tableChart = Charts.newTableChart()
   .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([1,2,3])) // criação da tabela com os dados da planilha
   .build();
   
   var columnChart = Charts.newColumnChart()
   .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([1,3])) // gráfico de coluna
   .build();
   
    var lineChart = Charts.newLineChart()
   .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([1,3])) // gráfico de linha
   .build();
   
   var dashboard = Charts.newDashboardPanel().setDataTable(data)
   .bind([yearFilter, revenueFilter, categoryFilter], [tableChart, columnChart, lineChart]) // criação do dashboard
   .build();
   
   var app = UiApp.createApplication();
   var filterPanel = app.createVerticalPanel(); // criar os filtros no painel
   var chartPanel = app.createHorizontalPanel(); // criar os gráficos no painel
   filterPanel.add(yearFilter).add(revenueFilter).add(categoryFilter).setSpacing(10); // configuração e criação dos filtros
   chartPanel.add(tableChart).add(columnChart).add(lineChart).setSpacing(5); // configuração e criar os gráficos
   
   dashboard.add(app.createVerticalPanel().add(filterPanel).add(chartPanel)); // adicionar dashboard e configurações
   app.add(dashboard);
   return app;
 }

Por fim, vá em ‘Publish’ e escolha a opção ‘Deploy as web app..’, abrirá um painel de permissões e um link a qual você deverá copiar e colar no browser, imagens abaixo:

Importante: devido a um possível ‘bug’ de login de contas do Google pode ser que tenhamos problemas para acessar a URL. Para resolver retire o ‘/u/0’ do endereço.

Para mais informações acesse o fórum: https://stackoverflow.com/questions/47045209/google-drive-page-not-found-sorry-unable-to-open-the-file-at-this-time

Dashboard construído

..com o ‘revenueFilter (Value)’.

… com o ‘yearFilter’ e ‘categoryFilter’.

Repare que no filtro ‘Year’ temos como default todos os anos selecionados para aparecer nos gráficos, para mudar devemos escrever o ano em que queremos dar enfase. No filtro ‘Value R$ // Revenue’ temos um ‘Range’, ou seja, podemos escolher apenas o intervalo de valores em que nos interessa para a análise em questão e, por fim, temos o filtro ‘Category’ que funciona como uma lista suspensa, no caso temos apenas uma categoria então aparecerá no filtro apenas a opção ROL (Receita Líquida Operacional).

Vale observar que o código do post consegue apenas trabalhar com uma das variáveis por vez, ano ou categoria, sendo assim ao acrescentar uma nova categoria na planilha teremos que selecionar apenas um ano para os dados aparecerem corretamente.

Nos próximos posts sobre GAS teremos a evolução deste dashboard.

Uma alternativa para acessar o painel mais rapidamente é acrescentar um link numa célula para que o acesso ao dashboard seja mais ágil.

Ou até mesmo criar um botão com as dicas de nosso post anterior da série, Mudando a aba da planilha usando script do Google, e vincular com o ‘Id’ da planilha.

Se conseguir, compartilha com a gente!

Considerações finais

Na Taller a gestão visual tem nos auxiliado na tomada de decisão, tornando-a mais ágil e otimizada uma vez que temos todas as informações centralizada em um único Painel de Controle. Em um único Dashboard financeiro conseguimos extrair informações, através de gráficos e números, como o saldo em caixa atual nos bancos, o crédito disponível em uma necessidade de alavancagem, valor das vendas por clientes, comparações com números de anos anteriores e indicadores de resultado (lucro operacional, LAIR, lucro líquido) otimizando assim a gestão financeira da empresa.

Post inspirado em:

O que acha de ter esse conteúdo e muito mais em um eBook completo com outros tutoriais incríveis?

Baixar eBook Google Apps Script – Spreadsheet →