Aspectos básicos de Apps Script con Hojas de cálculo de Google n.o 3: Cómo trabajar con datos

1. Introducción

Te damos la bienvenida a la tercera parte de la lista de reproducción del codelab de aspectos básicos de Apps Script con Hojas de cálculo de Google.

Al completar este codelab, aprenderás a usar la manipulación de datos, los menús personalizados y la recuperación de datos de la API pública en Apps Script para mejorar tu experiencia con Hojas de cálculo. Seguirás trabajando con las clases SpreadsheetApp, Spreadsheet, Sheet y Range que introdujeron los codelabs anteriores en esta lista de reproducción.

Qué aprenderás

• Cómo importar datos de una hoja de cálculo personal o compartida en Drive
• Cómo crear un menú personalizado con la función onOpen()
• Cómo analizar y manipular los valores de los datos de una string en las celdas de una hoja de cálculo de Google
• Cómo extraer y manipular datos JSON de una fuente de API pública

Antes de comenzar

Este es el tercer codelab de la lista de reproducción Aspectos básicos de Apps Script con Hojas de cálculo de Google. Antes de comenzar este codelab, asegúrate de completar los codelabs anteriores:

1. Macros y funciones personalizadas
2. Hojas de cálculo, hojas de cálculo y rangos

Requisitos

• Comprender los temas básicos de Apps Script que se analizaron en los codelabs anteriores de esta lista de reproducción
• Conocimientos básicos sobre el editor de Apps Script
• Conocimientos básicos de Hojas de cálculo de Google
• Capacidad para leer Hojas de cálculo A1 Notation
• Conocimientos básicos sobre JavaScript y su clase String

2. Configurar

Los ejercicios de este codelab requieren una hoja de cálculo para trabajar. Siga estos pasos para crear una hoja de cálculo y usarla en estos ejercicios:

1. Cree una hoja de cálculo en su unidad de Google Drive. Para hacerlo, selecciona Nuevo archivo de Hojas de cálculo de Google en la interfaz de Drive. Se creará y abrirá la hoja de cálculo nueva. Se guardará el archivo en tu carpeta de Drive.
2. Haz clic en el título de la hoja de cálculo y cámbiala de "Hoja de cálculo sin título" a "Manipulación de datos y menús personalizados". La hoja debería verse de la siguiente manera:

3. Para abrir el editor de secuencias de comandos, haga clic en Extensiones > Apps Script
4. Haz clic en el título del proyecto de Apps Script y cámbialo de "Proyecto sin título" a "Manipulación de datos y menús personalizados". Haz clic en Cambiar nombre para guardar el cambio de título.

Si tiene una hoja de cálculo y un proyecto en blanco, está listo para comenzar el lab. Avance a la siguiente sección para comenzar a aprender sobre los menús personalizados.

Nota: Si usas una cuenta de gmail.com, es posible que veas el mensaje "Esta app no se verificó" cuando uses la secuencia de comandos por primera vez. Google utiliza esta información para advertir a los usuarios que podrían utilizar código de autores desconocidos o no confiables. Si ves este diálogo, te recomendamos continuar, ya que eres el autor de la secuencia de comandos. Sigue las indicaciones en pantalla para seguir autorizando la secuencia de comandos:

1. En el diálogo no verificado de la app, haz clic en Configuración avanzada.
2. Haga clic en Ir a Manipulación de datos y menús personalizados (no seguro).
3. Haga clic en Permitir.

A lo largo de este codelab, es posible que recibas varios mensajes de permiso. Puede obtener más información sobre este proceso en Autorización para los Servicios de Google.

3. Descripción general: Importa datos con un elemento de menú personalizado

Apps Script le permite definir menús personalizados que pueden aparecer en Hojas de cálculo de Google. También puedes usar menús personalizados en Documentos, Presentaciones y Formularios de Google. Cuando define un elemento de menú personalizado, crea una etiqueta de texto y la conecta a una función de Apps Script en su proyecto de secuencia de comandos. Luego, puede agregar el menú a la IU para que aparezca en Hojas de cálculo de Google:

Cuando un usuario hace clic en un elemento de menú personalizado, se ejecuta la función de Apps Script que está asociada con él. Esta es una forma rápida de ejecutar funciones de Apps Script sin tener que abrir el editor de secuencias de comandos. También permite que otros usuarios de la hoja de cálculo ejecuten su código sin tener que saber nada sobre cómo funciona o cómo funciona Apps Script. Para ellos, es solo otro elemento del menú.

Los elementos de menú personalizados se definen en la función onOpen() activador simple, que aprenderás en la siguiente sección.

4. Función onOpen()

Los activadores simples de Apps Script ofrecen una manera de ejecutar código específico de Apps Script en respuesta a ciertas condiciones o eventos. Cuando crea un activador, debe definir qué evento lo activa y proporcionar una función de Apps Script que se ejecute para el evento.

onOpen() es un ejemplo de un activador simple. Son fáciles de configurar. Todo lo que tienes que hacer es escribir una función de Apps Script llamada onOpen() y Apps Script la ejecutará cada vez que se abra o vuelva a cargar la hoja de cálculo asociada:

/**
 * 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() {
 /* ... */ 
}

Concepto clave: Los activadores simples son fáciles de configurar y usar, pero tienen varias restricciones. Debido a estas limitaciones, onOpen() solo se utiliza para crear elementos de menú.

Los activadores son un tema complejo y, por lo tanto, están fuera del alcance de este codelab. Por ahora, puedes pensar en onOpen() como un controlador de eventos que agrega elementos de menú cada vez que cargas una hoja de cálculo.

Implementación

Creemos un menú personalizado.

1. Reemplaza el código del proyecto de la secuencia de comandos con lo siguiente:

/**
 * 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. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Revisemos este código para comprender cómo funciona. En onOpen(), la primera línea usa el método getUi() para adquirir un objeto Ui que representa la interfaz de usuario de la hoja de cálculo activa a la que está vinculada esta secuencia de comandos.

Las siguientes tres líneas crean el menú (Book-list), agregan un elemento de menú (Load Book-list) a ese menú y el menú a la interfaz de la hoja de cálculo. Para ello, se usan los métodos createMenu(caption), addItem(caption, functionName) y addToUi(), respectivamente.

El método addItem(caption, functionName) crea una conexión entre la etiqueta del elemento de menú y la función Apps Script que se ejecuta cuando se selecciona el elemento del menú. En este caso, si seleccionas el elemento de menú Load Book-list, Hojas de cálculo intenta ejecutar la función loadBookList() (que todavía no existe).

Resultados

Ejecute esta función ahora para verificar que funcione:

1. En Hojas de cálculo de Google, vuelve a cargar la hoja de cálculo. Nota: Por lo general, se cierra la pestaña con el editor de secuencias de comandos.
2. Para volver a abrir el editor de secuencias de comandos, seleccione Herramientas > Editor de secuencias de comandos.

Cuando se vuelva a cargar la hoja de cálculo, debería aparecer el nuevo menú Book-list en la barra de menú:

Si haces clic en Lista de libros, verás el menú resultante:

En la siguiente sección, se crea el código para la función loadBookList() y se presenta una manera en la que puedes interactuar con los datos en Apps Script: leyendo otras hojas de cálculo.

5. Importar datos de hojas de cálculo

Ahora que creaste un menú personalizado, puedes crear funciones que se puedan ejecutar haciendo clic en el elemento de menú.

En este momento, el menú personalizado Book-list tiene un elemento de menú: Load Book-list. La función a la que se llama cuando seleccionas el elemento de menú Load Book-list, loadBookList(), no existe en la secuencia de comandos, por lo que si seleccionas Libro de listas > Cargar lista de libros se mostrará un error:

Para corregir este error, implementa la función loadBookList().

Implementación

Si quieres que el elemento de menú nuevo complete la hoja de cálculo con datos para trabajar, debes implementar loadBookList() para leer los datos del libro de otra hoja de cálculo y copiarlos en este:

1. Agrega el siguiente código a tu secuencia de comandos en 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. Guarde el proyecto de la secuencia de comandos.

Revisión de código

¿Cómo funciona esta función? La función loadBookList() usa métodos principalmente de las clases Spreadsheet, Sheet y Range que introdujeron los codelabs anteriores. Con estos conceptos en mente, puedes dividir el código loadBookList() en las siguientes cuatro secciones:

1: Identifica la hoja de destino

La primera línea usa SpreadsheetApp.getActiveSheet() para obtener una referencia al objeto de hoja actual y lo almacena en la variable sheet. Esta es la hoja en la que se copiarán los datos.

2: Identifica los datos de origen

Las siguientes líneas establecen cuatro variables que hacen referencia a los datos de origen que recuperas:

• bookSS almacena una referencia a la hoja de cálculo desde la que lee datos el código. El código encuentra la hoja de cálculo por su ID de hoja de cálculo. En este ejemplo, proporcionamos el ID de una hoja de cálculo de origen para leerla y abrirla con el método SpreadsheetApp.openById(id).
• bookSheet almacena una referencia a una hoja de bookSS que contiene los datos que deseas. El código identifica la hoja desde la que se lee, por su nombre, codelab-book-list.
• bookRange almacena una referencia a un rango de datos en bookSheet. El método Sheet.getDataRange() muestra el rango que contiene todas las celdas no vacías en la hoja. Es una forma sencilla de asegurarte de obtener un rango que cubra todos los datos de una hoja de cálculo sin incluir filas ni columnas vacías.
• bookListValues es un array 2D que contiene todos los valores tomados de las celdas de bookRange. El método Range.getValues() genera este arreglo mediante la lectura de datos de la hoja de origen.

3: Copia los datos del origen al destino

En la siguiente sección de código, se copian los datos de bookListValues en sheet y, luego, se cambia el nombre de la hoja de cálculo:

• Sheet.getRange(row, column, numRows, numColumns) se usa para identificar dónde copiar los datos en sheet.
• Los métodos Range.getHeight() y Range.getWidth() se usan para medir el tamaño de los datos y definir un rango de destino de las mismas dimensiones.
• Range.setValues(values) copia el array 2D de bookListValues en el rango de destino y reemplaza los datos que ya existen allí.

4: Formatea la hoja de destino

Sheet.setName(name) se usa para cambiar el nombre de la hoja de destino a Book-list. La última línea de la función utiliza Sheet.autoResizeColumns(startColumn, numColumns) para cambiar el tamaño de las primeras tres columnas de la hoja de destino, lo que te permite leer los datos nuevos con mayor facilidad.

Resultados

Puedes ver esta función en acción. En Hojas de cálculo de Google, selecciona Lista de libros > Cargar lista de libros para ejecutar la función y completar la hoja de cálculo:

Ahora tienes una hoja con una lista de títulos de libros, autores y números ISBN de 13 dígitos. En la siguiente sección, aprenderás a modificar y actualizar los datos en esta lista de libros mediante la manipulación de strings y menús personalizados.

6. Descripción general: Limpie los datos de la hoja de cálculo

Ahora tienes información sobre el libro en tu hoja. Cada fila se refiere a un libro específico con su título, autor y número ISBN en columnas distintas. Sin embargo, también puedes ver algunos problemas con estos datos sin procesar:

1. En algunas filas, el título y el autor se colocan juntos en la columna del título, vinculados por una coma o la string & &t.
2. A algunas filas les falta el título o el autor del libro.

En las próximas secciones, podrás limpiar estos datos para corregir estos problemas. Para el primer problema, crearás funciones que lean la columna del título y dividan el texto cada vez que se encuentre una delimitador o una coma, y se coloquen las substrings de autor y título correspondientes en las columnas correctas. Para la segunda edición, deberás escribir código que busque automáticamente la información de los libros faltantes mediante una API externa y la agregue a la hoja.

7. Agrega elementos de menú

Te recomendamos que crees tres elementos de menú para controlar las operaciones de limpieza de datos que implementarás.

Implementación

Actualicemos onOpen() para que incluya los elementos adicionales del menú que necesitarás. Puede hacer lo siguiente:

1. En tu proyecto de secuencia de comandos, actualiza tu código onOpen() para que coincida con lo siguiente:

/**
 * 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. Guarde el proyecto de la secuencia de comandos.
3. En el editor de secuencias de comandos, selecciona onOpen en la lista de funciones y haz clic en Ejecutar. Se ejecutará onOpen() para volver a compilar el menú de la hoja de cálculo de modo que no tenga que volver a cargar la hoja de cálculo.

En este código nuevo, el método Menu.addSeparator() crea un divisor horizontal en el menú para mantener organizados visualmente los grupos de elementos de menú relacionados. Luego, se agregan elementos de menú nuevos debajo con las etiquetas Separate title/author at first comma, Separate title/author at last "by" y Fill in blank titles and author cells.

Nota: Los elementos de menú aparecen en Hojas de cálculo en el mismo orden en que los agregas al código onOpen().

Resultados

En la hoja de cálculo, haz clic en el menú Book-list para ver los nuevos elementos del menú:

Cuando haces clic en estos elementos nuevos, se produce un error, ya que no has implementado las funciones correspondientes. Por lo tanto, lo haremos a continuación.

8. Dividir texto en delimitadores de comas

El conjunto de datos que importaste a la hoja de cálculo tiene algunas celdas en las que el autor y el título se combinaron incorrectamente en una celda con una coma:

Dividir strings de texto en columnas separadas es una tarea común en hojas de cálculo. Hojas de cálculo de Google proporciona una función SPLIT() que divide las strings en columnas. Sin embargo, los conjuntos de datos suelen tener problemas que no se pueden resolver fácilmente con Hojas de cálculo integradas. En estos casos, puedes escribir código de Apps Script a fin de realizar las operaciones complejas necesarias para limpiar y organizar tus datos.

Para comenzar a limpiar tus datos, primero implementa una función llamada splitAtFirstComma() que divide el autor y título en sus respectivas celdas cuando se encuentran comas.

La función splitAtFirstComma() debe seguir estos pasos:

1. Obtén el rango que representa las celdas seleccionadas actualmente.
2. Verifica si las celdas del rango tienen una coma.
3. Cuando se encuentran comas, divide la string en dos (y solo dos) substrings en la ubicación de la primera coma. Para simplificar el proceso, puedes suponer que cualquier coma indica un patrón de string de [authors], [title]. También puedes suponer que aparecerán varias comas en la celda, ya que resulta apropiado dividirlas en la primera coma de la string.
4. Configura las substrings como el contenido nuevo del título y las celdas de autor correspondientes.

Implementación

Para implementar estos pasos, debes usar los mismos métodos de servicio de hoja de cálculo que utilizaste antes, pero también debes usar JavaScript para manipular los datos de string. Sigue estos pasos:

1. En el editor de Apps Script, agregue la siguiente función al final de su proyecto de secuencia de comandos:

/**
 * 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. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Revisemos el nuevo código que consta de tres secciones principales:

1: Recupera los valores de título destacados

Las primeras tres líneas establecen tres variables que hacen referencia a los datos actuales de la hoja:

• activeRange representa el rango que el usuario destacó cuando se llamó a la función splitAtFirstComma(). Para que este ejercicio sea sencillo, podemos suponer que el usuario solo hace esto cuando se resaltan las celdas de la columna A.
• titleAuthorRange representa un rango nuevo que abarca las mismas celdas que activeRange, pero también incluye una columna más a la derecha. titleAuthorRange se crea con el método Range.offset(rowOffset, columnOffset, numRows, numColumns). El código necesita este rango expandido, ya que necesita un lugar para colocar los autores que encuentra en la columna de título.
• titleAuthorValues es un array de datos en 2D que se extrae de titleAuthorRange con Range.getValues().

2: Examina cada título y divídelo en el delimitador de la primera coma encontrado

En la siguiente sección, se analizan los valores en titleAuthorValues para encontrar comas. Se usa JavaScript For Loop para examinar todos los valores de la primera columna de titleAuthorValues. Cuando se encuentra una substring de comas (", ") con el método JavaScript String indexOf(), el código hace lo siguiente:

1. El valor de la string de celda se copia en la variable titlesAndAuthors.
2. La ubicación de la coma se determina con el método JavaScript String indexOf().
3. Se llama dos veces al método slice String() de JavaScript para obtener la substring antes del delimitador de coma y la substring después del delimitador.
4. Las substrings se copian en el array titleAuthorValues 2D y se reemplazan los valores existentes en esa posición. Dado que suponemos que el patrón "[authors], [title]" está invertido, el orden de las dos substrings está invertido para colocar el título en la primera columna y los autores en la segunda.

Nota: Si el código no encuentra una coma, los datos de la fila no se modifican.

3. Vuelve a copiar los valores nuevos en la hoja

Una vez que se examinan todos los valores de las celdas de título, el arreglo de titleAuthorValues 2D actualizado se copia en la hoja de cálculo con el método Range.setValues(values).

Punto clave: El código podría haber actualizado cada celda de la hoja de cálculo de forma individual, ya que encontró cada coma, llamando a Range.setValue(value) cada vez que se debía cambiar una celda. Sin embargo, esto generaría un código mucho más lento, ya que cada llamada a Range.setValue(value) requiere una comunicación con el servidor.

Cuando se actualizan datos en Hojas de cálculo, casi siempre es mejor obtener los valores en un array 2D, actualizarlos y, luego, enviarlos de vuelta a la hoja con una sola llamada a Range.setValues(values).

Resultados

Ahora puedes ver los efectos de la función splitAtFirstComma() en acción. Intenta ejecutarla seleccionando el elemento de menú Separar el autor o título en la primera coma después de seleccionar...

...una celda:

...o varias celdas:

Nota: Puedes seleccionar cualquier columna de texto de tu hoja de cálculo. Usa esta función para encontrar comas y dividir el texto según las reglas que estableciste. Sin embargo, si el código encuentra un delimitador de comas, se reemplaza la celda a la derecha del original.

Ahora creaste una función de Apps Script que procesa datos de Hojas de cálculo. A continuación, implementarás la segunda función de divisor.

9. Dividir texto en delimitadores

Si observas los datos originales, verás otro problema. Al igual que algunos de los formatos de datos títulos y autores en una sola celda como "[authors], [título]", otros formatos de formatos como autor y título como "[título] de [autores]":

Implementación

Puedes resolver este problema con la misma técnica de la última sección, mediante la creación de una función llamada splitAtLastBy(). Esta función tiene un trabajo similar a splitAtFirstComma(); la única diferencia real es que se busca un patrón de texto ligeramente diferente. Para implementar esta función, haz lo siguiente:

1. En el editor de Apps Script, agregue la siguiente función al final de su proyecto de secuencia de comandos:

/** 
 * 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. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Existen algunas diferencias clave entre este código y splitAtFirstComma():

1. La substring by se usa como delimitador de strings, en lugar de ,.
2. Aquí se usa el método String.lastIndexOf(substring) de JavaScript en lugar de String.indexOf(substring). Por lo tanto, si hay varias substrings de by en la string inicial, se supone que todas forman parte del título, excepto la última by.
3. Después de dividir la string, la primera substring se establece como título y la segunda como autor (este es el orden opuesto de splitAtFirstComma()).

Resultados

Ahora puedes ver los efectos de la función splitAtLastBy() en acción. Intenta ejecutarlo seleccionando el elemento de menú Título/autor independiente por último después de seleccionar...

...una celda:

...o varias celdas:

Completaste esta sección del codelab. Ahora puede usar Apps Script para leer y modificar datos de strings en una hoja de cálculo y usar menús personalizados para ejecutar diferentes comandos de Apps Script.

En la siguiente sección, aprenderás a mejorar este conjunto de datos completando celdas en blanco con datos provenientes de una API pública.

10. Descripción general: Obtén datos de API públicas

Hasta ahora, definiste mejor tu conjunto de datos para corregir algunos problemas de formato de los títulos y los autores, pero aún falta información en el conjunto de datos, que se destaca en las siguientes celdas:

No puedes obtener los datos faltantes con las operaciones de string en los datos que tienes en la actualidad. En cambio, deberás obtener los datos que faltan de otra fuente. Para ello, puede solicitar información a las API externas que brinden datos adicionales.

Las API son interfaces de programación de aplicaciones. Es un término general, pero básicamente es un servicio que sus programas y secuencias de comandos pueden llamar para solicitar información o realizar ciertas acciones. En esta sección, llamarás a una API disponible públicamente para solicitar información sobre libros que puedes insertar en las celdas vacías de tu hoja.

En esta sección, aprenderás a realizar las siguientes acciones:

• Solicita datos de libros a una fuente de API externa.
• Extrae la información de título y autor de los datos obtenidos y escríbela en tu hoja de cálculo.

11. Recupera datos externos con UrlFetch

Antes de profundizar en el código que funciona directamente con tu hoja de cálculo, puedes aprender a trabajar con API externas en Apps Script creando una función de ayuda específicamente para solicitar información de libros desde la API de Open Library pública.

Nota: Open Library es un archivo de Internet que busca crear una página para cada publicación de libro. Si te interesa, puedes obtener más información sobre la documentación de la API en el sitio web oficial.

Nuestra función auxiliar, fetchBookData_(ISBN), toma un número de ISBN de 13 dígitos de un libro como parámetro y muestra datos sobre ese libro. Se conecta con la API de Open Library y recupera información, y luego analiza el objeto JSON que se muestra.

Implementación

Para implementar esta función auxiliar, haz lo siguiente:

1. En el editor de Apps Script, agregue el siguiente código al final de la secuencia:

/**
 * 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. Guarde el proyecto de la secuencia de comandos.

Nota: En Apps Script, los nombres de las funciones que terminan en _ (un guion bajo) se consideran privados. Otras secuencias de comandos no pueden llamar a estas funciones cuando están presentes en una biblioteca o por parte de los clientes durante la comunicación entre servidor y cliente. Sin embargo, estos son temas avanzados, por lo que, si sabes que una secuencia de comandos solo usará la función actual, se recomienda poner fin al nombre de la función con _.

Revisión de código

Este código se divide en dos secciones principales:

1: Solicitud de la API

En las primeras dos líneas, fetchBookData_(ISBN) se conecta a la API pública de Open Library mediante el extremo de URL de la API y el servicio de recuperación de URL de Apps Script.

La variable url es solo una string de URL, como una dirección web. Apunta a una ubicación en los servidores de Open Library. También incluye tres parámetros (bibkeys, jscmd y format) que indican a los servidores de la Biblioteca abierta qué información solicitas y cómo estructurar la respuesta. En este caso, debes proporcionar el número de ISBN del libro y pedir que se muestre información detallada en formato JSON.

Nota: Cada API pública define sus propios formatos para realizar solicitudes y mostrar resultados. Para que tu código se comunique de manera eficaz con una API, debes cumplir con los formatos que define. En el caso de la biblioteca abierta, los formatos de solicitud y respuesta se describen en la documentación de la API de la Biblioteca abierta. El formato de respuesta utilizado es JSON (notación de objeto de JavaScript), que es solo una forma particular de organizar los datos. JSON es similar a HTML o XML, pero suele ser más fácil de leer para los humanos.

Una vez que hayas creado la string de URL, el código enviará una solicitud a la ubicación y recibirá una respuesta. Para ello, se usa el método UrlFetchApp.fetch(url, params). Envía una solicitud de información a la URL externa que proporciones y almacena la respuesta resultante en la variable response. Además de la URL, el código establece el parámetro opcional muteHttpExceptions en true. Esta configuración significa que el código se detendrá si la solicitud genera un error de API. En su lugar, se muestra la respuesta de error.

La solicitud muestra un objeto HTTPResponse que se almacena en la variable response. Las respuestas HTTP incluyen un código de respuesta, encabezados HTTP y el contenido de la respuesta principal. La información de interés aquí es el contenido JSON principal, por lo que el código debe extraerlo y, luego, analizarlo para ubicar y mostrar la información deseada.

2: Analiza la respuesta de la API y muestra la información de interés

En las últimas tres líneas de código, el método HTTPResponse.getContentText() muestra el contenido principal de la respuesta como una string. Esta string está en formato JSON, pero la API de Open Library define el contenido y el formato exactos. El método JSON.parse(jsonString) convierte la string JSON en un objeto JavaScript para que se puedan extraer fácilmente diferentes partes de los datos. Por último, la función muestra los datos correspondientes al número de ISBN del libro.

Nota: Si crees que necesitas familiarizarte con JSON, consulta la documentación de JavaScript para trabajar con JSON o la referencia de clase JSON.

Resultados

Ahora que implementaste fetchBookData_(ISBN), otras funciones de tu código podrán encontrar información sobre cualquier libro mediante su número ISBN. Usarás esta función para completar las celdas de tu hoja de cálculo.

12. Escribir datos de API en una hoja de cálculo

Ahora puedes implementar una función fillInTheBlanks() que hace lo siguiente:

1. Identificar los datos de título y autor faltantes dentro del rango de datos activos
2. Para recuperar datos faltantes de un libro específico, llama a la API de Open Library con el método de ayuda fetchBookData_(ISBN).
3. Actualizar los valores de título o autor faltantes en sus respectivas celdas.

Implementación

Para implementar esta función nueva, haz lo siguiente:

1. En el editor de Apps Script, agregue el siguiente código al final de su proyecto de secuencia de comandos:

/**
 * 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. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Este código se divide en tres secciones:

1: Lee la información existente del libro

Las primeras tres líneas de la función definen constantes para ayudar a que el código sea más legible. En las siguientes dos líneas, se usa la variable bookValues para mantener una copia local de la información del libro de la hoja. El código leerá información de bookValues, usará la API para completar la información faltante y escribirá estos valores en la hoja de cálculo.

Nota: Esta disposición difiere de splitAtFirstComma() y splitAtLastBy(). En esas funciones, el código solo examinó los títulos destacados actualmente y sus filas. Aquí, examinarás toda la hoja con Spreadsheet.getDataRange().

2: Obtén la información faltante con la función auxiliar

El código se repite indefinidamente en cada fila de bookValues a fin de encontrar títulos o autores faltantes. Para reducir la cantidad de llamadas a la API y mejorar la eficiencia, el código solo llama a la API si se cumplen las siguientes condiciones:

1. La columna ISBN de la fila tiene un valor.
2. El campo de título o autor de la fila está vacío.

Si se cumplen las condiciones, el código llama a la API mediante la función auxiliar fetchBookData_(isbn) que implementaste antes y almacena el resultado en la variable bookData. Ahora debería tener la información faltante que quieres insertar en la hoja.

La única tarea que queda es agregar la información de bookData en nuestra hoja de cálculo. Sin embargo, hay una advertencia. Lamentablemente, las API públicas, como la API de Open Library Book, a veces no tienen la información que solicitaste o, en ocasiones, pueden tener algún otro problema que le impide proporcionarla. Si suponemos que todas las solicitudes a la API serán exitosas, tu código no será lo suficientemente sólido como para manejar errores inesperados.

Para asegurarse de que su código pueda manejar errores de API, debe verificar que la respuesta de la API sea válida antes de intentar usarla. Una vez que el código tiene bookData, realiza una verificación simple para verificar que bookData y bookData.details existan antes de intentar leerlos. Si falta alguno, significa que la API no tenía los datos que querías. En este caso, el comando continue le indica al código que omita esa fila (no puedes completar las celdas faltantes, pero al menos la secuencia de comandos no fallará).

3: Vuelve a escribir la información actualizada en la hoja

La última parte del código tiene verificaciones similares para verificar el título que se mostró y la información del autor. El código solo actualiza el array de bookValues si el título original o la celda del autor están vacíos y la API mostró un valor que puedes colocar allí.

El bucle sale después de examinar todas las filas de la hoja. El último paso es volver a escribir el array bookValues actualizado en la hoja de cálculo con Range.setValues(values).

Resultados

Ahora puedes terminar de limpiar los datos del libro. Puede hacer lo siguiente:

1. Si aún no lo hiciste, destaca el rango A2:A15 de tu hoja y selecciona Lista de libros > Separar título/autor en la primera coma para solucionar los problemas relacionados con la coma.
2. Si aún no lo hiciste, destaca el rango A2:A15 de tu hoja y selecciona Lista de libros > Separar título/autor por último" para solucionar los problemas.
3. Para completar todas las celdas restantes, selecciona Lista de libros > completa los títulos en blanco y escribe las celdas de autor:

13. Conclusión

Felicitaciones por completar este codelab. Aprendiste a crear menús personalizados para activar diferentes partes de tu código de Apps Script. También viste cómo importar datos en Hojas de cálculo de Google mediante los servicios de Apps Script y las API públicas. Esta es una operación común en el procesamiento de hojas de cálculo, y Apps Script le permite importar datos de una amplia variedad de fuentes. Por último, notó que se pueden usar los servicios de Apps Script y JavaScript para leer, procesar e insertar datos de hojas de cálculo.

¿Te resultó útil este codelab?

Sí No

Lo que aprendiste

• Cómo importar datos desde una hoja de cálculo de Google
• Cómo crear un menú personalizado en la función onOpen()
• Cómo analizar y manipular valores de datos de string.
• Cómo llamar a las API públicas con el Servicio de recuperación de URL
• Cómo analizar datos de objetos JSON recuperados de una fuente de API pública

Conceptos clave:

1. onOpen() es un activador simple que se usa para crear elementos de menú. Los activadores simples tienen varias restricciones que debes cumplir.
2. Cuando leas o escribas datos de celdas de hojas de cálculo, la práctica recomendada es leer o escribir un rango completo de una vez con Range.getValues() o Range.setValues(values). No leas ni establezcas cada celda de forma individual, ya que será más lenta.
3. Los nombres de las funciones de Apps Script que terminan en _ (un guion bajo) se consideran privados. Otras secuencias de comandos no pueden llamar a estas funciones cuando están presentes en una biblioteca, o bien a través de una comunicación entre el servidor y el cliente. Si sabes que una secuencia de comandos solo usará la función actual, se recomienda terminar el nombre de esta con _.
4. Cuando uses datos que muestren las API externas, valida los datos antes de intentar usarlos. Esto reduce los errores en tu código si la API muestra un resultado inesperado.

Términos clave:

• Activa (estado): Indica que la hoja de cálculo, la hoja, el rango o la celda especificados son los que el usuario de la hoja de cálculo está viendo o resaltando actualmente.
• Celda activa: Es la celda destacada destacada dentro de la hoja activa con el cursor enfocado.
• Rango activo: Es un grupo de una o más celdas destacadas actualmente en la hoja activa.
• API: Una interfaz de programación de aplicaciones es un servicio que los programas o las secuencias de comandos pueden llamar para solicitar información o realizar ciertas acciones.
• Autorización: Es el proceso del usuario que otorga permisos para permitir que Apps Script acceda a los datos del usuario en los servicios de Google relevantes o realice acciones en nombre del usuario.
• Secuencia de comandos vinculada a un contenedor: Cualquier secuencia de comandos que esté vinculada a un documento de Google Workspace y que se haya creado a partir de él, como una hoja de cálculo o un Documento de Google.
• Función de ayuda: una función, por lo general privada, que llama otra persona para completar una pequeña subtarea. Las funciones auxiliares se usan cuando es necesario completar la subtarea muchas veces en diferentes lugares.
• JSON: es el formato de archivo para definir objetos de datos y sus características a partir de texto.
• onOpen(): Si se define en una secuencia de comandos, es un activador sencillo que se activa cada vez que se abre o se vuelve a cargar un archivo.
• Funciones privadas: Es una función que los clientes no pueden llamar como parte de una biblioteca ni a través de la comunicación entre el servidor y el cliente. Las funciones privadas de Apps Script tienen nombres que terminan en _.
• Rango**: Un grupo de una o más celdas adyacentes dentro de una hoja. La clase Range** te permite leer y actualizar rangos en una hoja.
• Editor de secuencias de comandos: Es el editor de código en el navegador para Apps Script.
• Activadores simples: Un subtipo de activadores en Apps Script con nombres de funciones de activador reservados y ciertas restricciones
• Hoja**: Es una página única de una hoja de cálculo. En ocasiones, se hace referencia a un archivo de Hojas de cálculo de Google en Google Drive. La clase Sheet** te permite acceder a hojas y modificarlas.
• SpreadsheetApp: Esta clase sirve como clase superior del servicio de Hojas de cálculo y proporciona un punto de partida para el código que lee o manipula datos de Hojas de cálculo de Google.
• Hoja de cálculo**: Archivo de Hojas de cálculo de Google que se encuentra en Google Drive. La clase Spreadsheet** te permite acceder a hojas de cálculo y modificarlas.
• Servicio de hojas de cálculo: Un servicio de Apps Script que permite que las secuencias de comandos creen, accedan y modifiquen archivos de Hojas de cálculo de Google.
• Activadores: Un evento con clave para una función de Apps Script Cuando ocurre el evento (como cuando se abre una hoja de cálculo), el activador &activa y ejecuta automáticamente la función asociada de Apps Script.
• Servicio de recuperación de URL: El servicio de Apps Script que permite a las secuencias de comandos conectarse a extremos de URL para realizar solicitudes y recibir respuestas.

¿Qué sigue?

En el siguiente codelab de esta lista de reproducción, obtendrás información más detallada sobre cómo dar formato a datos dentro de una hoja de cálculo.

Consulta el siguiente codelab sobre formato de datos.