Fundamentos do Apps Script com o Planilhas Google #3: Como trabalhar com dados

1. Introdução

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

Com este codelab, você aprenderá a usar a manipulação de dados, menus personalizados e a recuperação de dados de APIs públicas no Apps Script para melhorar sua experiência no Planilhas. Você continuará trabalhando com as classes SpreadsheetApp, Spreadsheet, Sheet e Range dos codelabs anteriores desta playlist.

O que você vai aprender

• Saiba como importar dados de uma planilha pessoal ou compartilhada no Drive.
• Como criar um menu personalizado com a função onOpen().
• Como analisar e manipular valores de dados de string em células do Planilhas Google.
• Como extrair e manipular dados de objetos JSON de uma fonte de API pública.

Antes de começar

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

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

É preciso usar uma planilha para fazer os exercícios deste codelab. Siga estas etapas para criar uma planilha para usar nestes exercícios:

1. Crie uma planilha no Google Drive. Para fazer isso na interface do Drive, selecione Novo &Planilhas Google. Isso cria e abre a nova planilha. O arquivo é salvo na sua pasta do Drive.
2. Clique no título da planilha e altere-a de "Texto sem formatação" para "Manipulação de dados e menus personalizados". Sua planilha terá esta aparência:

3. Para abrir o editor de script, clique em Extensões> Apps Script
4. Clique no título do projeto do Apps Script e altere-o de "Untitled Project" para "Data Manipulation e Custom Menus." Clique em Renomear para salvar a alteração.

Com uma planilha em branco e um projeto, você está pronto para começar o laboratório. Vá para a próxima seção e comece a aprender sobre os menus personalizados.

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.

3. Visão geral: importar dados com um item de menu personalizado

Com o Apps Script, você pode definir menus personalizados que podem aparecer no Planilhas Google. Você também pode usar os menus personalizados no Documentos, Apresentações e Formulários Google. Ao definir um item de menu personalizado, você cria um rótulo de texto e o conecta a uma função do Apps Script no seu projeto de script. Em seguida, adicione o menu à IU para ele aparecer no Planilhas Google:

Quando um usuário clica em um item de menu personalizado, a função do Apps Script associada a ele é executada. Essa é uma forma rápida de executar as funções do Apps Script sem precisar abrir o editor de script. Ele também permite que outros usuários da planilha executem seu código sem precisar saber nada sobre como ela ou o Apps Script funciona. Para eles, é só mais um item do cardápio.

Os itens de menu personalizados são definidos na função onOpen() acionador simples, que você aprenderá na próxima seção.

4. A função onOpen()

Os acionadores simples no Apps Script permitem executar códigos específicos em resposta a determinadas condições ou eventos. Ao criar um acionador, defina qual evento faz com que ele seja disparado e forneça uma função do Apps Script executada para o evento.

onOpen() é um exemplo de gatilho simples. Eles são fáceis de configurar. Tudo o que você precisa fazer é escrever uma função do Apps Script chamada onOpen(). Ela será executada sempre que a planilha associada for aberta ou atualizada:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Conceito-chave: os acionadores simples são fáceis de configurar e usar, mas têm várias restrições. Devido a essas limitações, o onOpen() só é usado para criar itens de menu.

Os gatilhos são um tópico complexo e, portanto, estão fora do escopo deste codelab. Por enquanto, pense em onOpen() como um manipulador de eventos que adiciona itens de menu sempre que você carregar uma planilha.

Implementação

Vamos criar um menu personalizado.

1. Substitua o código no seu projeto de script pelo seguinte:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}

2. Salve seu projeto de script.

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 três linhas criam o menu (Book-list), adicionam um item (Load Book-list) a esse 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 a função do Apps Script que é executada quando o item do menu é selecionado. Nesse caso, selecionar o item de menu Load Book-list faz com que o Planilhas tente executar a função loadBookList() (que ainda não existe).

Resultados

Execute esta função agora para ver se ela funciona:

1. Atualize a planilha no Planilhas Google. Observação: geralmente, isso fecha a guia com seu editor de script.
2. Reabra seu editor de script selecionando Ferramentas e editor de script.

Depois que a planilha for recarregada, o novo menu Book-list será exibido na barra de menus:

Clique em Lista de livros para ver o menu resultante:

A próxima seção cria o código para a função loadBookList() e introduz uma maneira de interagir com os dados no Apps Script: lendo outras planilhas.

5. Importar dados de uma planilha

Agora que você criou um menu personalizado, pode criar funções que podem ser executadas clicando no item de menu.

No momento, o menu personalizado Book-list tem um item de menu: Load Book-list.. A função chamada quando você seleciona o item de menu Load Book-list, loadBookList(),, não existe no seu script. Por isso, selecionar Book-list > Load Book-list gera um erro:

Para corrigir esse erro, implemente a função loadBookList().

Implementação

Você quer que o novo item de menu preencha a planilha com os dados a serem usados. Portanto, implemente loadBookList() para ler os dados de livros de outra planilha e copiá-los para esta planilha:

1. Adicione o seguinte código ao seu script em onOpen():

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}

2. Salve seu projeto de script.

Revisão de código

Então, como essa função funciona? A função loadBookList() usa métodos principalmente das classes Spreadsheet, Sheet e Range dos codelabs anteriores introduzidos. Com esses conceitos em mente, você pode dividir o código loadBookList() nas quatro seções a seguir:

1: Identificar a planilha de destino

A primeira linha usa SpreadsheetApp.getActiveSheet() para conseguir uma referência ao objeto atual da planilha e a armazena na variável sheet. Esta é a planilha para a qual os dados serão copiados.

2: Identificar os dados de origem

As próximas linhas estabelecem quatro variáveis referentes aos dados de origem que você está recuperando:

• bookSS armazena uma referência à planilha da qual o código está lendo dados. O código encontra a planilha pelo ID dela. Neste exemplo, fornecemos o ID de uma planilha de origem para leitura e abri-la usando o método SpreadsheetApp.openById(id).
• bookSheet armazena uma referência a uma página dentro de bookSS que contém os dados desejados. O código identifica a página a ser lida pelo nome, codelab-book-list.
• bookRange armazena uma referência a um intervalo de dados no bookSheet. O método Sheet.getDataRange() retorna o intervalo que contém todas as células não vazias da página. Essa é uma maneira fácil de garantir que você tenha um intervalo que cubra todos os dados em uma página sem incluir linhas e colunas vazias.
• bookListValues é uma matriz 2D que contém todos os valores extraídos das células em bookRange. O método Range.getValues() gera essa matriz lendo os dados da página de origem.

3: copiar os dados da origem para o destino

A próxima seção de código copia os dados de bookListValues para sheet e renomeia a página também:

• O Sheet.getRange(row, column, numRows, numColumns) é usado para identificar onde copiar os dados no sheet.
• Os métodos Range.getHeight() e Range.getWidth() são usados para medir o tamanho dos dados e definir um intervalo de destino com as mesmas dimensões.
• O Range.setValues(values) copia a matriz 2D de bookListValues para o intervalo de destino, substituindo os dados já existentes.

4: formate a página de destino

O Sheet.setName(name) é usado para alterar o nome da página de destino para Book-list. A última linha da função usa Sheet.autoResizeColumns(startColumn, numColumns) para redimensionar as três primeiras colunas na página de destino, permitindo que você leia os novos dados com mais facilidade.

Resultados

É possível ver essa função em ação. No Planilhas Google, selecione Book-list > Load book-list para executar a função e preencher a planilha:

Agora você tem uma planilha com uma lista de títulos de livros, autores e números ISBN de 13 dígitos. Na próxima seção, você aprenderá a modificar e atualizar os dados desta lista de livros usando manipulação de strings e menus personalizados.

6. Visão geral: limpar os dados da planilha

Agora você tem as informações do livro na sua planilha. Cada linha se refere a um livro específico, listando o título, o autor e o número ISBN em colunas separadas. No entanto, você também pode ver alguns problemas com esses dados brutos:

1. Para algumas linhas, o título e o autor são colocados na coluna do título juntos, vinculados por uma vírgula ou pela string " por ".
2. Algumas linhas estão sem o título ou o autor do livro.

Nas próximas seções, você corrigirá esses problemas limpando os dados. Para o primeiro problema, você criará funções que leem a coluna de título e dividem o texto sempre que uma vírgula ou "delimitador" é encontrado, colocando as substrings de autor e título correspondentes nas colunas corretas. No segundo problema, você escreverá um código que pesquisa automaticamente informações ausentes sobre o livro usando uma API externa e as adicionará à sua planilha.

7. Adicionar itens de menu

Crie três itens de menu para controlar as operações de limpeza de dados que serão implementadas.

Implementação

Vamos atualizar onOpen() para incluir os itens de menu extras que você precisará. Faça o seguinte:

1. No projeto do script, atualize o código onOpen() para corresponder ao seguinte:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}

2. Salve seu projeto de script.
3. No editor de script, selecione onOpen na lista de funções e clique em Executar. Isso executará onOpen() para recriar o menu da planilha. Assim, você não precisará atualizar a planilha.

Nesse novo código, o método Menu.addSeparator() cria um divisor horizontal no menu para manter os grupos de itens relacionados organizados visualmente. Os novos itens de menu são adicionados abaixo dela, com os rótulos Separate title/author at first comma, Separate title/author at last "by" e Fill in blank titles and author cells.

Observação: os itens de menu aparecem no Planilhas na mesma ordem em que você os adiciona ao código onOpen().

Resultados

Na planilha, clique no menu Book-list para ver os novos itens:

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

8. Dividir texto em delimitadores de vírgula

O conjunto de dados importado para a planilha tem algumas células em que o autor e o título são combinados incorretamente em uma célula usando uma vírgula:

Dividir strings de texto em colunas separadas é uma tarefa comum em planilhas. O Planilhas Google tem uma função SPLIT() que divide as strings em colunas. No entanto, os conjuntos de dados costumam ter problemas que não podem ser facilmente resolvidos com as funções integradas do Planilhas. Nesses casos, é possível escrever o código do Apps Script para fazer as operações complexas necessárias para limpar e organizar seus dados.

Comece a limpar seus dados implementando primeiro uma função chamada splitAtFirstComma(), que divide o autor e o título nas respectivas células quando são encontradas vírgulas.

A função splitAtFirstComma() precisa executar as seguintes etapas:

1. Receba o intervalo que representa as células selecionadas.
2. Verifique se as células no intervalo têm vírgulas.
3. Quando vírgulas forem encontradas, divida a string em duas (e somente duas) substrings no local da primeira vírgula. Para simplificar, suponha que qualquer vírgula indique um padrão de string "[autor], [title]". Também é possível saber se várias vírgulas aparecem na célula. Nesse caso, divida a primeira vírgula na string.
4. Defina as substrings como o novo conteúdo do respectivo título e das células de autor.

Implementação

Para implementar essas etapas, você usará os mesmos métodos do serviço de planilhas, mas também será necessário usar JavaScript para manipular os dados da string. Siga estas etapas:

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

/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. Salve seu projeto de script.

Revisão de código

Vamos analisar o novo código que consiste em três seções principais:

1: recuperar os valores de título destacados

As três primeiras linhas estabelecem três variáveis referentes aos dados atuais na planilha:

• activeRange representa o intervalo destacado pelo usuário quando a função splitAtFirstComma() foi chamada. Para manter este exercício simples, podemos presumir que o usuário só faz isso ao destacar células na coluna A.
• titleAuthorRange representa um novo intervalo que cobre as mesmas células que activeRange, mas também inclui mais uma coluna à direita. titleAuthorRange é criado usando o método Range.offset(rowOffset, columnOffset, numRows, numColumns). O código precisa desse intervalo expandido porque precisa de um local para colocar os autores encontrados na coluna de título.
• titleAuthorValues é uma matriz 2D de dados extraídos de titleAuthorRange usando Range.getValues().

2: Analise cada título e divida no primeiro delimitador de vírgula encontrado

A próxima seção analisa os valores em titleAuthorValues para encontrar vírgulas. Um JavaScript para repetição é usado para examinar todos os valores na primeira coluna de titleAuthorValues. Quando uma substring de vírgula é encontrada (", ") usando o método JavaScript String indexOf(), o código faz o seguinte:

1. O valor da string de célula é copiado para a variável titlesAndAuthors.
2. A localização das vírgulas é determinada com o método JavaScript String indexOf().
3. O método JavaScript String slice() é chamado duas vezes para conseguir a substring antes do delimitador de vírgula e a substring após o delimitador.
4. As substrings são copiadas de volta para a matriz titleAuthorValues 2D, substituindo os valores existentes nessa posição. Como estamos presumindo um padrão "[autor], [título]", a ordem das duas substrings é invertida para colocar o título na primeira coluna e os autores na segunda.

Observação: quando o código não encontra uma vírgula, ele deixa os dados na linha inalterados.

3: Copie os novos valores de volta na página

Depois que todos os valores da célula de título forem examinados, a matriz 2D de titleAuthorValues será copiada para a planilha usando o método Range.setValues(values).

Importante: o código poderia ter atualizado cada célula da planilha individualmente, já que cada vírgula era chamada, chamando o método Range.setValue(value) toda vez que uma célula precisava ser alterada. No entanto, isso resultaria em um código muito mais lento, já que cada chamada Range.setValue(value) requer comunicação com o servidor.

Para atualizar os dados do Planilhas, é quase sempre melhor colocar os valores em uma matriz 2D, atualizá-los e enviá-los de volta à planilha com uma única chamada Range.setValues(values).

Resultados

Agora é possível ver os efeitos da função splitAtFirstComma() em ação. Para executar, selecione o item de menu Título/autor separado na primeira vírgula depois de selecionar...

...uma célula:

...ou com várias células:

Observação: é possível selecionar qualquer coluna de texto na planilha. Use essa função para encontrar vírgulas e dividir o texto de acordo com as regras que você definiu. No entanto, se o código encontrar um delimitador de vírgula, ele substituirá a célula à direita da original.

Você criou uma função do Apps Script que processa dados do Planilhas. Agora, você implementará a segunda função do divisor.

9. Dividir texto em delimitadores de "por"

Observando os dados originais, é possível ver outro problema. Assim como alguns dos dados formatam títulos e autores em uma única célula, como "[authors], [title]", outras células formatam o autor e o título como "[title] por [authors]":

Implementação

É possível resolver esse problema usando a mesma técnica da última seção, criando uma função com o nome splitAtLastBy(). Essa função tem um job semelhante a splitAtFirstComma(). A única diferença real é que ela procura um padrão de texto um pouco diferente. Para implementar essa função, faça o seguinte:

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

/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. Salve seu projeto de script.

Revisão de código

Há algumas diferenças importantes entre esse código e splitAtFirstComma():

1. A substring " by " é usada como delimitador de string, em vez de ", ".
2. Aqui o método JavaScript String.lastIndexOf(substring) é usado em vez de String.indexOf(substring). Isso significa que, se houver várias substrings "quot; by" na string inicial, todas as outras, exceto a última" by " serão consideradas parte do título.
3. Após dividir a string, a primeira substring é definida como o título e a segunda como o autor (essa é a ordem oposta do splitAtFirstComma()).

Resultados

Agora é possível ver os efeitos da função splitAtLastBy() em ação. Selecione a opção Separar título/autor no final " depois de selecionar...

...uma célula:

...ou com várias células:

Você concluiu esta seção do codelab. Agora você pode usar o Apps Script para ler e modificar dados de string em uma página e usar menus personalizados para executar diferentes comandos do Apps Script.

Na próxima seção, você aprenderá a melhorar ainda mais esse conjunto de dados preenchendo células vazias com dados extraídos de uma API pública.

10. Visão geral: acessar dados de APIs públicas

Até agora, você refinau seu conjunto de dados para corrigir alguns problemas de formatação de título e autor, mas ainda faltam algumas informações, destacadas nas células abaixo:

Não é possível ver os dados ausentes usando operações de string nos dados que você tem atualmente. Em vez disso, você precisará obter os dados ausentes de outra origem. Você pode fazer isso no Apps Script solicitando informações de APIs externas que podem fornecer dados adicionais.

As APIs são interfaces de programação de aplicativo. Este é um termo geral, mas é basicamente um serviço que os programas e scripts podem chamar para pedir informações ou realizar determinadas ações. Nesta seção, você chamará uma API disponível publicamente para solicitar informações do livro que podem ser inseridas nas células vazias da sua planilha.

Nesta seção, você aprenderá a fazer o seguinte:

• Solicitar dados de livros de uma fonte de API externa.
• Extraia informações de títulos e autores dos dados retornados e grave-as na planilha.

11. Buscar dados externos com UrlFetch

Antes de se aprofundar no código que funciona diretamente com sua planilha, você pode aprender a trabalhar com APIs externas no Apps Script criando uma função auxiliar especificamente para solicitar informações de livros da API Open Library pública.

Observação: a Biblioteca aberta é um arquivo de Internet que busca criar uma página para cada publicação de livros. Se tiver interesse, leia mais sobre a documentação da API no site oficial da empresa.

Nossa função auxiliar, fetchBookData_(ISBN), usa um número ISBN de 13 dígitos de um livro como parâmetro e retorna dados sobre esse livro. Ele se conecta e recupera informações da API Open Library e analisa o objeto JSON retornado.

Implementação

Para implementar essa função auxiliar, faça o seguinte:

1. No editor do Apps Script, adicione o seguinte código ao fim do script:

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}

2. Salve seu projeto de script.

Observação: no Apps Script, os nomes de função que terminam em _ (um sublinhado) são considerados privados. Outros scripts não podem chamar essas funções quando estão presentes em uma biblioteca ou por clientes durante a comunicação cliente-servidor. No entanto, esses são os temas avançados. Portanto, se você souber que uma função será usada apenas pelo script atual, a prática recomendada é encerrar o nome de uma função com _.

Revisão de código

Esse código é dividido em duas seções principais:

1: a solicitação de API

Nas duas primeiras linhas, fetchBookData_(ISBN) se conecta à API Open Library pública usando o endpoint de URL da API e o serviço de busca de URL do Apps Script.

A variável url é apenas uma string de URL, como um endereço da Web. Ele aponta para um local nos servidores da Open Library. Também inclui três parâmetros (bibkeys, jscmd e format) que informam aos servidores da Open Library quais informações você está solicitando e como estruturar a resposta. Nesse caso, forneça o número ISBN do livro e peça informações detalhadas para serem retornadas no formato JSON.

Observação: cada API pública define os próprios formatos para fazer solicitações e retornar resultados. Para que seu código se comunique de maneira eficaz com uma API, você precisa aderir aos formatos definidos por ele. Na Open Library, os formatos de solicitação e resposta são descritos na documentação da API Open Library Books. O formato de resposta usado é o JSON (JavaScript Object Notation), que é apenas uma maneira específica de organizar dados. O JSON é semelhante ao HTML ou XML, mas tende a ser mais fácil para as pessoas lerem.

Depois que você cria a string do URL, o código envia uma solicitação ao local e recebe uma resposta. Isso é feito usando o método UrlFetchApp.fetch(url, params). Ele envia uma solicitação de informações ao URL externo fornecido e armazena a resposta resultante na variável response. Além do URL, o código define o parâmetro opcional muteHttpExceptions como true. Essa configuração significa que seu código não será interrompido se a solicitação resultar em um erro de API. Em vez disso, a resposta de erro será retornada.

A solicitação retorna um objeto HTTPResponse armazenado na variável response. As respostas HTTP incluem um código de resposta, cabeçalhos HTTP e o conteúdo principal da resposta. As informações de interesse são o conteúdo JSON principal. Por isso, o código precisa extraí-las e analisar o JSON para localizar e retornar as informações desejadas.

2: Analise a resposta da API e retorne as informações de interesse

Nas últimas três linhas de código, o método HTTPResponse.getContentText() retorna o conteúdo principal da resposta como uma string. Essa string está no formato JSON, mas a API Open Library define o conteúdo e o formato exatos. O método JSON.parse(jsonString) converte a string JSON em um objeto JavaScript para facilitar a extração de diferentes partes dos dados. Por fim, a função retorna os dados correspondentes ao número ISBN do livro.

Observação: se você precisar de algum conhecimento sobre o JSON, acesse a documentação do JavaScript para trabalhar com o JSON ou a referência da classe JSON.

Resultados

Agora que você implementou o fetchBookData_(ISBN), outras funções no seu código podem encontrar informações de qualquer livro usando o número ISBN dele. Você usará essa função para preencher as células na sua planilha.

12. Gravar dados da API em uma planilha

Agora, é possível implementar uma função fillInTheBlanks() que faça o seguinte:

1. Identifique o título e os dados do autor ausentes no intervalo de dados ativo.
2. Recupere os dados ausentes de um livro específico chamando a API Open Library usando o método auxiliar fetchBookData_(ISBN).
3. Atualize os títulos ou os valores do autor ausentes nas respectivas células.

Implementação

Para implementar essa nova função, faça o seguinte:

1. No editor do Apps Script, adicione o seguinte código ao final do projeto do script:

/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

2. Salve seu projeto de script.

Revisão de código

Esse código é dividido em três seções:

1: Ler as informações do livro

As três primeiras linhas da função definem constantes para ajudar a tornar o código mais legível. Nas próximas duas linhas, a variável bookValues é usada para manter uma cópia local das informações do livro da página. O código lerá as informações do bookValues, usará a API para preencher as informações ausentes e gravará esses valores na planilha.

Observação: essa disposição é diferente de splitAtFirstComma() e splitAtLastBy(). Nessas funções, o código examinava apenas os títulos destacados e as linhas deles. Aqui, você está examinando a planilha inteira usando Spreadsheet.getDataRange().

2: Busque informações ausentes usando a função auxiliar

O código faz um loop em cada linha em bookValues para encontrar títulos ou autores ausentes. Para reduzir o número de chamadas de API e melhorar a eficiência, o código só chama a API se o seguinte for verdadeiro:

1. A coluna "ISBN" da linha tem um valor.
2. O título ou a célula de autor na linha está vazio.

Se as condições forem verdadeiras, o código chamará a API usando a função auxiliar fetchBookData_(isbn) implementada anteriormente e armazenará o resultado na variável bookData. Agora você verá as informações ausentes que quer inserir na planilha.

A única tarefa restante é adicionar as informações do bookData à nossa planilha. No entanto, há uma ressalva. APIs públicas, como a API Open Library Book, às vezes não têm as informações solicitadas ou podem ter outro problema que impede o fornecimento delas. Se você presumir que todas as solicitações de API serão bem-sucedidas, seu código não será robusto o suficiente para lidar com erros inesperados.

Para garantir que seu código possa processar erros de API, ele precisa verificar se a resposta da API é válida antes de tentar usá-lo. Depois que o código tiver bookData, ele executa uma verificação simples para confirmar se bookData e bookData.details existem antes de tentar ler deles. Se um desses valores estiver ausente, significa que a API não tem os dados desejados. Nesse caso, o comando continue instrui o código a pular essa linha. Não é possível preencher as células ausentes, mas pelo menos o script não falha.

3. Gravar informações atualizadas na planilha

A última parte do código tem verificações semelhantes para confirmar se a API retornou informações de título e autor. O código só atualizará a matriz bookValues se o título ou a célula de autor original estiver vazio e a API retornar um valor que possa ser colocado nela.

A repetição será encerrada depois que todas as linhas da página forem examinadas. A última etapa é gravar a matriz bookValues atualizada na planilha usando Range.setValues(values).

Resultados

Agora é possível concluir a limpeza dos dados do livro. Faça o seguinte:

1. Se você ainda não tiver feito isso, destaque o intervalo A2:A15 na sua planilha e selecione Lista de livros e autor na primeira vírgula para limpar os problemas de vírgula.
2. Se ainda não tiver feito isso, destaque o intervalo A2:A15 na sua planilha e selecione Lista de livros > Separe o título/autor ao final " para limpar os problemas.
3. Para preencher todas as células restantes, selecione Lista de livros "> Preencha títulos em branco e células de autor:

13. Conclusão

Parabéns por concluir este codelab. Você aprendeu a criar menus personalizados para ativar diferentes partes do seu código do Apps Script. Você também viu como importar dados para o Planilhas Google usando os serviços do Apps Script e as APIs públicas. Essa é uma operação comum no processamento de planilhas, e o Apps Script permite importar dados de várias fontes. Por fim, vimos que é possível usar o Apps Script e o JavaScript para ler, processar e inserir dados de planilhas.

Você achou este codelab útil?

Sim Não

O que você aprendeu

• Saiba como importar dados de uma planilha do Google.
• Como criar um menu personalizado na função onOpen().
• Como analisar e manipular valores de dados de string.
• Como chamar APIs públicas usando o serviço de busca de URL.
• Como analisar dados de objetos JSON recuperados de uma fonte de API pública

Principais conceitos:

1. onOpen() é um gatilho simples usado para criar itens de menu. Os acionadores simples têm várias restrições que você precisa seguir.
2. Ao ler ou gravar dados da célula da planilha, recomendamos ler ou gravar um intervalo inteiro de uma só vez com Range.getValues() ou Range.setValues(values). Não leia nem defina cada célula individualmente, porque ela será mais lenta.
3. Nomes de funções do Apps Script que terminam em _ (um sublinhado) são considerados privados. Outros scripts não podem chamar essas funções quando estão em uma biblioteca ou por clientes usando comunicação entre servidor e cliente. Se você sabe que uma função será usada apenas pelo script atual, a prática recomendada é encerrar o nome dela com _.
4. Ao usar dados retornados por APIs externas, valide os dados antes de tentar usá-los. Isso reduz os erros no código se a API retornar um resultado inesperado.

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 por meio da comunicação entre servidores e clientes. 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.

A seguir

O próximo codelab desta playlist aborda em mais detalhes como formatar dados em uma planilha.

Encontre o próximo codelab em Formatação de dados.