Aspectos básicos de Apps Script con Hojas de cálculo de Google n.o 2: Hojas de cálculo, Hojas de cálculo y Rangos

1. Introducción

Te damos la bienvenida a la segunda parte de la lista de reproducción del codelab Aspectos básicos de Apps Script con Hojas de cálculo de Google. El codelab anterior se enfocó en los conceptos del editor de secuencias de comandos, en macros y funciones personalizadas. Este codelab profundiza en el servicio de hojas de cálculo que puedes usar para leer, escribir y manipular datos en Hojas de cálculo de Google.

Qué aprenderás

  • Cómo se representan las hojas de cálculo, las hojas y los rangos en Apps Script
  • Cómo acceder a la hoja de cálculo activa (abierta) y cambiarle el nombre con las clases SpreadsheetApp y Spreadsheet
  • Cómo cambiar el nombre de una hoja y la orientación de columnas y filas de un rango mediante la clase Sheet.
  • Cómo especificar, activar, mover y ordenar un grupo de celdas o rango de datos con la clase Range.

Antes de comenzar

Este es el segundo codelab de la lista de reproducción Aspectos básicos de Apps Script con Hojas de cálculo de Google. Antes de comenzar, asegúrate de completar el primer codelab: Macros y funciones personalizadas.

Requisitos

  • Comprender los temas básicos de Apps Script que se analizaron en el codelab anterior 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

En la siguiente sección, se presentan las clases principales del servicio de hojas de cálculo.

2. Introducción al servicio de hojas de cálculo

Cuatro clases abarcan la base del servicio de hojas de cálculo: SpreadsheetApp, Spreadsheet, Sheet y Range. En esta sección, se describen estas clases y para qué se usan.

Clase SheetApp

Antes de analizar hojas de cálculo, rangos y hojas de cálculo, debes revisar su clase superior: SpreadsheetApp. Muchas secuencias de comandos llaman a los métodos SpreadsheetApp, ya que pueden proporcionar el punto de acceso inicial a tus archivos de Hojas de cálculo de Google. Puedes considerar que SpreadsheetApp es la clase principal del servicio de hojas de cálculo. La clase SpreadsheetApp no se explora en profundidad aquí. Sin embargo, más adelante en este codelab, encontrarás ejemplos y ejercicios que te ayudarán a comprender esta clase.

Hojas de cálculo, hojas de cálculo y sus clases

Como término de Hojas de cálculo, una hoja de cálculo es un archivo de Hojas de cálculo de Google (almacenado en Google Drive) que contiene datos organizados por filas y columnas. En ocasiones, una hoja de cálculo se conoce como "Hoja de cálculo de Google" de la misma forma en que se hace referencia a un documento como "Documento de Google"

Puedes usar la clase Spreadsheet para acceder y modificar datos de archivos de Hojas de cálculo de Google. También puedes usar esta clase para otras operaciones a nivel de archivo, como agregar colaboradores.

f00cc1a9eb606f77.png

Una hoja** representa la página individual de una hoja de cálculo, a veces conocida como"pestaña". Cada hoja de cálculo puede contener una o más hojas. Puedes usar la clase Sheet** para acceder a datos y configuraciones a nivel de la hoja y modificarlos, como mover filas o columnas de datos.

39dbb10f83e3082.png

En resumen, la clase Spreadsheet opera en la colección de hojas de cálculo y define un archivo de Hojas de cálculo de Google en Google Drive. La clase Sheet funciona en hojas individuales dentro de una hoja de cálculo.

La clase Range

La mayoría de las operaciones de manipulación de datos (por ejemplo, lectura, escritura o formato de datos de celdas) requieren que definas a qué celdas se aplica la operación. Puedes usar la clase Range para seleccionar conjuntos de celdas específicos en una hoja. Las instancias de esta clase representan un rango: un grupo de una o más celdas adyacentes en una hoja. Puedes especificar rangos por su número de fila y columna, o por medio de la notación A1.

En el resto del codelab, se muestran ejemplos de secuencias de comandos que funcionan con estas clases y sus métodos.

3. Configurar

Antes de continuar, necesitas una hoja de cálculo con algunos datos. Te proporcionamos una: haz clic en este vínculo para copiar la hoja de datos y, luego, en Crear una copia.

5376f721894b10d9.png

Se coloca una copia de la hoja de cálculo de ejemplo para usarla en su carpeta de Google Drive y se le llama "Copia de hoja de cálculo sin título". Usa esta hoja de cálculo para completar los ejercicios de este codelab.

Le recordamos que puede abrir el editor de secuencias de comandos desde Hojas de cálculo de Google haciendo clic en Extensiones > Apps Script.

Cuando se abre un proyecto de Apps Script en el editor de secuencias de comandos por primera vez, este crea un proyecto de secuencia de comandos y un archivo de secuencia de comandos por usted.

En la siguiente sección, se muestra cómo usar la clase Spreadsheet para mejorar esta hoja de cálculo.

4. Cómo acceder a las hojas de cálculo y modificarlas

En esta sección, puedes aprender a usar las clases SpreadsheetApp y Spreadsheet para acceder a las hojas de cálculo y modificarlas. Específicamente, los ejercicios le enseñan a cambiar el nombre de una hoja de cálculo y duplicar las hojas dentro de otra.

Estas son operaciones simples, pero suelen ser parte de un flujo de trabajo más grande y complejo. Cuando comprendas cómo automatizar estas tareas con código de secuencia de comandos, te será más fácil aprender a automatizar operaciones más elaboradas.

Cambiar el nombre de la hoja de cálculo activa

Supongamos que deseas cambiar el nombre predeterminado “Copia de hoja de cálculo sin título” por un título que refleje mejor el propósito de la hoja de cálculo. Puedes hacerlo con las clases SpreadsheetApp y Spreadsheet.

  1. En el editor de secuencias de comandos, reemplaza el bloque de código myFunction() predeterminado por el siguiente código:
function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. Para guardar la secuencia de comandos, haz clic en Guardar save.
  2. Para cambiar el nombre del proyecto de Apps Script, haz clic en Proyecto sin título, ingresa los precios de aguacate como el nuevo nombre del proyecto y haz clic en Cambiar nombre.
  3. Para ejecutar la secuencia de comandos, selecciona renameSpreadsheet en la lista de funciones y haz clic en Ejecutar.
  4. Sigue las instrucciones en pantalla para autorizar la macro. Si recibes un mensaje de "La app no está verificada", haz clic en Configuración avanzada y, luego, en Ir a precios de aguacate (no seguro). En la siguiente pantalla, haga clic en Permitir.

Una vez que se ejecute la función, el nombre de archivo de tu hoja de cálculo debería cambiar:

226c7bc3c2fbf33e.png

Veamos el código que ingresaste. El método getActiveSpreadsheet() muestra un objeto que representa la hoja de cálculo activa, es decir, la copia de la hoja de cálculo que creaste. Este objeto de hoja de cálculo se almacena en la variable mySS. La llamada a rename(newName) en mySS cambia el nombre del archivo de hoja de cálculo de Google Drive por el precio de aguacate de 2017 en Portland, Seattle.

Como la variable mySS es una referencia a la hoja de cálculo, puedes hacer que tu código sea más limpio y más eficiente llamando a los métodos Spreadsheet en mySS en lugar de llamar a getActiveSpreadsheet() varias veces.

Duplicar la hoja activa

En tu hoja de cálculo actual, solo tienes una hoja. Puedes llamar al método Spreadsheet.duplicateActiveSheet() para crear una copia de la hoja:

  1. Agrega la siguiente función nueva debajo de la función renameSpreadsheet() que ya está en tu proyecto de secuencia de comandos:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. Guarde el proyecto de la secuencia de comandos.
  2. Para ejecutar la secuencia de comandos, selecciona duplicateAndOrganizeActiveSheet en la lista de funciones y haz clic en Ejecutar.

Regresa a Hojas de cálculo para ver que se agregó una nueva hoja de hoja de cálculo de Hojas de cálculo_Original.

d24f9f4ae20bf7d4.gif

En esta nueva función, el método duplicateActiveSheet() crea, activa y muestra la hoja duplicada en tu hoja de cálculo. Esta hoja resultante se almacena en duplicateSheet, pero el código aún no realiza ninguna acción con esa variable.

En la siguiente sección, usarás la clase Sheet para cambiar el nombre de la hoja duplicada y aplicarle formato.

5. Aplica formato a tu hoja con la clase Sheet

La clase Sheet proporciona métodos que permiten que las secuencias de comandos lean y actualicen hojas. En esta sección, puedes obtener información sobre cómo cambiar el nombre de una hoja y el ancho de las columnas con métodos de la clase Sheet.

Cambiar el nombre de la hoja

Cambiar el nombre de las hojas de cálculo es tan simple como cambiarle el nombre a la hoja de cálculo en renameSpreadsheet(). Solo se requiere una llamada al método.

  1. En Hojas de cálculo de Google, haz clic en la hoja Sheet_Original para activarla.
  2. En Apps Script, modifica la función duplicateAndOrganizeActiveSheet() para que coincida con lo siguiente:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}
  1. Guarda y ejecuta la función.

En Hojas de cálculo de Google, se crea una hoja duplicada y se le cambia el nombre cuando ejecutas la función:

91295f42354f62e7.gif

En el código agregado, el método setName(name) cambia el nombre de duplicateSheet y usa getSheetID() para obtener el número de ID único de la hoja. El operador + concatena el ID de la hoja al final de la string "Sheet_".

Modificar las columnas y las filas de una hoja

También puedes usar la clase Sheet para dar formato a la hoja. Por ejemplo, podemos actualizar la función duplicateAndOrganizeActiveSheet() para que también se cambie el tamaño de las columnas de la hoja duplicada y se agreguen las filas inmovilizadas:

  1. En Hojas de cálculo de Google, haz clic en la hoja Sheet_Original para activarla.
  2. En Apps Script, modifica la función duplicateAndOrganizeActiveSheet() para que coincida con lo siguiente:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}
  1. Guarda y ejecuta la función.

En Hojas de cálculo de Google, se crea una hoja duplicada, se le cambia el nombre, se activa y se le da formato:

2e57c917ab157.gif

El código que agregaste usa autoResizeColumns(startColumn, numColumns) para cambiar el tamaño de las columnas de la hoja y facilitar su lectura. El método setFrozenRows(rows) inmoviliza la cantidad determinada de filas (en este caso, dos), lo que mantiene visibles las filas del encabezado a medida que el lector se desplaza hacia abajo en la hoja de cálculo.

En la siguiente sección, aprenderás sobre rangos y manipulación básica de datos.

6. Cómo reorganizar datos con la clase Range

La clase Range y sus métodos proporcionan la mayoría de las opciones de manipulación y formato de datos en el servicio Sheet.

En esta sección, se introduce la manipulación básica de datos con rangos. Estos ejercicios se centran en cómo usar rangos en Apps Script, mientras que otros codelabs de esta lista de reproducción profundizan en la manipulación y el formato de los datos.

Mover rangos

Puedes activar y mover rangos de datos con métodos de clase y la notación A1, una abreviatura para identificar conjuntos específicos de celdas en las hojas de cálculo. Si necesitas volver a familiarizarte, consulta esta descripción de la notación A1.

Actualicemos tu método duplicateAndOrganizeActiveSheet() para mover también algunos datos:

  1. En Hojas de cálculo de Google, haz clic en la hoja Sheet_Original para activarla.
  2. En Apps Script, modifica la función duplicateAndOrganizeActiveSheet() para que coincida con lo siguiente:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}
  1. Guarda y ejecuta la función.

Cuando ejecutas esta función, se crea, activa y da formato a una hoja duplicada. Y el contenido de la columna F se mueve a la columna C:

10ea483aec52457e.gif

El código nuevo usa el método getRange(a1Notation) para identificar el rango de datos que se moverán. Si ingresas la notación A1 "F2:F" como el parámetro del método, debes especificar la columna F (sin incluir F1). Si el rango especificado existe, el método getRange(a1Notation) muestra su instancia Range. El código almacena la instancia en la variable myRange para facilitar su uso.

Una vez que se identifica el rango, el método moveTo(target) toma el contenido de myRange (tanto los valores como el formato) y los mueve. El destino (columna C) se especifica con la notación A1 "C2" Esta es una sola celda, en lugar de una columna. Cuando mueves datos, no necesitas hacer coincidir los tamaños con los rangos de destino ni de destino. Apps Script simplemente alinea la primera celda de cada una.

Ordenar rangos

La clase Range te permite leer, actualizar y organizar grupos de celdas. Por ejemplo, puedes ordenar un rango de datos con el método Range.sort(sortSpecObj):

  1. En Hojas de cálculo de Google, haz clic en la hoja Sheet_Original para activarla.
  2. En Apps Script, modifica la función duplicateAndOrganizeActiveSheet() para que coincida con lo siguiente:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}
  1. Guarda y ejecuta la función.

Ahora, la función, además de nuestro formato anterior, ordena todos los datos de la tabla con la información de precios en la columna C:

a6cc9710245fae8d.png

El código nuevo usa getRange(a1Notation) para especificar un nuevo rango que cubre A3:D55 (en toda la tabla, excepto los encabezados de columna). El código luego llama al método sort(sortSpecObj) para ordenar la tabla. Aquí, el parámetro sortSpecObj es el número de columna que se debe ordenar. El método ordena el rango de modo que los valores de columna indicados queden de menor a mayor (valores ascendentes). El método sort(sortSpecObj) puede realizar requisitos de ordenamiento más complejos, pero aquí no los necesitas. Puedes ver todas las formas en las que puedes llamar a los rangos de orden en la documentación de referencia del método.

¡Felicitaciones! Completaste correctamente todos los ejercicios del codelab. En la siguiente sección, se revisan los puntos clave de este codelab y se muestra una vista previa del siguiente codelab de esta lista de reproducción.

7. Conclusión

Llegaste al final de este codelab. Ahora puede usar y definir las clases y los términos esenciales del servicio de Hojas de cálculo en Apps Script.

Ya puedes continuar con el siguiente codelab.

¿Te resultó útil este codelab?

No

Temas abordados

¿Qué sigue?

En el siguiente codelab de esta lista de reproducción, obtendrás información más detallada sobre cómo leer, escribir y modificar datos en una hoja de cálculo.

Encuentra el siguiente codelab en Cómo trabajar con datos.