Transforme Big Data em insights com as Planilhas e Apresentações Google

Existem muitas ferramentas para cientistas de dados realizarem análises de Big Data, mas, no fim das contas, você ainda precisa justificar esses resultados à diretoria? Muitos números no papel ou em um banco de dados não são apresentáveis para os principais stakeholders. Este codelab intermediário do Google Apps Script usa duas plataformas para desenvolvedores do Google, o G Suite e o Google Cloud Platform (GCP), para ajudar você nesta etapa final.

Com as ferramentas para desenvolvedores do Google Cloud, você pode fazer uma análise detalhada de dados, colocar os resultados em uma planilha e gerar uma apresentação de slides com esses dados, oferecendo um cenário mais adequado para que os resultados sejam entregues à gerência. Neste codelab, você vai usar a API BigQuery do GCP (como um serviço avançado do Apps Script) e os serviços integrados do Apps Script para o Planilhas Google e o Apresentações Google.

Motivação/anterioridade

O app de exemplo neste codelab foi inspirado nestes outros exemplos de código...

Embora o app de exemplo do codelab da API Slides também tenha o BigQuery e o Apresentações, ele difere do app de exemplo deste codelab de várias maneiras:

  • App Node.js x nosso app Apps Script
  • Usa APIs REST enquanto usamos serviços do Apps Script
  • Usa o Google Drive, mas não as Planilhas Google, enquanto este app usa as Planilhas, mas não o Drive.

Para este codelab, queríamos reunir o máximo possível de tecnologias em um único app, mostrando recursos e APIs do Google Cloud de uma forma que se assemelhe mais a um caso de uso real. O objetivo é inspirar você a usar a imaginação e considerar o uso do GCP e do G Suite para resolver problemas desafiadores da organização ou dos clientes.

O que você vai aprender

  • Como usar o Google Apps Script com serviços do Google (GCP e G Suite)
  • Como usar o Google BigQuery para fazer uma análise de Big Data
  • Como criar uma planilha Google e preenchê-la com dados
  • Como criar um gráfico nas Planilhas Google
  • Como transferir gráficos e dados das Planilhas Google para uma apresentação Google

O que é necessário

  • Acesso à Internet e a um navegador da Web
  • Uma Conta do Google (as contas do G Suite podem exigir a aprovação do administrador).
  • Habilidades básicas de JavaScript
  • Ter conhecimento sobre o desenvolvimento do Apps Script pode ser útil, mas não é obrigatório

Como você vai usar este codelab/tutorial?

Ler para fins informativos, talvez repassando para colegas técnicos Fazer o máximo possível e tentar o máximo de exercícios que puder De qualquer jeito, vou concluir todo o codelab

Como você classificaria sua experiência com as ferramentas e APIs para desenvolvedores do G Suite?

Iniciante Intermediário Proficiente

Como você classificaria sua experiência com o Apps Script especificamente?

Iniciante Intermediário Proficiente

Como você classificaria sua experiência com as ferramentas e APIs para desenvolvedores do GCP?

Iniciante Intermediário Proficiente

Agora que você já sabe sobre o que é este codelab, o que vai fazer exatamente?

  1. Pegar uma amostra do Apps Script-BigQuery e fazê-la funcionar
  2. Com base nessa amostra, saiba como enviar uma consulta ao BigQuery e receber os resultados.
  3. Crie uma planilha Google e preencha os resultados do BigQuery nela
  4. Modifique um pouco o código para alterar levemente os dados retornados e adicionados à planilha.
  5. Usar o serviço Planilhas no Apps Script para criar um gráfico com os dados do BigQuery
  6. Usar o serviço Apresentações Google para criar uma apresentação de slides
  7. Adicione um título e um subtítulo ao slide de título padrão criado automaticamente para todas as novas apresentações de slides.
  8. Crie um novo slide com uma tabela de dados e importe as células de dados da planilha para ele.
  9. Adicione outro slide e o gráfico da planilha a ele.

Vamos começar com algumas informações básicas sobre o Apps Script, o BigQuery, as Planilhas e as Apresentações.

Google Apps Script e BigQuery

O Google Apps Script é uma plataforma de desenvolvimento do G Suite que opera em um nível mais alto do que as APIs REST do Google. Ele é um ambiente de desenvolvimento e hospedagem de aplicativos sem servidor que pode ser usado por desenvolvedores com os mais variados níveis de experiência. Resumindo, "o Apps Script é um ambiente de execução JavaScript sem servidor para automação, extensão e integração do G Suite".

Assim como o Node.js, ele é uma plataforma para desenvolvimento com JavaScript no servidor. Porém, a principal atividade do Apps Script é otimizar a integração com o G Suite e com outros serviços do Google, e não a hospedagem de aplicativos rápida, assíncrona e orientada a eventos. Além disso, o ambiente de desenvolvimento dele pode ser completamente diferente do que você conhece. Com o Apps Script, é possível:

  • desenvolver em um editor de código baseado em navegador, mas pode trabalhar localmente usando a clasp, que é uma ferramenta de implantação de linha de comando para o Apps Script;
  • programar em uma versão especializada do JavaScript, personalizada para acessar o G Suite e outros serviços do Google ou externos (usando os serviços URLfetch ou Jdbc do Apps Script);
  • não se preocupar em escrever o código de autorização, já que o Apps Script faz isso por você;
  • não hospedar seu aplicativo, já que ele é armazenado e executado em servidores do Google na nuvem.

OBSERVAÇÃO: este codelab não ensina a usar o Apps Script. Você encontra vários recursos on-line para isso. A documentação oficial inclui uma visão geral com guias de início rápido, tutoriais e vídeos. Por fim, não se esqueça do codelab introdutório do Apps Script, que precisa ser concluído antes deste.

O Apps Script interage com outras tecnologias do Google de duas maneiras diferentes:

  • serviço integrado/nativo
  • como um serviço avançado.

Um serviço integrado oferece métodos de alto nível para acessar dados do G Suite e de produtos do Google, ou outros métodos utilitários. Um serviço avançado é apenas um wrapper simples de uma API REST do G Suite ou do Google. mas são totalmente compatíveis com a API REST e podem desempenhar mais funções do que os serviços integrados. No entanto, eles exigem uma programação mais complexa (embora ainda sejam mais fáceis de usar do que a API REST). É preciso ativar os serviços avançados para um projeto de script antes de usá-los.

Sempre que possível, os desenvolvedores devem preferir um serviço integrado porque eles são mais fáceis de usar e fazem mais trabalho pesado do que os serviços avançados. No entanto, algumas APIs do Google não têm serviços integrados. Nesses casos, usar um serviço avançado pode ser a única opção. O Google BigQuery é um exemplo disso. Ele não tem um serviço integrado, mas tem o serviço avançado correspondente. Melhor do que nada, não é? Para quem não conhece, o BigQuery é um serviço do GCP usado para fazer consultas simples ou complexas em volumes de dados muito grandes, com vários terabytes. Ainda assim, ele apresenta os resultados em segundos.

Acesse as Planilhas e Apresentações Google pelo Apps Script

Ao contrário do BigQuery, os apps Planilhas e Apresentações têm serviços integrados e avançados, que só são usados para acessar recursos encontrados apenas na API. Consulte a documentação dos serviços integrados Planilhas e Apresentações antes de começar a programar. Claro que também há documentação para os serviços avançados. Confira os links para Planilhas e Apresentações, respectivamente.

Introdução

Este codelab começa pela tarefa maior. Na verdade, quando você terminar aqui, já terá concluído metade do codelab. Dividido em várias subseções, você vai fazer o seguinte:

  • Iniciar um novo projeto do Google Apps Script
  • Ativar o acesso ao serviço avançado do BigQuery
  • Acessar o editor de desenvolvimento e inserir o código-fonte do aplicativo
  • Passar pelo processo de autorização de aplicativo (OAuth2)
  • Executar o aplicativo que envia uma solicitação ao BigQuery
  • Ver um novo documento no Planilhas Google criado com os resultados do BigQuery

Configuração

  1. a) Acesse script.google.com e crie um projeto do Apps Script . Existem diferentes linhas de produtos do G Suite, e a maneira de criar um novo projeto varia de acordo com a versão usada. Se você estiver usando apenas sua conta do Gmail e não tiver experiência em desenvolvimento de projetos, uma tela em branco vai aparecer com um botão para criar seu primeiro projeto:


b) Caso contrário, você verá todos os seus projetos e um grande botão +Novo no canto superior esquerdo. Clique nele.



c) Se nenhuma das opções acima for o caso, sua tela poderá ser parecida com a imagem abaixo. Se sim, procure um ícone de menu hambúrguer no canto superior esquerdo e selecione +Novo script.



d) Para quem prefere a linha de comando. Sua ferramenta é o clasp. Especificamente, você vai executar o comando clasp create.

e) A última maneira de criar um projeto de script é acessar o link de atalho: https://script.google.com/create.

  1. Independente da técnica usada para iniciar um novo projeto, você vai acessar o editor de código do Apps Script, uma tela parecida com esta:


  2. Clique em Arquivo > Salvar e dê um nome ao projeto.
  3. Em seguida, crie um projeto do console do Google Cloud para executar consultas do BigQuery.
  1. Crie um projeto, dê um nome a ele, selecione uma conta de faturamento e clique em CRIAR.
  1. Quando a criação do projeto é concluída, uma notificação aparece no canto superior direito da página. Clique na entrada Criar projeto: <Nome do projeto> para abri-lo.
  2. Clique no ícone de menu no canto superior esquerdo e acesse APIs e serviços > Credenciais. Clique na guia Tela de consentimento OAuth (link direto).
  3. No campo Nome do aplicativo, insira "Big Data Codelab" e clique no botão Salvar na parte de baixo.
  4. Clique no ícone de três pontosno canto superior direito para abrir o menu e selecione Configurações do projeto (link direto).
  5. Copie o valor listado em Número do projeto. Há um campo separado ID do produto que vamos usar mais tarde no codelab.
  6. Volte ao editor do Apps Script e clique em Recursos > Projeto do Cloud Platform.
  7. Insira o número do projeto na caixa de texto e clique em Definir projeto. Quando solicitado, clique em Confirmar.
  8. Quando terminar, clique no botão Fechar para dispensar a caixa de diálogo.
  9. Agora que você tem um novo projeto configurado, é necessário ativar o serviço avançado do BigQuery. Para isso, acesse Recursos -> Serviços avançados do Google e ative a opção da API BigQuery.


  10. Você vai encontrar a seguinte mensagem na parte de baixo: "Esses serviços também precisam ser ativados no painel de APIs do Google Cloud Platform". Clique nesse link para abrir outra guia do navegador no console do desenvolvedor ou "devconsole".
  11. No devconsole, clique no botão +Ativar APIs e serviços na parte de cima, pesquise "bigquery", selecione a API BigQuery (não a API BigQuery Data Transfer) e clique em Ativar para ativar. Deixe essa guia do navegador aberta.

    OBSERVAÇÃO: depois que a API for ativada, talvez apareça uma nota nesta página dizendo algo como "Para usar essa API, você precisa criar credenciais...", mas não se preocupe com isso por enquanto. O Apps Script vai cuidar dessa etapa para você.


  12. Na guia do navegador do editor de código, você ainda está no menu "Advanced Google Services". Clique em OK para fechar a caixa de diálogo e voltar ao editor de código. Clique no nome do projeto na parte de cima e dê o nome que quiser, como "Demonstração do BigQuery" ou algo parecido. Nós chamamos o nosso de "última etapa".

Agora você já pode inserir o código, passar pelo processo de autorização e colocar em funcionamento a primeira versão do aplicativo.

Fazer upload e executar o aplicativo

  1. Copie o código na caixa abaixo e cole no editor de código, substituindo qualquer conteúdo presente:
// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BQ job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the new results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}


Agora salve o arquivo que você acabou de criar, mas renomeie de Code.gs para bq-sheets-slides.js. O que esse código faz? Já informamos que ele consulta o BigQuery e registra os resultados em um novo documento do Planilhas Google, mas qual é essa consulta? Ele aparece perto da parte de cima de runQuery():

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10


Essa consulta examina as obras de Shakespeare, que fazem parte do conjunto de dados públicos do BigQuery, e retorna as 10 palavras mais comuns em todas as obras, classificadas em ordem decrescente de frequência. Para você ter uma ideia de como o BigQuery é útil, imagine como seria divertido fazer isso manualmente (só que não).

  1. Estamos quase prontos para testar. Como você pode ver na parte de cima deste snippet de código, é necessário um ID de projeto válido. Portanto, precisamos adicionar o seu ao código do aplicativo. Para isso, volte à janela ou guia do navegador com a página do console do desenvolvedor. (Pedimos para você deixar aberto, lembra?)
  2. Na parte de cima, à esquerda do avatar da sua Conta do Google, está o seletor de menu suspenso (). Clique nele e selecione Configurações do projeto. Você vai ver o nome, o ID e o número do projeto. Copie o ID do projeto e defina a variável PROJECT_ID na parte de cima de bq-sheets-slides.js com o valor que você recebeu do console do desenvolvedor. OBSERVAÇÃO: se o seletor de menu travar e parar de funcionar, recarregue a página.
  3. A instrução if impede que o aplicativo continue sem um ID do projeto. Depois de adicionar o seu, salve o arquivo e execute o código acessando a barra de menu e selecionando Executar > Executar função > runQuery. Clique na caixa de diálogo Revisar permissões. Este app não foi verificado. Abaixo, há um GIF animado (de outro app) que ilustra as próximas etapas:
  4. Depois de solicitar a revisão das permissões, uma nova caixa de diálogo vai aparecer, conforme mostrado acima. Escolha a Conta do Google correta que vai executar o script, selecione Avançado, role a tela para baixo e clique em "Acessar <NOME DO SEU PROJETO> (não seguro)" para acessar a tela de autorização do aplicativo OAuth2. Leia mais sobre o processo de verificação para saber por que essa tela está entre você e a caixa de diálogo de autorização do OAuth2 abaixo.


    OBSERVAÇÃO:depois de autorizar o app, não será necessário repetir esse processo a cada execução. Essa caixa de diálogo só vai aparecer novamente na Tarefa 3 deste tutorial. Ela vai pedir a permissão do usuário para criar e gerenciar arquivos do app Apresentações Google.
  5. Depois de clicar em Permitir na janela de diálogo do OAuth2, o script começa a ser executado. Uma caixa de diálogo amarelo-pastel aparece na parte de cima. Ele é executado rapidamente, então talvez você não perceba que ele está em execução ou que a execução foi concluída.

  6. Essa caixa de diálogo desaparece quando o processo é concluído. Se ela não aparecer, é provável que já tenha terminado. Acesse o Google Drive (drive.google.com) e procure uma nova planilha Google chamada "Most common words in all of Shakespeare's works" ou o nome que você atribuiu à variável QUERY_NAME:


  7. Abra a planilha e você verá 10 linhas de palavras, com as respectivas contagens totais classificadas em ordem decrescente:

Resumo da tarefa 1

Você executou um código que consultou todas as obras de Shakespeare. Não é uma quantidade enorme de dados, mas não seria nada fácil analisar manualmente todas as peças, contar cada palavra e classificar os resultados em ordem decrescente de frequência. Além de delegar essa tarefa ao BigQuery, você também usou o serviço integrado do Apps Script para o Planilhas Google com o intuito de organizar e facilitar a apresentação dos dados.

O código de bq-sheets-slides.js (nome de arquivo escolhido) que você colou acima (além de PROJECT_ID, que precisa ter um ID de projeto real) também pode ser encontrado na pasta step1 no repositório do GitHub deste codelab em github.com/googlecodelabs/bigquery-sheets-slides. O código foi inspirado no exemplo original na página de serviços avançados do BigQuery, que executou uma consulta um pouco diferente: quais são as palavras mais usadas por Shakespeare com 10 ou mais caracteres? Você também pode conferir esse exemplo no repositório do GitHub.

Se você quiser saber mais sobre outras consultas que podem ser feitas nas obras de Shakespeare ou em outras tabelas de dados públicos, confira esta página da Web e esta. Independente da consulta usada, sempre é possível testar no console do BigQuery antes de executar no Apps Script. A interface do usuário do BigQuery está disponível para desenvolvedores em bigquery.cloud.google.com. Por exemplo, veja como fica nossa consulta usando a interface do BigQuery:

Embora as etapas acima tenham usado o editor de código do Apps Script, você também pode desenvolver localmente pela linha de comando. Se preferir, crie um script chamado bq-sheets-slides.js, cole o código acima nele e faça upload para o Google com o comando clasp push. Se você perdeu antes, aqui está novamente o link para o clasp e como usá-lo.

O objetivo de runQuery() é se comunicar com o BigQuery e enviar os resultados para uma planilha. Agora precisamos criar um gráfico com os dados. Vamos criar uma nova função chamada createColumnChart() que chama o método newChart() do documento do Planilhas para fazer isso.

  1. Criar gráfico. Adicione o corpo de createColumnChart() mostrado abaixo a bq-sheets-slides.js logo após runQuery(). Ela busca a planilha com as informações e solicita um gráfico de colunas com todos os dados. O intervalo de dados começa na célula A2, porque a primeira linha tem os cabeçalhos das colunas, e não os dados.
/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above params.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}
  1. Retornar planilha. Acima, createColumnChart() precisa do objeto da planilha. Por isso, precisamos atualizar runQuery() para retornar o objeto spreadsheet e transmiti-lo a createColumnChart(). Depois de registrar a criação do arquivo do Planilhas Google, retorne o objeto spreadsheet ao final de runQuery(), logo após a linha de registro:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Função createBigQueryPresentation() de direção. É uma boa ideia separar logicamente o BigQuery e os recursos de criação de gráficos. Agora, vamos criar uma função createBigQueryPresentation() para acionar o app, chamando runQuery() e createColumnChart(). O código adicionado será parecido com este:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}
  1. Torne o código mais reutilizável. Você retornou o objeto da planilha e criou uma função de acionamento, que são duas etapas importantes. E se um colega quiser reutilizar o runQuery() sem que o URL seja registrado? Para adaptar o runQuery() para uso geral, mova essa linha de registro. E qual é o lugar ideal para ela? Se você pensou em createBigQueryPresentation(), acertou! Depois que você mover a linha de registro, o código vai ficar assim:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

Com essas mudanças acima (exceto PROJECT_ID), seu bq-sheets-slides.js vai ficar assim (e também pode ser encontrado na pasta step2 do repositório do GitHub):

// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BQ job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the new results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above params.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

Salve o arquivo e, na parte de cima do editor de código, execute createBigQueryPresentation() em vez de runQuery(). Depois que você executar essa função, outro arquivo das Planilhas Google será criado e terá um gráfico junto aos dados:

Na parte final deste codelab, você vai criar um novo arquivo das Apresentações Google, preencher o título e o subtítulo no slide correspondente e incluir dois novos slides, um com todas as células de dados e outro com o gráfico.

  1. Criar apresentação de slides. Todo o trabalho na apresentação de slides será feito em createSlidePresentation(), que vamos adicionar a bq-sheets-slides.js logo após createColumnChart(). Vamos começar criando uma apresentação de slides e adicionando um título e um subtítulo ao slide padrão que é gerado com todas as apresentações novas.
/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Slide deck with results
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');
  1. Adicionar tabela de dados. A próxima etapa em createSlidePresentation() é importar os dados das células do app Planilhas para a nova apresentação de slides. Adicione este snippet de código à função:
  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it of
  // the dimensions of the data range; fails if Sheet empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }
  1. Importar gráfico. A etapa final em createSlidePresentation() é criar mais um slide, importar o gráfico da planilha e retornar o objeto Presentation. Adicione este snippet final à função:
  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}
  1. Gráfico de retorno. Agora que a função final está completa, confira de novo a assinatura dela. Sim, createSlidePresentation() exige um objeto de planilha e outro de gráfico. Já ajustamos runQuery() para retornar o objeto Spreadsheet, mas agora precisamos fazer uma mudança semelhante em createColumnChart() para retornar o objeto de gráfico (EmbeddedChart). Volte ao aplicativo para adicionar uma última linha ao final de createColumnChart():
  // NEW: Return chart object for later use
  return chart;
}
  1. Atualize createBigQueryPresentation(). Como createColumnChart() retorna o gráfico, é necessário salvá-lo em uma variável e transmitir a planilha e o gráfico para createSlidePresentation(). Da mesma forma que o URL da nova planilha é registrado, também é possível registrar o URL da nova apresentação de slides. Atualize o createBigQueryPresentation() para que ele fique assim:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}
  1. Salve e execute createBigQueryPresentation() de novo. Antes da execução, o app vai precisar de mais um conjunto de permissões do usuário para visualizar e gerenciar as apresentações Google. Depois que você permitir essa permissão, ele vai funcionar como antes.
  2. Agora, além da planilha criada, você também vai receber uma nova apresentação com três slides (título, tabela de dados e gráfico), conforme mostrado abaixo:

Parabéns! Você criou um aplicativo que aproveita os dois lados do Google Cloud. Ele faz uma solicitação do Google BigQuery que consulta um dos conjuntos de dados públicos, cria uma nova planilha Google para armazenar os resultados, adiciona um gráfico com base nos dados recém-recuperados e, por fim, cria uma apresentação Google com os resultados e o gráfico na planilha.

Tecnicamente, foi isso que você fez. De um modo geral, você transformou uma análise de Big Data em algo fácil de apresentar a um público e usou código para automatizar todo o processo. Esperamos que este exemplo inspire você a usar e personalizar para seus próprios projetos. Ao final deste codelab, vamos dar algumas sugestões de como melhorar ainda mais esse app de exemplo.

Com as mudanças acima da tarefa final (exceto PROJECT_ID), seu bq-sheets-slides.js vai ficar assim (e também pode ser encontrado na pasta final no repositório do GitHub):

bq-sheets-slides.js

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BQ job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the new results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above params.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);

  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Returns a slide deck with results
 * @see http://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it of
  // the dimensions of the data range; fails if Sheet empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

O "segundo arquivo" deste codelab, que é o arquivo de manifesto do Apps Script, appsscript.json, não tem função neste codelab. Para acessar, vá até a guia do navegador do editor de código e selecione Visualizar > Mostrar arquivo de manifesto no menu na parte de cima. O conteúdo deve ser semelhante a isto:

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

O arquivo de manifesto é um arquivo de configuração no nível do sistema que o Apps Script usa para saber qual ambiente de execução disponibilizar para seu aplicativo. Abordar o conteúdo de um arquivo de manifesto está fora do escopo deste codelab, mas você pode ter uma ideia do que ele faz.

Confira a seguir alguns recursos adicionais para você se aprofundar no que foi abordado neste codelab e ainda conhecer outras formas de acessar as ferramentas para desenvolvedores do Google de maneira programática. Procuramos manter este codelab sincronizado com o repositório.

Recursos para este aplicativo

Documentação

Outros codelabs

Básico

Intermediário

Apps de referência

Confira abaixo vários "desafios de programação", diferentes maneiras de melhorar ou aumentar o exemplo que criamos neste codelab. Essa lista não é completa, mas pode dar algumas ideias de onde você pode ir.

  • Aplicativo. Não quer se limitar ao usar JavaScript ou restrições impostas pelo Apps Script? Porte esse aplicativo para sua linguagem de programação favorita que usa as APIs REST do Google BigQuery, Planilhas e Apresentações.
  • BigQuery: Teste uma consulta diferente para o conjunto de dados de Shakespeare. Talvez você encontre uma que seja do seu interesse. Outra consulta de exemplo pode ser encontrada no app de exemplo do BigQuery no Apps Script original.
  • BigQuery: Teste alguns dos outros conjuntos de dados públicos do BigQuery e encontre um que seja mais significativo para você.
  • BigQuery: Antes, mencionamos outras consultas que você pode fazer nas obras de Shakespeare ou em outras tabelas de dados públicos. Queremos compartilhar novamente esta página da Web e esta.
  • Planilhas. Teste outros tipos de gráficos.
  • Google Sheets e BigQuery. Mude a situação... talvez você tenha um grande conjunto de dados em uma planilha em algum lugar. Em 2016, a equipe do BigQuery lançou o recurso que permite aos desenvolvedores usar uma planilha como fonte de dados. Consulte as postagens do blog 1 e 2 para mais informações.
  • Apresentações. Adicione outros slides à apresentação gerada, como imagens ou outros recursos vinculados à sua análise de big data. Confira um guia do serviço integrado das Apresentações Google para começar.
  • G Suite. Encontrar uso para outros serviços integrados do G Suite ou do Google no Apps Script, por exemplo: Gmail, Google Drive, Agenda, Documentos, Maps, Analytics, YouTube etc., além de outros serviços avançados. Consulte a visão geral de referência para serviços integrados e avançados e saiba mais.