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

Consolidar varios libros en uno solo en Google Sheets
— by

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

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de xiomara
    xiomara

    Buenas noches, gracias por toda la información util que dejas en cada uno de tus videos, quisiera saber como hacer un query para filtrar una tabla los valores que se encuentran en la columna de otra tabla, espero me pueda ayudar

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Ciomara

      Puedes explicar un poco más el ejemplo, si quieres sube una imagen. Saludos!

  2. Avatar de Aye
    Aye

    Hola!! es posible usar importrange y luego usar select where para traerme los datos en una columna tengan un valor MAYOR A cero en Col12….? usando esta formula, al contrario de lo que necesito, no me trae ninguna información (cuando en Col12 hay registros con valores mayores a cero… 🙁

    =query(IMPORTRANGE(«archivo «;»Incidencias internas!A1:T3000»);CONCATENAR(«SELECT Col1,Col4,Col5,Col6,Col12,Col16 WHERE Col12 >’»;0;»‘»);4)

    1. Avatar de jsguzmanb
      jsguzmanb

      Tu error está aca ‘»;0;»‘»)
      No necesitas poner comillas simples porque estamos hablando de un numero
      Saludos!

  3. Avatar de Carlos
    Carlos

    Hola Juan, una consulta, estoy intentando importar 3 archivos a una sola Hoja, utilizando estas fórmulas, pero el problema es que por ejemplo, en el segundo archivo, por ahora, no hay datos que cumplan el query, con lo cual me da error en toda la importación.
    Quisiera saber si podrías decirme por favor como solucionar este error.
    Desde ya muchas gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Carlos
      Claro que si
      Aquí te dejo un video que te puede ayudar
      https://youtu.be/YVf1WuF06jA
      Saludos!

  4. Avatar de juan pablo
    juan pablo

    Buenas noches he tenido el problema que me aparece la formula importrange con error (me dice que no tengo permisos) a la tabla que me quiero conectar soy el propietario que puede estar pasando ??? pero tampoco me aparece el botón para solicitar

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Dejame ver como tienes la fórmula

      Saludos!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This