Conceptos básicos de Apps Script con Hojas de cálculo de Google, parte 2: Hojas de cálculo, hojas y rangos

1. Introducción

Te damos la bienvenida a la segunda parte de la playlist del codelab de Fundamentos de Apps Script con Hojas de cálculo de Google. El codelab anterior se centró en los conceptos del editor de secuencias de comandos, las macros y las funciones personalizadas. En este codelab, se 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), crearla y cambiarle el nombre con las clases SpreadsheetApp y Spreadsheet
• Cómo cambiar el nombre de una hoja y la orientación de filas o columnas de un rango con la clase Sheet
• Cómo especificar, activar, mover y ordenar un grupo de celdas o un rango de datos con la clase Range

Antes de comenzar

Este es el segundo codelab de la playlist de Fundamentos de Apps Script con Hojas de cálculo de Google. Antes de comenzar, asegúrate de completar el primer codelab: Macros and Custom Functions.

Requisitos

• Comprensión de los temas básicos de Apps Script que se exploraron en el codelab anterior 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

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.

La clase SpreadsheetApp

Antes de profundizar en las hojas de cálculo, las hojas y los rangos, debes revisar su clase principal: SpreadsheetApp. Muchos secuencias de comandos comienzan llamando a métodos SpreadsheetApp, ya que pueden proporcionar el punto de acceso inicial a tus archivos de Hojas de cálculo de Google. Puedes considerar SpreadsheetApp como la clase principal del servicio de hojas de cálculo. Aquí no se explora en profundidad la clase SpreadsheetApp. 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 y sus clases

En 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. A veces, se hace referencia a una hoja de cálculo como una "Hoja de Google", de la misma manera que se hace referencia a un documento como un "Documento de Google".

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

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

Punto clave: Además de un nombre, las hojas de cálculo también tienen una cadena de ID única para cada hoja. El ID está presente en la URL de la hoja de cálculo con el siguiente formato:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

Cada hoja de cálculo también tiene un número de ID único para esa hoja de cálculo. Este número de ID se asigna de forma aleatoria, pero también puedes verlo en la URL de la hoja de cálculo con el siguiente formato:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=sheetID

Puedes ver ambos IDs en esta URL de ejemplo:

Dado que los IDs de las hojas de cálculo y las hojas son únicos (y los nombres de las hojas de cálculo no lo son), es mejor usar IDs para especificar hojas de cálculo y hojas siempre que sea posible. Por ejemplo, puedes tener varias hojas de cálculo con el mismo nombre en tu unidad de Google Drive. Si usas métodos de secuencias de comandos como SpreadsheetApp.openById(id) para abrir tus hojas de cálculo, puedes evitar editar el archivo incorrecto por accidente.

Obtén más información sobre los IDs en Hojas de cálculo de Google en la Introducción a Hojas de cálculo de Google.

En resumen, la clase Spreadsheet opera en la colección de hojas y define un archivo de Hojas de cálculo de Google en Google Drive. La clase Sheet opera 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, leer, escribir o dar formato a los datos de las celdas) requieren que definas a qué celdas se aplica la operación. Puedes usar la clase Range para seleccionar conjuntos específicos de celdas dentro de una hoja. Las instancias de esta clase representan un rango, es decir, un grupo de una o más celdas adyacentes en una hoja. Puedes especificar rangos por sus números de fila y columna, o bien con la notación A1.

Término clave: En Apps Script, la hoja activa es la que está abierta y visible actualmente en la interfaz de Hojas de cálculo de Google. Del mismo modo, el rango activo es el grupo de una o más celdas que se destacan actualmente en la hoja activa. La celda activa es la única celda destacada dentro de la hoja activa con el enfoque del cursor.

Muchos métodos de clase devuelven el objeto activo de la clase solicitada. Por ejemplo, ejecutar SpreadsheetApp.getActiveSpreadsheet() devuelve la hoja de cálculo activa o actual del usuario. Cuando obtienes la hoja de cálculo activa, puedes aplicar operaciones a esa hoja. Del mismo modo, métodos como el método Sheet.activate() pueden cambiar el objeto activo.

Activar una hoja, un rango o una celda equivale a hacer clic en esa hoja, ese rango o esa celda en la interfaz de Hojas de cálculo de Google, lo que mueve el enfoque del cursor a esa ubicación.

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.

Se colocará una copia de la hoja de cálculo de ejemplo para que la uses en tu carpeta de Google Drive con el nombre “Copia de Hoja de cálculo sin título”. Usa esta hoja de cálculo para completar los ejercicios de este codelab.

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

Cuando abres un proyecto de Apps Script en el editor de secuencia de comandos por primera vez, el editor de secuencia de comandos crea un proyecto de secuencia de comandos y un archivo de secuencia de comandos para ti.

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

4. Acceder a hojas de cálculo y modificarlas

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

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

Cómo 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 tu 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");
}

2. Para guardar el script, haz clic en Guardar .
3. Para cambiar el nombre de tu proyecto de Apps Script, haz clic en Proyecto sin título, ingresa "Precios de aguacate" como el nuevo nombre del proyecto y haz clic en Cambiar nombre.
4. Para ejecutar tu secuencia de comandos, selecciona renameSpreadsheet en la lista de funciones y haz clic en Ejecutar.
5. Para autorizar la macro, sigue las instrucciones en pantalla. Si recibes el mensaje "Esta app no está verificada", haz clic en Opciones avanzadas y, luego, en Ir a Avocado prices (no seguro). En la siguiente pantalla, haz clic en Permitir.

Nota: Si usas una cuenta de gmail.com, es posible que aparezca un diálogo de app no 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 con la autorización de la secuencia de comandos.

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

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

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

Duplicar la hoja activa

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

1. Agrega la siguiente función nueva debajo de la función renameSpreadsheet() que ya se encuentra en tu proyecto de secuencia de comandos:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}

2. Guarda tu proyecto de secuencia de comandos.
3. Para ejecutar tu secuencia de comandos, selecciona duplicateAndOrganizeActiveSheet en la lista de funciones y haz clic en Ejecutar.

Vuelve a Hojas para ver que se agregó una nueva pestaña de hoja "Copia de Sheet_Original" a tu hoja de cálculo.

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

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

5. Da 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 aprender a cambiar el nombre de una hoja y los anchos de las columnas con métodos de la clase Sheet.

Nota**: Las clases Spreadsheet** y Sheet comparten varios métodos comunes específicos de la hoja, como Spreadsheet.deleteColumn(columnPosition) y Sheet.deleteColumn(columnPosition).

Cuando llamas a Spreadsheet.deleteColumn(columnPosition), el método quita columnas de la hoja activa (abierta) actualmente dentro de la hoja de cálculo. Puedes obtener el mismo resultado llamando a getActiveSheet() y, luego, a Sheet.deleteColumn(columnPosition). La versión Spreadsheet simplemente te ahorra tiempo.

Cómo cambiar el nombre de la hoja

Cambiar el nombre de las hojas es tan simple como cambiar el nombre de 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());
}

3. 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:

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

Cómo modificar las columnas y filas de una hoja

También puedes usar la clase Sheet para darle formato a tu hoja. Por ejemplo, podemos actualizar tu función duplicateAndOrganizeActiveSheet() para que también cambie el tamaño de las columnas de la hoja duplicada y agregue 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);
}

3. Guarda y ejecuta la función.

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

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

Nota: Cuando trabajas con arrays en Apps Script, sigues la convención de que la lista comienza en el índice 0. Sin embargo, cuando trabajas con filas y columnas, el índice comienza en 1. Esta lógica se alinea con el formato convencional de las hojas de cálculo, en el que se comienza en la fila 1 y la columna A en Hojas de cálculo.

Ten en cuenta esto cuando uses métodos relacionados con columnas y filas, como autoResizeColumns(startColumn, numColumns). En el ejercicio actual, ingresas 1 (no 0) como parámetro para hacer referencia a la columna A de duplicateSheet.

En la siguiente sección, aprenderás sobre los rangos y la 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 de hojas de cálculo.

En esta sección, se presenta la manipulación básica de datos con rangos. Estos ejercicios se enfocan en cómo utilizar rangos en Apps Script, mientras que otros codelabs de esta playlist profundizan en la manipulación y el formato de datos.

Cómo 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 dentro de las hojas de cálculo. Si necesitas volver a familiarizarte con ella, puedes consultar esta descripción de la notación A1.

Actualicemos tu método duplicateAndOrganizeActiveSheet() para que también transfiera 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"));
}

3. Guarda y ejecuta la función.

Cuando ejecutas esta función, se crea, activa y formatea una hoja duplicada. Además, el contenido de la columna F se mueve a la columna C:

El código nuevo usa el método getRange(a1Notation) para identificar el rango de datos que se debe mover. Si ingresas la notación A1 "F2:F" como parámetro del método, especificas la columna F (sin incluir F1). Si existe el rango especificado, el método getRange(a1Notation) devuelve 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 lo mueve. El destino (columna C) se especifica con la notación A1 "C2". Esta es una sola celda, no una columna. Cuando mueves datos, no es necesario que coincidan los tamaños con los rangos de destino. Apps Script simplemente alinea la primera celda de cada una.

Nota: Cuando escribas tus propios secuencias de comandos, asegúrate de probarlos a fondo para asegurarte de que tus datos terminen donde esperabas. Apps Script reemplaza los valores de datos existentes cuando cambia los valores de las celdas. Si no tienes cuidado, podrías reemplazar datos que quieres conservar. En este ejemplo, la columna C estaba vacía, pero, si hubiera habido datos allí, el método moveTo(target) los habría quitado.

Cómo 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);
}

3. Guarda y ejecuta la función.

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

El código nuevo usa getRange(a1Notation) para especificar un rango nuevo que abarca de A3 a D55 (toda la tabla, excepto los encabezados de columna). Luego, el código llama al método sort(sortSpecObj) para ordenar la tabla. Aquí, el parámetro sortSpecObj es el número de columna por el que se ordenará. El método ordena el rango de modo que los valores de la columna indicada vayan del más bajo al más alto (valores ascendentes). El método sort(sortSpecObj) puede realizar requisitos de ordenamiento más complejos, pero no los necesitas aquí. Puedes ver todas las diferentes formas en que puedes llamar a los rangos de ordenamiento 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 un avance del siguiente codelab de esta playlist.

7. Conclusión

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

Ya puedes pasar al siguiente codelab.

¿Te resultó útil este codelab?

Sí No

Temas abordados

• Cómo se representan las hojas de cálculo, las hojas y los rangos en Apps Script
• Algunos usos básicos de las clases SpreadsheetApp, Spreadsheet, Sheet y Range

Conceptos clave:

1. En Apps Script, todos los arrays siguen la convención de que la lista comienza en el índice 0. Sin embargo, las filas y las columnas se indexan a partir del 1.
2. En Apps Script, la hoja, el rango destacado o la celda que están abiertos en ese momento se consideran activos. Muchos métodos de Apps Script acceden al elemento activo o lo modifican.
3. Las clases Spreadsheet y Sheet comparten varios métodos específicos de la hoja. Los métodos duplicados en la clase Spreadsheet son solo para la comodidad del usuario, ya que suelen dirigirse a la hoja activa cuando se los llama.
4. Cada hoja de cálculo y hoja tiene un ID único que puedes encontrar en la URL del navegador. Los métodos de clase de la hoja de cálculo y de la hoja utilizan IDs. Puedes obtener más información sobre los IDs y las URLs en Introducción a Hojas de cálculo de Google.

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.
• 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.
• 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.
• Hoja**: Es una sola página de una hoja de cálculo (similar a los libros de trabajo de Microsoft Excel). 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.

¿Qué sigue?

El siguiente codelab de esta playlist profundiza en cómo leer, escribir y modificar datos en una hoja de cálculo.

Encuentra el siguiente codelab en Trabajar con datos.