Encontrarte con algo que no sabías que estaba ahí siempre resulta gratificante. Es como dar con un tesoro ✨ en tu jardín cuando pensabas que en él no había nada más que hierbas, gnomos y otros bichos.

Y si cambiamos el jardín por las hojas de cálculo de Google entonces la sensación de maravilla es aún mayor. Al menos para los que no tenemos jardín 😜.

Vamos con #3# funciones de las hojas de cálculo de Google que tal vez no conocías, casi se diría secretas ㊙️. Y que te pueden venir de rechupete a la hora de manipular datos como un pro 🤓 (o tener un excitante tema de conversación para esas sobremesas con los cuñados).

👉 Si prefieres leer menos, aquí tienes un resumen en formato #hilotutorial 🐦.

1️⃣ #UNO

La inefable y polimórfica función QUERY esconde secretos insospechados que no asoman el morro por ningún lado en la documentación oficial de su lenguaje de consultas (que no exactamente SQL).

Pero si desencadenamos cierto tipo de errores al usarla, el mensaje que nos lanzará #GoogleSheets nos dará una pista para desvelar uno de esos misteriosos secretos:

La clausula
La clausula SKIPPING de QUERY asoma la patita.

¿Y para qué sirve ese enigmático parámetro SKIPPING? Veámoslo con una micro animación:

Y un detalle más: contrariando la creencia popular, frecuentemente no es necesario hacer un SELECT dentro de QUERY. En general, cuando nos quedemos con todas las columnas del intervalo de consulta podremos pasar de él. Por tanto, cosas como esta son perfectamente válidas:

=QUERY( A1:B13 ; "WHERE B='C' ORDER BY A DESC" ; 1 )

¡No desgastes tus teclas sin necesidad, amigo o amiga!

2️⃣ #DOS

Pero QUERY se guarda más sorpresas. 

¿Te has preguntado alguna vez qué ocurrirá si jugamos con su tercer parámetro?

Sí, me refiero a la indicación del nº de filas del encabezado en el intervalo de datos de consulta:

QUERY( datos ; consulta ; 𝗲𝗻𝗰𝗮𝗯𝗲𝘇𝗮𝗱𝗼𝘀 )

Veámoslo con otra espero que certera micro animación:

Efectiviwonder, utilizando un valor para el nº de filas del encabezado igual o superior al nº de filas con datos conseguiremos consolidar o plegar, como quieras, verticalmente los datos contenidos en cada columna, que quedarán dispuestos para cada una de ellas en una celda única, con los valores separados por espacios.

Probablemente pensarás que esto es equivalente a...

=JOIN( " " ; A1:A4 )

...y no te faltará en parte la razón.

Pero abusando de QUERY del modo que te propongo conseguimos aplicar este efecto sobre varias columnas a la vez con una sola fórmula.

Y esto nos puede venir muy bien cuando estemos aplicando manipulaciones transformativas complicadas. Por ejemplo, @Oriol TORRENTS CABESTANY preguntaba hace algún tiempo en esta tu comunidad cómo demonios cierta enrevesada fórmula hacía lo que hacía:

Una fórmula mágica que, de algún modo, consolida un intervalo de datos eliminando las celdas vacías.
Una fórmula mágica que, de algún modo, consolida un intervalo de datos eliminando las celdas vacías.

...pues en parte lo logra gracias a este truco con QUERY, combinado con un uso astuto de FILTER, para eliminar las celdas en blanco del intervalo de modo impactante, casi mágico.

#sheetconsejo 👉  Cuando veas que en una fórmula de hdc se combinan simultaneamente ARRAYFORMULA, SPLIT, QUERY y FILTER  y además aparecen emojis ♥️ ¡sal corriendo!... o aplica pacientemente la famosa técnica de análisis por capas 🔎🧅 para tratar de entenderla.

3️⃣ #TRES

Me guardaba lo mejor para el postre.

¿Conocías la función FLATTEN? ⁉️

Pues no aparece en la documentación oficial. Y si intentas usarla, el auto completado no dirá ni (sí, con tilde, para que suene más fuerte 🐮).

Ni rastro de los clásicos
Ni rastro de los clásicos tips de auto completado...

Pero ahí está. Y funciona. ¡Vaya si funciona!

FLATTEN aplana un intervalo de datos, colocando el contenido de todas sus celdas en una columna única, trazando una ruta izquierda ➡️ derecha y arriba ⬇️ abajo.

FLATTEN en acción.

Y cuando la acompañamos de la magia matricial de ARRAYFORMULA y SPLIT (y emojis a gogó 😬) ya es la leche.

Con ella estamos perfectamente pertrechados para resolver en un pispás y de un modo escalofriantemente breve y contundente un problema clásico en preparación de datos: hacer UNPIVOT, o lo que viene a ser lo mismo, transformar una representación de datos ancha/horizontal a estrecha/vertical, algo a menudo muy conveniente para poder mostrar posteriormente la información de manera significativa, agrupando, resumiendo y desglosando allí donde haga falta.

Vamos pues con la 3ª y última micro animación:

¡El hallazgo es morrocotudo!

Parece ser que alguien dio con FLATTEN tratando de programar algo parecido usando Apps Script. Al dar a su función personalizada exactamente el mismo nombre se dio cuenta de que ya existía otra igual, oculta en las tripas del editor de hojas de cálculo de Google.

Me pregunto qué hace FLATTEN ahí. ¿Es un vestigio de algo que nunca llegó a producción? ¿Se trata de un olvido del equipo de desarrollo? ¿Tal vez pruebas preliminares antes de su incorporación al repertorio, oficial y documentado, de funciones? Ni idea. Pero mola mucho.

Y una pregunta más. ¿Habrá otras funciones ocultas, como FLATTEN? ¿Sería posible diseñar algún procedimiento para revelarlas? Ahí lo dejo...

Para terminar, te facilito 👐 la hoja de cálculo utilizada para capturar las micro animaciones anteriores, por si te apetece analizar tranquilamente sus formulitas.

📌 
📌  Características no documentadas #GoogleSheets

¡Feliz y muy hojacalcúlico 2021! 🚀