Conceptos básicos de Apps Script con Hojas de cálculo de Google, parte 3: Cómo trabajar con datos

1. Introducción

Te damos la bienvenida a la tercera parte de la playlist del codelab de Fundamentos de Apps Script con Hojas de cálculo de Google.

Si completas este codelab, podrás aprender 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 se presentaron en los codelabs anteriores de esta playlist.

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 valores de datos de cadenas en celdas de Hojas de cálculo de Google
• Cómo extraer y manipular datos de objetos JSON de una fuente de API pública

Antes de comenzar

Este es el tercer codelab de la playlist de Fundamentos 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 y rangos

Requisitos

• Conocimiento de los temas básicos de Apps Script que se exploraron en los codelabs anteriores de esta playlist
• Conocimientos básicos sobre el editor de Apps Script
• Conocimientos básicos de Hojas de cálculo de Google
• Capacidad de leer la notación A1 de Hojas de cálculo
• Conocimientos básicos de JavaScript y su clase String

2. Configurar

Los ejercicios de este codelab requieren una hoja de cálculo para trabajar. Sigue estos pasos para crear una hoja de cálculo que puedas usar en estos ejercicios:

1. Crea una hoja de cálculo en tu unidad de Google Drive. Para ello, selecciona Nuevo > Hojas de cálculo de Google en la interfaz de Drive. Se creará y abrirá la nueva hoja de cálculo. El archivo se guarda en tu carpeta de Drive.
2. Haz clic en el título de la hoja de cálculo y cámbialo de "Hoja de cálculo sin título" a "Manipulación de datos y menús personalizados". Tu hoja debería verse así:

3. Para abrir el editor de secuencias de comandos, haz clic en Extensiones> Apps Script.
4. Haz clic en el título del proyecto de Apps Script y cámbialo de "Untitled Project" a "Data Manipulation and Custom Menus". Haz clic en Cambiar nombre para guardar el cambio de título.

Con una hoja de cálculo y un proyecto en blanco, ya puedes comenzar el lab. Pasa 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 aparezca el diálogo "Esta app no está verificada" cuando uses el script por primera vez. Google usa esta información para advertir a los usuarios que podrían estar usando código de autores desconocidos o que no son de confianza. Si ves este diálogo, puedes continuar, ya que eres el autor de la secuencia de comandos. Sigue las instrucciones en pantalla para continuar autorizando la secuencia de comandos:

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

A lo largo de este codelab, es posible que aparezcan varios mensajes de permisos. Puedes 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 te 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 defines un elemento de menú personalizado, creas una etiqueta de texto y la conectas a una función de Apps Script en tu proyecto de secuencia de comandos. Luego, puedes 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 asociaste 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 tu 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 de activador simple onOpen(), sobre la que obtendrás información en la siguiente sección.

4. La función onOpen()

Los activadores simples en Apps Script proporcionan una forma de ejecutar código específico de Apps Script en respuesta a ciertas condiciones o eventos. Cuando creas un activador, defines qué evento hace que se active y proporcionas una función de Apps Script que se ejecuta para el evento.

onOpen() es un ejemplo de un activador simple. Son fáciles de configurar: solo debes escribir una función de Apps Script llamada onOpen(), y Apps Script la ejecutará cada vez que se abra o se 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 usa para crear elementos de menú.

Los activadores son un tema complejo y, como tal, están fuera del alcance de este codelab. Por ahora, puedes considerar 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 de tu proyecto de secuencias de comandos por 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. Guarda tu proyecto de 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, luego, agregan 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 de Apps Script que se ejecuta cuando se selecciona el elemento de menú. En este caso, si seleccionas el elemento de menú Load Book-list, Hojas de cálculo intentará ejecutar la función loadBookList() (que aún no existe).

Resultados

Ejecuta esta función ahora para ver que funciona:

1. En Hojas de cálculo de Google, vuelve a cargar tu hoja de cálculo. Nota: Por lo general, esta acción cierra la pestaña con el editor de secuencias de comandos.
2. Vuelve a abrir el editor de secuencias de comandos seleccionando Herramientas > Editor de secuencias de comandos.

Después de que 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 Book-list, verás el siguiente menú:

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

5. Importa datos de hojas de cálculo

Ahora que creaste un menú personalizado, puedes crear funciones que se ejecuten cuando se haga clic en el elemento del menú.

En este momento, el menú personalizado Book-list tiene un elemento de menú: Load Book-list. La función que se llama cuando seleccionas el elemento de menú Load Book-list, loadBookList(),, no existe en tu secuencia de comandos, por lo que, si seleccionas Book-list > Load Book-list, se arroja un error:

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

Implementación

Quieres que el nuevo elemento de menú complete la hoja de cálculo con datos para trabajar, por lo que implementarás loadBookList() para leer datos de libros de otra hoja de cálculo y copiarlos en esta:

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. Guarda tu proyecto de secuencia de comandos.

Revisión de código

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

Paso 1: Identifica la hoja de destino

La primera línea usa SpreadsheetApp.getActiveSheet() para obtener una referencia al objeto de la hoja actual y la almacena en la variable sheet. Esta es la hoja a 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 de la que el código lee datos. El código encuentra la hoja de cálculo por su ID. En este ejemplo, proporcionamos el ID de una hoja de cálculo de origen para leer y abrir la hoja de cálculo con el método SpreadsheetApp.openById(id).
• bookSheet almacena una referencia a una hoja dentro de bookSS que contiene los datos que deseas. El código identifica la hoja de la que se leerá por su nombre, codelab-book-list.
• bookRange almacena una referencia a un rango de datos en bookSheet. El método Sheet.getDataRange() devuelve el rango que contiene todas las celdas no vacías de la hoja. Es una forma sencilla de asegurarte de obtener un rango que abarque todos los datos de una hoja sin incluir filas ni columnas vacías.
• bookListValues es un array bidimensional que contiene todos los valores tomados de las celdas en bookRange. El método Range.getValues() genera este array leyendo los datos de la hoja de origen.

3: Copia los datos de la fuente al destino

La siguiente sección de código copia los datos de bookListValues en sheet y, luego, también cambia el nombre de la hoja:

• 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 bidimensional de bookListValues en el rango de destino y sobrescribe los datos que ya se encuentran allí.

4: Da formato a la hoja de destino

El 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 usa Sheet.autoResizeColumns(startColumn, numColumns) para cambiar el tamaño de las tres primeras 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 Book-list > Load book-list para ejecutar la función y completar tu 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 de esta lista de libros con manipulación de cadenas y menús personalizados.

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

Ahora tienes información del libro en tu hoja. Cada fila hace referencia a un libro específico y muestra su título, autor y número ISBN en columnas separadas. 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 de título, vinculados por una coma o la cadena "de ".
2. En algunas filas, faltan el título o el autor del libro.

En las siguientes secciones, corregirás estos problemas limpiando los datos. Para el primer problema, crearás funciones que lean la columna de título y dividan el texto cada vez que se encuentre una coma o un delimitador "by", y colocarás las subcadenas de título y autor correspondientes en las columnas correctas. Para el segundo problema, escribirás código que busque automáticamente la información faltante de los libros con una API externa y agregue esa información a tu hoja.

7. Agrega elementos de menú

Deberás crear tres elementos de menú para controlar las operaciones de limpieza de datos que implementarás.

Implementación

Actualicemos onOpen() para incluir los elementos de menú adicionales que necesitarás. Puedes hacer lo siguiente:

1. En tu proyecto de secuencia de comandos, actualiza el código de 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. Guarda tu proyecto de secuencia de comandos.
3. En el editor de secuencias de comandos, selecciona onOpen en la lista de funciones y haz clic en Ejecutar. Esto ejecutará onOpen() para volver a compilar el menú de la hoja de cálculo, de modo que no tengas que volver a cargarla.

En este nuevo código, 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 los nuevos elementos del menú debajo de él, 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 del menú aparecen en Hojas de cálculo en el mismo orden en que los agregas en el código de onOpen().

Resultados

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

Si haces clic en estos elementos nuevos, se producirá un error, ya que no implementaste sus funciones correspondientes. Por lo tanto, hagámoslo a continuación.

8. Divide el texto en delimitadores de comas

El conjunto de datos que importaste a tu hoja de cálculo tiene algunas celdas en las que el autor y el título se combinan de forma incorrecta en una sola celda con una coma:

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

Comienza a limpiar tus datos implementando primero una función llamada splitAtFirstComma() que divida el autor y el título en sus respectivas celdas cuando se encuentren comas.

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

1. Obtiene el rango que representa las celdas seleccionadas actualmente.
2. Comprueba si las celdas del rango tienen una coma.
3. Cuando se encuentren comas, divide la cadena en dos (y solo dos) subcadenas en la ubicación de la primera coma. Para simplificar las cosas, puedes suponer que cualquier coma indica un patrón de cadena "[autores], [título]". También puedes suponer que, si aparecen varias comas en la celda, es adecuado dividir la cadena en la primera coma.
4. Establece las subcadenas como el nuevo contenido de las celdas de título y autor respectivas.

Implementación

Para implementar estos pasos, usarás los mismos métodos del servicio de hojas de cálculo que usaste antes, pero también deberás usar JavaScript para manipular los datos de cadena. Debes seguir estos pasos:

1. En el editor de Apps Script, agrega la siguiente función al final de tu 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. Guarda tu proyecto de secuencia de comandos.

Revisión de código

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

1: Recupera los valores del título destacado

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 tiene destacado actualmente cuando se llamó a la función splitAtFirstComma(). Para simplificar este ejercicio, podemos suponer que el usuario solo hace esto cuando destaca celdas en 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 porque necesita un lugar para colocar los autores que encuentre en la columna de título.
• titleAuthorValues es un array bidimensional de datos extraídos de titleAuthorRange con Range.getValues().

2: Examina cada título y divídelo en el primer delimitador de coma que encuentres

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

1. El valor de cadena de la celda se copia en la variable titlesAndAuthors.
2. La ubicación de la coma se determina con el método indexOf() de la cadena de JavaScript.
3. El método JavaScript String slice() se llama dos veces para obtener la subcadena antes del delimitador de coma y la subcadena después del delimitador.
4. Las subcadenas se copian de nuevo en el array bidimensional titleAuthorValues, lo que reemplaza los valores existentes en esa posición. Como suponemos un patrón "[autores], [título]", el orden de las dos subcadenas se invierte para colocar el título en la primera columna y los autores en la segunda.

Nota: Cuando el código no encuentra una coma, deja los datos de la fila sin cambios.

Paso 3: Copia los valores nuevos en la hoja

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

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

Cuando actualices los datos de Hojas de cálculo, casi siempre es mejor obtener los valores en un array 2D primero, actualizar el array y, luego, enviar los datos 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. Para probarlo, selecciona el elemento de menú Separate title/author at first comma después de seleccionar…

…una celda:

…o varias celdas:

Nota: Puedes seleccionar cualquier columna de texto en 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, sobrescribirá la celda inmediatamente a la derecha de la original.

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

9. Divide el texto en delimitadores "por"

Si observas los datos originales, puedes ver otro problema. Así como algunos formatos de datos incluyen los títulos y los autores en una sola celda como "[autores], [título]", otras celdas incluyen el autor y el título como "[título] de [autores]":

Implementación

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

1. En el editor de Apps Script, agrega la siguiente función al final de tu 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. Guarda tu proyecto de secuencia de comandos.

Revisión de código

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

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

Resultados

Ahora puedes ver los efectos de la función splitAtLastBy() en acción. Intenta ejecutarlo seleccionando el elemento de menú Separate title/author at last "by" después de seleccionar…

…una celda:

…o varias celdas:

Completaste esta sección del codelab. Ahora puedes usar Apps Script para leer y modificar datos de cadenas en una hoja, y usar menús personalizados para ejecutar diferentes comandos de Apps Script.

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

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

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

No puedes obtener los datos faltantes usando operaciones de cadenas en los datos que tienes actualmente. En cambio, deberás obtener los datos faltantes de otra fuente. Puedes hacerlo en Apps Script solicitando información de APIs externas que pueden proporcionar datos adicionales.

Las APIs son interfaces de programación de aplicaciones. Es un término general, pero básicamente es un servicio al que pueden llamar tus programas y secuencias de comandos 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 hacer lo siguiente:

• Solicitar datos de libros desde una fuente de API externa
• Extrae el título y la información del autor de los datos devueltos y escríbelos en tu hoja de cálculo.

11. Cómo recuperar 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 APIs externas en Apps Script creando una función auxiliar 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 se esfuerza por crear una página para cada publicación de libros. Si te interesa, puedes obtener más información sobre la documentación de su API en su sitio web oficial.

Nuestra función de ayuda, fetchBookData_(ISBN), toma el número ISBN de 13 dígitos de un libro como parámetro y devuelve datos sobre ese libro. Se conecta a la API de Open Library y recupera información de ella, 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, agrega el siguiente código al final de tu secuencia de comandos:

/**
 * 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. Guarda tu proyecto de secuencia de comandos.

Nota: En Apps Script, los nombres de funciones que terminan en _ (un guion bajo) se consideran privados. Otros lenguajes de secuencias de comandos no pueden llamar a estas funciones cuando están presentes en una biblioteca, o por clientes durante la comunicación cliente-servidor. Sin embargo, estos son temas avanzados, por lo que, si sabes que una función solo se usará en la secuencia de comandos actual, la práctica recomendada es terminar el nombre de la función con _.

Revisión de código

Este código se divide en dos secciones principales:

1: La solicitud a la API

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

La variable url es solo una cadena 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 les indican a los servidores de Open Library qué información solicitas y cómo estructurar la respuesta. En este caso, proporcionas el número ISBN del libro y solicitas que se devuelva información detallada en formato JSON.

Nota: Cada API pública define sus propios formatos para realizar solicitudes y devolver 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 Open Library, los formatos de solicitud y respuesta se describen en la documentación de la API de Open Library Books. El formato de respuesta que se usa es JSON (JavaScript Object Notation), 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 las personas.

Una vez que hayas creado la cadena 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 proporcionas 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. Este parámetro de configuración significa que tu código no se detendrá si la solicitud genera un error de API. En su lugar, se devuelve la respuesta de error.

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

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

En las últimas tres líneas de código, el método HTTPResponse.getContentText() devuelve el contenido principal de la respuesta como una cadena. Esta cadena 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 cadena JSON en un objeto JavaScript para que se puedan extraer fácilmente diferentes partes de los datos. Por último, la función devuelve los datos correspondientes al número 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 la clase JSON.

Resultados

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

12. Escribe datos de la API en una hoja de cálculo

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

1. Identifica los datos faltantes del título y el autor dentro del período de datos activo.
2. Recupera los datos faltantes de un libro específico llamando a la API de Open Library con el método auxiliar fetchBookData_(ISBN).
3. Actualiza los valores faltantes del título o el autor en sus celdas respectivas.

Implementación

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

1. En el editor de Apps Script, agrega el siguiente código al final de tu 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. Guarda tu proyecto de 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, la variable bookValues se usa para mantener una copia local de la información del libro de la hoja. El código leerá la información de bookValues, usará la API para completar la información faltante y volverá a escribir estos valores en la hoja de cálculo.

Nota: Esta disposición es diferente de splitAtFirstComma() y splitAtLastBy(). En esas funciones, el código solo examinaba los títulos destacados y sus filas. Aquí, examinas toda la hoja con Spreadsheet.getDataRange().

2: Recupera la información faltante con la función de ayuda

El código itera sobre cada fila de bookValues para encontrar los títulos o autores faltantes. Para reducir la cantidad de llamadas a la API y, al mismo tiempo, 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. La celda del título o del autor de la fila está vacía.

Si las condiciones son verdaderas, el código llama a la API con la función de ayuda fetchBookData_(isbn) que implementaste anteriormente y almacena el resultado en la variable bookData. Ahora debería tener la información faltante que deseas insertar en la hoja.

La única tarea que queda es agregar la información de bookData a nuestra hoja de cálculo. Sin embargo, hay una advertencia. Lamentablemente, las APIs públicas, como la API de Open Library Book, a veces no tienen la información que solicitas o, en ocasiones, pueden tener algún otro problema que les impida proporcionar la información. Si supones que todas las solicitudes a la API se realizarán correctamente, tu código no será lo suficientemente sólido como para controlar errores inesperados.

Para asegurarte de que tu código pueda controlar los errores de la 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 comprobar que bookData y bookData.details existan antes de intentar leerlos. Si falta alguno de los dos, 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 tu secuencia de comandos no fallará.

3: Escribe la información actualizada en la hoja

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

El bucle sale después de que se examinan 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 de tu libro. Puedes hacer lo siguiente:

1. Si aún no lo hiciste, destaca el rango de A2 a A15 en tu hoja y selecciona Book-list > Separate title/author at first comma para solucionar los problemas con las comas.
2. Si aún no lo hiciste, destaca el rango de A2 a A15 en tu hoja y selecciona Book-list > Separate title/author at last "by" para corregir los problemas con "por".
3. Para completar todas las celdas restantes, selecciona Book-list > Fill in blank titles and author cells:

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 a Hojas de cálculo de Google con los servicios de Apps Script y las APIs públicas. Esta es una operación común en el procesamiento de hojas de cálculo, y Apps Script te permite importar datos de una amplia variedad de fuentes. Por último, viste cómo puedes 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

Qué aprendiste

• Cómo importar datos de 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 cadenas.
• Cómo llamar a las APIs públicas con el servicio de recuperación de URL
• Cómo analizar los 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 se leen o escriben datos de celdas de hojas de cálculo, la práctica recomendada es leer o escribir un rango completo a la vez con Range.getValues() o Range.setValues(values). No leas ni establezcas cada celda de forma individual, ya que será más lento.
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, ni los clientes a través de la comunicación cliente-servidor. Si sabes que una función solo se usará en la secuencia de comandos actual, es una práctica recomendada terminar el nombre de la función con _.
4. Cuando uses datos que devuelvan las APIs externas, valídalos antes de intentar usarlos. Esto reduce los errores en tu código si la API devuelve 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 destacando actualmente.
• Celda activa: Es la única celda destacada dentro de la hoja activa en la que se enfoca el cursor.
• Rango activo: Es un grupo de una o más celdas que se destacan actualmente en la hoja activa.
• API: Una interfaz de programación de aplicaciones es un servicio al que pueden llamar los programas o las secuencias de comandos para solicitar información o realizar ciertas acciones.
• Autorización: Es el proceso por el que el usuario otorga permisos para permitir que Apps Script acceda a los datos del usuario en los servicios de Google pertinentes o realice acciones en nombre del usuario.
• Secuencia de comandos vinculada a un contenedor: Es cualquier secuencia de comandos que esté vinculada a un documento de Google Workspace, como una Hoja de cálculo o un documento de Google, y que se haya creado a partir de él.
• Función auxiliar: Es una función, por lo general privada, a la que llama otra para completar una pequeña subtarea. Las funciones auxiliares se usan cuando la subtarea debe completarse muchas veces en diferentes lugares.
• JSON: Es un 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 simple que se activa cada vez que se abre o se vuelve a cargar un archivo.
• Funciones privadas: Son funciones que no se pueden llamar como parte de una biblioteca ni por clientes 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**: Es un grupo de una o más celdas adyacentes dentro de una hoja. La clase Range** te permite leer y actualizar rangos dentro de una hoja.
• Editor de secuencias de comandos: Es el editor de código en el navegador para Apps Script.
• Activadores simples: Es un subtipo de activadores en Apps Script con nombres de funciones de activador reservados y ciertas restricciones.
• Hoja**: Es una sola página de una hoja de cálculo. A veces, se usa para hacer referencia a un archivo de Hojas de cálculo de Google dentro de Google Drive. La clase Sheet** te permite acceder a las hojas y modificarlas.
• SpreadsheetApp: Esta clase sirve como clase principal para el 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**: Es un archivo de Hojas de cálculo de Google que se encuentra en Google Drive. La clase Spreadsheet** te permite acceder a las hojas de cálculo y modificarlas.
• Servicio de hojas de cálculo: Es un servicio de Apps Script que permite que las secuencias de comandos creen, modifiquen y accedan a archivos de Hojas de cálculo de Google.
• Activadores: Es un evento asociado a una función de Apps Script. Cuando se produce el evento (por ejemplo, se abre una hoja de cálculo), el activador se "activa" y ejecuta automáticamente la función de Apps Script asociada.
• Servicio de recuperación de URL: Es el servicio de Apps Script que permite que las secuencias de comandos se conecten a endpoints de URL para realizar solicitudes y recibir respuestas.

¿Qué sigue?

En el siguiente codelab de esta playlist, se explica con más detalle cómo dar formato a los datos en una hoja de cálculo.

Encuentra el siguiente codelab en Formato de datos.