Conceptos básicos de Apps Script con Hojas de cálculo de Google #4: Formato de datos

Conceptos básicos de Apps Script con Hojas de cálculo de Google #4:

Formato de datos

Acerca de este codelab

Última actualización: sept 18, 2024

Escrito por Google Workspace Developer Relations

1. Introducción

Te damos la bienvenida a la cuarta parte de la playlist del codelab de Aspectos básicos de Apps Script con Hojas de cálculo de Google.

Si completas este codelab, podrás aprender a dar formato a los datos de tu hoja de cálculo en Apps Script y escribir funciones para crear hojas de cálculo organizadas y llenas de datos con formato 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 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
3. Trabaja con datos

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

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. Debes seguir estos pasos:

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

3. Selecciona Extensiones > Apps Script para abrir el editor de secuencias de comandos.
4. 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 este proyecto, ya puedes comenzar el codelab. Pasa a la siguiente sección para comenzar a aprender sobre el formato básico en Apps Script.

3. Cómo crear un menú personalizado

Puedes aplicar varios métodos de formato básicos en Apps Script a tus hojas de cálculo. En los siguientes ejercicios, se muestran algunas formas de dar formato a los datos. Para ayudarte a controlar tus acciones de formato, crearemos un menú personalizado con los elementos que necesitarás. El proceso para crear menús personalizados se describió en el codelab Trabaja con datos, pero lo resumiremos aquí nuevamente.

Implementación

Creemos un menú personalizado.

1. En el editor de Apps Script, reemplaza el código de tu proyecto de secuencia de comandos por 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();
}

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 ejecuta onOpen() para volver a compilar el menú de la hoja de cálculo, por lo que no tienes que volver a cargarla.

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 de menú. Por ejemplo, si seleccionas el elemento de menú Format row header, Hojas de cálculo intentará ejecutar la función formatRowHeader() (que aún no existe).

Resultados

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

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

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

Los conjuntos de datos en hojas de cálculo suelen tener filas de encabezado para identificar los datos de cada columna. Es una buena idea dar formato a las filas de encabezado para separarlas visualmente del resto de los datos de la hoja de cálculo.

En el primer codelab, creaste una macro para tu encabezado y ajustaste su código. Aquí, formatearás una fila de encabezado desde cero con Apps Script. La fila de encabezado que crearás pondrá en negrita el texto del encabezado, coloreará el fondo de un azul verdoso oscuro, coloreará el texto de blanco y agregará algunas líneas de borde sólidas.

Implementación

Para implementar la operación de formato, usarás los mismos métodos del servicio de hojas de cálculo que usaste antes, pero ahora también usarás algunos de los métodos de formato del servicio. 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:

/**
 * 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);

}

2. Guarda tu proyecto de 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 dos primeras líneas usan métodos que ya viste para obtener una referencia a la hoja activa actual (sheet) y a la primera fila de la hoja (headerRange)). El método Sheet.getRange(row, column, numRows, numColumns) especifica la primera fila, incluidas solo las columnas que contienen datos. El método Sheet.getLastColumn() devuelve el índice de la última columna que contiene datos en la hoja. En nuestro ejemplo, es la columna E (url).

Nota: Aquí suponemos que la fila de encabezado que queremos formatear siempre está en la fila 1, que nuestros datos comienzan en A1 y que se extienden a lo largo de una cantidad de columnas contiguas. Esto está bien para este ejercicio, pero, en general, es mejor evitar estas suposiciones y permitir que los usuarios especifiquen qué celdas desean formatear (en caso de que la estructura de su hoja de cálculo sea diferente).

El resto del código simplemente llama a varios métodos Range para aplicar las opciones de formato a todas las celdas de headerRange. Para que el código sea fácil de leer, usamos el encadenamiento de métodos para llamar a cada método de formato uno después del otro:

• Range.setFontWeight(fontWeight) se usa para establecer el grosor de la fuente en negrita.
• Range.setFontColor(color) se usa para establecer el color de la fuente en blanco.
• Range.setBackground(color) se usa para establecer el color de fondo en un azul verdoso oscuro.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) coloca un borde negro sólido alrededor de las celdas del rango.

El último método tiene varios parámetros, por lo que revisaremos qué hace cada uno. Los primeros cuatro parámetros (todos establecidos en true) le indican a Apps Script que el borde se debe agregar arriba, abajo, y a la izquierda y a la derecha del rango. El quinto y el sexto parámetro (null y null) indican a Apps Script que evite cambiar las líneas de borde dentro del rango seleccionado. El séptimo parámetro (null) indica que el color del borde debe ser negro de forma predeterminada. Por último, el último parámetro especifica el tipo de estilo de borde que se usará, tomado de las opciones proporcionadas por SpreadsheetApp.BorderStyle.

Resultados

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

1. Si aún no lo hiciste, guarda tu proyecto de secuencia de comandos en el editor de Apps Script.
2. Haz clic en el elemento de menú Formatos rápidos > Formatear encabezado de fila.

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.

Los resultados deberían verse así:

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

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

Si puedes crear un encabezado de fila personalizado, también puedes crear un encabezado de columna. Los encabezados de columna 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 aplicar negrita al texto
• Cómo poner el texto en cursiva
• Cómo agregar bordes a las celdas
• Insertar hipervínculos con el contenido de la columna url Una vez que hayas agregado estos hipervínculos, puedes quitar la columna url para limpiar la hoja.

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 más fácil de leer, también implementarás dos funciones auxiliares.

Implementación

Como antes, debes agregar una función para automatizar el formato del encabezado de la columna. Debes seguir estos pasos:

1. En el editor de Apps Script, agrega la siguiente función formatColumnHeader() al final de tu 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); 
}

2. 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; 
}

3. Guarda tu proyecto de secuencia de comandos.

Revisión de código

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

formatColumnHeader()

Como probablemente ya esperabas, las primeras líneas de esta función establecen variables que hacen referencia a la hoja y al rango que nos interesan:

• La hoja activa se almacena en sheet.
• La cantidad de filas del 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 columna: title.
• El rango que abarca el encabezado de la columna se almacena en columnHeaderRange.

Luego, el código aplica los bordes y el formato en negrita al rango del encabezado de columna, al igual que en formatRowHeader(). Aquí, también se usa Range.setFontStyle(fontStyle) para poner el texto en cursiva.

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

hyperlinkColumnHeaders_(headerRange, numRows)

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

La función obtiene un rango nuevo (urlRange) que abarca las URLs correspondientes a las filas de la columna de encabezado. Esto se hace con el método Range.offset(rowOffset, columnOffset), que garantiza que los dos rangos tengan el 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 cada valor de celda del encabezado de columna y lo reemplaza por una fórmula de Hojas de cálculo =HYPERLINK() construida con el encabezado y el contenido de la columna url. Luego, los valores de 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) para quitar la columna url.

columnIndexOf_(colName)

Esta función auxiliar es solo 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 viste para obtener una lista de nombres de encabezados de columna de la fila 1 de la hoja de cálculo. Estos nombres se almacenan en la columna de variables columnNames.

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

Resultados

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

1. Si aún no lo hiciste, guarda tu proyecto de secuencia de comandos en el editor de Apps Script.
2. Haz clic en el elemento de menú Formatos rápidos > Dar formato al encabezado de columna.

Los resultados deberían verse así:

Ahora automatizaste otra tarea de formato. Con los encabezados de columna y fila formateados, en la siguiente sección, se muestra cómo formatear los datos.

6. Da formato a tu conjunto de datos

Ahora que tienes encabezados, creemos una función que dé formato al resto de los datos de tu hoja. Usaremos las siguientes opciones de formato:

• Colores de fondo alternativos para las filas (conocidos como bandas)
• Cómo cambiar los formatos de fecha
• Cómo aplicar bordes
• Ajustar automáticamente el tamaño de todas las columnas y filas

Ahora crearás una función formatDataset() y un método auxiliar 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. Debes seguir estos pasos:

1. En el editor de Apps Script, agrega la siguiente función formatDataset() al final de tu 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());
}

2. Agrega la siguiente función auxiliar al final de tu 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)");
}

3. Guarda tu proyecto de secuencia de comandos.

Revisión de código

Revisemos el código de 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).

En segundo lugar, usa el método Range.offset(rowOffset, columnOffset, numRows, numColumns) para crear un rango (noHeadersRange) que abarque todos los datos de la hoja, sin incluir los encabezados de columna y fila. Luego, el código verifica si este nuevo rango tiene bandas existentes (con Range.getBandings()). Esto es necesario porque Apps Script arroja un error si intentas aplicar bandas nuevas donde ya existen. Si no hay bandas, la función agrega bandas 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 formatear las fechas en la columna etiquetada como “release_date” (que se describe a continuación). La columna se especifica con la función auxiliar columnIndexOf_(colName) que implementaste antes.

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

formatDates_(colIndex)

Esta función de ayuda 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)".

Nota: Hay muchos formatos de fecha y número diferentes que puedes usar en tus secuencias de comandos y hojas de cálculo. Para obtener más información, consulta la guía Formatos de fecha y número.

Primero, la función verifica que el índice de columna proporcionado sea válido (es decir, 0 o mayor). De lo contrario, se devuelve sin hacer nada. Esta verificación evita errores que podrían producirse si, por ejemplo, la hoja no tuviera una columna “release_date”.

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

Resultados

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

1. Si aún no lo hiciste, guarda tu proyecto de secuencia de comandos en el editor de Apps Script.
2. Haz clic en el elemento de menú Formatos rápidos > Formatear conjunto de datos.

Los resultados deberían verse así:

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

7. Recupera y formatea datos de la API

Hasta ahora, en este codelab, viste cómo puedes usar Apps Script como un medio alternativo para dar formato a tu hoja de cálculo. A continuación, escribirás código que extrae datos de una API pública, los inserta en tu hoja de cálculo y los formatea para que sean legibles.

En el último codelab, aprendiste a extraer datos de una API. Aquí usarás las mismas técnicas. En este ejercicio, usaremos la API pública de Star Wars (SWAPI) para completar tu 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, le dará formato a 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 de wrapper que pasa 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 las partes lógicamente compartimentadas 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 tu 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();
}

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 ejecuta onOpen() para volver a compilar el menú de la hoja de cálculo con las nuevas opciones que agregaste.
4. Para crear un archivo de Apps Script, junto a Files, haz clic en Agregar un archivo > Script.
5. Asigna el nombre "API" 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).
6. Reemplaza 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();

}

6. Agrega 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);
}

7. Guarda tu proyecto de 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ú Quick formats. Estableciste una línea de separación 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 wrapper

Todos los elementos de menú agregados hacen algo similar: intentan crear una hoja con datos extraídos de la SWAPI. La única diferencia es que cada uno se enfoca en una película diferente.

Sería conveniente escribir una sola función para crear la hoja y que esta acepte un parámetro para 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 evitas escribir el mismo código tres veces?

La respuesta son las funciones de wrapper. Son funciones ligeras que puedes llamar y que, de inmediato, llaman a otra función con parámetros específicos establecidos.

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

Nota: Si quieres, puedes crear más funciones de wrapper para completar diferentes tipos de hojas con datos. Solo necesitas saber qué tipo de recurso solicitar y qué ID de película usar. Entre los tipos de recursos de ejemplo, se incluyen "planets" y "starships", y la API acepta números del 1 al 7 como IDs de películas.

createResourceSheet_(resourceType, idNumber, episodeNumber)

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

Primero, la función usa fetchApiResourceObject_(url) para hacer una solicitud a la API y recuperar información básica de la película. La respuesta de la API incluye una colección de URLs que el código puede usar 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) de forma repetida para llamar a la API para 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 de la película.

Los objetos de datos de recursos tienen varias claves comunes que se asignan a la información sobre ese personaje. Por ejemplo, la clave “name” se asigna al nombre del personaje de la película. Suponemos que las claves de cada objeto de datos de recursos son idénticas, ya que están diseñadas para 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 con 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. Llamar a esta función auxiliar también activa la nueva hoja.

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

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

fetchApiResourceObject_(url)

Esta función de ayuda es similar a la función de ayuda fetchBookData_(ISBN) que se usó en el codelab anterior Trabaja con datos. Toma la URL proporcionada 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 JSON.parse(json) de JavaScript. Luego, se devuelve el objeto JSON resultante.

Nota: Si trabajas mucho con APIs, esta función puede ser útil cuando necesites recuperar información de la API. Se generaliza para funcionar con casi cualquier URL de API, por lo que te recomendamos que la guardes para usarla en el futuro.

createNewSheet_(name)

Esta función de ayuda es bastante simple. Primero, verifica si existe una hoja con el nombre determinado en la hoja de cálculo. Si es así, la función activa la hoja y la devuelve.

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

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Esta función auxiliar se encarga de completar la nueva hoja con datos de la API. Toma como parámetros la nueva hoja, la lista de claves de objetos y la lista de objetos de recursos de la API. 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 nuevos datos de la API. Este es el tamaño de la lista de recursos y claves, respectivamente. Luego, la función define un rango de salida (resourceRange) en el que se colocarán los datos y agrega una fila adicional para contener los encabezados de columna. La variable resourceValues contiene un array de valores 2D extraído de resourceRange.

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

Nota: Recuerda que las filas y las columnas en Hojas de cálculo se indexan a partir del 1, mientras que los arrays de JavaScript se indexan a partir del 0. Debido a esta diferencia, a menudo tenemos que sumar o restar 1 a los índices cuando programamos en Apps Script.

Después de que se completa resourceValues, se borra la hoja de destino con Sheet.clear() en caso de que contenga datos de clics anteriores en elementos del menú. Por último, los valores nuevos se escriben en la hoja.

Resultados

Para ver los resultados de tu trabajo, haz lo siguiente:

1. Si aún no lo hiciste, guarda tu proyecto de secuencia de comandos en el editor de Apps Script.
2. Haz clic en el elemento de menú Quick formats > Create character sheet > Episode IV.

Los resultados deberían verse así:

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 la API.

¿Te resultó útil este codelab?

SíNo

Qué 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 nueva hoja de datos con Apps Script

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.
• Funciones de wrapper: Son funciones de utilidad simples que se usan para llamar a funciones más complejas y pasarles un conjunto específico de parámetros.

¿Qué sigue?

En el siguiente codelab de esta playlist, se muestra 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 crear gráficos y presentar datos en Presentaciones.