¡Las hojas de cálculo de Google y su maravillosa explosión de complejidad 🎇 cuando intentamos hacer filigranas con sus fórmulas!

He comentado en más de una ocasión que uno de los modos más eficaces de contenerla tiene que ver con usar Apps Script, pero en este mini artículo voy a tratar de poner un ejemplo concreto y explicarme en 16 pasos.  Ni uno más.

Vamos a ver si lo consigo.

0️⃣1️⃣ Definamos el problema:

Partimos de una sencilla tabla en una hoja de cálculo de Google (B1:E4). La columna 🅴 contiene valores numéricos que indican el nº de veces que deseamos repetir cada registro (fila): 2 - 5 - 4. A su derecha, el resultado esperado.

Veamos un posible modo de resolver esto usando las funciones integradas en las HdC de Google.

0️⃣2️⃣ Vamos a aprovecharnos de una característica poco conocida de la función QUERY para comenzar transformando la estructura de la tabla de modo que podamos meterla mano al problema.

Por esa razón, lo primero que vamos a tener que hacer es "marcar" con un carácter especial las celdas vacías. Lo entenderás más abajo..

Puedes ver en color púrpura la fórmula utilizada en cada paso.

0️⃣3️⃣ Por la misma razón (este es un QUERY raruno, paciencia) vamos a tener que sustituir los espacios que pudiera haber dentro de cada celda por otro símbolo diferente.

⚠️ En ambos casos hemos usado símbolos de sustitución (✖️, ➖) que no deberían formar parte del contenido original de las celdas de la tabla. Esto puede ser un inconveniente, que no obstante podría salvarse utilizando alguna técnica de relleno previo de caracteres, pero no me voy a complicar la vida con eso dado que no es relevante para lo que quiero contarte.

0️⃣4️⃣ Ahora transponemos los datos y tiramos de esa rareza de QUERY de la que te hablaba antes.

Encontrarás una explicación más detallada del extraño comportamiento de QUERY del que nos estamos aprovechando aquí  (es el truco #2).

Gracias a él consolidamos los valores de las celdas del modo idóneo para lograr nuestro objetivo. Como puedes ver, todos los valores de cada fila quedan agrupados en una misma celda, separados por espacios.

0️⃣5️⃣ En el paso anterior consolidamos en una sola celda cada una de las filas usando un espacio separador para los valores en columnas.

En este paso generaremos los duplicados de cada fila. Para ello se realizarán tres procesos:

  1. Transponer los datos (otra vez).
  2. Añadir como sufijo al elemento presente en cada celda un nuevo símbolo mágico (🔙).
  3. Repetir cada elemento tantas veces como indique la columna 🅴 .

0️⃣6️⃣ El siguiente paso consiste en unir el contenido de todas las celdas de la tabla original en una única cadena de texto. En esta ocasión no necesitaremos un carácter separador, ya tenemos todos los 🔙 que necesitamos 😏.

Haz clic en la imagen de aquí abajo para entender bien qué está pasando. Ojo, todo eso es una sola celda. Fíjate en que el carácter 🔙 es algo así como un salto de línea que indica cuando pasamos de un conjunto de registros repetidos al siguiente.

0️⃣7️⃣ Ahora ya solo nos queda "trocear" nuestra larga cadena de texto en filas utilizando como carácter delimitador el símbolo 🔙, que tan astutamente hemos introducido en el paso 5️⃣.

0️⃣8️⃣ Es el momento de "desempaquetar" las columnas de cada registro (fila), que en esta ocasión quedan delimitadas por espacios mondos y lirondos.

Ya casi lo tenemos 😅.

0️⃣9️⃣ Lo que queda es fácil, solo hay que poner en su sitio los espacios que sustituimos hace mil años por el símbolo ➖ y deshacernos de las ahora molestas ✖️, que nos han servido para que nuestra franken-fórmula no tuviera problemas con celdas vacías en la tabla.

Y ya estaría ✌️.

1️⃣0️⃣ Pongámoslo todo juntito y apreciemos en su justa medida la pequeña (pero adorable) monstruosidad 👺 que hemos creado.

1️⃣1️⃣ Y es que esta fórmula tiene problemas:

  • 🌧️ Usa una característica no oficial de QUERY.
  • 🌧️ Usa emojis para marcar & trocear partes de las cadenas de texto, una de esas "ideas felices" poco obvias.
  • 🌧️ Sus múltiples funciones anidadas la hacen poco legible.

Es decir, la expresión es cualquier cosa menos intuitiva.

Ahora imagínate varias de estas fórmulas en tu hoja de cálculo. Un infierno 🔥 para tu yo futuro, no cabe duda.

1️⃣2️⃣ ¿Y cuál es la alternativa?

💡 Crearemos una función personalizada usando Apps Script que haga todo lo anterior ¡de una vez! Y esta función podrá utilizarse en nuestras fórmulas del mismo modo 🔀 que cualquiera de las nativas, propias de las hojas de cálculo, combinándose con ellas en expresiones compuestas.

1️⃣3️⃣ De cómo crear funciones GAS personalizadas para hojas de cálculo de Google hablaremos largo y tendido en un próximo Básicos Apps Script (una serie de artículos sobre GAS que he iniciado recientemente), sin duda. Por el momento solo pretendo mostrarte cómo se puede resolver el problema anterior de un modo más elegante.

Repositorio Básicos Apps Script

1️⃣4️⃣ Estas escasas líneas de código GAS, introducidas en el editor de secuencias de comandos de la HdC sobre la que estamos trabajando, consiguen lo mismo que todas las fórmulas que hemos utilizado en los pasos 2️⃣ a 9️⃣ descritos anteriormente .

/**
* Repite cada fila un nº indicado de veces
* @param {A2:C4} intervaloDatos Intervalo de datos cuyas filas se van a repetir
* @param {D2:D4} colRepeticiones Vector columna que indica el nº de veces a repetior cada fila
* @customfunction
*/
function repetirFila(intervaloDatos, colRepeticiones) {

 // ⚠️ ¡Se debería realizar un control de errores sobre los parámetros de la función!

 const resultado = [];

 intervaloDatos.forEach((fila, nFila) => {
   for (let i = 0; i < colRepeticiones[nFila][0]; i++) {
     resultado.push(fila);
   }
 });

 return resultado;
}

1️⃣5️⃣ Y es que las fórmulas habituales de las hojas de cálculo no están diseñadas para representar de manera natural el tipo de procesos iterativos que un sencillo y mucho más comprensible bucle for es capaz de realizar en un pispás ⚡️ usando cualquier lenguaje de programación.

1️⃣6️⃣ Ya solo nos queda invocar a nuestra función GAS desde el editor de la hoja de cálculo. Como puedes ver, incluso de dispone de ayuda contextual en el editor al introducir una fórmula que utilice la función GAS que hemos creado. Esto es posible gracias a los elementos JSDoc (precedidos por @) que he introducido en las primeras líneas del código (sí, los que aparentemente solo eran comentarios).

Así de fácil. Y limpio 💪.

Evidentemente no vamos a crear funciones personalizadas Apps Script en todos los casos. De hecho, la mayor parte del tiempo no lo haremos. Pero habrá algunas ocasiones en las que sustituir una expresión formulaica especialmente complicada  (o parte de ella) por una función personalizada GAS puede facilitarnos el trabajo de mantenimiento del modelo de cálculo a lo largo del tiempo. Solo me gustaría que lo tuvieras en cuenta.

Aquí tienes una hoja de cálculo de ejemplo, que incluye el código de la función GAS que te he mostrado.

Hoja de cálculo de ejemplo

No dejes de visitar la hoja Proceso, donde podrás seguir el paso-a-paso de la fórmula utilizada.

NOTA: Jugando limpio, la fórmula utilizada en primera instancia podría ser un poquito más simple, pero he preferido complicarla algo más de lo justo y necesario para reforzar la idea que pretendía transmitir (uso de funciones personalizadas Apps Script en lugar de las estándar para reducir la complejidad).

Realmente podemos evitar el paso en el que se sustituye el contenido de las celdas vacías por ✖️ y consecuentemente su restitución final. Para ello nos fijaremos en que la función SPLIT dispone de un 4º parámetro, opcional y por defecto VERDADERO, que permite indicar si queremos saltarnos fragmentos vacíos de los resultados divididos.

SPLIT(texto; delimitador; [dividir_por_carácter]; [quitar_texto_vacío])

Esto (fragmentos vacíos) es precisamente lo que se encuentra SPLIT cuando "mastica" el resultado de nuestro QUERY marciano aplicado sobre un intervalo de datos con celdas en blanco, como aquí:

De este modo, esa franken-fórmula realmente podría mini simplificarse de este modo:

=ArrayFormula(SUSTITUIR(SPLIT(TRANSPONER(SPLIT(JOIN("🔙";REPETIR(TRANSPONER(QUERY(TRANSPONER(SUSTITUIR(A2:C4;" ";"➖"));;FILAS(A2:C4))) & "🔙";D2:D4));"🔙"));" ";;FALSO);"➖";" "))

Lo que, por otra y en mi opinión, constituye un pobre alivio por lo que hace a su complejidad y no reduce la relevancia del argumento inicial. Pero al César lo que es del César.

El esfuerzo necesario para construir, optimizar, adaptar y mantener en el tiempo, en definitiva, ciertas fórmulas de hojas de cálculo no debe ser minusvalorado. En esos casos, unas pocas líneas de tranquilizador e iterativo código Apps Script pueden hacer tu vida más fácil.

Piensa en ello.