Aspectos básicos de Apps Script con Hojas de cálculo de Google n.o 4: Formato de datos

Aspectos básicos de Apps Script con Hojas de cálculo de Google n.o 4:
Formato de datos

Acerca de este codelab

subjectÚltima actualización: abr 4, 2022
account_circleEscrito por Google Workspace Developer Relations

1. Introducción

Te damos la bienvenida a la cuarta 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 dar formato a los datos de tus hojas de cálculo en Apps Script y escribir funciones para crear hojas de cálculo organizadas con los datos recuperados de una API pública.

Qué aprenderás

  • Cómo aplicar varias operaciones de formato de Hojas de cálculo de Google en Apps Script
  • Cómo transformar una lista de objetos JSON y sus atributos en una hoja de datos organizada con Apps Script

Antes de comenzar

Este es el cuarto codelab en 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
  3. Cómo trabajar con datos

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

Antes de continuar, necesitas una hoja de cálculo con algunos datos. Al igual que antes, proporcionamos una hoja de datos que puedes copiar para estos ejercicios. Sigue estos pasos:

  1. Haga clic en este vínculo para copiar la hoja de datos y, luego, en Crear una copia. La hoja de cálculo nueva se guarda en tu carpeta de Google Drive y se llama "Copia de formato de datos".
  2. Haz clic en el título de la hoja de cálculo y cámbiala de "Formato de datos" a "Formato de datos". Tu hoja debería verse de la siguiente manera, con información básica sobre las primeras tres películas de Star Wars:

c4f49788ed82502b.png

  1. Seleccione Extensiones > Apps Script para abrir el editor de secuencias de comandos.
  2. Haz clic en el título del proyecto de Apps Script y cámbialo de "Proyecto sin título" a "Formato de datos". Haz clic en Cambiar nombre para guardar el cambio de título.

Con esta hoja de cálculo y proyecto, estarás listo para comenzar el codelab. Avance a la siguiente sección para comenzar a aprender sobre el formato básico en Apps Script.

3. Cómo crear un menú personalizado

En sus secuencias de comandos de Apps Script puede aplicar varios métodos de formato básico. Los siguientes ejercicios demuestran algunas formas de dar formato a los datos. Para controlar tus acciones de formato, creemos un menú personalizado con los elementos que necesitarás. El proceso para crear menús personalizados se describió en el codelab Cómo trabajar con datos, pero volveremos a resumirlo aquí.

Implementación

Creemos un menú personalizado.

  1. En el editor de Apps Script, reemplace el código de su proyecto de secuencia de comandos con lo siguiente:
/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .addToUi();
}
  1. Guarde el proyecto de la secuencia de comandos.
  2. En el editor de secuencias de comandos, selecciona onOpen en la lista de funciones y haz clic en Ejecutar. Esto ejecuta onOpen() para volver a compilar el menú de la hoja de cálculo, de modo que no tengas que volver a cargar la hoja de cálculo.

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 líneas crean un menú (Quick formats), agregan elementos de menú (Format row header, Format column header y Format dataset) al 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 una función de Apps Script que se ejecuta cuando se selecciona el elemento del menú. Por ejemplo, si seleccionas el elemento de menú Format row header, Hojas de cálculo intenta ejecutar la función formatRowHeader() (que todavía no existe).

Resultados

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

1d639a41f3104864.png

Cuando haces clic en estos elementos se produce un error, ya que no implementaste sus funciones correspondientes, así que vamos a hacerlo a continuación.

4. Cómo dar formato a una fila de encabezado

Los conjuntos de datos en las hojas de cálculo suelen tener filas de encabezado para identificar los datos en cada columna. Te recomendamos que formatees las filas del encabezado para separarlas visualmente del resto de los datos de la hoja de cálculo.

En el primer codelab, compilaste una macro para tu encabezado y ajustaste su código. Aquí darás formato a una fila de encabezado desde cero con Apps Script. La fila del encabezado que crearás colocará en negrita el texto del encabezado, aplicará un color de fondo verde azulado oscuro al color, y agregará algunas líneas de borde continuas.

Implementación

Para implementar la operación de formato, usarás los mismos métodos de servicio de hoja de cálculo que utilizaste antes, pero ahora usarás algunos de los métodos de formato. Sigue estos pasos:

  1. En el editor de Apps Script, agregue la siguiente función al final de su proyecto de secuencia de comandos:
/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}
  1. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Al igual que muchas tareas de formato, el código de Apps Script para implementarlo es sencillo. Las primeras dos líneas usan métodos que viste antes para obtener una referencia a la hoja activa actual (sheet) y a la fila superior de la hoja (headerRange)). El método Sheet.getRange(row, column, numRows, numColumns) especifica la fila superior, incluidas solo las columnas con datos. El método Sheet.getLastColumn() muestra el índice de la última columna que contiene datos en la hoja. En nuestro ejemplo, es la columna E (url).

El resto del código simplemente llama a varios métodos Range para aplicar opciones de formato a todas las celdas de headerRange. A fin de facilitar la lectura del código, usamos el encadenamiento de métodos para llamar a cada uno de los métodos de formato uno tras otro:

El último método tiene varios parámetros, así que revisemos qué hace cada uno. Los primeros cuatro parámetros aquí (todos establecidos en true) indican a Apps Script que se debe agregar el borde superior, inferior y a la izquierda y derecha del rango. Los parámetros quinto y sexto (null y null) dirigen a Apps Script para que no cambien las líneas de borde del rango seleccionado. El séptimo parámetro (null) indica que el color del borde debe ser el predeterminado de color negro. Por último, el último parámetro especifica el tipo de estilo de borde que se usará, seleccionado por las opciones proporcionadas por SpreadsheetApp.BorderStyle.

Resultados

Para ver la función de formato en acción, haz lo siguiente:

  1. Si aún no lo hiciste, guarda el proyecto de la secuencia de comandos en el editor de Apps Script.
  2. Haga clic en el elemento de menú Formato rápido > Formato de encabezado de fila.

Los resultados deberían verse así:

a1a63770c2c3becc.gif

Ahora automatizaste una tarea de formato. En la siguiente sección, se aplica la misma técnica a fin de crear un estilo de formato diferente para los encabezados de columna.

5. Cómo dar formato a un encabezado de columna

Si puede crear un encabezado de fila personalizado, también puede crear un encabezado de columna. Los encabezados de columnas aumentan la legibilidad de ciertos conjuntos de datos. Por ejemplo, la columna títulos de esta hoja de cálculo se puede mejorar con las siguientes opciones de formato:

  • Cómo subsanar el texto
  • Cursiva del texto
  • Agregar bordes de celda
  • Insertar hipervínculos con el contenido de la columna url Una vez que hayas agregado estos hipervínculos, puedes quitar la columna url para ayudar a limpiar la hoja de cálculo.

A continuación, implementarás una función formatColumnHeader() para aplicar estos cambios a la primera columna de la hoja. Para que el código sea un poco más fácil de leer, también implementarás dos funciones auxiliares.

Implementación

Al igual que antes, debe agregar una función para automatizar el formato del encabezado de columna. Sigue estos pasos:

  1. En el editor de Apps Script, agrega la siguiente función formatColumnHeader() al final del proyecto de secuencia de comandos:
/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}
  1. Agrega las siguientes funciones auxiliares al final de tu proyecto de secuencia de comandos, después de la función formatColumnHeader():
/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}
  1. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Revisemos el código en cada una de estas tres funciones por separado:

formatColumnHeader()

Como es de esperar, las primeras líneas de esta función establecen variables que hacen referencia a la hoja y el rango que nos interesan:

  • La hoja activa se almacena en sheet.
  • La cantidad de filas en el encabezado de la columna se calcula y se guarda en numRows. Aquí, el código resta uno para que el recuento de filas no incluya el encabezado de la columna: title.
  • El rango que cubre el encabezado de la columna se almacena en columnHeaderRange.

Luego, el código aplica los bordes y la negrita al rango de encabezados de la columna, como en formatRowHeader(). Aquí, también se usa Range.setFontStyle(fontStyle) para aplicar cursiva al texto.

Agregar los hipervínculos a la columna de encabezado es más complejo, por lo que formatColumnHeader() llama a hyperlinkColumnHeaders_(headerRange, numRows) para encargarse de la tarea. Esto ayuda a mantener el código ordenado y legible.

hyperlinkColumnHeaders_(headerRange, numRows)

Esta función auxiliar primero identifica los índices de columna del encabezado (que se supone que es el índice 1) y la columna url. Llama a columnIndexOf_('url') para obtener el índice de la columna de la URL. Si no se encuentra una columna url, el método se cierra sin modificar ningún dato.

La función obtiene un nuevo rango (urlRange) que cubre las URL correspondientes a las filas de columna del encabezado. Para ello, se usa el método Range.offset(rowOffset, columnOffset), que garantiza que los dos rangos serán del mismo tamaño. Luego, se recuperan los valores de las columnas headerColumn y url (headerValues y urlValues).

Luego, la función se repite indefinidamente en el valor de la celda del encabezado de la columna y la reemplaza por una fórmula de Hojas de cálculo =HYPERLINK() creada con el encabezado y el contenido de la columna url. Luego, los valores del encabezado modificados se insertan en la hoja con Range.setValues(values).

Por último, para mantener la hoja limpia y eliminar la información redundante, se llama a Sheet.deleteColumn(columnPosition) a fin de quitar la columna url.

columnIndexOf_(colName)

Esta función auxiliar es simplemente una función de utilidad simple que busca un nombre específico en la primera fila de la hoja. Las primeras tres líneas usan métodos que ya conoces para obtener una lista de nombres de encabezados de columnas de la fila 1 de la hoja de cálculo. Estos nombres se almacenan en los columnNames variables.

La función luego revisa cada nombre en orden. Si encuentra uno que coincida con el nombre que se busca, se detiene y muestra el índice de la columna. Si llega al final de la lista de nombres sin encontrar el nombre, muestra -1 para indicar que no se encontró el nombre.

Resultados

Para ver la función de formato en acción, haz lo siguiente:

  1. Si aún no lo hiciste, guarda el proyecto de la secuencia de comandos en el editor de Apps Script.
  2. Haga clic en el elemento de menú Formato rápido > Formato de encabezado de columna.

Los resultados deberían verse así:

7497cf1b982aeff6.gif

Ahora automatizaste otra tarea de formato. Con los encabezados de columnas y filas con formato, en la siguiente sección se muestra cómo dar formato a los datos.

6. Cómo dar formato a su conjunto de datos

Ahora que tienes encabezados, vamos a crear una función que formatee el resto de los datos de la hoja. Utilizaremos las siguientes opciones de formato:

  • Colores de fondo de filas alternadas (conocidos como bandas)
  • Cómo cambiar los formatos de fecha
  • Cómo aplicar bordes
  • Ajuste automático del tamaño de todas las columnas y filas

Ahora crearás una función formatDataset() y un método de ayuda adicional para aplicar estos formatos a los datos de tu hoja.

Implementación

Como antes, agrega una función para automatizar el formato de los datos. Sigue estos pasos:

  1. En el editor de Apps Script, agrega la siguiente función formatDataset() al final del proyecto de secuencia de comandos:
/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
  1. Agrega la siguiente función auxiliar al final del proyecto de secuencia de comandos, después de la función formatDataset():
/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}
  1. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Revisemos el código en cada una de estas dos funciones por separado:

formatDataset()

Esta función sigue un patrón similar a las funciones de formato anteriores que ya implementaste. Primero, obtiene variables para contener referencias a la hoja activa (hoja) y al rango de datos (fullDataRange).

Luego, usa el método Range.offset(rowOffset, columnOffset, numRows, numColumns) para crear un rango (noHeadersRange) que cubra todos los datos de la hoja, sin incluir los encabezados de columnas y filas. El código luego verifica si este nuevo rango tiene bandas existentes (con Range.getBandings()). Esto es necesario porque Apps Script genera un error si intentas aplicar una banda nueva donde existe. Si no hay bandas, la función agrega una banda de color gris claro con Range.applyRowBanding(bandingTheme, showHeader, showFooter). De lo contrario, la función continúa.

En el siguiente paso, se llama a la función auxiliar formatDates_(colIndex) para aplicar formato a las fechas de la columna denominada "release_date" (descrita a continuación). La columna se especifica con la función auxiliar columnIndexOf_(colName) que implementaste antes.

Por último, el formato finaliza con la adición de otro borde (como antes) y cambia el tamaño de cada columna y fila automáticamente para que se ajusten a los datos que contienen mediante los métodos Sheet.autoResizeColumns(columnPosition) y Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

Esta función auxiliar aplica un formato de fecha específico a una columna con el índice de columna proporcionado. Específicamente, da formato a los valores de fecha como "Mes Día, Año (Día de la Semana)"

Primero, la función verifica que el índice de columna proporcionado sea válido (es decir, 0 o más). De lo contrario, regresa sin hacer nada. Esta verificación evita errores que podrían ocurrir si, por ejemplo, la hoja no tuviera la columna "release_date".

Una vez que el índice de columnas se valida, la función obtiene el rango que cubre esa columna (sin incluir la fila de encabezado) y usa Range.setNumberFormat(numberFormat) para aplicar el formato.

Resultados

Para ver la función de formato en acción, haz lo siguiente:

  1. Si aún no lo hiciste, guarda el proyecto de la secuencia de comandos en el editor de Apps Script.
  2. Haga clic en el elemento de menú Formato rápido > Formatear conjunto de datos.

Los resultados deberían verse así:

3cfedd78b3e25f3a.gif

Automatizaste otra tarea de formato. Ahora que estos comandos de formato están disponibles, agreguemos más datos para aplicarlos.

7. Recupera datos y dales formato

Hasta ahora, en este codelab, viste cómo usar Apps Script como una manera alternativa de dar formato a la hoja de cálculo. A continuación, escribirás código que extraiga datos de una API pública, los inserte en tu hoja de cálculo y los formatee para que se puedan leer.

En el último codelab, aprendiste a extraer datos de una API. Aquí usarás las mismas técnicas. En este ejercicio, usaremos la API de Star Wars (SWAPI) pública para propagar la hoja de cálculo. Específicamente, usarás la API para obtener información sobre los personajes principales que aparecen en las tres películas originales de Star Wars.

Tu código llamará a la API para obtener una gran cantidad de datos JSON, analizar la respuesta, colocar los datos en una hoja nueva y, luego, formatear la hoja.

Implementación

En esta sección, agregarás algunos elementos de menú adicionales. Cada elemento de menú llama a una secuencia de comandos del wrapper que pasa las variables específicas del elemento a la función principal (createResourceSheet_()). Implementarás esta función y tres funciones auxiliares adicionales. Al igual que antes, las funciones auxiliares ayudan a aislar partes lógicamente compartimentales de la tarea y a mantener el código legible.

Realiza las siguientes acciones:

  1. En el editor de Apps Script, actualiza la función onOpen() en el proyecto de secuencia de comandos para que coincida con lo siguiente:
/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}
  1. Guarde el proyecto de la secuencia de comandos.
  2. En el editor de secuencias de comandos, selecciona onOpen en la lista de funciones y haz clic en Ejecutar. Esto ejecuta onOpen() para volver a compilar el menú de la hoja de cálculo con las nuevas opciones que agregaste.
  3. Para crear un archivo de Apps Script, junto a Archivos, haz clic en Agregar un archivo agregar un archivo > Script.
  4. Asigna un nombre a la nueva secuencia de comandos y presiona Intro. (Apps Script agrega automáticamente una extensión .gs al nombre del archivo de secuencia de comandos).
  5. Reemplace el código del nuevo archivo API.gs por lo siguiente:
/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}
  1. Agregue las siguientes funciones auxiliares al final del archivo del proyecto de la secuencia de comandos API.gs:
/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}
  1. Guarde el proyecto de la secuencia de comandos.

Revisión de código

Acabas de agregar mucho código. Analicemos cada función de forma individual para comprender cómo funcionan:

onOpen()

Aquí agregaste algunos elementos de menú al menú de Quick formats. Configuraste una línea de separador y, luego, usaste el método Menu.addSubMenu(menu) para crear una estructura de menú anidada con tres elementos nuevos. Los elementos nuevos se agregan con el método Menu.addItem(caption, functionName).

Funciones de wrapper

Los elementos de menú agregados están haciendo algo similar: intentan crear una hoja con datos extraídos de SWAPI. La única diferencia es que cada una se centra en una película distinta.

Sería conveniente escribir una sola función para crear la hoja y hacer que la función acepte un parámetro a fin de determinar qué película usar. Sin embargo, el método Menu.addItem(caption, functionName) no te permite pasarle parámetros cuando lo llama el menú. Entonces, ¿cómo puedo evitar escribir el mismo código tres veces?

La respuesta es las funciones de wrapper. Estas son funciones ligeras que puedes llamar y que llaman de inmediato a otra con parámetros específicos configurados.

Aquí, el código usa tres funciones wrapper: createPeopleSheetIV(), createPeopleSheetV() y createPeopleSheetVI(). Los elementos de menú están vinculados a estas funciones. Cuando se hace clic en un elemento de menú, la función wrapper se ejecuta, llama inmediatamente a la función del compilador de hojas principal createResourceSheet_(resourceType, idNumber, episodeNumber) y pasa los parámetros apropiados para el elemento del menú. En este caso, significa pedir a la función de creador de hojas que cree una hoja llena de datos de personajes principales de una de las películas de Star Wars.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Esta es la función principal del compilador de hojas de cálculo para este ejercicio. Con la ayuda de algunas funciones auxiliares, obtiene los datos de la API, los analiza, crea una hoja de cálculo, escribe los datos de la API en la hoja y, luego, le da formato mediante las funciones que creaste en las secciones anteriores. Revisemos los detalles:

Primero, la función usa fetchApiResourceObject_(url) a fin de realizar una solicitud a la API para recuperar información básica de la película. La respuesta de la API incluye una colección de URL que el código puede utilizar para obtener más detalles sobre personas específicas (conocidas aquí como recursos) de las películas. El código lo recopila todo en el array resourceUrls.

A continuación, el código usa fetchApiResourceObject_(url) repetidamente para llamar a la API de cada URL de recurso en resourceUrls. Los resultados se almacenan en el array resourceDataList. Cada elemento de este array es un objeto que describe un personaje diferente a la película.

Los objetos de datos de recursos tienen varias claves comunes que se asignan a información sobre ese carácter. Por ejemplo, la tecla "name" corresponde al nombre del personaje de la película. Suponemos que todas las claves de cada objeto de datos de recursos son idénticas, ya que deben usar estructuras de objetos comunes. La lista de claves se necesita más adelante, por lo que el código almacena la lista de claves en resourceObjectKeys mediante el método Object.keys() de JavaScript.

A continuación, la función del compilador llama a la función auxiliar createNewSheet_(name) para crear la hoja en la que se colocarán los datos nuevos. Si se llama a esta función auxiliar, también se activa la hoja nueva.

Después de crear la hoja, se llama a la función auxiliar fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) para agregar todos los datos de API a ella.

Por último, se llama a todas las funciones de formato que creó anteriormente para aplicar las mismas reglas de formato a los datos nuevos. Como la hoja nueva es la que está activa, el código puede reutilizar estas funciones sin modificaciones.

fetchApiResourceObject_(url)

Esta función auxiliar es similar a la función auxiliar fetchBookData_(ISBN) que se usó en el codelab anterior Cómo trabajar con datos. Toma la URL determinada y usa el método UrlFetchApp.fetch(url, params) para obtener una respuesta. Luego, la respuesta se analiza en un objeto JSON con los métodos HTTPResponse.getContextText() y JavaScript JSON.parse(json). Luego, se muestra el objeto JSON resultante.

createNewSheet_(name)

Esta función auxiliar es bastante simple. Primero verifica si existe una hoja con el nombre determinado en la hoja de cálculo. Si lo hace, la función activa la hoja y la muestra.

Si la hoja no existe, la función la crea con Spreadsheet.insertSheet(sheetName), la activa y muestra la hoja nueva.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Esta función auxiliar se encarga de completar la hoja nueva con datos de la API. Toma como parámetros la hoja nueva, la lista de claves de objetos y la lista de objetos de recursos de la API como parámetros. Cada clave de objeto representa una columna en la hoja nueva y cada objeto de recurso representa una fila.

Primero, la función calcula la cantidad de filas y columnas necesarias para presentar los datos nuevos de la API. Este es el tamaño del recurso y la lista de claves, respectivamente. Luego, la función define un rango de salida (resourceRange) en el que se ubicarán los datos, lo que agrega una fila adicional para mantener los encabezados de columna. La variable resourceValues contiene un array de valores 2D extraído de resourceRange.

Luego, la función se repite indefinidamente en cada clave de objeto de la lista objectKeys. La clave se configura como el encabezado de la columna y, luego, un segundo bucle pasa por cada objeto de recurso. Para cada par (fila, columna), la información de API correspondiente se copia en el elemento resourceValues[row][column].

Una vez que se llene resourceValues, la hoja de destino se borrará con Sheet.clear() en caso de que contenga datos de clics en elementos de menú anteriores. Por último, los valores nuevos se escriben en la hoja.

Resultados

Para ver los resultados de tu trabajo, sigue estos pasos:

  1. Si aún no lo hiciste, guarda el proyecto de la secuencia de comandos en el editor de Apps Script.
  2. Haz clic en el elemento de menú Formatos de &gt rápido; crear hoja de caracteres > Episodio IV.

Los resultados deberían verse así:

d9c472ab518d8cef.gif

Ahora escribiste código para importar datos a Hojas de cálculo y darles formato automáticamente.

8. Conclusión

Felicitaciones por completar este codelab. Viste algunas de las opciones de formato de Hojas de cálculo que puedes incluir en tus proyectos de Apps Script y compilaste una aplicación impresionante que importa y da formato a un gran conjunto de datos de API.

¿Te resultó útil este codelab?

Lo que aprendiste

  • Cómo aplicar varias operaciones de formato de Hojas de cálculo con Apps Script
  • Cómo crear submenús con la función onOpen()
  • Cómo dar formato a una lista recuperada de objetos JSON en una hoja de datos nueva con Apps Script.

¿Qué sigue?

El siguiente codelab de esta lista de reproducción te mostrará cómo usar Apps Script para visualizar datos en un gráfico y exportar gráficos a presentaciones de Presentaciones de Google.

Encuentra el siguiente codelab en Cómo mostrar y presentar datos en Presentaciones.

bug_report Informar un error