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

1. Introdução

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

Ao concluir este codelab, você vai aprender a usar a manipulação de dados, os menus personalizados e a recuperação de dados da API pública no Apps Script para melhorar sua experiência com as Planilhas Google. Você vai continuar trabalhando com as classes SpreadsheetApp, Spreadsheet, Sheet e Range apresentadas nos codelabs anteriores desta playlist.

O que você vai aprender

• 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 das 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 as Planilhas Google. Antes de iniciar este codelab, conclua os anteriores:

1. Macros e funções personalizadas
2. Planilhas, páginas e intervalos

O que é necessário

• Conhecimento dos tópicos básicos do Apps Script abordados nos codelabs anteriores desta playlist.
• Conhecimento básico do editor do Apps Script
• Conhecimento básico das Planilhas Google.
• Capacidade de ler a notação A1 do Google Sheets
• Conhecimento básico de JavaScript e da classe String.

2. Configurar

Os exercícios deste codelab exigem uma planilha para trabalhar. Siga estas etapas para criar uma planilha que será usada nestes exercícios:

1. Crie uma planilha no Google Drive. Para fazer isso, selecione Novo > Planilhas Google na interface do Drive. A planilha será criada e aberta. O arquivo é salvo na sua pasta do Drive.
2. Clique no título da planilha e mude de "Planilha sem título" para "Manipulação de dados e menus personalizados". Sua planilha vai ficar assim:

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

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

Observação:se você estiver usando uma conta gmail.com, talvez apareça uma caixa de diálogo "Este app não foi verificado" quando você usar o script pela primeira vez. O Google usa isso para alertar os usuários que podem estar usando código de autores desconhecidos ou não confiáveis. Se essa caixa de diálogo aparecer, você pode continuar, já que é 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 Acessar manipulação de dados e menus personalizados (não seguro).
3. Clique em Permitir.

Ao longo deste codelab, você pode receber várias solicitações de permissão. Leia mais sobre esse processo em Autorização para os 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 aparecem nas Planilhas Google. Você também pode usar menus personalizados nos 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 projeto de script. Em seguida, adicione o menu à interface para que ele apareça nas 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 maneira rápida de executar 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 ele ou o Apps Script funciona. Para eles, é apenas mais um item do cardápio.

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

4. A função onOpen()

Os gatilhos simples no Apps Script oferecem uma maneira de executar um código específico do Apps Script em resposta a determinadas condições ou eventos. Ao criar um acionador, você define qual evento o faz ser disparado e fornece uma função do Apps Script que é executada para o evento.

onOpen() é um exemplo de um gatilho simples. É fácil configurar: basta escrever uma função do Apps Script chamada onOpen(), e o Apps Script a executa sempre que a planilha associada é aberta ou recarregada:

/**
 * 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 principal: os acionadores simples são fáceis de configurar e usar, mas têm várias restrições. Devido a essas limitações, onOpen() é usado apenas para criar itens de menu.

Os gatilhos são um assunto 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ê carrega uma planilha.

Implementação

Vamos criar um menu personalizado.

1. Substitua o código no 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 o projeto de script.

Revisão de código

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

As três linhas seguintes criam o menu (Book-list), adicionam um item (Load Book-list) a ele e, em seguida, adicionam o menu à interface da planilha. Isso é feito com os métodos createMenu(caption), addItem(caption, functionName) e addToUi(), respectivamente.

O método addItem(caption, functionName) cria uma conexão entre o rótulo do item de menu e a função do Apps Script que é executada quando o item é selecionado. Nesse caso, selecionar o item de menu Load Book-list faz com que o app tente executar a função loadBookList(), que ainda não existe.

Resultados

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

1. No Planilhas Google, recarregue a planilha. Observação: isso geralmente fecha a guia com o editor de scripts.
2. Reabra o editor de script selecionando Ferramentas > Editor de script.

Depois que a planilha recarregar, o novo menu Book-list vai aparecer na barra de menus:

Ao clicar em Lista de livros, você vai ver o menu resultante:

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

5. Importar dados de uma planilha

Agora que você criou um menu personalizado, é possível criar funções que podem ser executadas ao clicar no item do menu.

No momento, o menu personalizado Book-list tem um item: Load Book-list.. A função chamada quando você seleciona o item de menu Load Book-list, loadBookList(),, não existe no script. Portanto, ao selecionar Lista de livros > Carregar lista de livros, um erro é gerado:

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

Implementação

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

1. Adicione o seguinte código ao 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 o 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 apresentadas nos codelabs anteriores. Com esses conceitos em mente, você pode dividir o código loadBookList() nas quatro seções a seguir:

1: Identifique a planilha de destino

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

2: identificar os dados de origem

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

• bookSS armazena uma referência à planilha de onde o código está lendo os dados. O código encontra a planilha pelo ID. Neste exemplo, fornecemos o ID de uma planilha de origem para leitura e abrimos a planilha usando o método SpreadsheetApp.openById(id).
• bookSheet armazena uma referência a uma página em bookSS que contém os dados desejados. O código identifica a planilha a ser lida pelo nome, codelab-book-list.
• bookRange armazena uma referência a um intervalo de dados em bookSheet. O método Sheet.getDataRange() retorna o intervalo que contém todas as células não vazias na planilha. É uma maneira fácil de garantir que você tenha um intervalo que abranja todos os dados em uma planilha 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 dados da planilha de origem.

3: Copiar os dados da origem para o destino

A próxima seção de código copia os dados bookListValues para sheet e renomeia a planilha:

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

4: Formatar a planilha de destino

O Sheet.setName(name) é usado para mudar o nome da planilha de destino para Book-list. A última linha da função usa Sheet.autoResizeColumns(startColumn, numColumns) para redimensionar as três primeiras colunas na planilha de destino, facilitando a leitura dos novos dados.

Resultados

Você pode ver essa função em ação. No Planilhas Google, selecione Lista de livros > Carregar lista de livros 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 de ISBN de 13 dígitos. Na próxima seção, você vai aprender a modificar e atualizar os dados nessa 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, também é possível notar alguns problemas com esses dados brutos:

1. Em algumas linhas, o título e o autor são colocados juntos na coluna de título, vinculados por uma vírgula ou pela string "de ".
2. Algumas linhas não têm o título ou o autor do livro.

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

7. Adicionar itens do cardápio

Crie três itens de menu para controlar as operações de limpeza de dados que você vai implementar.

Implementação

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

1. No projeto de 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 o projeto de script.
3. No editor de scripts, selecione onOpen na lista de funções e clique em Executar. Isso vai executar onOpen() para recriar o menu da planilha sem que você precise recarregá-la.

Nesse novo código, o método Menu.addSeparator() cria um divisor horizontal no menu para manter os grupos de itens relacionados visualmente organizados. Os novos itens de menu são adicionados abaixo dele, 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 Google Sheets na mesma ordem em que são adicionados no código onOpen().

Resultados

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

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

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 estão combinados incorretamente em uma célula usando uma vírgula:

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

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

A função splitAtFirstComma() precisa seguir estas etapas:

1. Recebe o intervalo que representa as células selecionadas no momento.
2. Verifique se as células no intervalo têm uma vírgula.
3. Quando houver vírgulas, divida a string em duas (e apenas duas) substrings no local da primeira vírgula. Para simplificar, considere que qualquer vírgula indica um padrão de string "[autores], [título]". Você também pode presumir que, se várias vírgulas aparecerem na célula, é apropriado dividir na primeira vírgula da string.
4. Defina as substrings como o novo conteúdo das respectivas células de título e autor.

Implementação

Para implementar essas etapas, use os mesmos métodos do serviço de planilhas que você já usou, mas também precisará usar JavaScript para manipular os dados de string. Siga estas etapas:

1. No editor do Apps Script, adicione a seguinte função ao final do projeto de 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 o projeto de script.

Revisão de código

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

1: extrair os valores de título destacados

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

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

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

A próxima seção examina os valores em titleAuthorValues para encontrar vírgulas. Um loop for do JavaScript é 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 da célula é copiado para a variável titlesAndAuthors.
2. A posição da vírgula é determinada usando o método JavaScript String indexOf().
3. O método JavaScript String slice() é chamado duas vezes para extrair a substring antes e depois do delimitador de vírgula.
4. As substrings são copiadas de volta para a matriz bidimensional titleAuthorValues, substituindo os valores existentes nessa posição. Como estamos supondo um padrão "[autores], [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 para a planilha

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

Importante: o código poderia ter atualizado cada célula da planilha individualmente ao encontrar cada vírgula, chamando Range.setValue(value) sempre 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) exige comunicação com o servidor.

Ao atualizar dados do Google Sheets, é quase sempre melhor colocar os valores em uma matriz 2D, atualizar a matriz e enviar os dados de volta para a planilha com uma única chamada Range.setValues(values).

Resultados

Agora você pode ver os efeitos da função splitAtFirstComma() em ação. Para testar, selecione o item de menu Separar título/autor na primeira vírgula depois de selecionar...

...uma célula:

...ou 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 definidas. No entanto, se o código encontrar um delimitador de vírgula, ele vai substituir a célula imediatamente à direita da original.

Agora você criou uma função do Apps Script que processa dados das Planilhas. Em seguida, você vai implementar a segunda função de divisor.

9. Dividir texto em delimitadores "por"

Analisando os dados originais, você pode notar outro problema. Assim como alguns formatos de dados incluem títulos e autores em uma única célula como "[autores], [título]", outras células formatam autor e título como "[título] de [autores]":

Implementação

Para resolver esse problema, use a mesma técnica da última seção e crie uma função chamada splitAtLastBy(). Essa função tem uma tarefa 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 final do projeto de 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 o 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 um 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 " by " na string inicial, todas, exceto a última " by ", serão consideradas parte do título.
3. Depois de dividir a string, a primeira substring é definida como o título e a segunda como o autor (essa é a ordem oposta de splitAtFirstComma()).

Resultados

Agora você pode ver os efeitos da função splitAtLastBy() em ação. Para executar, selecione o item de menu Separar título/autor no último "por" depois de selecionar...

...uma célula:

...ou várias células:

Você concluiu esta seção do codelab. Agora é possível usar o Apps Script para ler e modificar dados de string em uma planilha e usar menus personalizados para executar diferentes comandos do Apps Script.

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

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

Até agora, você refinou 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 recuperar os dados ausentes usando operações de string nos dados que você tem. Em vez disso, você precisará buscar os dados ausentes em outra fonte. É possível 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 aplicativos. É um termo geral, mas basicamente um serviço que seus programas e scripts podem chamar para solicitar informações ou realizar determinadas ações. Nesta seção, você vai chamar uma API disponível publicamente para solicitar informações de livros que podem ser inseridas nas células vazias da planilha.

Nesta seção, você vai aprender a:

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

11. Buscar dados externos com UrlFetch

Antes de se aprofundar no código que funciona diretamente com sua planilha, aprenda 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 Open Library (link em inglês) é um arquivo da Internet que se esforça para criar uma página para cada publicação de livro. Se quiser, leia mais sobre a documentação da API no site oficial.

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 ele. Ele se conecta e recupera informações da API Open Library e analisa o objeto JSON retornado.

Implementação

Implemente essa função auxiliar fazendo o seguinte:

1. No editor do Apps Script, adicione o seguinte código ao final 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 o projeto de script.

Observação:no Apps Script, os nomes de função que terminam em _ (um sublinhado) são considerados particulares. Outros scripts não podem chamar essas funções quando presentes em uma biblioteca ou por clientes durante a comunicação entre servidor e cliente. No entanto, esses são tópicos avançados. Portanto, se você souber que uma função será usada apenas pelo script atual, a prática recomendada é terminar 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 pública da Open Library 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. Ele 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, você fornece o número ISBN do livro e pede que informações detalhadas sejam 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, é necessário seguir os formatos definidos por ela. Para a 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 é 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 de ler para humanos.

Depois de criar a string de URL, o código envia uma solicitação para o local e recebe uma resposta. Isso é feito com o método UrlFetchApp.fetch(url, params). Ele envia uma solicitação de informações para o 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 o código não será interrompido se a solicitação resultar em um erro de API. Em vez disso, a resposta de erro é 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 aqui são o conteúdo JSON principal. Portanto, o código precisa extrair 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 três últimas 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 que diferentes partes dos dados possam ser extraídas facilmente. Por fim, a função retorna os dados correspondentes ao número ISBN do livro.

Observação: se você quiser se familiarizar com JSON, acesse a documentação do JavaScript para trabalhar com JSON ou a referência da classe JSON.

Resultados

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

12. Gravar dados da API em uma planilha

Agora, você pode implementar uma função fillInTheBlanks() que faz o seguinte:

1. Identifique os dados de título e autor que estão faltando no período 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 valores ausentes de título ou autor nas células respectivas.

Implementação

Implemente essa nova função fazendo o seguinte:

1. No editor do Apps Script, adicione o seguinte código ao final do projeto de 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 o projeto de script.

Revisão de código

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

1: leia 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 duas linhas seguintes, a variável bookValues é usada para manter uma cópia local das informações do livro da planilha. O código vai ler informações de bookValues, usar a API para preencher as informações ausentes e gravar esses valores de volta na planilha.

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

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

O código faz um loop em cada linha de 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 as seguintes condições forem verdadeiras:

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

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

A única tarefa restante é adicionar as informações de bookData à planilha. No entanto, há uma ressalva. Infelizmente, APIs públicas como a Open Library Book API às vezes não têm as informações solicitadas ou podem ter algum 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á-la. Depois que o código tem bookData, ele realiza uma verificação simples para verificar se bookData e bookData.details existem antes de tentar ler deles. Se algum deles estiver faltando, significa que a API não tinha os dados que você queria. Nesse caso, o comando continue diz ao código para pular essa linha. Não é possível preencher as células ausentes, mas pelo menos o script não vai falhar.

3: gravar as informações atualizadas de volta na planilha

A última parte do código tem verificações semelhantes para verificar o título e as informações do autor retornados pela API. O código só atualiza a matriz bookValues se a célula original de título ou autor estiver vazia e a API tiver retornado um valor que possa ser colocado ali.

O loop é encerrado depois que todas as linhas da planilha são examinadas. A última etapa é gravar a matriz bookValues atualizada de volta na planilha usando Range.setValues(values).

Resultados

Agora você pode concluir a limpeza dos dados do livro. Faça o seguinte:

1. Se ainda não fez isso, destaque o intervalo A2:A15 na sua planilha e selecione Lista de livros > Separar título/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 > Separar título/autor no último "por" para limpar os problemas de "por".
3. Para preencher todas as células restantes, selecione Lista de livros > Preencher células em branco de título e autor:

13. Conclusão

Parabéns por concluir este codelab. Você aprendeu a criar menus personalizados para ativar diferentes partes do código do Apps Script. Você também aprendeu a importar dados para o Google Sheets usando serviços do Apps Script e 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, você aprendeu a usar os serviços do 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

• Como importar dados de uma planilha 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 gatilhos simples têm várias restrições que precisam ser seguidas.
2. Ao ler ou gravar dados de células de planilhas, a prática recomendada é ler ou gravar um intervalo inteiro de uma vez com Range.getValues() ou Range.setValues(values). Não leia nem defina cada célula individualmente, porque isso vai ser mais lento.
3. Os nomes de funções do Apps Script que terminam em _ (um sublinhado) são considerados particulares. Outros scripts não podem chamar essas funções quando elas estão em uma biblioteca ou por clientes usando a comunicação entre servidor e cliente. Se você souber que uma função será usada apenas pelo script atual, é recomendável terminar o nome dela com _.
4. Ao usar dados retornados por APIs externas, valide-os antes de tentar usá-los. Isso reduz os erros no código se a API retornar um resultado inesperado.

Termos-chave:

• Ativo (status): indica que a planilha, a página, o intervalo ou a célula especificada é aquela que o usuário da planilha está visualizando ou destacando no momento.
• Célula ativa: a única célula destacada na planilha ativa com o foco do cursor.
• Intervalo ativo: um grupo de uma ou mais células destacadas na planilha ativa.
• API: uma interface de programação do aplicativo é um serviço que programas ou scripts podem chamar para solicitar informações ou realizar determinadas ações.
• Autorização: o processo em que o usuário concede permissões para permitir que o Apps Script acesse dados do usuário nos Serviços do Google relevantes ou execute ações em nome dele.
• Script vinculado ao contêiner: qualquer script vinculado e criado em um documento do Google Workspace, como uma planilha ou um documento Google.
• Função auxiliar: uma função, geralmente particular, que é chamada por outra para concluir uma pequena subtarefa. As funções auxiliares são usadas quando a subtarefa precisa ser concluída muitas vezes em lugares diferentes.
• JSON: formato de arquivo para definir objetos de dados e suas características com base em texto.
• onOpen(): se definido em um script, é um gatilho simples que é acionado sempre que um arquivo é aberto ou recarregado.
• Funções particulares: uma função que não pode ser chamada como parte de uma biblioteca ou por clientes por meio da comunicação entre servidor e cliente. 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 planilha. A classe Range** permite ler e atualizar intervalos em uma planilha.
• 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ções de acionamento reservados e determinadas restrições.
• Página**: uma única página de uma planilha. Às vezes, usado para se referir a um arquivo das Planilhas Google no Google Drive. A classe Sheet** permite acessar e modificar planilhas.
• SpreadsheetApp: essa classe serve como classe mãe do serviço de planilhas e oferece um ponto de partida para o código que lê ou manipula dados das Planilhas Google.
• Planilha**: um arquivo das Planilhas Google que fica no Google Drive. A classe Spreadsheet** permite acessar e modificar planilhas.
• Serviço de planilhas: um serviço do Apps Script que permite que os scripts criem, acessem e modifiquem arquivos das Planilhas Google.
• Acionadores: um evento associado a uma função do Apps Script. Quando o evento (como a abertura de uma planilha) ocorre, o gatilho é acionado e executa 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 com mais detalhes como formatar dados em uma planilha.

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