Fundamentos do Apps Script com as Planilhas Google #4: formatação de dados

1. Introdução

Esta é a quarta parte da playlist de codelabs sobre os fundamentos do Apps Script com as Planilhas Google.

Ao concluir este codelab, você vai aprender a formatar os dados da sua planilha no Apps Script e escrever funções para criar planilhas organizadas com dados formatados extraídos de uma API pública.

O que você vai aprender

• Como aplicar várias operações de formatação das Planilhas Google no Apps Script.
• Como transformar uma lista de objetos JSON e seus atributos em uma planilha organizada de dados com o Apps Script.

Antes de começar

Este é o quarto 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

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 formatação de dados".
2. Clique no título da planilha e mude de "Cópia de formatação de dados" para "Formatação de dados". Sua planilha precisa ficar assim, com algumas informações básicas sobre os três primeiros filmes de Star Wars:

3. Selecione Extensões > Apps Script para abrir o editor de script.
4. Clique no título do projeto do Apps Script e mude de "Projeto sem título" para "Formatação de dados". Clique em Renomear para salvar a mudança de título.

Com essa planilha e esse projeto, você já pode começar o codelab. Vá para a próxima seção para começar a aprender sobre formatação básica no Apps Script.

3. Criar um menu personalizado

É possível aplicar vários métodos básicos de formatação no Apps Script às suas Planilhas. Os exercícios a seguir demonstram algumas maneiras de formatar dados. Para ajudar a controlar suas ações de formatação, vamos criar um menu personalizado com os itens necessários. O processo de criação de menus personalizados foi descrito no codelab Trabalhar com dados, mas vamos resumir aqui novamente.

Implementação

Vamos criar um menu personalizado.

1. No editor do Apps Script, substitua o código no projeto de script pelo seguinte:

/**  * A special function that runs when the spreadsheet is opened  * or reloaded, used to add a custom menu to the spreadsheet.  */ function onOpen() {   // Get the spreadsheet's user-interface object.   var ui = SpreadsheetApp.getUi();    // Create and add a named menu and its items to the menu bar.   ui.createMenu('Quick formats')    .addItem('Format row header', 'formatRowHeader')    .addItem('Format column header', 'formatColumnHeader')    .addItem('Format dataset', 'formatDataset')    .addToUi(); } 

2. Salve o projeto de script.
3. No editor de scripts, selecione onOpen na lista de funções e clique em Executar. Isso executa onOpen() para recriar o menu da planilha, sem precisar recarregar o documento.

Revisão de código

Vamos analisar esse código para entender como ele funciona. Em onOpen(), a primeira linha usa o método getUi() para adquirir um objeto Ui que representa a interface do usuário da planilha ativa a que o script está vinculado.

As próximas linhas criam um menu (Quick formats), adicionam itens (Format row header, Format column header e Format dataset) e, por fim, adicionam o menu à interface da planilha. Isso é feito com os métodos createMenu(caption), addItem(caption, functionName) e addToUi(), respectivamente.

O método addItem(caption, functionName) cria uma conexão entre o rótulo do item de menu e uma função do Apps Script que é executada quando o item é selecionado. Por exemplo, selecionar o item de menu Format row header faz com que o app Planilhas tente executar a função formatRowHeader(), que ainda não existe.

Resultados

Na sua planilha, clique no menu Quick formats para ver os novos itens:

Clicar nesses itens causa um erro porque você não implementou as funções correspondentes. Vamos fazer isso agora.

4. Formatar uma linha de cabeçalho

Os conjuntos de dados em planilhas geralmente têm linhas de cabeçalho para identificar os dados em cada coluna. É recomendável formatar as linhas de cabeçalho para separá-las visualmente do restante dos dados na planilha.

No primeiro codelab, você criou uma macro para o cabeçalho e ajustou o código dela. Aqui, você vai formatar uma linha de cabeçalho do zero usando o Apps Script. A linha de cabeçalho que você vai criar coloca o texto em negrito, colore o plano de fundo de azul-esverdeado escuro, colore o texto de branco e adiciona algumas linhas de borda sólidas.

Implementação

Para implementar a operação de formatação, use os mesmos métodos do serviço de planilhas que você usou antes, mas agora também use alguns dos métodos de formatação do serviço. Siga estas etapas:

1. No editor do Apps Script, adicione a seguinte função ao final do projeto de script:

/**  * Formats top row of sheet using our header row style.  */ function formatRowHeader() {   // Get the current active sheet and the top row's range.   var sheet = SpreadsheetApp.getActiveSheet();   var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());     // Apply each format to the top row: bold white text,   // blue-green background, and a solid black border   // around the cells.   headerRange     .setFontWeight('bold')     .setFontColor('#ffffff')     .setBackground('#007272')     .setBorder(       true, true, true, true, null, null,       null,       SpreadsheetApp.BorderStyle.SOLID_MEDIUM);  } 

2. Salve o projeto de script.

Revisão de código

Como muitas tarefas de formatação, o código do Apps Script para implementar isso é simples. As duas primeiras linhas usam métodos que você já viu para receber uma referência à planilha ativa atual (sheet) e à linha superior da planilha (headerRange)). O método Sheet.getRange(row, column, numRows, numColumns) especifica a linha superior, incluindo apenas as colunas com dados. O método Sheet.getLastColumn() retorna o índice da última coluna que contém dados na planilha. No nosso exemplo, é a coluna E (url).

O restante do código simplesmente chama vários métodos Range para aplicar as opções de formatação a todas as células em headerRange. Para facilitar a leitura do código, usamos o encadeamento de métodos para chamar cada método de formatação um após o outro:

• Range.setFontWeight(fontWeight) é usado para definir a espessura da fonte como negrito.
• Range.setFontColor(color) é usado para definir a cor da fonte como branco.
• Range.setBackground(color) é usado para definir a cor do plano de fundo como um azul-esverdeado escuro.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) coloca uma borda preta sólida ao redor das células do intervalo.

O último método tem vários parâmetros. Vamos revisar o que cada um faz. Os quatro primeiros parâmetros aqui (todos definidos como true) informam ao Apps Script que a borda deve ser adicionada acima, abaixo e à esquerda e à direita do intervalo. O quinto e o sexto parâmetros (null e null) direcionam o Apps Script para evitar a mudança de linhas de borda no intervalo selecionado. O sétimo parâmetro (null) indica que a cor da borda deve ser preta por padrão. Por fim, o último parâmetro especifica o tipo de estilo de borda a ser usado, extraído das opções fornecidas por SpreadsheetApp.BorderStyle.

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 Formatos rápidos > Formatar cabeçalho da linha.

Os resultados vão ter a aparência abaixo:

Agora você automatizou uma tarefa de formatação. A próxima seção aplica a mesma técnica para criar um estilo de formato diferente para cabeçalhos de coluna.

5. Formatar um cabeçalho de coluna

Se você consegue criar um cabeçalho de linha personalizado, também consegue criar um cabeçalho de coluna. Os cabeçalhos de coluna aumentam a legibilidade de determinados conjuntos de dados. Por exemplo, a coluna títulos nesta planilha pode ser aprimorada com as seguintes opções de formatação:

• Deixar o texto em negrito
• Deixar o texto em itálico
• Como adicionar bordas de células
• Inserir hiperlinks usando o conteúdo da coluna url. Depois de adicionar esses hiperlinks, você pode remover a coluna url para limpar a planilha.

Em seguida, você vai implementar uma função formatColumnHeader() para aplicar essas mudanças à primeira coluna da planilha. Para facilitar um pouco a leitura do código, você também vai implementar duas funções auxiliares.

Implementação

Como antes, você precisa adicionar uma função para automatizar a formatação do cabeçalho da coluna. Siga estas etapas:

1. No editor do Apps Script, adicione a seguinte função formatColumnHeader() ao final do projeto de script:

/**  * Formats the column header of the active sheet.  */  function formatColumnHeader() {     var sheet = SpreadsheetApp.getActiveSheet();      // Get total number of rows in data range, not including   // the header row.   var numRows = sheet.getDataRange().getLastRow() - 1;      // Get the range of the column header.   var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);      // Apply text formatting and add borders.   columnHeaderRange     .setFontWeight('bold')     .setFontStyle('italic')     .setBorder(       true, true, true, true, null, null,       null,       SpreadsheetApp.BorderStyle.SOLID_MEDIUM);     // Call helper method to hyperlink the first column contents   // to the url column contents.   hyperlinkColumnHeaders_(columnHeaderRange, numRows);  } 

2. Adicione as seguintes funções auxiliares ao final do projeto de script, depois da função formatColumnHeader():

/**  * Helper function that hyperlinks the column header with the  * 'url' column contents. The function then removes the column.  *  * @param {object} headerRange The range of the column header  *   to update.  * @param {number} numRows The size of the column header.  */ function hyperlinkColumnHeaders_(headerRange, numRows) {   // Get header and url column indices.   var headerColIndex = 1;    var urlColIndex = columnIndexOf_('url');        // Exit if the url column is missing.   if(urlColIndex == -1)     return;       // Get header and url cell values.   var urlRange =     headerRange.offset(0, urlColIndex - headerColIndex);   var headerValues = headerRange.getValues();   var urlValues = urlRange.getValues();      // Updates header values to the hyperlinked header values.   for(var row = 0; row < numRows; row++){     headerValues[row][0] = '=HYPERLINK("' + urlValues[row]       + '","' + headerValues[row] + '")';   }   headerRange.setValues(headerValues);      // Delete the url column to clean up the sheet.   SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex); }  /**  * Helper function that goes through the headers of all columns  * and returns the index of the column with the specified name  * in row 1. If a column with that name does not exist,  * this function returns -1. If multiple columns have the same  * name in row 1, the index of the first one discovered is  * returned.  *   * @param {string} colName The name to find in the column  *   headers.   * @return The index of that column in the active sheet,  *   or -1 if the name isn't found.  */  function columnIndexOf_(colName) {   // Get the current column names.   var sheet = SpreadsheetApp.getActiveSheet();   var columnHeaders =     sheet.getRange(1, 1, 1, sheet.getLastColumn());   var columnNames = columnHeaders.getValues();      // Loops through every column and returns the column index   // if the row 1 value of that column matches colName.   for(var col = 1; col <= columnNames[0].length; col++)   {     if(columnNames[0][col-1] === colName)       return col;    }    // Returns -1 if a column named colName does not exist.    return -1;  } 

3. Salve o projeto de script.

Revisão de código

Vamos analisar o código em cada uma dessas três funções separadamente:

formatColumnHeader()

Como você já deve ter percebido, as primeiras linhas dessa função definem variáveis que fazem referência à planilha e ao intervalo de que precisamos:

• A planilha ativa é armazenada em sheet.
• O número de linhas no cabeçalho da coluna é calculado e salvo em numRows. Aqui, o código subtrai um para que a contagem de linhas não inclua o cabeçalho da coluna: title.
• O intervalo que abrange o cabeçalho da coluna é armazenado em columnHeaderRange.

Em seguida, o código aplica as bordas e o negrito ao intervalo do cabeçalho da coluna, assim como em formatRowHeader(). Aqui, Range.setFontStyle(fontStyle) também é usado para colocar o texto em itálico.

Adicionar os hiperlinks à coluna de cabeçalho é mais complexo, então formatColumnHeader() chama hyperlinkColumnHeaders_(headerRange, numRows) para cuidar da tarefa. Isso ajuda a manter o código organizado e legível.

hyperlinkColumnHeaders_(headerRange, numRows)

Essa função auxiliar primeiro identifica os índices de coluna do cabeçalho (presumido como índice 1) e da coluna url. Ele chama columnIndexOf_('url') para receber o índice da coluna de URL. Se uma coluna url não for encontrada, o método será encerrado sem modificar nenhum dado.

A função recebe um novo intervalo (urlRange) que abrange os URLs correspondentes às linhas da coluna de cabeçalho. Isso é feito com o método Range.offset(rowOffset, columnOffset), que garante que os dois intervalos tenham o mesmo tamanho. Os valores nas colunas headerColumn e url são recuperados (headerValues e urlValues).

Em seguida, a função gera repetições em cada valor de célula de cabeçalho de coluna e o substitui por uma fórmula do Planilhas =HYPERLINK() criada com o cabeçalho e o conteúdo da coluna url. Os valores de cabeçalho modificados são inseridos na planilha usando Range.setValues(values).

Por fim, para manter a planilha limpa e eliminar informações redundantes, Sheet.deleteColumn(columnPosition) é chamado para remover a coluna url.

columnIndexOf_(colName)

Essa função auxiliar é apenas uma função utilitária simples que pesquisa um nome específico na primeira linha da planilha. As três primeiras linhas usam métodos que você já conhece para receber uma lista de nomes de cabeçalho de coluna da linha 1 da planilha. Esses nomes são armazenados na variável columnNames.

Em seguida, a função analisa cada nome em ordem. Se encontrar uma correspondência com o nome pesquisado, ele vai parar e retornar o índice da coluna. Se ele chegar ao final da lista sem encontrar o nome, vai retornar -1 para sinalizar que o nome não foi encontrado.

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 Formatos rápidos > Formatar cabeçalho da coluna.

Os resultados vão ter a aparência abaixo:

Agora você automatizou outra tarefa de formatação. Com os cabeçalhos de coluna e linha formatados, a próxima seção mostra como formatar os dados.

6. Formatar o conjunto de dados

Agora que você tem cabeçalhos, vamos criar uma função que formata o restante dos dados na sua planilha. Vamos usar as seguintes opções de formatação:

• Cores de fundo alternadas das linhas (conhecidas como faixas)
• Como mudar formatos de data
• Aplicar bordas
• Dimensionar todas as colunas e linhas automaticamente

Agora, você vai criar uma função formatDataset() e um método auxiliar extra para aplicar esses formatos aos dados da planilha.

Implementação

Como antes, adicione uma função para automatizar a formatação dos dados. Siga estas etapas:

1. No editor do Apps Script, adicione a seguinte função formatDataset() ao final do projeto de script:

/**  * Formats the sheet data, excluding the header row and column.  * Applies the border and banding, formats the 'release_date'  * column, and autosizes the columns and rows.  */ function formatDataset() {   // Get the active sheet and data range.   var sheet = SpreadsheetApp.getActiveSheet();    var fullDataRange = sheet.getDataRange();    // Apply row banding to the data, excluding the header   // row and column. Only apply the banding if the range   // doesn't already have banding set.   var noHeadersRange = fullDataRange.offset(     1, 1,     fullDataRange.getNumRows() - 1,     fullDataRange.getNumColumns() - 1);    if (! noHeadersRange.getBandings()[0]) {     // The range doesn't already have banding, so it's     // safe to apply it.     noHeadersRange.applyRowBanding(       SpreadsheetApp.BandingTheme.LIGHT_GREY,       false, false);   }    // Call a helper function to apply date formatting   // to the column labeled 'release_date'.   formatDates_( columnIndexOf_('release_date') );      // Set a border around all the data, and resize the   // columns and rows to fit.   fullDataRange.setBorder(     true, true, true, true, null, null,     null,     SpreadsheetApp.BorderStyle.SOLID_MEDIUM);    sheet.autoResizeColumns(1, fullDataRange.getNumColumns());   sheet.autoResizeRows(1, fullDataRange.getNumRows()); } 

2. Adicione a seguinte função auxiliar ao final do projeto de script, depois da função formatDataset():

/**   * Helper method that applies a  * "Month Day, Year (Day of Week)" date format to the  * indicated column in the active sheet.   *  * @param {number} colIndex The index of the column  *   to format.  */  function formatDates_(colIndex) {   // Exit if the given column index is -1, indicating   // the column to format isn't present in the sheet.   if (colIndex < 0)     return;     // Set the date format for the date column, excluding   // the header row.   var sheet = SpreadsheetApp.getActiveSheet();   sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)     .setNumberFormat("mmmm dd, yyyy (dddd)"); } 

3. Salve o projeto de script.

Revisão de código

Vamos analisar o código em cada uma dessas duas funções separadamente:

formatDataset()

Essa função segue um padrão semelhante às funções de formato anteriores que você já implementou. Primeiro, ele recebe variáveis para armazenar referências à planilha ativa (sheet) e ao intervalo de dados (fullDataRange).

Em segundo lugar, ele usa o método Range.offset(rowOffset, columnOffset, numRows, numColumns) para criar um intervalo (noHeadersRange) que abrange todos os dados na planilha, exceto os cabeçalhos de coluna e linha. Em seguida, o código verifica se esse novo intervalo tem faixas (usando Range.getBandings()). Isso é necessário porque o Apps Script gera um erro se você tentar aplicar novas faixas onde já existe uma. Se não houver bandas, a função adicionará uma banda cinza-claro usando Range.applyRowBanding(bandingTheme, showHeader, showFooter). Caso contrário, a função continua.

A próxima etapa chama a função auxiliar formatDates_(colIndex) para formatar as datas na coluna "release_date" (descrita abaixo). A coluna é especificada usando a função auxiliar columnIndexOf_(colName) implementada anteriormente.

Por fim, a formatação é concluída com a adição de outra borda (como antes) e o redimensionamento automático de cada coluna e linha para ajustar os dados que elas contêm usando os métodos Sheet.autoResizeColumns(columnPosition) e Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

Essa função auxiliar aplica um formato de data específico a uma coluna usando o índice fornecido. Especificamente, ele formata valores de data como "Mês Dia, Ano (Dia da semana)".

Primeiro, a função verifica se o índice de coluna fornecido é válido (ou seja, 0 ou maior). Caso contrário, ele retorna sem fazer nada. Essa verificação evita erros que podem ocorrer se, por exemplo, a planilha não tiver uma coluna "release_date".

Depois que o índice da coluna é validado, a função recebe o intervalo que abrange essa coluna (excluindo a linha de cabeçalho) e usa Range.setNumberFormat(numberFormat) para aplicar a formatação.

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 Formatos rápidos > Formatar conjunto de dados.

Os resultados vão ter a aparência abaixo:

Você automatizou mais uma tarefa de formatação. Agora que você tem esses comandos de formatação disponíveis, vamos adicionar mais dados para aplicar a eles.

7. Buscar e formatar dados da API

Até agora neste codelab, você aprendeu a usar o Apps Script como uma maneira alternativa de formatar sua planilha. Em seguida, você vai escrever um código que extrai dados de uma API pública, insere na sua planilha e formata para que seja legível.

No último codelab, você aprendeu a extrair dados de uma API. Você vai usar as mesmas técnicas aqui. Neste exercício, vamos usar a API pública Star Wars (SWAPI) para preencher sua planilha. Especificamente, você vai usar a API para receber informações sobre os principais personagens que aparecem nos três filmes originais de Star Wars.

Seu código vai chamar a API para receber uma grande quantidade de dados JSON, analisar a resposta, colocar os dados em uma nova planilha e formatar a planilha.

Implementação

Nesta seção, você vai adicionar mais itens ao menu. Cada item de menu chama um script wrapper que transmite variáveis específicas do item para a função principal (createResourceSheet_()). Você vai implementar essa função e três outras funções auxiliares. Como antes, as funções auxiliares ajudam a isolar partes logicamente compartimentadas da tarefa e a manter o código legível.

Faça o seguinte:

1. No editor do Apps Script, atualize a função onOpen() no projeto de script para que ela fique assim:

/**  * A special function that runs when the spreadsheet is opened  * or reloaded, used to add a custom menu to the spreadsheet.  */ function onOpen() {   // Get the Ui object.   var ui = SpreadsheetApp.getUi();    // Create and add a named menu and its items to the menu bar.   ui.createMenu('Quick formats')     .addItem('Format row header', 'formatRowHeader')     .addItem('Format column header', 'formatColumnHeader')     .addItem('Format dataset', 'formatDataset')     .addSeparator()     .addSubMenu(ui.createMenu('Create character sheet')                 .addItem('Episode IV', 'createPeopleSheetIV')                 .addItem('Episode V', 'createPeopleSheetV')                 .addItem('Episode VI', 'createPeopleSheetVI')                 )     .addToUi(); } 

2. Salve o projeto de script.
3. No editor de scripts, selecione onOpen na lista de funções e clique em Executar. Isso executa onOpen() para recriar o menu da planilha com as novas opções adicionadas.
4. Para criar um arquivo do Apps Script, ao lado de Arquivos, clique em Adicionar um arquivo > Script.
5. Nomeie o novo script como "API" e pressione "Enter". O Apps Script adiciona automaticamente uma extensão .gs ao nome do arquivo de script.
6. Substitua o código no novo arquivo API.gs pelo seguinte:

/**  * Wrapper function that passes arguments to create a  * resource sheet describing the characters from Episode IV.  */ function createPeopleSheetIV() {   createResourceSheet_('characters', 1, "IV"); }  /**  * Wrapper function that passes arguments to create a  * resource sheet describing the characters from Episode V.  */ function createPeopleSheetV() {   createResourceSheet_('characters', 2, "V"); }  /**  * Wrapper function that passes arguments to create a  * resource sheet describing the characters from Episode VI.  */ function createPeopleSheetVI() {   createResourceSheet_('characters', 3, "VI"); }  /**   * Creates a formatted sheet filled with user-specified  * information from the Star Wars API. If the sheet with  * this data exists, the sheet is overwritten with the API  * information.  *  * @param {string} resourceType The type of resource.  * @param {number} idNumber The identification number of the film.  * @param {number} episodeNumber The Star Wars film episode number.  *   This is only used in the sheet name.  */ function createResourceSheet_(     resourceType, idNumber, episodeNumber) {       // Fetch the basic film data from the API.    var filmData = fetchApiResourceObject_(       "https://swapi.dev/api/films/" + idNumber);    // Extract the API URLs for each resource so the code can   // call the API to get more data about each individually.   var resourceUrls = filmData[resourceType];      // Fetch each resource from the API individually and push   // them into a new object list.   var resourceDataList = [];    for(var i = 0; i < resourceUrls.length; i++){     resourceDataList.push(       fetchApiResourceObject_(resourceUrls[i])     );    }       // Get the keys used to reference each part of data within   // the resources. The keys are assumed to be identical for   // each object since they're all the same resource type.   var resourceObjectKeys = Object.keys(resourceDataList[0]);      // Create the sheet with the appropriate name. It   // automatically becomes the active sheet when it's created.   var resourceSheet = createNewSheet_(       "Episode " + episodeNumber + " " + resourceType);      // Add the API data to the new sheet, using each object   // key as a column header.    fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);      // Format the new sheet using the same styles the   // 'Quick Formats' menu items apply. These methods all   // act on the active sheet, which is the one just created.   formatRowHeader();   formatColumnHeader();      formatDataset();  } 

6. Adicione as seguintes funções auxiliares ao final do arquivo de projeto de script API.gs:

/**   * Helper function that retrieves a JSON object containing a  * response from a public API.  *  * @param {string} url The URL of the API object being fetched.  * @return {object} resourceObject The JSON object fetched  *   from the URL request to the API.  */ function fetchApiResourceObject_(url) {   // Make request to API and get response.   var response =     UrlFetchApp.fetch(url, {'muteHttpExceptions': true});      // Parse and return the response as a JSON object.   var json = response.getContentText();   var responseObject = JSON.parse(json);    return responseObject;  }  /**   * Helper function that creates a sheet or returns an existing  * sheet with the same name.  *  * @param {string} name The name of the sheet.  * @return {object} The created or existing sheet  *   of the same name. This sheet becomes active.  */  function createNewSheet_(name) {   var ss = SpreadsheetApp.getActiveSpreadsheet();      // Returns an existing sheet if it has the specified   // name. Activates the sheet before returning.   var sheet = ss.getSheetByName(name);   if (sheet) {     return sheet.activate();   }      // Otherwise it makes a sheet, set its name, and returns it.   // New sheets created this way automatically become the active   // sheet.   sheet = ss.insertSheet(name);    return sheet;  }  /**   * Helper function that adds API data to the sheet.  * Each object key is used as a column header in the new sheet.  *  * @param {object} resourceSheet The sheet object being modified.  * @param {object} objectKeys The list of keys for the resources.  * @param {object} resourceDataList The list of API  *   resource objects containing data to add to the sheet.  */ function fillSheetWithData_(     resourceSheet, objectKeys, resourceDataList) {   // Set the dimensions of the data range being added to the sheet.   var numRows = resourceDataList.length;   var numColumns = objectKeys.length;      // Get the resource range and associated values array. Add an   // extra row for the column headers.   var resourceRange =     resourceSheet.getRange(1, 1, numRows + 1, numColumns);   var resourceValues = resourceRange.getValues();       // Loop over each key value and resource, extracting data to   // place in the 2D resourceValues array.   for (var column = 0; column < numColumns; column++) {      // Set the column header.     var columnHeader = objectKeys[column];     resourceValues[0][column] = columnHeader;          // Read and set each row in this column.     for (var row = 1; row < numRows + 1; row++) {       var resource = resourceDataList[row - 1];       var value = resource[columnHeader];       resourceValues[row][column] = value;     }   }      // Remove any existing data in the sheet and set the new values.   resourceSheet.clear()   resourceRange.setValues(resourceValues); } 

7. Salve o projeto de script.

Revisão de código

Você acabou de adicionar muito código. Vamos analisar cada função individualmente para entender como elas funcionam:

onOpen()

Aqui, você adicionou alguns itens ao menu Quick formats. Você definiu uma linha separadora e usou o método Menu.addSubMenu(menu) para criar uma estrutura de menu aninhada com três novos itens. Os novos itens são adicionados com o método Menu.addItem(caption, functionName).

Funções wrapper

Os itens de menu adicionados fazem algo semelhante: eles tentam criar uma planilha com dados extraídos da SWAPI. A única diferença é que cada um se concentra em um filme diferente.

Seria conveniente escrever uma única função para criar a página e fazer com que ela aceite um parâmetro para determinar qual filme usar. No entanto, o método Menu.addItem(caption, functionName) não permite transmitir parâmetros quando chamado pelo menu. Então, como evitar escrever o mesmo código três vezes?

A resposta é funções de wrapper. São funções leves que você pode chamar e que imediatamente chamam outra função com parâmetros específicos definidos.

Aqui, o código usa três funções de wrapper: createPeopleSheetIV(), createPeopleSheetV() e createPeopleSheetVI(). Os itens do menu estão vinculados a essas funções. Quando um item de menu é clicado, a função de wrapper é executada e chama imediatamente a função principal do builder de planilha createResourceSheet_(resourceType, idNumber, episodeNumber), transmitindo os parâmetros adequados para o item de menu. Nesse caso, significa pedir à função de criação de planilhas para criar uma planilha preenchida com dados dos principais personagens de um dos filmes de Star Wars.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Esta é a principal função de criação de planilhas para este exercício. Com a ajuda de algumas funções auxiliares, ele recebe os dados da API, os analisa, cria uma planilha, grava os dados da API nela e a formata usando as funções criadas nas seções anteriores. Vamos revisar os detalhes:

Primeiro, a função usa fetchApiResourceObject_(url) para fazer uma solicitação à API e recuperar informações básicas sobre filmes. A resposta da API inclui uma coleção de URLs que o código pode usar para receber mais detalhes sobre pessoas específicas (conhecidas aqui como recursos) dos filmes. O código coleta tudo na matriz resourceUrls.

Em seguida, o código usa fetchApiResourceObject_(url) repetidamente para chamar a API em todos os URLs de recursos em resourceUrls. Os resultados são armazenados na matriz resourceDataList. Cada elemento dessa matriz é um objeto que descreve um personagem diferente do filme.

Os objetos de dados de recursos têm várias chaves comuns que mapeiam informações sobre esse personagem. Por exemplo, a chave "name" mapeia para o nome do personagem no filme. Presumimos que as chaves de cada objeto de dados de recurso são idênticas, já que devem usar estruturas de objeto comuns. A lista de chaves será necessária mais tarde. Por isso, o código armazena a lista em resourceObjectKeys usando o método Object.keys() do JavaScript.

Em seguida, a função builder chama a função auxiliar createNewSheet_(name) para criar a planilha em que os novos dados serão colocados. Chamar essa função auxiliar também ativa a nova planilha.

Depois que a planilha é criada, a função auxiliar fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) é chamada para adicionar todos os dados da API à planilha.

Por fim, todas as funções de formatação criadas anteriormente são chamadas para aplicar as mesmas regras aos novos dados. Como a nova planilha é a ativa, o código pode reutilizar essas funções sem modificação.

fetchApiResourceObject_(url)

Essa função auxiliar é semelhante à função fetchBookData_(ISBN) usada no codelab anterior Como trabalhar com dados. Ele usa o URL fornecido e o método UrlFetchApp.fetch(url, params) para receber uma resposta. Em seguida, a resposta é analisada em um objeto JSON usando os métodos HTTPResponse.getContextText() e JSON.parse(json) do JavaScript. O objeto JSON resultante é retornado.

createNewSheet_(name)

Essa função auxiliar é bem simples. Primeiro, ele verifica se uma página com o nome especificado existe na planilha. Se isso acontecer, a função vai ativar e retornar a planilha.

Se a planilha não existir, a função vai criá-la com Spreadsheet.insertSheet(sheetName), ativá-la e retornar a nova planilha.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Essa função auxiliar é responsável por preencher a nova planilha com dados da API. Ele usa como parâmetros a nova planilha, a lista de chaves de objeto e a lista de objetos de recursos da API. Cada chave de objeto representa uma coluna na nova planilha, e cada objeto de recurso representa uma linha.

Primeiro, a função calcula o número de linhas e colunas necessárias para apresentar os novos dados da API. Esse é o tamanho da lista de recursos e chaves, respectivamente. Em seguida, a função define um intervalo de saída (resourceRange) em que os dados serão colocados, adicionando uma linha extra para conter os cabeçalhos das colunas. A variável resourceValues contém uma matriz de valores 2D extraída de resourceRange.

Em seguida, a função faz um loop em cada chave de objeto na lista objectKeys. A chave é definida como o cabeçalho da coluna, e um segundo loop passa por todos os objetos de recurso. Para cada par (linha, coluna), as informações da API correspondentes são copiadas para o elemento resourceValues[row][column].

Depois que resourceValues é preenchido, a planilha de destino é limpa usando Sheet.clear() caso contenha dados de cliques anteriores em itens de menu. Por fim, os novos valores são gravados na planilha.

Resultados

Para conferir os resultados do seu trabalho, 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 Formatos rápidos > Criar ficha de personagem > Episódio IV.

Os resultados vão ter a aparência abaixo:

Agora você escreveu um código para importar dados para as Planilhas e formatá-los automaticamente.

8. Conclusão

Parabéns por concluir este codelab. Você já viu algumas das opções de formatação do Planilhas Google que podem ser incluídas nos seus projetos do Apps Script e criou um aplicativo impressionante que importa e formata um grande conjunto de dados de API.

O que você aprendeu

• Como aplicar várias operações de formatação das Planilhas com o Apps Script.
• Como criar submenus com a função onOpen().
• Como formatar uma lista buscada de objetos JSON em uma nova planilha de dados com o Apps Script.

A seguir

O próximo codelab desta playlist mostra como usar o Apps Script para visualizar dados em um gráfico e exportar gráficos para apresentações do Google Slides.

Confira o próximo codelab em Criar gráficos e apresentar dados no Apresentações.