¿Te gustaría combinar datos de varias hojas en Google Sheets automáticamente, sin tener que editar tu fórmula cada vez que agregas o quitas una hoja? En este tutorial te muestro cómo automatizar la combinación de hojas usando Google Apps Script, generando un QUERY dinámico que se actualiza con solo presionar un botón. Este método es ideal si trabajas con reportes mensuales, consolidación de sucursales, inventarios, o cualquier caso donde la estructura de hojas puede cambiar con el tiempo. ¡Olvídate de los errores manuales y ahorra tiempo!
Si prefieres ver el paso a paso en video, aquí te lo dejo a continuación:
¿Cómo combinar varias hojas manualmente con QUERY?
Antes de entrar en la automatización, quiero mostrarte cómo se hace a la antigua: combinando hojas manualmente con la función QUERY. Imagina que tienes tres hojas llamadas México, Perú y Bolivia, todas con la misma estructura de columnas.
La fórmula típica para combinar estos datos sería así:
=QUERY({México!A:Z; Perú!A:Z; Bolivia!A:Z}; "SELECT * WHERE Col1 IS NOT NULL"; 1)¿Qué hace cada parte de esta fórmula?
- {México!A:Z; Perú!A:Z; Bolivia!A:Z}: Une los datos de las tres hojas, una debajo de la otra.
- SELECT *: Selecciona todas las columnas.
- WHERE Col1 IS NOT NULL: Filtra las filas vacías, mostrando solo las que tienen datos en la primera columna.
- 1: Indica que la primera fila es el encabezado.
El problema es que si mañana agregas la hoja Argentina o eliminas la de Bolivia, tienes que editar la fórmula manualmente. Esto se vuelve insostenible cuando tienes muchas hojas o cambian frecuentemente. Así que, vamos a automatizarlo.
Automatizando el QUERY con Google Apps Script
Paso 1: Abre el editor de Apps Script
- Haz clic en Extensiones > Apps Script en tu archivo de Google Sheets.
- Borra cualquier código que tengas y pega el siguiente script (más abajo te explico cómo funciona).
Este script genera automáticamente la fórmula QUERY combinando todas las hojas (excepto las que tú decidas excluir, como hojas de configuración o de resumen).
Aquí te dejo el código completo, pero si eres miembro de mi academia puedes ver la explicación detallada y el código comentado paso a paso:
¿Cómo funciona el script?
- Obtiene todas las hojas del archivo con getSheets().
- Filtra las hojas excluyendo las que no quieres combinar (puedes personalizar la lista hojasExcluidas).
- Construye la fórmula QUERY automáticamente con las hojas seleccionadas.
- Escribe la fórmula en la celda A1 de la hoja «Resumen».
Así, cada vez que agregues o elimines hojas (por ejemplo, un nuevo mes o una nueva sucursal), solo tienes que ejecutar el script y el QUERY se actualizará solo. ¡Nada de fórmulas eternas y errores de dedo!
Agrega un menú personalizado para ejecutar el script
Para que no tengas que entrar al editor cada vez, vamos a añadir un menú personalizado en tu archivo de Sheets. Así puedes actualizar el QUERY con un solo clic.
Con este código, cada vez que abras tu archivo, verás un menú llamado «Personalizado» en la barra superior. Desde ahí puedes elegir «Regenerar QUERY» para actualizar la consolidación de hojas.
¿Cómo probar el sistema paso a paso?
- Ejecuta la función onOpen() una vez desde el editor de Apps Script (o simplemente recarga la hoja).
- En tu hoja de cálculo, busca el menú «Personalizado» y haz clic en «Regenerar QUERY».
- Verifica que en la hoja «Resumen» aparezcan los datos combinados de todas las hojas activas.
- Agrega o elimina una hoja (por ejemplo, crea una hoja llamada «Chile» con la misma estructura), y vuelve a ejecutar «Regenerar QUERY». ¡Listo! El QUERY se ajusta automáticamente.
Errores comunes y recomendaciones
- Encabezados inconsistentes: Todas las hojas que vayas a combinar deben tener los mismos encabezados y el mismo rango de columnas (por ejemplo, de A a Z).
- Hojas vacías: Si una hoja está vacía, no pasa nada, el QUERY la ignorará por el filtro Col1 IS NOT NULL.
- Exclusión de hojas: Personaliza la lista hojasExcluidas para evitar combinar hojas de configuración, auxiliares o de resumen.
- Rango de columnas: Si tus hojas tienen menos columnas, ajusta el rango en el script (por ejemplo, A:F en vez de A:Z).
Conclusión
Con este sistema, puedes automatizar la combinación de hojas en Google Sheets y olvidarte de editar fórmulas cada vez que cambia la estructura de tu archivo. Es una solución flexible, escalable y fácil de usar. Si quieres profundizar aún más en automatizaciones con Apps Script y consolidación de datos, te recomiendo revisar mi curso de Google Apps Script desde cero.
Si quieres aprender a usar Google Forms y Google Sheets desde cero, te invito a tomar un curso gratis de 2 horas.