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

1. Introdução

Esta é a quarta parte da playlist de fundamentos do Apps Script com o Planilhas Google.

Com este codelab, você aprenderá a formatar seus dados de planilhas no Apps Script e a criar funções para criar planilhas organizadas cheias de dados formatados recuperados de uma API pública.

O que você vai aprender

• Aplicar várias operações de formatação do Planilhas Google no Apps Script.
• Veja como transformar uma lista de objetos JSON e os respectivos 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 o Planilhas Google". Antes de iniciar este codelab, conclua os codelabs anteriores:

1. Macros e funções personalizadas
2. Planilhas, Planilhas e intervalos
3. Como trabalhar com dados

Pré-requisitos

• Conhecimentos dos tópicos básicos do Apps Script explorados nos codelabs anteriores desta playlist.
• Ter noções básicas sobre o Editor do Apps Script
• Ter noções básicas sobre o Planilhas Google
• Capacidade de ler a notação A1 no Planilhas
• Ter noções básicas sobre o JavaScript e a classe String dele

2. Configurar

Para 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 clique em Fazer uma cópia. A nova planilha é colocada na sua pasta do Google Drive e denominada "quot;Cópia de formatação de dados".
2. Clique no título da planilha e altere-o de "Cópia da formatação de dados" para "formatação de dados". Sua planilha terá esta aparência, 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 altere-o de "Projeto sem título" para "Formatação de dados" Clique em Renomear para salvar a alteração.

Com esta planilha e projeto, você está pronto para iniciar o codelab. Vá para a próxima seção e comece a aprender sobre a formatação básica no Apps Script.

3. Criar um menu personalizado

Você pode aplicar vários métodos básicos de formatação no Apps Script ao 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 de que você precisará. O processo de criação de menus personalizados foi descrito no codelab Como trabalhar com dados, mas vamos resumi-lo aqui novamente.

Implementação

Vamos criar um menu personalizado.

1. No editor do Apps Script, substitua o código no seu 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 seu projeto de script.
3. No editor de script, selecione onOpen na lista de funções e clique em Executar. Isso executa onOpen() para recriar o menu da planilha. Assim, você não precisa atualizar a planilha.

Revisão de código

Vamos analisar esse código para entender como ele funciona. No 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 este script está vinculado.

As próximas linhas criam um menu (Quick formats), adicionam itens (Format row header, Format column header e Format dataset) ao menu e adicionam o menu à interface da planilha. Isso é feito usando 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 de menu é selecionado. Por exemplo, selecionar o item de menu Format row header faz com que o Planilhas tente executar a função formatRowHeader() (que ainda não existe).

Resultados

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

Clicar nesses itens causa um erro, já que você não implementou as funções correspondentes. Portanto, vamos fazer isso a seguir.

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. Recomendamos formatar as linhas de cabeçalho para separá-las visualmente dos outros dados da planilha.

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

Implementação

Para implementar a operação de formatação, você usará os mesmos métodos do serviço de planilhas usados anteriormente, mas agora também usará 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 fim do projeto do 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 seu projeto de script.

Revisão de código

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

Observação: presumimos que a linha do cabeçalho que queremos formatar está sempre na linha 1, que nossos dados começam na A1 e que ela se estende por algumas colunas contíguas. Isso é bom para este exercício, mas, em geral, é recomendável evitar essas suposições, permitindo que os usuários especifiquem quais células serão formatadas (caso a estrutura da planilha seja diferente).

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

• Range.setFontWeight(fontWeight) é usado para definir o peso da fonte como negrito.
• Range.setFontColor(color) é usado para definir a cor da fonte como branca.
• Range.setBackground(color) é usado para definir a cor do plano de fundo como um azul-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, portanto vamos analisar o que cada um está fazendo. Os primeiros quatro parâmetros aqui (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 mudanças nas linhas de borda no intervalo selecionado. O sétimo parâmetro (null) indica que a cor da borda deve ser preta. Por fim, o último parâmetro especifica o tipo de estilo de borda a ser usado, retirado das opções fornecidas por SpreadsheetApp.BorderStyle.

Resultados

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

1. Salve seu projeto de script no editor do Apps Script, caso ainda não tenha feito isso.
2. Clique no item de menu Formatos rápidos &cabeçalho de linha de formato.

Observação:se você estiver usando uma conta do gmail.com, poderá ver a caixa de diálogo ""Este app não foi verificado" ao usar o script pela primeira vez. O Google usa isso para avisar os usuários que talvez estejam usando o código de autores desconhecidos ou não confiáveis. Se você vir essa caixa de diálogo, poderá prosseguir, já que você é o autor do script. Siga as instruções na tela para continuar autorizando o script:

1. Na caixa de diálogo do app não verificado, clique em Avançado.
2. Clique em Manipulação de dados e menus personalizados (não seguros).
3. Clique em Allow.

Durante este codelab, você vai receber várias solicitações de permissão. Leia mais sobre esse processo em Autorização para serviços do Google.

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

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 colunas.

5. Formatar um cabeçalho de coluna

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

• Aplicar negrito ao texto
• Como iterar o texto
• Adicionar bordas de células
• Inserir hiperlinks com o conteúdo da coluna url. Depois de adicionar esses hiperlinks, remova a coluna url para ajudar a limpar a página.

Em seguida, implemente uma função formatColumnHeader() para aplicar essas alterações à primeira coluna da página. Para ajudar a facilitar a leitura do código, você também implementará duas funções auxiliares.

Implementação

Como antes, é preciso 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 do 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 seu projeto de script.

Revisão de código

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

formatColumnHeader()

Como é de se esperar, as primeiras linhas dessa função definem variáveis que fazem referência à planilha e ao intervalo em que temos interesse:

• A planilha ativa é armazenada em sheet.
• O número de linhas no cabeçalho da coluna é calculado e salvo no 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 cobre o cabeçalho da coluna é armazenado em columnHeaderRange.

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

Adicionar os hiperlinks à coluna do cabeçalho é mais complexo. 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 (supondo que seja o índice 1) e a coluna url. Ele chama columnIndexOf_('url') para receber o índice da coluna do URL. Se uma coluna url não for encontrada, o método será encerrado sem modificar os dados.

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 faz um loop sobre cada valor de célula de cabeçalho da coluna e a substitui por uma fórmula do Planilhas Google =HYPERLINK() criada com o conteúdo do cabeçalho e da coluna url. Os valores modificados do cabeçalho são inseridos na planilha usando Range.setValues(values).

Por fim, para ajudar a manter a página limpa e eliminar informações redundantes, o método 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 página. As três primeiras linhas usam métodos que você já viu para ver uma lista dos nomes dos cabeçalhos de colunas da linha 1 da planilha. Esses nomes são armazenados na variável columnNames.

Em seguida, a função revisa cada nome na ordem. Se encontrar um que corresponda ao nome que está sendo pesquisado, ele vai parar e retornar o índice da coluna. Se chegar ao fim da lista, sem encontrar o nome, 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. Salve seu projeto de script no editor do Apps Script, caso ainda não tenha feito isso.
2. Clique no item de menu Formatos rápidos &cabeçalho de coluna Formatar.

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

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 seu conjunto de dados

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

• Alternar cores de plano de fundo das linhas (conhecida como bandagem)
• Como alterar formatos de data
• Como aplicar bordas
• Como dimensionar automaticamente todas as colunas e linhas

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

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 do 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 seu 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 as variáveis para manter as referências à planilha ativa (página) e ao intervalo de dados (fullDataRange).

Em seguida, ele usa o método Range.offset(rowOffset, columnOffset, numRows, numColumns) para criar um intervalo (noHeadersRange) que abrange todos os dados na página, exceto os cabeçalhos de linha e coluna. Em seguida, o código verifica se esse novo intervalo tem faixas existentes (usando Range.getBandings()). Isso é necessário porque o Apps Script gerará um erro se você tentar aplicar uma nova faixa onde já existe uma. Se a pulseira não existir, a função adicionará uma faixa cinza clara usando Range.applyRowBanding(bandingTheme, showHeader, showFooter). Caso contrário, a função prosseguirá.

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 termina com a adição de outra borda (como antes) e redimensiona automaticamente 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 de colunas fornecido. Especificamente, os valores de data são formatados como "Dia" e "Ano", "Dia (Mês)".

Observação: há muitos formatos de data e número que podem ser usados nos scripts e nas planilhas. Para mais informações, consulte o guia Formatos de data e número.

Primeiro, a função verifica se o índice da coluna fornecida é válido (ou seja, zero ou maior). Caso contrário, ele retorna sem fazer nada. Essa verificação evita erros que podem ser causados se, por exemplo, a página não tiver uma coluna "release_date&#39".

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. Salve seu projeto de script no editor do Apps Script, caso ainda não tenha feito isso.
2. Clique no item de menu Formatos rápidos &conjunto de dados.

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

Você automatizou mais uma tarefa de formatação. Agora que esses comandos de formatação estão disponíveis, vamos adicionar mais dados aos quais aplicá-los.

7. Buscar e formatar dados da API

Até aqui neste codelab, você viu como usar o Apps Script como uma forma alternativa de formatar sua planilha. Em seguida, você escreverá um código que extrai dados de uma API pública, os insere na planilha e os formata para que possam ser lidos.

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

Seu código chamará a API para receber uma grande quantidade de dados JSON, analisar a resposta, colocar os dados em uma nova página e formatá-la.

Implementação

Nesta seção, você adicionará alguns itens de menu. Cada item de menu chama um script de wrapper que transmite variáveis específicas do item para a função principal (createResourceSheet_()). Você implementará essa função e três funções auxiliares adicionais. Como antes, as funções auxiliares ajudam a isolar logicamente as partes compartimental da tarefa e a ajudar 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 corresponder ao 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 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 seu projeto de script.
3. No editor de script, 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, clique em Adicionar um arquivo > Script ao lado de Arquivos.
5. Nomeie o novo script como "quot;API" e pressione Enter. O Apps Script anexa 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 seu 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()

Você adicionou alguns itens ao seu 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 de wrapper

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

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

A resposta é funções de wrapper. Essas são funções leves que você pode chamar, chamando imediatamente 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 de menu estão vinculados a essas funções. Quando um item de menu é clicado, a função do wrapper é executada e chama imediatamente a função createResourceSheet_(resourceType, idNumber, episodeNumber) do criador de páginas principal, transmitindo os parâmetros adequados para o item de menu. Nesse caso, isso significa solicitar que a função de criação de páginas crie uma planilha preenchida com os principais dados de personagens de um dos filmes da série Star Wars.

Observação: se quiser, você poderá criar mais funções de wrapper para preencher diferentes tipos de página com dados. Você só precisa saber que tipo de recurso pedir e qual ID de filme usar. Exemplos de tipos de recurso incluem "planets' e "starships'" e a API aceita números de 1 a 7 como IDs de filmes.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Essa é a principal função do builder de páginas para este exercício. Com a ajuda de algumas funções auxiliares, ele recebe, analisa e cria os dados da API, gravando os dados da API na página e, em seguida, formata a página usando as funções que você construiu nas seções anteriores. Vamos analisar os detalhes:

Primeiro, a função usa fetchApiResourceObject_(url) para fazer uma solicitação da API para recuperar informações básicas do filme. A resposta da API inclui um conjunto de URLs que o código pode usar para ver mais detalhes sobre pessoas específicas (conhecidas 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 para cada URL de recurso em resourceUrls. Os resultados são armazenados na matriz resourceDataList. Cada elemento dessa matriz é um objeto que descreve um caractere diferente do filme.

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

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

Após a criação da página, a função auxiliar fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) é chamada para adicionar todos os dados da API a ela.

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

fetchApiResourceObject_(url)

Essa função auxiliar é semelhante à função auxiliar fetchBookData_(ISBN) usada no codelab anterior, Como trabalhar com dados. Ele usa o URL fornecido e usa 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 JavaScript JSON.parse(json). Em seguida, o objeto JSON resultante é retornado.

Observação: essa função pode ser útil quando você precisa recuperar informações de uma API. Ele é generalizado para funcionar com quase todos os URLs de API, portanto, considere salvá-lo para uso futuro.

createNewSheet_(name)

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

Se a página não existir, a função a criará com Spreadsheet.insertSheet(sheetName), ativará a página e retornará a nova página.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Essa função auxiliar é responsável por preencher a nova planilha com dados da API. Ela usa como parâmetros a nova página, a lista de chaves do objeto e a lista de objetos de recurso da API como parâmetros. Cada chave de objeto representa uma coluna na nova página, 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 de coluna. 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 da lista objectKeys. A chave é definida como o cabeçalho da coluna e, em seguida, um segundo loop passa por todos os objetos de recursos. Para cada par (linha, coluna), as informações da API correspondentes são copiadas para o elemento resourceValues[row][column].

Observação: as linhas e colunas no Planilhas são indexadas, enquanto as matrizes JavaScript são 0 indexadas. Devido a essa diferença, muitas vezes precisamos adicionar ou subtrair 1 dos índices ao programar no Apps Script.

Depois que a resourceValues for preenchida, a página de destino será limpa usando Sheet.clear(), caso contenha dados de cliques anteriores no item de menu. Por fim, os novos valores são gravados na planilha.

Resultados

Para ver os resultados do seu trabalho, faça o seguinte:

1. Salve seu projeto de script no editor do Apps Script, caso ainda não tenha feito isso.
2. Clique no item de menu Formatos rápidos > Criar folha de caracteres > Episódio IV.

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

Você escreveu um código para importar dados e formatá-los automaticamente.

8. Conclusão

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

Você achou este codelab útil?

Sim Não

O que você aprendeu

• aplicar várias operações de formatação do 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.

Termos-chave:

• Ativa (status): indica que a planilha, a planilha, o intervalo ou a célula especificada é a que o usuário da planilha está visualizando ou destacando.
• Célula ativa: a única célula destacada na página ativa com o foco do cursor em destaque.
• Intervalo ativo: um grupo de uma ou mais células destacadas na página ativa.
• API: uma interface de programação de aplicativos que é um serviço chamado por programas ou scripts para solicitar informações ou executar determinadas ações.
• Autorização: o processo de autorização do usuário para que o Apps Script acesse os dados do usuário nos Serviços do Google relevantes ou realize ações em nome do usuário.
• Script vinculado ao contêiner: é qualquer script vinculado e criado a partir de um documento do Google Workspace, como um arquivo do Planilhas ou Documentos Google.
• Função auxiliar: uma função, geralmente particular, chamada por outra pessoa para concluir uma pequena subtarefa. As funções auxiliares são usadas quando a subtarefa precisa ser concluída muitas vezes em diferentes locais.
• JSON: formato de arquivo para definir objetos de dados e as características deles a partir do texto.
• onOpen(): quando definido em um script, é um acionador simples que é disparado sempre que um arquivo é aberto ou atualizado.
• Funções privadas: uma função que não pode ser chamada como parte de uma biblioteca ou por clientes usando a comunicação cliente-servidor. As funções particulares do Apps Script têm nomes que terminam em _.
• Intervalo**: um grupo de uma ou mais células adjacentes em uma página. Com a classe Range**, você lê e atualiza intervalos em uma página.
• Editor de script: o editor de código no navegador para o Apps Script.
• Acionadores simples: um subtipo de acionadores no Apps Script com nomes de função de acionador reservados e determinadas restrições.
• Planilha**: uma única página de uma planilha Às vezes usado para fazer referência a um arquivo do Planilhas Google no Google Drive. Com a classe Sheet**, você pode acessar e modificar páginas.
• SpreadsheetApp: essa classe serve como a classe pai para o serviço de planilhas e fornece um ponto de partida para o código que lê ou manipula dados do Planilhas Google.
• Arquivo**: um arquivo do Planilhas Google que reside no Google Drive. Com a classe Spreadsheet**, você pode acessar e modificar planilhas.
• Serviço do Planilhas: um serviço do Apps Script que permite criar, acessar e modificar arquivos do Planilhas Google.
• Acionadores: um evento vinculado a uma função do Apps Script. Quando ocorre um evento (como uma planilha aberta), o acionador dispara e dispara automaticamente a função associada do Apps Script.
• Serviço de busca de URL: o serviço do Apps Script que permite que os scripts se conectem a endpoints de URL para fazer solicitações e receber respostas.
• Funções de wrapper: funções de utilitário simples usadas para chamar funções mais complexas, transmitindo um conjunto específico de parâmetros a elas.

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 arquivos do Apresentações Google.

Encontre o próximo codelab em Gráfico e apresentação de dados no Apresentações.