Consolidar varios archivos en una sola pestaña con arreglo, QUERY e IMPORTRANGE en Google Sheets

En este artículo vemos como combinar o consolidar distintas hojas de distintos archivos o libros en una sola con la ayuda de funciones de Google Sheets como IMPORTRANGE, QUERY y SORT

Hay varias formas de combinar hojas de distintos archivos en Google Sheets, desde la manual hasta la automatización total con macros. Vamos a ver todas las opciones, desde la más sencilla (pero que quita más tiempo) hasta la más compleja en el momento (pero que en últimas nos ahorra más tiempo).

Supuestos

Para todos los casos que vamos a trabajar vamos a manejar algunos supuestos que es mejor escribir desde el principio:

1. Siempre vamos a consolidar las mismas pestañas
2. Todas las pestañas tienen los mismos campos o columnas
3. Las columnas están en el mismo orden

Para el articulo vamos a usar un mismo ejemplo: una base «Consolidado», y tres archivos diferentes cada uno para un año: «2017», «2018» y «2019». Todos los archivos tienen una sola pestaña llamada «Datos»

Forma manual

La forma manual es el clásicocopiar y pegar

Primero copio y pego el encabezado de alguna de las bases en la pestaña Total

Despues me ubico en la celda a2 de la pestaña «Datos» del archivo «2017», presiono ctrl shift sbajo y ctrl shift derecha para seleccionar todos los datos(sin encabezado)

Ahora repetimos este proceso para las demas pestañas

Obviamente es el peor método, pero al detallar sus limitaciones nos ayuda a entender mejor lo que queremos hacer con nuestros métodos más avanzados.

Desventajas de este método

1. Es muy demorado si tengo más de 3 archivos
2. Ya no funciona si alguna pestaña cambia de nombre o cambio el orden de dos de las columnas
3. Cada vez que se actualizan los datos, el consolidado ya no es confiable
4. Cada vez que agrego nuevos datos debo actualizar

Método 2: ImportRange

Desafortunadamente Sheets no funciona como Excel donde yo puedo simplemente referenciar otras celdas de otros archivos.

Para esto necesitamos la ayuda de una función adicional: IMPORTRANGE

Les dejo un artículo entero dedicado al IMPORTRANGE

En este caso vamos a empezar trayendo el IMPORTRANGE de 2017

Para esto necesitamos dos cosas: el ID del archivo y el rango que queremos traer

Así quedaría nuestro IMPORTRANGE para el 2017

importrange("1lcLuHi2h_Miww-ZGSIqpez1WAc2msGHCemHg6aiHXU4";"Datos!A2:I85")

Aquí haríamos una copia de los datos del 2017

Nota: La primera vez que usan el importrange les va a pedir que le den permiso al archivo para acceder

Lo mismo para los otros paises

Pero como lo que queremos es consolidar, entonces necesitamos alguna manera en que podamos juntar una serie de datos después de la otra

Para esto vamos a usar los arreglos

Aquí les dejo un artículo sobre arreglos

Con la ayuda del punto y coma vamos a juntar todos los importrange asi:

={
importrange("1lcLuHi2h_Miww-ZGSIqpez1WAc2msGHCemHg6aiHXU4";"Datos!A2:I85");
importrange("1SEjsECHkW1C5qy7FtyBVV-xM1YKX0qLZbiarWPH9pfE";"Datos!A2:I100");
importrange("1NI715qzJnJI4dM_MK4DJmryJeypsr7NT0j0acoWsf_4;"Datos!A2:I92")
}

Nota: Les recomiendo que primero usen cada importrange de manera individual para que les pida cada permiso antes de consolidarlo, porque si no puede que no les funcione el arreglo

En principio ya con esto solucionamos el problema

El único problema con esto es que si llegamos a agregar nuevas filas a cualquiera de nuestras hojas externas, tendríamos que modificar los rangos en la fórmula

Para esto deberíamos dejar abiertos los rangos, es decir borrar el número final: en vez de Datos!A2:I85 quedaría Datos!A2:I

Entonces nuestra fórmula completa quedaría

={
importrange("1lcLuHi2h_Miww-ZGSIqpez1WAc2msGHCemHg6aiHXU4";"Datos!A2:I");
importrange("1SEjsECHkW1C5qy7FtyBVV-xM1YKX0qLZbiarWPH9pfE";"Datos!A2:I");
importrange("1NI715qzJnJI4dM_MK4DJmryJeypsr7NT0j0acoWsf_4;"Datos!A2:I")
}

El problema de este método es que nos van a quedar una cantidad de espacios vacíos entre base y base.

Para quitar estos espacios tenemos varias opciones. Hoy les muestro dos:

1. Ordenar

Tan fácil como envolver todo en una función SORT

=SORT({
importrange("1lcLuHi2h_Miww-ZGSIqpez1WAc2msGHCemHg6aiHXU4";"Datos!A2:I");
importrange("1SEjsECHkW1C5qy7FtyBVV-xM1YKX0qLZbiarWPH9pfE";"Datos!A2:I");
importrange("1NI715qzJnJI4dM_MK4DJmryJeypsr7NT0j0acoWsf_4;"Datos!A2:I")
})

2. Filtrar con un QUERY

Aquí vamos a usar un QUERY con una función que filtre los vacíos, para esto vamos a escoger una columna que estemos seguros de que siempre va a tener datos, en nuestro caso la primera columna. Y le vamos a decir «WHERE Col1 IS NOT NULL».

Así quedaría nuestro ejemplo:

=QUERY({
importrange("1lcLuHi2h_Miww-ZGSIqpez1WAc2msGHCemHg6aiHXU4";"Datos!A2:I");
importrange("1SEjsECHkW1C5qy7FtyBVV-xM1YKX0qLZbiarWPH9pfE";"Datos!A2:I");
importrange("1NI715qzJnJI4dM_MK4DJmryJeypsr7NT0j0acoWsf_4;"Datos!A2:I")
};"SELECT * WHERE Col1 IS NOT NULL")

Conclusión

Si lo ven, no es tan difícil consolidar hojas de distintos libros en unos solo. Solo necesitamos saber las claves ID de cada libro, y usar un importrange con cada una y por último unirlos todos con un arreglo o con alguna función adicional como SORT, QUERY o FILTER

Me cuentan si les funciona y que limitaciones le ven