Fundamentos do Apps Script com as Planilhas Google #5: criar gráficos e apresentar dados nas Apresentações

1. Introdução

Esta é a quinta parte da playlist de codelabs Fundamentos do Apps Script com as Planilhas Google. Este codelab ensina como usar o serviço de planilhas no Apps Script para criar um gráfico de um conjunto de dados. Você também vai aprender a usar o serviço Apresentações para exportar o gráfico para uma nova apresentação no Google Apresentações.

O que você vai aprender

  • Como criar um gráfico de linhas com o Apps Script.
  • Como exportar gráficos para uma nova apresentação Google.
  • Como apresentar caixas de diálogo ao usuário nas Planilhas.

Antes de começar

Este é o quinto codelab da playlist Fundamentos do Apps Script com as Planilhas Google. Antes de iniciar este codelab, conclua os anteriores:

  1. Macros e funções personalizadas
  2. Planilhas, páginas e intervalos
  3. Como trabalhar com dados
  4. Formatação de dados

O que é necessário

  • Conhecimento dos tópicos básicos do Apps Script abordados nos codelabs anteriores desta playlist.
  • Conhecimento básico do editor do Apps Script
  • Conhecimento básico das Planilhas Google.
  • Capacidade de ler a notação A1 do Google Sheets
  • Conhecimento básico de JavaScript e da classe String.

2. Configurar

Antes de continuar, você precisa de uma planilha com alguns dados. Como antes, fornecemos uma planilha de dados que você pode copiar para esses exercícios. Siga estas etapas:

  1. Clique neste link para copiar a planilha de dados e depois em Fazer uma cópia. A nova planilha é colocada na sua pasta do Google Drive e recebe o nome "Cópia de Datas e taxas de câmbio em USD".
  2. Clique no título da planilha e mude de "Cópia de Datas e taxas de câmbio em dólar" para "Datas e taxas de câmbio em dólar". Sua planilha deve ter esta aparência, com algumas informações básicas sobre diferentes taxas de câmbio do dólar americano em datas diferentes:

45a3e8814ecb07fc.png

  1. Para abrir o editor de script, clique em Extensões> Apps Script.

Para economizar tempo, incluímos um pouco de código para configurar um menu personalizado nessa planilha. Talvez você tenha visto o menu aparecer quando sua cópia da planilha foi aberta:

9b9caf6c1e9de34b.png

Com essa planilha e esse projeto, você já pode começar o codelab. Vá para a próxima seção e comece a aprender sobre gráficos e gatilhos baseados em tempo.

3. Criar um gráfico nas Planilhas com o Apps Script

Suponha que você queira criar um gráfico específico para visualizar um conjunto de dados. Você pode usar o Apps Script para criar, editar e inserir gráficos nas Planilhas Google. Quando um gráfico está em uma planilha, ele é chamado de gráfico incorporado.

Os gráficos são usados para mostrar uma ou mais séries de dados. Para gráficos incorporados, os dados apresentados geralmente vêm da planilha. Normalmente, a atualização dos dados na planilha faz com que o Planilhas também atualize o gráfico automaticamente.

Você pode usar o Apps Script para criar gráficos personalizados e incorporados do zero ou atualizar os gráficos atuais. Esta seção apresenta os conceitos básicos da criação de gráficos incorporados nas Planilhas Google com o Apps Script e o serviço Spreadsheet.

Implementação

Na sua cópia da planilha de dados, o conjunto de dados "Datas e taxas de câmbio" mostra as taxas de câmbio (para US$ 1) de diferentes moedas em datas diferentes. Você vai implementar uma função do Apps Script que cria um gráfico para visualizar parte desses dados.

Siga estas etapas:

  1. No editor do Apps Script, adicione a seguinte função ao final do script Code.gs do projeto, depois da função onOpen():
/**
 * Creates and inserts an embedded
 * line chart into the active sheet.
 */
function createEmbeddedLineChart() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chartDataRange = sheet.getRange(
    'Dates and USD Exchange Rates dataset!A2:F102');
  var hAxisOptions = {
    slantedText: true,
    slantedTextAngle: 60,
    gridlines: {
      count: 12
    }
  };
  
  var lineChartBuilder = sheet.newChart().asLineChart();
  var chart = lineChartBuilder
    .addRange(chartDataRange)
    .setPosition(5, 8, 0, 0)
    .setTitle('USD Exchange rates')
    .setNumHeaders(1)
    .setLegendPosition(Charts.Position.RIGHT)
    .setOption('hAxis', hAxisOptions)
    .setOption("useFirstColumnAsDomain", true)
    .build();
 
  sheet.insertChart(chart);  
}
  1. Salve o projeto de script.

Revisão de código

O código que você adicionou implementa a função chamada pelo item de menu Gráfico "Conjunto de dados de datas e taxas de câmbio em USD" para criar um gráfico de linhas básico. Vamos analisar o código.

As primeiras linhas configuram as três variáveis a seguir:

  • sheet: uma referência à planilha ativa atual.
  • chartDataRange: o período que queremos visualizar. O código usa a notação A1 para especificar que o intervalo abrange as células A2 a F102 na planilha chamada Conjunto de dados de datas e taxas de câmbio em dólar. Ao nomear a planilha especificamente, garantimos que o item de menu funcione mesmo que uma planilha diferente esteja ativa, já que o intervalo sempre cobre a posição dos dados. Começar na linha 2 significa que estamos incluindo os cabeçalhos das colunas e vamos criar um gráfico apenas com as 100 datas (linhas) mais recentes.
  • hAxisOptions: um objeto JavaScript básico que inclui algumas informações de configuração usadas pelo código para configurar a aparência do eixo horizontal. Especificamente, eles definem os rótulos de texto do eixo horizontal em uma inclinação de 60 graus e definem o número de linhas de grade verticais como 12.

A próxima linha cria um objeto criador de gráficos de linhas. Os gráficos incorporados no Apps Script são criados usando um padrão de projeto Builder. Uma explicação completa desse padrão de design está fora do escopo deste codelab. Por enquanto, entenda que o serviço Spreadsheet oferece várias classes EmbeddedChartBuilder. Para criar um gráfico, seu código primeiro cria um objeto builder de gráfico incorporado, usa os métodos dele para definir as configurações do gráfico e, em seguida, chama um método build() para criar o objeto EmbeddedChart final. Seu código nunca modifica o objeto EmbeddedChart diretamente, já que toda a configuração do gráfico é gerenciada pelas classes de builder.

O serviço de planilha fornece uma classe EmbeddedChartBuilder principal e várias classes de builder secundárias (como EmbeddedLineChartBuilder) que herdam dela. As classes secundárias permitem que o Apps Script forneça os métodos de configuração de gráficos dos builders que são aplicáveis apenas a determinados tipos de gráficos. Por exemplo, a classe EmbeddedPieChartBuilder oferece um método set3D() que só se aplica a gráficos de pizza.

No seu código, esta linha cria a variável de objeto builder lineChartBuilder:

var lineChartBuilder = sheet.newChart().asLineChart();

O código chama o método Sheet.newChart() para criar um objeto EmbeddedChartBuilder e usa EmbeddedChartBuilder.asLineChart() para definir o tipo de builder como EmbeddedLineChartBuilder.

Em seguida, o código cria o gráfico usando lineChartBuilder. Essa parte do código é apenas uma série de chamadas de método para definir as configurações do gráfico, seguidas por uma chamada build() para criar o gráfico. Como você viu em codelabs anteriores, o código usa o encadeamento de métodos para manter o código legível para humanos. Veja o que as chamadas de método estão fazendo:

Por fim, o código chama Sheet.insertChart(chart) para colocar o gráfico criado na planilha ativa.

Resultados

Para ver a função de formatação em ação, faça o seguinte:

  1. Se você ainda não fez isso, salve o projeto de script no editor do Apps Script.
  2. Clique no item de menu Apresentar conjunto de dados > Gráfico "Conjunto de dados de datas e taxas de câmbio em USD".

O script agora coloca um novo gráfico à direita dos dados:

bbf856699b6d2b45.gif

Parabéns! Você criou um gráfico de linhas incorporado com o Apps Script. A próxima seção ensina como exportar seu gráfico para as Apresentações Google.

4. Exportar seus gráficos para as Apresentações Google

Um dos grandes pontos fortes do Apps Script é que ele permite mover dados com facilidade de um aplicativo do Google Workspace para outro. A maioria desses aplicativos tem um serviço dedicado do Apps Script, semelhante ao serviço de planilha. Por exemplo, o Gmail tem o serviço do Gmail, os Documentos Google têm o serviço de documentos e as Apresentações Google têm o serviço de apresentações. Com todos esses serviços integrados, é possível extrair dados de um aplicativo, processá-los e gravar o resultado em outro.

Nesta seção, você vai aprender a exportar todos os gráficos incorporados em uma planilha Google para uma nova apresentação do Apresentações Google. Você também vai conhecer duas maneiras de mostrar as mensagens personalizadas do usuário nas Planilhas.

Implementação

Aqui você vai implementar a função chamada pelo item de menu Apresentar conjunto de dados > Exportar gráficos para o Apresentações. Siga estas etapas:

  1. No editor do Apps Script, adicione a seguinte função ao final do script Code.gs do projeto, depois da função createEmbeddedLineChart():
/**
 * Create a Slides presentation and export
 * all the embedded charts in this spreadsheet
 * to it, one chart per slide.
 */
function exportChartsToSlides() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Fetch a list of all embedded charts in this
  // spreadsheet.
  var charts = [];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    charts = charts.concat(sheets[i].getCharts());
  }
  
  // If there aren't any charts, display a toast
  // message and return without doing anything
  // else.
  if (charts.length == 0) {
    ss.toast('No charts to export!');
    return;
  }
  
  // Create a Slides presentation, removing the default
  // title slide.
  var presentationTitle =
    ss.getName() + " Presentation";
  var slides = SlidesApp.create(presentationTitle);
  slides.getSlides()[0].remove();  
  
  // Add charts to the presentation, one chart per slide.
  var position = {left: 40, top: 30};
  var size = {height: 340, width: 430};
  for (var i = 0; i < charts.length; i++) {
    var newSlide = slides.appendSlide();
    newSlide.insertSheetsChart(
      charts[i],
      position.left,
      position.top,
      size.width,
      size.height);   
  }
  
  // Create and display a dialog telling the user where to
  // find the new presentation.
  var slidesUrl = slides.getUrl();
  var html = "<p>Find it in your home Drive folder:</p>"
      + "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
      + presentationTitle + "</a></p>";
  
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(html)
      .setHeight(120)
      .setWidth(350),
      "Created a presentation!"
  );
}
  1. Salve o projeto de script.

Revisão de código

Esse código pode ser menor do que o esperado. Vamos analisar o que ele faz dividindo o código em cinco seções:

1: acessar os gráficos

As primeiras linhas pesquisam a planilha ativa para encontrar todos os gráficos incorporados, reunindo-os na matriz charts. Essas linhas usam os métodos Spreadsheet.getSheets() e Sheet.getCharts() para receber listas de planilhas e gráficos. O método JavaScript Array.concat() é usado para anexar a lista de gráficos de cada planilha em charts.

2: verifique se há gráficos para exportar

O código verifica se há gráficos para exportar. Para evitar uma apresentação em branco, se não houver gráficos, o código vai criar uma mensagem de toast usando Spreadsheet.toast(message). Essa é uma pequena caixa de diálogo que aparece no canto inferior direito do app Planilhas, fica por alguns segundos e depois desaparece:

db7e87dcb8010bef.gif

Se não houver gráficos para exportar, o código vai criar a mensagem de toast e sair sem fazer mais nada. Se houver gráficos para exportar, o código vai continuar criando uma apresentação nas próximas linhas.

3: crie uma apresentação

A variável presentationTitle é criada para armazenar o nome do arquivo da nova apresentação. Ele é definido como o nome da planilha, com " Presentation" concatenado no final. Em seguida, o código chama o método do serviço Apresentações SlidesApp.create(name) para criar uma apresentação.

As novas apresentações são criadas com um único slide em branco. Não queremos isso na nossa apresentação. Por isso, o código remove o elemento com Presentation.getSlides() e Slide.remove().

4: exportar os gráficos

Na próxima seção, o código define o position e o size dos objetos JavaScript para definir onde os gráficos importados são colocados no slide e qual será o tamanho deles (em pixels).

O código faz um loop em todos os gráficos na lista. Para cada gráfico, um newSlide é criado com Presentation.appendSlide(), adicionando o slide ao final da apresentação. O método Slide.insertSheetsChart(sourceChart, left, top, width, height) é usado para importar o gráfico para o slide com os position e size especificados.

5: Compartilhe o local da apresentação

Por fim, o código precisa informar ao usuário onde a nova apresentação está localizada, de preferência com um link em que ele possa clicar para abrir. Para isso, o código usa o HTML serviço do Apps Script para criar uma caixa de diálogo modal personalizada. As caixas de diálogo modais (também conhecidas como caixas de diálogo personalizadas no Apps Script) são janelas que aparecem na interface do Planilhas. Quando mostradas, as caixas de diálogo personalizadas impedem que o usuário interaja com o app Planilhas.

Para criar uma caixa de diálogo personalizada, o código precisa do HTML que define o conteúdo dela. Isso é fornecido na variável html. O conteúdo inclui um pequeno parágrafo e um hiperlink. O hiperlink é a variável presentationTitle, vinculada ao URL da apresentação fornecido por Presentation.getUrl(). O hiperlink também usa o atributo target="_blank" para que a apresentação seja aberta em uma nova guia do navegador, em vez de dentro da caixa de diálogo.

O HTML é analisado em um objeto HtmlOutput pelo método HtmlService.createHtmlOutput(html). O objeto HtmlOutput permite que o código defina o tamanho da caixa de diálogo personalizada com HtmlOutput.setHeight(height) e HtmlOutput.setWidth(width).

Depois que htmlOutput é criado, o código usa o método Ui.showModalDialog(htmlOutput, title) para mostrar a caixa de diálogo com o título especificado.

Resultados

Agora que você implementou o segundo item de menu, é possível vê-lo em ação. Para testar a função exportChartsToSlides():

  1. Se você ainda não fez isso, salve o projeto de script no editor do Apps Script.
  2. Abra a planilha e clique no item de menu Apresentar conjunto de dados > Gráfico "Conjunto de dados de datas e taxas de câmbio em USD" para criar um gráfico para exportar. Ele vai aparecer ancorado à célula H5 na planilha ativa.
  3. Clique no item de menu Apresentar conjunto de dados > Exportar gráficos para as Apresentações. Talvez seja necessário autorizar o script novamente.
  4. O script vai processar a solicitação e mostrar a caixa de diálogo personalizada.
  5. Para abrir a nova apresentação do Google Apresentações, clique no link Apresentação de datas e taxas de câmbio em dólar:

51326ceaeb3e49b2.gif

Se quiser, você também pode adicionar mais gráficos à planilha e selecionar novamente o item de menu para criar uma apresentação com vários slides.

Agora você pode exportar gráficos criados no Planilhas para uma apresentação Google. Você também pode escrever código para criar uma caixa de diálogo personalizada.

Você concluiu o último exercício deste codelab. Vá para a próxima seção para revisar o que você aprendeu.

5. Conclusão

Parabéns! Você concluiu este codelab e toda a playlist de codelabs Fundamentos do Apps Script com o Google Sheets. Use os princípios ensinados nesta playlist para ampliar sua experiência com o app Planilhas e conhecer os recursos do Apps Script.

Você achou este codelab útil?

Sim Não

O que você aprendeu

  • Como criar um gráfico de linhas incorporado com o Apps Script.
  • Como apresentar mensagens de toast e caixas de diálogo personalizadas a um usuário no app Planilhas.
  • Como exportar um gráfico para uma nova apresentação Google.

A seguir

Você concluiu esta playlist. No entanto, ainda há muito mais para aprender sobre o Apps Script.

Confira estes recursos (em inglês):

Boa programação!

Você achou esta playlist de codelabs útil?

Sim Não

Você gostaria de ver mais codelabs do Apps Script no futuro?

Sim Não