Planilhas do Google Curso de Capacitação de Servidores
Início do conteúdo
Formatação de Dados

Configurando

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

  1. Clique neste link para copiar a planilha de dados e, em seguida, clique no botão Fazer uma cópia . A nova planilha é colocada na pasta inicial do Google Drive e é chamada de “Cópia da 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 folha deve ficar assim, com algumas informações básicas sobre os três primeiros filmes de Star Wars:

  1. Selecione Ferramentas> Editor de scripts para abrir o editor de scripts.
  2. Clique no título “Projeto sem título” e altere para “Formatação de dados”.
  3. Clique em OK para salvar a alteração do título.

 

 

Crie um menu personalizado

Você pode 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 de que você precisa. O processo de criação de menus personalizados foi descrito no codelab Trabalhando 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 em 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 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') 
  .addToUi();
}
  1. Salve seu projeto de script.
  2. No editor de script, selecione onOpenno menu suspenso da barra de tarefas e clique no botão. Isso será executado onOpen()para reconstruir o menu da planilha, para que você não precise recarregar a planilha.

 

Revisão de código

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

As próximas linhas criar um novo menu ( Quick formats), itens de menu add ( Format row header, Format column headere Format dataset) para esse menu, e depois adicionar o menu para a interface do planilha. Isto é feito com as createMenu(caption), addItem(caption, functionName)e addToUi()métodos, respectivamente.

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

 

Resultados

Em sua planilha, você pode clicar no Quick formatsmenu para ver os novos itens de menu:

    Clicar em qualquer um desses itens agora causará um erro, pois você ainda não implementou suas funções correspondentes. As próximas seções cobrem a implementação dessas funções.

 

 

Formate uma linha de cabeçalho

Os conjuntos de dados em planilhas geralmente têm linhas de cabeçalho que identificam os dados em cada coluna. Normalmente, é uma boa ideia formatar linhas de cabeçalho para separá-los visualmente do restante dos dados da planilha.

No primeiro codelab, você construiu uma macro para seu cabeçalho e ajustou seu código. Aqui, você formatará uma linha de cabeçalho do zero usando o Apps Script. A linha de cabeçalho que você criar aqui irá colocar em negrito o texto do cabeçalho, colorir o fundo de um azul-esverdeado escuro, colorir o texto de branco e adicionar algumas bordas sólidas.

 

Implementação

Para implementar a operação de formatação, você usará os mesmos métodos de serviço de planilha usados ​​antes, mas agora também usará alguns dos métodos de formatação do serviço. Execute as seguintes etapas:

  1. No editor do Apps Script, adicione a seguinte função ao final do seu projeto de script:
/**
 * Formats top row of the 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')
    .setBackgroundColor('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}
  1. Salve seu projeto de script.

 

Revisão de código

Como muitas tarefas de formatação, o código do Apps Script necessário para implementá-lo é direto. As primeiras duas linhas usam métodos que você viu antes para obter uma referência para a planilha ativa atual ( sheet) e a linha superior da planilha ( headerRange). O Sheet.getRange(row, column, numRows, numColumns) método especifica a linha superior, incluindo apenas as colunas com dados nelas. O Sheet.getLastColumn()método apenas retorna o índice da última coluna que contém os dados da planilha. Em nosso exemplo, é a coluna E ( url ).

 

 

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

O último método tem muitos parâmetros, então vamos revisar o que cada um está fazendo. As primeiras quatro opções de parâmetro aqui (todas definidas 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 ( nulle null) direcionam o Apps Script para evitar a alteração de quaisquer linhas de fronteira que estejam dentro do intervalo selecionado. O sétimo parâmetro ( null) indica que a cor da borda deve ser preto por padrão. Finalmente, o último parâmetro indica o tipo de estilo de borda a usar, tirado das opções fornecidas por SpreadsheetApp.BorderStyle.

 

Resultados

Você pode ver sua função de formatação em ação fazendo 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> Formato da linha de cabeçalho .

 

 

Os resultados devem ser semelhantes aos seguintes:

    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.

 

 

Formate um cabeçalho de coluna

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

  • A negrito do texto
  • Itálico no texto
  • Adicionando bordas de células
  • Inserindo hiperlinks, usando o conteúdo da coluna url . Depois de adicionar esses hiperlinks, você pode remover a coluna url para ajudar a limpar a planilha.

Agora você implementará uma formatColumnHeader() função para aplicar essas alterações à primeira coluna da planilha! Para ajudar a tornar o código um pouco mais fácil de ler, você também implementará duas funções auxiliares.

 

Implementação

Como antes, aqui você só precisa adicionar uma função para automatizar a formatação do cabeçalho da coluna. Execute as seguintes etapas:

  1. No editor do Apps Script, adicione a seguinte formatColumnHeader() função ao final do seu 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); 
}
  1. Adicione também as seguintes funções auxiliares ao final do seu projeto de script, após a formatColumnHeader()função:
/**
 * Helper function that hyperlinks the column header with the
 * 'url' contents. The function then removes the url 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 header of all the 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 look for 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; 
}
  1. Salve seu projeto de script.

 

Revisão de código

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

formatColumnHeader()

Como você provavelmente deve esperar, as primeiras linhas desta função definem variáveis ​​que fazem referência à planilha e ao intervalo em que estamos interessados:

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

O código então aplica as bordas e o negrito ao intervalo do cabeçalho da coluna, basta inserir formatRowHeader(). Aqui, Range.setFontStyle(fontStyle) também é usado para deixar o texto em itálico.

Obter os hiperlinks adicionados à coluna do cabeçalho é mais complexo, portanto, formatColumnHeader() chamadas hyperlinkColumnHeaders_(headerRange, numRows) para cuidar dessa tarefa. Isso ajuda a manter o código organizado e legível.

hyperlinkColumnHeaders_(headerRange, numRows)

Esta função identifica primeiro os índices da coluna do cabeçalho (assumido como sendo o índice 1) e a url coluna. Ele chama columnIndexOf_('url') para obter o índice da coluna url. Se uma url coluna não for encontrada, o método sai sem modificar nenhum dado.

A função obtém um novo intervalo ( urlRange) que cobre os urls correspondentes às linhas da coluna do cabeçalho. Isso é feito com o Range.offset(rowOffset, columnOffset) método, que garante que as duas faixas sejam do mesmo tamanho. Os valores de headerColumn e da url coluna são então recuperados ( headerValuese urlValues, respectivamente).

A função então percorre cada valor de célula de cabeçalho de coluna e o substitui por uma =HYPERLINK() fórmula do Planilhas construída com o cabeçalho e o url conteúdo da coluna. Os valores de cabeçalho modificados são então empurrados para trás na folha usando Range.setValues(values).

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

columnIndexOf_(colName)

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

A função então examina cada nome em ordem. Se encontrar um que corresponda ao nome que está sendo procurado, ele para e retorna o índice dessa coluna. Se chegar ao final da lista de nomes sem encontrar o nome, retorna -1 para sinalizar que o nome não foi encontrado.

Resultados

Você pode ver sua função de formatação em ação fazendo 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> Formatar cabeçalho da coluna .

Os resultados devem ser semelhantes aos seguintes:

    Você automatizou outra tarefa de formatação! Agora que os cabeçalhos de coluna e linha estão formatados, a próxima seção mostrará como formatar os próprios dados.

 

 

Formate seu conjunto de dados

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

  • Cores de fundo de linha alternadas (conhecidas como faixas )
  • Alterando formatos de data.
  • Aplicando bordas.
  • Dimensionamento automático de todas as colunas e linhas.

Agora você criará uma função formatDataset() e um método auxiliar adicional que pode aplicar esses formatos aos dados da planilha.

Implementação

Como antes, aqui você só precisa adicionar uma função para automatizar a formatação dos dados. Execute as seguintes etapas:

  1. No editor do Apps Script, adicione a seguinte formatDataset() função ao final do seu projeto de script:
/**
 * Formats the sheet data, excluding the header row and column.
 * Applies a border and banding, formats the 'release_date'
 * column, and auto-sizes the rows and columns.
 */
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 a banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have a banding, so it's
    // safe to apply a new one.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply a date formatting
  // to a column labeled as '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());
}
  1. Além disso, adicione a seguinte função auxiliar no final do seu projeto de script, após a formatDataset()função:
/** 
 * 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 column 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 formatting 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)");
}
  1. Salve seu projeto de script.

 

Revisão de código

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

formatDataset()

Esta função segue um padrão semelhante às funções de formato anteriores que você já implementou. Primeiro, ele obtém variáveis ​​para conter referências à planilha ativa (planilha) e ao intervalo de dados (fullDataRange).

Em segundo lugar, ele usa o Range.offset(rowOffset, columnOffset, numRows, numColumns) método para criar um novo intervalo ( noHeadersRange) que cobre todos os dados da planilha, excluindo os cabeçalhos de coluna e linha. O código então verifica se este novo intervalo possui alguma faixa existente (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 uma faixa ainda não existir, a função adiciona uma faixa cinza claro usando Range.applyRowBanding(bandingTheme, showHeader, showFooter). Caso contrário, a função passa para a próxima etapa.

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

Finalmente, a formatação é finalizada adicionando outra borda (como antes) e redimensionando automaticamente cada linha e coluna para ajustar os dados que elas contêm usando os métodos e .Sheet.autoResizeColumns(columnPosition) Sheet.autoResizeColumns(columnPosition)

formatDates_(colIndex)

Esta função auxiliar aplica um formato de data específico a uma coluna indicada pelo índice de coluna fornecido. Especificamente, ele formata os valores de data como “Mês, Dia, Ano (Dia da Semana)”.

 

 

Primeiro, a função verifica se o índice da coluna fornecido é válido (ou seja, 0 ou maior). Caso contrário, ele retorna sem fazer mais nada. Esta verificação evita erros que podem ser causados ​​se, por exemplo, a planilha não tiver uma release_date coluna ‘ ‘.

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

 

Resultados

Você pode ver sua função de formatação em ação fazendo 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> Formatar conjunto de dados .

Os resultados devem ser semelhantes aos seguintes:

    Você automatizou mais uma tarefa de formatação! Agora que você tem esses comandos de formatação disponíveis, vamos adicionar mais um monte de dados para aplicá-los!

 

 

Buscar e formatar dados da API

Até agora, neste codelab, você viu como pode usar o Apps Script como um meio alternativo de formatar sua planilha. Em seguida, você escreverá o código que extrai dados de uma API pública, coloca esses dados em sua planilha e formata-os de maneira adequada para que sejam mais legíveis!

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

Seu código irá chamar a API para obter uma grande quantidade de dados JSON, analisar a resposta, colocar os dados em uma nova planilha e, em seguida, formatar essa planilha.

 

Implementação

Nesta seção, você adicionará alguns itens de menu adicionais. Cada item de menu chamará um script de invólucro que apenas passa variáveis ​​específicas do item para a função principal (createResourceSheet_ ()). Você implementará esta função e três funções auxiliares adicionais. Como antes, as funções auxiliares ajudam a isolar partes logicamente compartimentadas da tarefa e ajudam a manter o código legível.

Realize as seguintes ações:

  1. No editor do Apps Script, atualize sua onOpen() função em seu 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();
}
  1. Salve seu projeto de script.
  2. Selecione onOpen no menu suspenso da barra de tarefas e clique no botão. Isso será executado onOpen() para reconstruir o menu da planilha com as novas opções que você acabou de adicionar.
  3. Selecione Arquivo> Novo> Arquivo de script no menu do editor. Nomeie o novo script ” API ” e clique em OK .
  4. 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 new formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with this data
 * already 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 episode number
 *   of the film.  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.co/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more information 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 are all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the new sheet with an appropriate name. It
  // automatically becomes the active sheet when it is 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();

}
  1. Agora você precisa adicionar as funções auxiliares. Adicione o seguinte código 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 new sheet, or returns an existing
 * sheet that has the same name.
 *
 * @param {string} name The name of the new sheet. 
 * @return {object} The newly-created sheet 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 make a new sheet, set its name, and return 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 given 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); 
}
  1. Salve seu projeto de script.

 

Revisão de código

Você acabou de adicionar muitos códigos! Vamos examinar cada função que você adicionou individualmente para entender como elas funcionam:

onOpen()

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

 

Funções de wrapper

Todos os itens de menu que você adicionou estão fazendo a mesma coisa: eles estão tentando criar uma nova planilha com dados extraídos de SWAPI . A única diferença é que cada um está focado em um filme diferente.

Seria conveniente escrever apenas uma única função para criar a folha e fazer com que essa função aceitasse um parâmetro para determinar qual filme usar. No entanto, o Menu.addItem(caption, functionName) método não permite que você passe parâmetros para a função chamada pelo menu. Então, como você evita escrever o mesmo código três vezes?

A resposta são funções de invólucro . Estas são funções leves que você pode chamar para chamar imediatamente outra função com parâmetros específicos definidos.

Aqui, o código utiliza três funções de mensagens publicitárias: 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 invólucro executa e imediatamente chama a função do criador de folha principal createResourceSheet_(resourceType, idNumber, episodeNumber), passando os parâmetros apropriados para aquele item de menu. Nesse caso, isso significa pedir à função de criador de planilhas para criar uma planilha preenchida com os dados dos personagens principais de um dos três primeiros filmes de Star Wars.

 

 

createResourceSheet_(resourceType, idNumber, episodeNumber)

Esta é a principal função do criador de folhas para este exercício. Com a ajuda de algumas funções auxiliares, ele obtém os dados da API, os analisa, cria uma nova planilha, grava os dados da API nessa planilha e formata a planilha usando as funções que você construiu nas seções anteriores. Vamos dar uma olhada nos detalhes:

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

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 resourceDataListmatriz. 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 para informações sobre aquele personagem. Por exemplo, a chave ‘ name‘ mapeia para o nome daquele personagem no filme. Assumimos que as chaves para cada objeto de dados de recursos são todas idênticas, uma vez que se destinam a usar estruturas de objetos comuns. A lista de chaves é necessária posteriormente, portanto, o código armazena a lista de chaves resourceObjectKeysusando o método JavaScript Object.keys () .

Em seguida, a função de construtor chama a createNewSheet_(name)função auxiliar para criar a nova planilha onde 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 à nova planilha.

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ção!

fetchApiResourceObject_(url)

Esta função auxiliar é semelhante à fetchBookData_(ISBN) função auxiliar usada no codelab anterior ( Trabalhando com dados ). Ele pega o URL fornecido e usa o UrlFetchApp.fetch(url, params) método para obter uma resposta. A resposta é então analisada em um objeto JSON usando os métodos HTTPResponse.getContextText() e JavaScript JSON.parse(json). O objeto JSON resultante é então retornado.

 

 

createNewSheet_(name)

Esta função auxiliar é bastante simples. Ele primeiro vê se uma folha com o nome fornecido já existe na planilha. Em caso afirmativo, a função ativa essa planilha e a retorna.

Se a planilha ainda não existe, a função a cria com Spreadsheet.insertSheet(sheetName) (o que também a ativa) e retorna a nova planilha.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Esta função auxiliar é responsável por preencher a nova planilha com os dados da API. Ele leva como parâmetros a nova planilha, a lista de chaves de objeto e a lista de objetos de recursos da API como parâmetros. 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. Este é apenas o tamanho da lista de recursos e chaves, respectivamente. A função então define um intervalo de saída ( resourceRange) onde os dados serão colocados, adicionando uma linha adicional para conter os cabeçalhos das colunas. A variável resourceValues contém uma matriz de valores 2D extraída resourceRange.

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

 

 

Depois de resourceValues preenchida, a planilha de destino é apagada usando Sheet.clear() (caso ela tivesse algum dado de cliques do item de menu anterior). Finalmente, os novos valores são gravados na folha.

 

Resultados

Você pode ver os resultados do seu trabalho árduo fazendo 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 ficha de personagem> Episódio IV .

Os resultados devem ser semelhantes aos seguintes:

    Você escreveu um código que importa dados para o Planilhas e os formata automaticamente! Bom trabalho!

 

O que você aprendeu?

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