GSP240

Visão geral

Existem muitas ferramentas de análise de Big Data disponíveis para os cientistas de dados, mas quais delas ajudam você a explicar e justificar sua análise à diretoria e aos acionistas da empresa? Não basta mostrar números no papel ou no banco de dados. Neste laboratório do Google Apps Script, você vai usar duas plataformas de desenvolvedores do Google para atingir esse objetivo: o Workspace e o Google Cloud.

Com as ferramentas para desenvolvedores do Google Cloud, além de coletar e processar dados, você gera apresentações de slides e planilhas e impressiona com análises e conclusões inteligentes.

Neste laboratório, você vai usar a API BigQuery do Google Cloud (como um serviço avançado do Apps Script) e os serviços integrados do Apps Script para os apps Planilhas e Apresentações.

Ele vai simular uma situação parecida com um caso de uso real. O uso do app vai apresentar recursos e APIs do Google Cloud. O objetivo é demonstrar o uso do Google Cloud e do Workspace para resolver problemas difíceis da organização ou dos clientes.

Conteúdo

• Como usar o Google Apps Script com vários serviços do Google
• Como usar o BigQuery para fazer uma análise de Big Data
• Como criar um arquivo nas Planilhas Google, preenchê-lo com dados e gerar um gráfico com as informações da planilha
• Como transferir gráficos e dados da planilha para slides separados nas Apresentações Google

Configuração

Antes de clicar no botão Start Lab

Leia estas instruções. Os laboratórios são cronometrados e não podem ser pausados. O timer é iniciado quando você clica em Começar o laboratório e mostra por quanto tempo os recursos do Google Cloud vão ficar disponíveis.

Este laboratório prático permite que você realize as atividades em um ambiente real de nuvem, não em uma simulação ou demonstração. Você vai receber novas credenciais temporárias para fazer login e acessar o Google Cloud durante o laboratório.

Confira os requisitos para concluir o laboratório:

• Acesso a um navegador de Internet padrão (recomendamos o Chrome).

Observação: para executar este laboratório, use o modo de navegação anônima ou uma janela anônima do navegador. Isso evita conflitos entre sua conta pessoal e a conta de estudante, o que poderia causar cobranças extras na sua conta pessoal.

• Tempo para concluir o laboratório---não se esqueça: depois de começar, não será possível pausar o laboratório.

Observação: não use seu projeto ou conta do Google Cloud neste laboratório para evitar cobranças extras na sua conta.

Introdução

Google Apps Script e BigQuery

O Google Apps Script é uma plataforma de desenvolvimento do Workspace 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 Workspace.

Assim como o Node.js, ele é uma plataforma para desenvolvimento com JavaScript do lado do servidor. Porém, a principal atividade do Apps Script é otimizar a integração com o Workspace 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 ou 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 Workspace 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: para saber mais sobre o Apps Script, consulte a documentação oficial, que inclui informações gerais com guias de início rápido, tutoriais e vídeos.

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

• como um serviço integrado;
• como um serviço avançado.

Os serviços integrados fornecem métodos de alto nível para acessar dados do Workspace e de produtos do Google, ou outros métodos utilitários. Já os serviços avançados são apenas wrappers simples de uma API REST do Workspace 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. Recomendamos que você priorize os serviços integrados sempre que possível, porque eles são mais robustos e fáceis de usar 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 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 é?

Observação: se você ainda não conhece o BigQuery, ele é um serviço do Google Cloud que você usa para realizar consultas simples (ou complexas) em volumes de dados muito grandes, na ordem de vários terabytes, e conseguir resultados em segundos, e não horas ou dias.

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

O BigQuery só está disponível como um serviço avançado do Apps Script. No entanto, os apps Planilhas e Apresentações têm serviços integrados e avançados do Apps Script para acessar recursos que não estão integrados e só estão disponíveis na API, por exemplo. Sempre que possível, use os serviços integrados (e não os avançados), porque eles oferecem construções de nível superior e chamadas de conveniência, o que simplifica o desenvolvimento.

Observação: se quiser fazer uma revisão antes de partir para o código, consulte o serviço Planilhas e o serviço Apresentações.

Tarefa 1: faça uma consulta no BigQuery e registre os resultados no app Planilhas

Este laboratório começa pela tarefa maior. Quando você terminar esta seção, já estará na metade do laboratório.

Veja o que você precisa fazer:

• 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

Criar um novo projeto do Apps Script

1. Crie um novo projeto do Apps Script em script.google.com. Para este laboratório, clique no link Criar Apps Script.

2. O editor de código do Apps Script será aberto:

3. Dê um nome ao projeto clicando no nome na parte de cima da página (neste caso, "Projeto sem título").

4. Na caixa de diálogo Renomear projeto, dê o nome que preferir (por exemplo: "BigQuery", "Sheets", "Demonstração das Apresentações" etc.) e clique em Renomear.

Ative o serviço avançado do BigQuery

Ative o serviço avançado do BigQuery para o novo projeto e a API BigQuery.

1. Clique no ícone Adicionar um serviço ao lado de Serviços.

2. Na caixa de diálogo Adicionar um serviço, selecione os serviços e as APIs aplicáveis.

3. Acesse o console do Cloud e selecione Menu de navegação > APIs e serviços > Biblioteca.

4. Digite ou cole API BigQuery na caixa de pesquisa e selecione a API BigQuery.

5. Clique em Ativar para ativar a API BigQuery, se necessário.

6. Volte ao seu projeto. A caixa de diálogo Adicionar um serviço ainda estará aberta.

7. Selecione API BigQuery e clique em Adicionar para fechar.

Insira e execute o código do seu aplicativo

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

1. Copie o código na caixa abaixo e cole no editor de código, substituindo qualquer conteúdo presente:

/** * 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 = '<YOUR_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/gsuitedevs/apps-script-samples/blob/master/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()); }

2. Salve o arquivo que você criou clicando no ícone Salvar projeto na barra de menus ou pressionando Ctrl + S.

3. Renomeie o arquivo clicando nos três pontos ao lado do nome e depois em Renomear.

4. Altere o nome do arquivo para bq-sheets-slides.gs e pressione Enter.

O que esse código faz? Você já sabe que ele consulta o BigQuery e registra os resultados em um novo documento do Planilhas Google, mas qual é essa consulta?

5. Confira o código da consulta na função 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).

Falta pouco. Você precisa ter um ID de projeto válido para definir a variável PROJECT_ID na parte de cima de bq-sheets-slides.gs.

6. Substitua <YOUR_PROJECT_ID> pelo ID do projeto, que aparece no painel à esquerda.

Confira um exemplo com um ID de projeto. O valor do seu PROJECT_ID será diferente.

Exemplo de código:

// 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 = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); Observação: neste snippet de código, a instrução if foi incluída para evitar que o aplicativo avance sem um ID de projeto. Observação: se o seletor de menu travar e parar de funcionar, recarregue a página.

7. Salve o arquivo e execute o código clicando na opção Executar na barra de menus.

8. Depois, clique em Revisar permissões.

Observação: se você receber um erro Exception: Service BigQuery API has not been enabled for your Apps., remova o serviço da API BigQuery e adicione-o novamente.

9. Na caixa de diálogo "Escolha uma conta no qwiklabs.net", clique no seu Username e em Allow.

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 seção "Coloque os dados dos resultados em uma apresentação de slides" deste laboratório. Ela vai pedir a permissão do usuário para criar e gerenciar arquivos do app Apresentações.

10. Uma pequena caixa de mensagem aparece na parte superior da tela quando a função é executada.

Essa caixa some quando a função é concluída. Se a caixa não aparecer na tela, é provável que a função já tenha sido executada.

11. Abra seu Google Drive e procure um novo arquivo das Planilhas Google chamado Most common words in all of Shakespeare's works ou o nome que você atribuiu à variável QUERY_NAME.

12. Quando você abrir a planilha, serão mostradas 10 linhas de palavras com as respectivas contagens totais classificadas em ordem decrescente.

Clique em Verificar meu progresso para conferir o objetivo. Faça uma consulta no BigQuery e registre os resultados no app Planilhas

Resumo

O que aconteceu? Você executou um código que consulta 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.

Sempre é possível testar a consulta no console do BigQuery antes de executá-la no Apps Script. A interface do usuário do BigQuery está disponível para desenvolvedores.

1. Acesse o console do Cloud e selecione Menu de navegação > BigQuery.

2. Clique em CONCLUÍDO na caixa de diálogo "Olá! Este é o BigQuery no console do Cloud".

O console do BigQuery vai abrir.

3. Insira seu código no "Editor de consultas" e clique em Executar:

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

Tarefa 2: crie um gráfico nas Planilhas Google

Volte ao editor de script. Até agora, você programou um aplicativo que consulta as obras de Shakespeare, depois classificou os resultados e os apresentou no Planilhas Google. No código, a função runQuery() se comunica com o BigQuery e envia os resultados para uma planilha. Agora você incluirá o código para criar um gráfico com esses dados. Nesta seção, você criará uma nova função chamada createColumnChart() que chama o método newChart() do Planilhas para colocar os dados em um gráfico.

A função createColumnChart() 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.

1. Crie o gráfico: adicione a função createColumnChart() ao arquivo bq-sheets-slides.gs logo após runQuery() (depois da última linha de código):

/** * 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); }

2. Retorne "spreadsheet": no código acima, createColumnChart() precisa do objeto "spreadsheet". Altere o app para retornar o objeto spreadsheet, que será transmitido para createColumnChart(). Após registrar a criação do documento do app Planilhas, retorne o objeto ao final de runQuery().

3. Substitua a última linha (que começa com Logger.log) pelo seguinte:

Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // Return the spreadsheet object for later use. return spreadsheet; }

4. Acione a função createBigQueryPresentation(): é uma boa ideia separar logicamente o BigQuery e os recursos de criação de gráficos. Crie a função createBigQueryPresentation() para acionar o app, chamando os dois elementos e createColumnChart(). O código que você adicionará é semelhante a este:

/** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); createColumnChart(spreadsheet); }

5. Insira a função createBigQueryPresentation() logo após este bloco de código:

// 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 = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup');

6. 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?

Se quiser 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()); createColumnChart(spreadsheet); }

Com as alterações acima, o conteúdo do arquivo bq-sheets-slides.js vai ficar parecido com este (exceto por PROJECT_ID):

/** * 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 = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * 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); } /** * 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/gsuitedevs/apps-script-samples/blob/master/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()); // 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); }

7. Salve o arquivo.

8. Na barra de menus, clique em runQuery e selecione createBigQueryPresentation no menu suspenso.

9. Depois, clique em Executar.

Depois que você executar essa função, outro arquivo das Planilhas Google será criado no seu Google Drive e terá um gráfico junto aos dados:

Tarefa 3: coloque os dados dos resultados em uma apresentação de slides

Na parte final deste laboratório, 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. Crie uma apresentação de slides: primeiro, crie uma nova apresentação e inclua um título e um subtítulo no slide padrão que é gerado com todas as apresentações novas. Os elementos relacionados à apresentação de slides estão na função createSlidePresentation(), que você adiciona em bq-sheets-slides.gs logo após o código da função createColumnChart():

/** * 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');

2. Adicione a 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 createSlidePresentation():

// 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])); } }

3. Importe o 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; }

4. Retorne o gráfico: 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. Você já ajustou a função runQuery() para retornar o objeto Spreadsheet, mas agora precisa fazer uma alteração semelhante em createColumnChart() para retornar o objeto de gráfico (EmbeddedChart). Para fazer isso, volte ao aplicativo e adicione uma última linha depois de createColumnChart():

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

5. Atualize createBigQueryPresentation(): como createColumnChart() retorna o gráfico, você precisa salvá-lo em uma variável e transmitir a planilha e o gráfico para createSlidePresentation(). Já que registrou o URL da planilha criada, registre também o URL da nova apresentação de slides. Substitua o seguinte bloco de código:

/** * 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); }

Por este código:

/** * 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()); }

Depois de todas as atualizações, o arquivo bq-sheets-slides.gs vai ficar assim (exceto o valor de PROJECT_ID):

bq-sheets-slides.gs - final version

// 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 = '<YOUR_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/gsuitedevs/apps-script-samples/blob/master/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()); }

6. Salve e execute createBigQueryPresentation() de novo. Antes da execução, você precisará aceitar mais um conjunto de permissões para visualizar e gerenciar arquivos do Apresentações Google.
7. Acesse "Meu Drive" e repare que, além da planilha criada, também há 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 utiliza os dois lados do Google Cloud, executando uma solicitação do BigQuery que consulta um conjunto de dados públicos. Além disso, você criou um novo arquivo nas Planilhas Google para armazenar os resultados, adicionou um gráfico com base nos dados coletados e criou um arquivo no app Apresentações 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. Agora você pode personalizar este laboratório para seus próprios projetos.

Termine a Quest

Este laboratório autoguiado faz parte das Quests Workspace: Integrations for Data e BigQuery Basics for Data Analysts. Uma Quest é uma série de laboratórios relacionados que formam um programa de aprendizado. Ao concluir uma Quest, você ganha um selo como reconhecimento da sua conquista. É possível publicar os selos e incluir um link para eles no seu currículo on-line ou nas redes sociais. Inscreva-se em qualquer Quest que tenha este laboratório para receber os créditos de conclusão na mesma hora. Consulte todas as Quests disponíveis no catálogo do Google Cloud Ensina.

Está procurando um laboratório com desafios práticos para demonstrar suas habilidades no BigQuery e validar seu conhecimento? Quando terminar a Quest, faça este laboratório com desafio para receber um selo digital exclusivo do Google Cloud.

Saiba mais

O código apresentado neste laboratório também está disponível no GitHub. Procuramos manter o laboratório sincronizado com esse repositório. Confira a seguir alguns recursos adicionais para você se aprofundar no que foi abordado neste laboratório e ainda conhecer outras formas de acessar as ferramentas para desenvolvedores do Google de maneira programática.

Documentação

• Site da documentação do Google Apps Script
• Serviço do Planilhas no Apps Script
• Serviço do Apresentações no Apps Script
• Serviço avançado do BigQuery no Apps Script

Vídeos relacionados e gerais

• Mais um segredo do Google (Apps): vídeo de introdução ao Apps Script (em inglês)
• Como acessar o Google Maps usando uma planilha: vídeo (em inglês)
• Mais conteúdo sobre o Google Apps Script: biblioteca de vídeos (em inglês)
• Launchpad Online: série de vídeos (em inglês)
• G Suite Dev Show: série de vídeos

Notícias e atualizações gerais e relacionadas

• Integração do BigQuery com o Google Drive (2016: link1 e link2)
• Blog do Google Developers
• Blog do Google Cloud Platform
• Big Data e machine learning no blog do Google Cloud
• Twitter do Google Developers (@GoogleDevs)
• Blog dos desenvolvedores do Workspace (em inglês)
• Twitter (em inglês) dos desenvolvedores do Workspace (@GSuiteDevs)
• Newsletter mensal dos desenvolvedores do Workspace
• Centro de aprendizagem do Google Workspace

Manual atualizado em 6 de março de 2023

Laboratório testado em 6 de março de 2023

Copyright 2024 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de produtos e empresas podem ser marcas registradas das respectivas empresas a que estão associados.