Trabalhando com Dados

Configurando

Os exercícios neste codelab requerem uma planilha para funcionar. Siga estas etapas para criar uma nova planilha para usar nestes exercícios:

Crie uma nova planilha em seu Google Drive. Você pode fazer isso na interface do Drive selecionando Novo> Planilhas Google . A nova planilha é colocada em seu Drive raiz por padrão.
Clique no título da planilha e altere de “Planilha sem título” para “Manipulação de dados e menus personalizados”. Sua planilha deve ser semelhante a esta:

Selecione Ferramentas> Editor de scripts para abrir o editor de scripts.
Clique no título do projeto e altere-o de “Projeto sem título” para “Manipulação de dados e menus personalizados”. Clique em OK para salvar a alteração do título:

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

Observação: se você estiver usando uma conta gmail.com , poderá ver uma caixa de diálogo “Este aplicativo não foi verificado” ao usar o script pela primeira vez. O Google usa isso para avisar os usuários que podem estar usando código de autores desconhecidos ou não confiáveis. Se você vir esta caixa de diálogo, não há problema em continuar, pois você é o autor do script. Nesses casos, continue autorizando o script fazendo o seguinte:

Na caixa de diálogo “Este aplicativo não foi verificado”, clique em Avançado .
Clique em Ir para manipulação de dados e menus personalizados (inseguro).
Na próxima tela, clique em Permitir .

Ao longo deste codelab, você pode ver vários prompts de permissão. Siga as instruções na tela para continuar autorizando o código. Você pode ler mais sobre esse processo no guia de autorização do Apps Script .

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

O Apps Script oferece a capacidade de definir menus personalizados que podem aparecer no Planilhas Google. Você também pode usar menus personalizados no Google Docs, Google Slides e Google Forms. Ao definir um item de menu personalizado, você cria um rótulo de texto e o conecta a uma função do Apps Script em seu projeto de script. Quando você adiciona o menu à IU, ele aparece no Planilhas Google:

Exemplo de menu personalizado do Planilhas

Quando um usuário clica em um item de menu personalizado, a função do Apps Script associada a ele é executada. Esta é uma maneira prática de fazer com que as funções do Apps Script sejam executadas sem ter que 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 funcionam – para eles, é apenas mais um item de menu.

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

A função onOpen ()

Os gatilhos no Apps Script fornecem uma maneira de executar códigos específicos do Apps Script em resposta a condições ou eventos específicos. Ao criar um gatilho, você define qual evento faz com que o gatilho seja disparado e fornece uma função do Apps Script que é executada quando esse evento acontece.

onOpen()é um exemplo de gatilho simples . Gatilhos simples são fáceis de configurar – tudo o que você precisa fazer é escrever uma função do Apps Script chamada onOpen() e o Apps Script a executará sempre que a planilha associada for 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() {
 /* ... */ 
}

Implementação

Vamos criar um menu personalizado!

Substitua o código em 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();
}

Salve seu projeto de script.

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 Ui objeto que representa a interface do usuário da planilha ativa à qual este script está vinculado.

As próximas três linhas criam um novo menu ( Book-list), adiciona um item de menu ( Load Book-list) a esse menu e, em seguida, adiciona o menu à interface da 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. Nesse caso, a seleção do Load Book-list item de menu faz com que o Planilhas tente executar a loadBookList() função (que ainda não existe).

Resultados

Vamos executar esta função agora para ver se funciona! Faça o seguinte:

No Planilhas Google, recarregue sua planilha. Nota: isso geralmente fecha a guia com seu editor de script.
Reabra seu editor de script selecionando Ferramentas> Editor de script .

Depois que a planilha for recarregada, você verá um novo menu chamado Book-list deve aparecer na barra de menus:

Menu personalizado: lista de livros

Ao clicar na lista de livros , você pode ver o menu suspenso resultante:

E é assim que você pode criar seu próprio menu personalizado no Planilhas! A próxima seção define a loadBookList() função e apresenta uma maneira de interagir com os dados no Apps Script: lendo outras planilhas.

Importando dados da planilha

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

No momento, o menu personalizado Book-list tem um item de menu: Load Book-list. Mas, a função chamada quando você escolhe o Load Book-list item de menu loadBookList(), não existe em seu script, portanto, selecionar Lista de livros> Carregar lista de livros gera um erro:

Você pode corrigir esse erro implementando a loadBookList() função.

Implementação

Você deseja que o novo item de menu preencha a planilha com dados para trabalhar, então implementará loadBookList() para ler dados de outra planilha e copiá-los para esta:

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

/** 
 * Creates a template book list based off of the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive via 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 that already
  // exist 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);
}

Salve seu projeto de script.

Revisão de código

Então, como essa função funciona? As loadBookList() utilizações de função principalmente métodos de as Spreadsheet, Sheet e Range as classes que os codelabs anteriores introduzidas. Com esses conceitos em mente, você pode dividir o loadBookList() código nas quatro seções a seguir:

1: Identifique a folha de destino

A primeira linha usa SpreadsheetApp.getActiveSheet() para obter e armazenar uma referência ao objeto de pasta atual na variável sheet. É aqui que os dados serão copiados.

2: Identifique 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 da qual o código está lendo os dados. O código encontra a planilha por seu ID de planilha . Neste exemplo, fornecemos o ID de uma planilha de origem para leitura e abrimos a planilha usando o SpreadsheetApp.openById(id) método.
bookSheet armazena uma referência a uma planilha bookSS que contém os dados desejados. O código identifica a folha a ser lida por seu nome codelab-book-list.
bookRange armazena uma referência a um intervalo de dados em bookSheet. O método Sheet.getDataRange() retorna o intervalo contendo todas as células não vazias na planilha. É uma maneira prática de garantir que você obtenha um intervalo que cubra todos os dados de uma planilha sem incluir muitas linhas e colunas vazias.
bookListValues é uma matriz 2D que contém todos os valores retirados das células em bookRange. O Range.getValues() método gera essa matriz lendo dados da planilha de origem.

3: Copie os dados da origem ao destino

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

Sheet.getRange(row, column, numRows, numColumns) é usado para identificar onde copiar os dados 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 o array 2D bookListValues para o intervalo de destino, substituindo todos os dados já existentes.

4: Formate a folha de destino

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

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 de preencher sua planilha:

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

Visão geral: limpeza de dados da planilha

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

Para algumas linhas, o título e o autor são colocados juntos na coluna do título, ligados por uma vírgula ou pela string “por”.
Algumas linhas não contêm informações sobre os títulos e autores dos livros.

próximas seções, você corrigirá esses problemas limpando os dados. Para a primeira edição, você criará funções que leem a coluna do título e dividem o texto sempre que uma vírgula ou delimitador “por” for encontrada, colocando o autor e as substrings de título correspondentes nas colunas corretas. Para o segundo problema, você escreverá um código que procura automaticamente as informações do livro ausentes usando uma API externa e preenche essas informações em sua planilha.

Adicionando itens de menu

Você desejará criar três novos itens de menu para controlar as operações de limpeza de dados que implementará.

Implementação

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

Em seu projeto de script, atualize seu onOpen() código para corresponder ao seguinte:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is usually 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();
}

Salve seu projeto de script.
No editor de script, selecione onOpen no 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.

Neste novo código, o Menu.addSeparator() método cria um divisor horizontal no menu suspenso para que você possa manter grupos de itens de menu relacionados visualmente organizados. Os novos itens de menu são então adicionadas 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 adicionados serão exibidos no Planilhas na mesma ordem em que você os adicionar ao onOpen() código.

Resultados

Em sua planilha, você pode clicar no Book-list menu para ver os novos itens de menu:

Clicar nesses itens agora causará um erro, pois você ainda não implementou suas funções correspondentes, então vamos fazer isso a seguir.

Divisão de texto em delimitadores de vírgula

O conjunto de dados que você importou para sua 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 Planilhas Google oferece uma SPLIT() função que divide as strings em colunas. No entanto, geralmente os conjuntos de dados apresentam problemas que não podem ser facilmente resolvidos com as funções do Planilhas. Nesses casos, você pode escrever o código do Apps Script para realizar as operações complexas necessárias para limpar e organizar seus dados.

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

A splitAtFirstComma() função deve seguir as seguintes etapas:

Obtenha o intervalo que representa as células atualmente selecionadas.
Verifique se as células nesse intervalo possuem uma vírgula.
Onde forem encontradas vírgulas, divida a string em duas (e apenas duas) substrings no local da primeira vírgula. Para tornar as coisas mais simples, você pode assumir que qualquer vírgula indica um padrão de string ” [autores], [título] “. Você também pode assumir que, se várias vírgulas aparecerem na célula, é apropriado dividir na primeira vírgula da string.
Defina as substrings como o novo conteúdo das respectivas células de título e autor.

Implementação

Para implementar essas etapas, você usará os mesmos métodos de serviço de planilha usados antes, mas também precisará usar um JavaScript simples para manipular os dados da string. Execute as seguintes etapas:

No editor do Apps Script, adicione a seguinte função ao final do seu 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);
}

Salve seu projeto de script.

Revisão de código

Vamos revisar o novo código. O código consiste em três seções principais:

1: Recupere os valores do título destacado

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 destacou no momento quando a splitAtFirstComma() função foi chamada. Para simplificar este exercício, podemos assumir 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 activeRange, mas também inclui mais uma coluna à direita. titleAuthorRange é criado usando o Range.offset(rowOffset, columnOffset, numRows, numColumns) método. O código precisa desse intervalo expandido porque precisa de um lugar para colocar os autores descobertos que encontrar na coluna do título.
titleAuthorValues é uma matriz 2D de dados que foram extraídos do titleAuthorRange uso Range.getValues().

2: Examine cada título e divida em qualquer delimitador de vírgula encontrado

A próxima seção examina os valores titleAuthorValuespara procurar vírgulas. Um loop for JavaScript é usado para examinar todos os valores na primeira coluna de titleAuthorValues. Quando uma substring de vírgula é encontrada ( ", ") com o método indexOf () da String JavaScript , o código faz o seguinte:

O valor da string da célula é copiado para a titlesAndAuthorsvariável.
A localização da vírgula é determinada usando o método indexOf () da String JavaScript .
O método slice () de String JavaScript é usado duas vezes para obter a substring antes do delimitador de vírgula e a substring depois do delimitador.
As substrings são copiadas de volta para o array 2D titleAuthorValues, sobrescrevendo os valores existentes naquela posição. Como estamos assumindo 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 dessa linha inalterados.

3: Copie os novos valores de volta para a planilha

Depois que todos os valores da célula do título foram examinados, a matriz 2D titleAuthorValues atualizada é copiada de volta para a planilha usando

o Range.setValues(values) método.

Resultados

Agora você pode ver os efeitos da splitAtFirstComma() função em ação! Tente executá-lo selecionando o item de menu Separar título / autor na primeira vírgula após selecionar …

… uma célula:

… ou várias células:

Agora você construiu uma função do Apps Script que processa dados do Planilhas Google! Em seguida, você implementará a segunda função divisora.

Dividindo o texto em delimitadores “por”

Olhando novamente para os dados originais, você pode ver outro problema. Assim como alguns dos dados formatam títulos e autores em uma única célula como “[autores], [título]”, outras células formatam autor e título como “[título] por [autores] : “

Implementação

Você pode resolver este problema usando a mesma técnica usada na última seção, criando uma nova função chamada splitAtLastBy(). Essa função tem uma função muito semelhante a splitAtFirstComma()– a única diferença real é que ela está procurando um padrão de texto ligeiramente diferente. Implemente esta função fazendo o seguinte:

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

/** 
 * Reformats title and author columns by splitting title column
 * at the last instance of the string " by ".
 */
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 an "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
      // our array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

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

Salve seu projeto de script.

Revisão de código

Existem algumas diferenças importantes entre este código e splitAtFirstComma():

A substring ” by ” é usada como um delimitador de string, em vez de ” , “.
Aqui, o JavaScript String.lastIndexOf(substring) é usado em vez de String.indexOf(substring). Isso significa que se houver várias by substrings ” ” na string inicial, todas, exceto o último ” by“, serão consideradas parte do título.
Depois de dividir a string, a primeira substring é definida como o título e a segunda como a string do autor (esta é a ordem oposta à usada splitAtFirstComma()).

Resultados

Agora você pode ver os efeitos da splitAtLastBy() função em ação! Tente executá-lo selecionando o item de menu Separar título / autor finalmente “por” depois de selecionar …

… uma célula:

… ou várias células:

Você concluiu esta seção do codelab. Agora você pode usar o Apps Script para ler e modificar os dados da string em uma planilha e usar menus personalizados para executar diferentes comandos do Apps Script!

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

Visão geral: Obtenção de 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 o conjunto de dados ainda está faltando algumas informações, destacadas nas células abaixo:

Os dados ausentes não podem ser obtidos por operações de string nos dados que você tem aqui. Em vez disso, você precisará obter os dados ausentes de outra fonte. Nesse caso, você pode fazer isso no Apps Script solicitando informações de uma API externa que pode fornecer ao seu script informações adicionais sobre o livro.

APIs são interfaces de programação de aplicativos . É um termo bastante geral, mas basicamente significa que alguém forneceu um serviço que seus programas e scripts podem chamar para fazer coisas específicas, como solicitar informações ou realizar certas ações. Nesta seção, você está chamando uma API disponível publicamente para solicitar informações adicionais do livro, que você então coloca nas células vazias de sua planilha.

Esta seção ensina como:

Solicite dados do livro de uma fonte de API externa.
Extraia as informações do título e do autor dos dados retornados e grave essas informações em sua planilha.

Buscando dados externos com UrlFetch

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

Essa função auxiliar fetchBookData_(ISBN) pega um número ISBN de 13 dígitos de um livro como parâmetro e retorna dados sobre esse livro conectando-se e recuperando informações da API Open Library e, em seguida, analisando o objeto JSON retornado .

Implementação

Implemente essa função auxiliar fazendo o seguinte:

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

/*
 * Helper function that retrieves book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to look up.
 * @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];
}

Salve seu projeto de script.

Revisão de código

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

1: Fazendo uma solicitação de API

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

A url variável é apenas uma string de URL, como um endereço da web. Ele aponta para um local nos servidores Open Library. Também inclui três parâmetros ( bibkeys, jscmde 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 para identificá-lo e pede que as informações detalhadas sejam retornadas no formato JSON.

Depois de criar a string do URL, o código envia uma solicitação para esse local e recebe uma resposta. Isso é feito com o UrlFetchApp.fetch(url, params) método. Este método envia uma solicitação de informação ao URL externo que você fornece e armazena a resposta resultante na response variável. Além do URL, o código define o parâmetro opcional muteHttpExceptions como true. Essa configuração significa apenas que seu código não parará se as solicitações resultarem em um erro de API; em vez disso, a resposta de erro é retornada.

A solicitação retorna um HTTPResponse objeto que está armazenado na response variável. As respostas HTTP incluem um código de resposta, cabeçalhos HTTP e o conteúdo da resposta principal. As informações de interesse aqui são o conteúdo JSON principal, portanto, o código deve extraí-lo e, em seguida, 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 do código, o HTTPResponse.getContentText() método retorna o conteúdo principal da resposta como uma string. Esta string está no formato JSON, mas o conteúdo e formato exatos são definidos pela API Open Library. O JSON.parse(jsonString) método converte a string JSON em um objeto JavaScript para que diferentes partes dos dados possam ser facilmente extraídas. Por fim, a função retorna a parte dos dados correspondente ao número ISBN do livro de interesse.

Resultados

Agora que você implementou fetchBookData_(ISBN), outras funções em seu código podem rapidamente pesquisar informações de qualquer livro usando seu número ISBN. Você usará esta função para ajudar a preencher os espaços em branco em sua planilha.

Gravando dados da API em uma planilha

Agora você pode implementar uma nova fillInTheBlanks() função que fará o seguinte:

Identifique o título ausente e os dados do autor dentro do intervalo de dados ativo.
Recupere os dados ausentes de um livro específico chamando a API Open Library por meio do fetchBookData_(ISBN) método auxiliar.
Atualize o título ausente e / ou os valores do autor em suas respectivas células.

Implementação

Implemente essa nova função fazendo o seguinte:

No editor do Apps Script, adicione o seguinte código ao final do seu 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 current 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 are 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 further.
      if (!bookData || !bookData.details) {
        continue;
      }

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

      // The API might not have an author name, so only fill it in
      // if the API returns one and 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; 
      }
    }
  }
  
  // Put the updated book data values back into the spreadsheet.
  dataRange.setValues(bookValues);   
}

Salve seu projeto de script.

Revisão de código

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

1: Lendo as informações do livro existente

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

Observação : esse arranjo é diferente do que você usou para splitAtFirstComma() e splitAtLastBy(). Nessas funções, o código examinava apenas os títulos atualmente destacados e suas linhas. Aqui, você está examinando a folha inteira usando Spreadsheet.getDataRange().

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

O código então percorre cada linha bookValues para procurar títulos ou autores ausentes. Para reduzir o número de chamadas de API que o código deve fazer (e assim evitar perda de tempo), o código apenas chama a API se o seguinte for verdadeiro:

A linha tem um valor na coluna ISBN (ou seja, o ISBN do livro é conhecido).
O título ou a célula do autor nessa linha estão vazios.

Se todas as condições forem verdadeiras, o código chama a API usando a fetchBookData_(isbn) função auxiliar que você implementou e armazena o resultado na bookData variável. Esta variável agora deve ter as informações que faltam que você deseja escrever de volta na planilha.

A única tarefa que resta agora é adicionar as bookData informações à nossa planilha. No entanto, há uma ressalva. Infelizmente, APIs públicas como a API Open Library Book às vezes não têm as informações solicitadas ou, ocasionalmente, podem ter algum outro problema que o impede de fornecer essas informações. Se você assumir cegamente 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 seja robusto em face de erros da API, o código deve verificar se a resposta da API é válida antes de tentar usá-la. Uma vez que o código tem bookData, ele conduz um simples verifica seleção que bookData e bookData.details existem antes de tentar ler a partir deles. Se algum estiver faltando, significa que a API não possui os dados que você deseja. Nesse caso, o continue comando diz ao código para pular essa linha – você não pode preencher as células ausentes, mas pelo menos seu script não travará.

3: Escrever informações atualizadas de volta na planilha

A próxima parte do código tem verificações semelhantes que verificam o título retornado da API e as informações do autor. O código só atualiza a bookValuesmatriz se o título original ou a célula do autor estiver vazia e a API retornar um valor que você pode colocar lá.

O loop sai depois que todas as linhas da planilha são examinadas. A última etapa é escrever o bookValuesarray agora atualizado de volta na planilha usando Range.setValues(values).

Resultados

Agora você pode terminar de limpar os dados do seu livro! Faça o seguinte:

Se ainda não o fez, realce o intervalo A2: A15 em sua planilha e selecione Lista de livros> Título / autor separado na primeira vírgula para limpar os problemas de vírgula.
Se ainda não o fez, realce o intervalo A2: A15 em sua planilha e selecione Lista de livros> Título / autor separado por último “por” para limpar os problemas “por”.
Selecione Lista de livros> Preencher títulos em branco e células de autor para que seu código preencha todas as células restantes:

O que você aprendeu?

Como importar dados de uma planilha do Google.
Como criar um menu personalizado em onOpen().
Como analisar e manipular valores de dados de string.
Como chamar APIs públicas usando o serviço UrlFetch .
Como analisar dados do objeto JSON recuperados de uma fonte de API pública.

Conceitos-chave:

onOpen()é um gatilho simples usado para criar itens de menu. Os gatilhos simples têm uma variedade de restrições que você deve seguir.
Ao ler ou gravar dados de célula de planilha, é uma prática recomendada ler ou gravar um intervalo inteiro de uma vez com Range.getValues() ou Range.setValues(values). Não leia ou configure cada célula individualmente, pois isso será muito mais lento.
As funções do Apps Script com nomes terminados em “_” são consideradas privadas . Essas funções não podem ser chamadas por outros scripts se incluídas como parte de uma biblioteca ou por clientes por meio da comunicação cliente-servidor . Se você sabe que uma função só será usada pelo script em que está, geralmente é uma boa ideia terminar seu nome com “_”.
Ao usar dados retornados por APIs externas, verifique se os dados retornados são válidos antes de tentar usá-los. Isso diminuirá a probabilidade de seu código ter erros se a API retornar um resultado inesperado.

Termos-chave :

Ativo (status) : indica que a planilha, planilha, intervalo ou célula especificada é a que está sendo exibida ou destacada pelo usuário da planilha.
Célula ativa : a única célula destacada na página ativa que tem o foco do cursor
Intervalo ativo : o grupo de uma ou mais células atualmente destacadas na página ativa.
API : interface de programação de aplicativos; este é um serviço que programas ou scripts podem contatar para recuperar informações ou realizar ações específicas.
Autorização : o processo em que o usuário concede permissões para permitir que o Apps Script acesse dados privados nos serviços relevantes do Google.
Script vinculado ao contêiner : qualquer script vinculado e criado a partir de um documento do G Suite, como uma planilha ou um documento do Google.
Função auxiliar : uma função, geralmente privada, que é chamada por outra pessoa para concluir uma pequena subtarefa. As funções auxiliares geralmente são usadas quando a subtarefa precisa ser realizada muitas vezes em locais diferentes.
JSON : formato de arquivo para definir objetos de dados e suas características de texto.
onOpen(): Se definido em um script, um gatilho simples que dispara sempre que um arquivo é aberto ou recarregado.
Funções privadas : uma função que não pode ser chamada como parte de uma biblioteca ou por clientes através da comunicação cliente-servidor . As funções privadas do Apps Script têm nomes que terminam com “_”.
Intervalo : um intervalo representa um grupo de uma ou mais células adjacentes em uma página. A Range classe oferece a capacidade de ler e atualizar intervalos em uma planilha.
Editor de script : o editor de código do Apps Script.
Gatilhos simples : um subtipo de gatilhos no Apps Script que têm nomes de função de gatilho reservados e certas restrições ; por exemplo onOpen(),.
Folha : uma única página de uma planilha. A Sheet classe permite acessar e modificar planilhas.
SpreadsheetApp: Esta classe serve como classe pai para o serviço de planilha e fornece um ponto de partida para o código que lê ou manipula dados do Planilhas Google.
Planilha : um arquivo do Planilhas Google que reside no Google Drive. A Spreadsheet classe permite que você acesse e modifique planilhas.
Gatilhos : um evento vinculado a uma função do Apps Script. Quando o evento (como uma planilha sendo aberta) ocorre, o acionador “dispara” e executa automaticamente a função do Apps Script associada.
UrlFetch : o serviço Apps Script que permite que os scripts se conectem a endpoints de URL para fazer solicitações e receber respostas.