BAS#001 | Consolidar un intervalo de datos de todas las hojas de cálculo contenidas en una carpeta de Google Drive

Por Pablo Felip 13/03/2021

📌 Este artículo fue publicado originalmente el 13/03/21 en la plataforma social utilizada para dar soporte a la comunidad de usuarios de GEG Spain entre septiembre de 2019 y diciembre de 2022, plataforma que lamentablemente ya no está disponible. He reproducido aquí el contenido de la publicación prácticamente sin cambios, así que es posible que encuentres en el texto alguna referencia descontextualizada.

Inicio la publicación de Básicos Apps Script {BAS} en este Área Developers de GEG Spain, una serie de artículos de iniciación a la programación en Google Apps Script orientada a la resolución comentada de problemas sencillos dentro del ecosistema Google Workspace, inspirados en consultas que recibo con frecuencia.

¿Por qué {BAS}? Es largo (solo un poco), así que lo explico aquí.

¡Y sin más anestesia, vamos con la primera 💊 píldora BAS!

Descripción del problema

En este BAS partimos de:

Una carpeta almacenada en Google Drive que contendrá un número indeterminado de hojas de cálculo de Google, en principio idénticas, de las que deseamos recuperar información.
Una hoja de cálculo destino en la que consolidaremos la información que se encuentra en un mismo intervalo de datos (tabla) de todas y cada una de las hojas de cálculo de la carpeta anterior. Consolidar supone tomar las filas de datos de cada uno de los intervalos de las hojas de cálculo de origen y concatenarlas verticalmente en una tabla única en la hoja de cálculo destino.

Una imagen suele valer más que mil palabras:

Todos los intervalos de datos de las HdC dentro de la carpeta se apilan en la HdC destino.

¿Te imaginas para qué puede servir algo así? Seguro que sí.

Por ejemplo, yo estoy pensando en varias hojas de cálculo con calificaciones, cada una gestionada por un profesor distinto, que queremos reunir en una única tabla.

Bien, lo cierto es que esto podría resolverse con la función integrada IMPORTRANGE() (con el auxilio de la función QUERY(), si somos más finos y queremos omitir las filas vacías), aunque no sin ciertos inconvenientes y particularidades... y probablemente una fórmula de hoja de cálculo larga como un día sin GAS:

Necesitamos conocer de antemano los URL de cada una de las hojas de cálculo a consolidar.
Hay que autorizar las conexiones de cada IMPORTRANGE, una a una, manualmente.
Cualquier cambio en los intervalos de datos de origen se propagará raudo y veloz a la hoja de cálculo destino. Esto podría ser lo deseable... o no, depende de las circunstancias.

Como puedes ver, (1) y (2) hacen que este problema no pueda ser resuelto satisfactoriamente empleando únicamente fórmulas.

En esta carpeta encontrarás las hojas de cálculo de ejemplo que sirven para demostrar la solución propuesta.

BAS#001 Archivos de ejemplo

Como este es el primer BAS, iremos un poco más despacio de lo habitual al hablar de los servicios de Apps Script que se utilizan para interactuar con carpetas, hojas de cálculo y otros elementos dentro del ecosistema Google Workspace.

Solución GAS

En GAS existen fundamentalmente dos tipos de scripts, atendiendo a dónde se guardan:

Scripts independientes, que aparecen como archivos dentro de nuestro Drive.
Scripts vinculados a documentos de texto, hojas de cálculo, presentaciones o formularios.

Por simplicidad, usaremos en esta ocasión una función GAS embebida en la hoja de cálculo destino, Consolidar HdCs.

Veamos el código, que he descompuesto en diversos bloques funcionales:

Inicializar constantes.
Identificar todas las hojas de cálculo de origen.
Leer y consolidar los datos de cada hoja de cálculo.
Escribir los datos consolidados en la hoja destino.

Vamos con ellos.

Inicializar

Y así empieza la cosa. Seguro que ya sabes dónde va todo esto, ¿verdad? Bueno, por si acaso te lo recuerdo: Extensiones → Apps Script.

/**
 * Importa intervalos de datos idénticos procedentes de todas
 * las hdc que se encuentran en la carpeta indicada
 * y los consolida en la actual.
 * No realiza ningún control de errores.
 * El orden de importación no está garantizado.
 * Demo: https://drive.google.com/drive/folders/1BZNT5TNcOpKaP5Hy3BuvVZhT_FhtrWjr?usp=sharing
 */

function consolidar() {

  const ID_CARPETA_ORIGEN = '1bqlCmxWaL-LCNb6T7vOWNtPky3bK99K-';
  const RANGO_ORIGEN = 'Hoja 1!A2:E';
  const CELDA_DESTINO = 'Hoja 1!A2';

Comenzamos con unas anotaciones en el encabezado. Por cierto:

💡 ¡Comenta siempre tu código, tu yo futuro será el primero en agradecértelo!

A continuación, definimos tres constantes, ya dentro de la función consolidar().

ID_CARPETA_ORIGEN referencia a la carpeta donde se encuentran las hojas de cálculo a consolidar. Esta carpeta se identifica unívocamente por medio de un IDentificador, que puede obtenerse a partir del propio URL de la carpeta:

https://drive.google.com/drive/folders/👉1bqlCmxWaL-LCNb6T7vOWNtPky3bK99K-👈

Las constantes RANGO ORIGEN y CELDA_DESTINO indican qué intervalo de datos deseamos leer en cada hoja de cálculo de origen y a partir de qué celda en la hoja de destino vamos a realizar la consolidación de datos.

Fíjate en que nuestra función de consolidación admite rangos abiertos (A2:E en el ejemplo) y omitirá las filas en blanco del modo deseable. De este modo no tendremos que preocuparnos por el nº de filas que realmente contienen datos en los intervalos de origen. Naturalmente, nada te impide usar un rango cerrado como A2:E20, por ejemplo.

💡 El uso de mayúsculas en las declaraciones de constantes globales o especialmente relevantes permite diferenciarlas fácilmente del resto de constantes y variables utilizadas dentro de la función.

Identificar todas las hojas de cálculo de origen

Ahora tenemos que meternos dentro de la carpeta de origen y obtener una lista de todas las hojas de cálculo en su interior.

Esto lo lograremos gracias a dos métodos de la clase DriveApp:

getFolderById(): Devuelve un objeto de la clase Folder (una carpeta) a partir de su ID, que si recuerdas establecimos manualmente en el bloque de inicialización.
getFilesByType(). Devuelve un objeto de tipo iterador de archivos, que no es otra cosa que una clase que maneja internamente DriveApp. En definitiva, una colección de archivos que podemos enumerar.

Piensa en clases y objetos como elementos que tienen una serie de características y a los que puedes darles distintas órdenes.

Las características son las propiedades del objeto, en tanto que las órdenes son los métodos.

Algunos métodos necesitan información adicional para poder cumplir su cometido. Estos datos adicionales se denominan parámetros y se introducen como expresiones separadas por comas, entre paréntesis, justo a continuación del nombre del método. Otros métodos, en cambio, no admiten parámetros, aunque los paréntesis deben mantenerse siempre al invocar el método.

Clases: Objetos, propiedades, métodos,y parámetros.

💡 Los métodos se diferencian a simple vista de las propiedades en que los primeros siempre irán seguidos de paréntesis, aunque carezcan de parámetros. ¡No tienes pérdida!

Pongamos esto en código:

  // Obtener referencias a las hdc dentro de la carpeta
  const hdcsOrigen = DriveApp.getFolderById(ID_CARPETA_ORIGEN).getFilesByType(MimeType.GOOGLE_SHEETS);

Las llamadas a estos dos métodos pueden secuenciarse separándolas mediante un punto [ . ], de modo que el resultado que devuelve un método se utiliza como objeto a partir del cual se invoca el siguiente. Simplemente lee el código de izquierda a derecha y piensa en términos de la jerarquía de objetos, métodos y parámetros para entender qué demonios está pasando.

DriveApp → getFolderById() → 📂 → getFilesByType() → 📒 ... 📒

Donde 📂 es la carpeta de origen y 📒 cada una de las hojas de cálculo que hay en su interior.

Con el iterador en la mano, construimos ahora el vector idHdcs, que contendrá los ID de cada una de las hojas de cálculo usando el método getId() de cada objeto de tipo File del iterador. Sabremos que hemos alcanzado el último archivo cuando hasNext() devuelva un valor FALSE.

  // Lista de IDs de las hdc halladas
  const idHdcs = [];

  // Obtener todos los IDs por medio del iterador
  while (hdcsOrigen.hasNext()) {
    idHdcs.push(hdcsOrigen.next().getId());
  }

Cada ID se añade al final del vector mediante una operación push(). Sí, push también es un método (¡recuerda, lleva paréntesis!), pero en este caso de la clase / objeto Array de JavaScript, lenguaje en el que está basado Google Apps Script.

Por cierto, prefiero utilizar los más castellanos términos vector y matriz para referirme a arrays unidimensionales y bidimensionales, respectivamente.

Leer los datos de cada hoja de cálculo

Usaremos un nuevo array (perdón, matriz), en el que iremos consolidando los datos procedentes del intervalo escogido de cada una de las hojas de cálculo de origen:

  let datosConsolidados = [];

A continuación recorremos el vector idHdcs que contiene los ID de cada hoja de cálculo usando el método forEach(), que ejecutará todo el código entre corchetes { .. } que va justo a continuación. Este código constituye esencialmente una función anónima, que recibe como parámetro la variable hdc. Esta variable tomará, de manera iterativa, el valor de cada elemento del vector en sucesivas ejecuciones del código de la función anónima.

El bloque de { Código } se ejecuta de manera parametrizada con cada elemento de la lista de IDs.

El bloque tendrá esta pinta:

  idHdcs.forEach(hdc => {
  
    // Aquí el código que se ejecuta para cada ID de las HdC en idHdcs
  
  });

Podríamos haber utilizado una estructura de repetición estándar, como es el clásico bucle for, pero usar en su lugar ciertos métodos específicos de la clase Array, que permiten realizar operaciones sobre cada uno de los elementos en su interior, suele ayudar a la hora de producir un código más elegante y fácil de entender. Cuanto antes te acostumbre a ellos, mejor.

Desmenucemos ahora esto:

  idHdcs.forEach(hdc => {
    let datos = SpreadsheetApp.openById(hdc).getRange(RANGO_ORIGEN).getValues();

    // Opcional: elimina filas vacías del intervalo de cada HdC
    datos = datos.filter(fila => fila.some(celda => celda != ''));

    // Consolidar datos
    datosConsolidados = [...datosConsolidados, ...datos];
  });

Primero se obtienen los valores de las celdas del intervalo en la hoja de cálculo. Se usan varios métodos de la clase SpreadsheetApp:

    let datos = SpreadsheetApp.openById(hdc).getRange(RANGO_ORIGEN).getValues();

openById(): Devuelve como resultado un objeto de la clase Sheet que representa a la hoja de cálculo cuyo ID se pasa como parámetro.
getRange(): Devuelve un objeto de tipo rango a partir de una expresión de rango como las usadas en las fórmulas de las hojas de cálculo.
getValues(): Devuelve una matriz con el contenido de las celdas del rango solicitado.

¿Recuerdas el trenecito que montamos hace un rato? Pues aquí tienes otro parecido:

SpreadsheetApp → OpenById() → 📒 → getRange() → 🟧 → getValues()

Donde 📒 es cada hoja de cálculo y 🟧 el rango de celdas cuyos valores son leídos.

Una vez tenemos los datos leídos de la hoja de cálculo, decidimos que eliminar las filas vacías va a ser una buena idea. Esto sería algo opcional de no utilizar esos rangos abiertos de los que hablábamos antes, pero muy necesario en caso contrario para evitar más que probables océanos de filas vacías entre los intervalos de datos procedentes de cada hoja de cálculo.

Lo conseguiremos combinando otros dos de esos métodos de la clase Array, en este caso filter() y some(), que funcionan del siguiente modo:

El método filter() devuelve solo aquellos elementos (filas) de la matriz datos para los que la expresión a la derecha de la flecha => se evalúe a TRUE. La variable fila es un vector que va tomando los valores de cada fila del rango de datos leído.
Si la expresión a la derecha de la flecha => devuelve un valor TRUE, esa fila estará en la matriz datos devuelta. En caso contrario se omitirá.
El método some() realiza, de un modo análogo, la prueba lógica a la derecha de su =>. Si alguna de las instancias de celda (cada uno de los valores de los elementos de fila) la supera, devolverá TRUE, en caso contrario FALSE.

Descartando filas vacías con filter() y some().

    datos = datos.filter(fila => fila.some(celda => celda != ''));

En definitiva, esta línea tan molona encierra en su interior algo equivalente a dos estructuras de repetición de tipo for anidadas. Con ella conseguimos que cualquier fila del intervalo importado que no contenga datos sea omitida, evitando de este modo las filas en blanco en la variable datos a la izquierda de la sentencia de asignación.

Tanto filter() como some() reciben como parámetro una función anónima, del mismo modo que hacíamos en el forEach() anterior.

Y por último, la última línea de este bloque añade las filas de datos del intervalo recién importado, sin líneas en blanco, al conjunto de datos que ya han sido consolidados hasta el momento. Se usa el operador de propagación de JavaScript [ ... ]. Esto no es otra cosa que una concatentación de las filas de todos los intervalos de datos procedentes de las hojas de cálculo de origen.

    datosConsolidados = [...datosConsolidados, ...datos];
  });

Escribir los datos consolidados en la hoja destino

Ahora solo queda escribir los valores de la matriz datosConsolidados a partir de la celda CELDA_DESTINO.

  // Adaptar dimensiones del intervalo destino a los datos a escribir
  const rangoDestino = SpreadsheetApp.getActive().getRange(CELDA_DESTINO).offset(0, 0, datosConsolidados.length, datosConsolidados[0].length);

  // Escribir datos importados a partir de celda destino
  rangoDestino.setValues(datosConsolidados);
}

El método getActive() devuelve la hoja de cálculo sobre la que se está ejecutando el código.
El método getRange() ya lo conoces, lo hemos usado antes para leer los datos de las hojas de cálculo en la carpeta de origen.
Con offset() lo que hacemos es expandir el rango destino, que inicialmente es una celda única, haciendo que crezca en número de filas y columnas para que quepa nuestra matriz de datos consolidados. Se usa la propiedad length de la clase Array (fíjate, ahora no hay paréntesis) para determinar el número de filas (datosConsolidados.length) y columnas (datosConsolidados[0].length) necesarias.

Creo que a estas alturas ya no necesitas otro trenecito ¿verdad?

Ahora solo tenemos que ejecutar la función consolidar() desde el editor GAS de la hoja de cálculo destino... y ya lo tenemos ✌️.

¡Nuestra función en acción!

Siguientes pasos

Lo cierto es que me he extendido más de lo que pensaba para comentar un script de apenas 40 líneas... pero los inicios siempre son duros.

Encontrarás el código completo en el repositorio GitHub de {BAS}.

Repositorio GitHub de {BAS}

Para terminar, un par de cosillas.

Por un lado, debes tener en cuenta que nuestro código no realiza ningún tipo de control de errores. En una situación real es muy aconsejable controlar las posibles excepciones en tiempo de ejecución utilizando bloques try...catch, aunque de esto supongo que hablaremos en algún BAS futuro.

Por otro, como puedes comprobar fácilmente el orden en el que se procesan las hojas de cálculo dentro de la carpeta de origen no se conoce, a priori, sino que estas se van leyendo a medida que se sacan del iterador de archivos.

Si deseamos tener control sobre esto puedes ordenar los archivos, por nombre por ejemplo, antes de procesarlos. O mejor, ordena la matriz consolidada datosConsolidados antes de escribir sus valores en la hoja destino. En este artículo te explico cómo puedes conseguirlo.

Nada más por hoy. Esto va de hacer comunidad, así que cualquier comentario, duda, sugerencia o lo que sea será muy bienvenido. Seguro que tu opinión me ayudará a que los próximos BAS sean mejores. O a descartar la idea totalmente 😬.