Puede que esto te haya ocurrido alguna vez: necesitas unificar información de varias pestañas de un excel o de varios documentos y esa información, además, se va actualizando constantemente en sus diversas localizaciones. ¿No encuentras la forma de poder hacerlo sin que se te rompa algún flujo de información? ¿O sin que recoja los datos nuevos de forma automática?
Pues bien, esto es un sencillo truco que te permite realizar Google Sheets sin demasiado esfuerzo para tener todos los datos que necesites unificados en una sola pestaña y de forma dinámica, tal y como vemos en la imagen de arriba. Vayamos a un ejemplo práctico para visualizarlo mejor. En este caso, nuestro cliente va a ser una empresa que se dedica a vender zepelines. Tienen 3 modelos de zepelines: el Sabbath, el Frost y el Symbolic. Todos ellos han facturado una cantidad similar durante los últimos 4 años, pero los directivos hicieron 3 archivos diferentes para los resultados de cada modelo, por lo que la información está disgregada y no se puede efectuar los cálculos sobre el total. Se te ha encargado la tarea de crear un único elemento con los datos de los 3 modelos apilados siempre teniendo en cuenta que los resultados del último año pueden variar dado que son provisionales y que pueden añadir nuevas filas de vez en cuando (así son los directivos de una empresa de zepelines).
Los archivos pertenecientes a cada uno de los modelos son los siguientes:
El formato que tienen todos los documentos sigue este orden:
Para poder realizar nuestra tarea de la forma más robusta y dinámica lo que haremos será utilizar dos funciones especiales de Google Sheets: Importrange y Query. La primera nos permitirá hacer una llamada de un google sheets a otro y así importar los datos elegidos. La segunda, por otro lado, es algo más compleja ya que se trata de una herramienta que permite utilizar el lenguaje de Google Visualization API (muy parecido a SQL), lo que nos hará capaces de formular consultas propias de herramientas de base de datos sin salir de Google Sheets. Esta es, para mí, la función más poderosa y útil de Sheets. Si consigues dominar esto, tus jefes quizá te regalen un zepelín.
Para aquellos más impacientes o con menos tiempo, aquí os dejo el google sheets final con la solución:
Datos de todos los modelos de zepelines ordenado por año descendiente
Para los que quieren ver el proceso paso a paso, aquí va desgranada la fórmula:
=query(
{
importrange("https://docs.google.com/spreadsheets/d/1alz7Mx8E6lcIB0T-K6Ex9biDfFl2PYs5JxrMNpI5CUA/edit#gid=0", "sabbath!A2:c");
importrange("https://docs.google.com/spreadsheets/d/1Bmy9kUqwmBlszKA9ZvUP-Kp2GzpxJh18d_F_7AO9tEA/edit#gid=0", "frost!a2:c");
importrange("https://docs.google.com/spreadsheets/d/1EfI9Hx5h7KnJ8K3LuVyCAK4MW_grWubLEmZdO2J9NWk/edit#gid=0", "symbolic!a2:c")
},
"select * where Col1 is not null order by Col1")
Aunque no sea este el formato habitual que utiliza Google Sheets, lo he dividido de esta manera para que fuera lo más legible y didáctico posible. Realmente, no hay nada demasiado complicado aquí, sólo tenemos que saber qué representa cada parte. Vayamos a la primera, la query. La estructura de la función query es la siguiente:
query(datos sobre los que se va a realizar la consulta,
la consulta que se va a realizar,
opción de poner títulos o no)
Bien, ahora ya sabemos que lo primero que debemos tener para poder lanzar nuestra query son los datos sobre los que queremos consultar. ¿Tenemos esta información? Sí, porque son todos los datos de los 3 modelos de zepelines almacenados en los otros 3 documentos de sheets. Para poder importar esos datos a nuestra pestaña de unificación, utilizamos el importrange, que sigue la estructura:
importrange(url del spreadsheets al que queremos acceder, nombre de pestaña y conjunto de datos)
En nuestro caso, queremos unificar 3 import ranges, por eso inmediatamente después de abrir la query, ponemos un bracket «{» y separaremos nuestros imports con un punto y coma «;». Esto le indica a Google sheets que se quiere hacer un agrupado de los datos y que lo tiene que hacer de forma vertical. Siguiendo con la estructura de la función que hemos visto arriba, nuestro primero import quedaría de la siguiente forma:
importrange("https://docs.google.com/spreadsheets/d/1alz7Mx8E6lcIB0T-K6Ex9biDfFl2PYs5JxrMNpI5CUA/edit#gid=0", "sabbath!A2:c")
Es muy importante en este paso, tener en cuenta el conjunto de datos que vamos a seleccionar, la segunda parte de nuestro import. ¿Por qué? Porque esto es lo que determinará que la importación se genere de forma automática siempre que se añada una nueva fila a nuestra selección. Y esto es tan sencillo como decir que en lugar de coger la información en formato fila (a2:c2) la cogemos en formato matriz (a2:c). Esto quiere decir que cogemos todo lo existente entre la celda a2 y la celda c»final». Por tanto, da igual que ahora mismo no haya información en la celda c137. Si algún día esa celda se rellena también la importaremos de forma automática.
Ya tenemos el primer import, repetimos este proceso para todas las urls que queramos introducir y cerramos nuestro bracket «}». Llega el turno de realizar nuestra consulta, el segundo paso fundamental de este proceso, con el que conseguiremos insertar los datos en nuestro documento de forma ordenada y limpia. Como ya hemos visto anteriormente, la parte de consulta en nuestra función era la siguiente:
"select * where Col1 is not null order by Col1"
Básicamente lo que le decimos con esto a Google Sheets es que de los datos que hemos importado (a través de los 3 importranges) sólo nos extraiga aquellos donde la primera columna no está en blanco, y que además, nos ordene los datos a través de esta columna (year). ¿Por qué debemos decirle esto? Porque sino, lo que haría Google Sheets sería traerse absolutamente todas las celdas entre a2 y c-final, por lo que tendríamos espacios de celdas en blanco gigantes entre un import y otro (todas las celdas en blanco que existan hasta el final del documento verticalmente). Lo de añadir el orden por año es simplemente una cuestión higiénica.
Debo añadir que es posible que te salga un error de ref debido a los importranges. Esto ocurre porque se tienen que dar acceso unos sheets a otros. Con clicar el permitir lo tendríamos resuelto:
El tema aquí está en que es posible que si esto ocurre y lo estás haciendo dentro de una query, el resultado será celdas en blanco. Para solucionarlo, simplemente intenta realizar el import fuera de la query y permite el acceso. La query se pondrá a trabajar al instante.
Y ya está, esto es todo. Ya tienes lista tu función para agregar datos desde diferentes sheets unificados en una sola pestaña. Espero vuestros comentarios, ¿cuál es tu solución en estos casos?
Visión de Funnel 