En este tutorial vamos a aprender a crear listados desplegables condicionales en Google Sheets utilizando la función FILTER de Google Apps Script. La idea es que, dependiendo de la selección que hagamos en un listado desplegable (por ejemplo, un continente), se actualice automáticamente otro desplegable con los países correspondientes a esa selección. Esto es súper útil para organizar datos jerárquicos como continente, país, ciudad y más niveles, de forma sencilla y flexible, especialmente cuando los datos están organizados en una tabla simple y no en múltiples pestañas o columnas separadas. Además, este método es escalable y permite agregar nuevos datos sin tener que reorganizar toda la estructura.
Ahora vamos a detallar cómo construir esta solución desde cero, explicando cada paso y el código necesario para que puedas replicarlo fácilmente.
Preparando los datos para los listados desplegables
Lo primero es tener una tabla con la información organizada en columnas, donde cada columna representa un nivel de jerarquía. Por ejemplo, en nuestro caso tendremos:
| Continente | País | Ciudad |
|---|---|---|
| Suramérica | Colombia | Bogotá |
| Europa | Alemania | Berlín |
| África | Egipto | El Cairo |
Esta estructura es mucho más flexible que tener los datos en varias pestañas o columnas separadas con encabezados, porque podemos agregar más niveles (departamento, barrio, etc.) simplemente agregando más columnas a la tabla. Además, es más fácil mantener los datos actualizados sin tener que reorganizarlos.
Creando el primer listado desplegable (Continentes)
Para el primer desplegable, que es el de Continentes, simplemente creamos una validación de datos con la lista de continentes únicos que tenemos en la tabla. Como los listados desplegables no necesitan que los valores sean únicos, podemos seleccionar toda la columna de continentes, desde la fila 2 hacia abajo, para que incluya futuros datos que agreguemos.
Para hacerlo:
- Selecciona la celda donde quieres el desplegable de continentes.
- Ve a Datos > Validación de datos.
- En el criterio elige Lista desde un rango.
- Selecciona el rango de continentes, por ejemplo
A2:A2000para que incluya posibles futuros datos. - Marca la opción para rechazar entradas no válidas y acepta.
Con esto ya tendrás un desplegable con los continentes para elegir.
Creando el listado desplegable condicional (Países) con Google Apps Script
Ahora viene la parte interesante: hacer que el desplegable de países muestre solo los países que pertenecen al continente seleccionado. Para esto usaremos Google Apps Script con la función FILTER de JavaScript, que nos permite filtrar el arreglo de datos según la categoría escogida.
La idea general del código es:
- Detectar la celda activa donde se hizo la selección del continente.
- Verificar que la edición suceda en la hoja y columna correctas (por ejemplo, la columna del continente).
- Traer todos los datos de referencia (la tabla con continentes, países y ciudades) en un arreglo para procesarlos rápido.
- Filtrar ese arreglo para quedarnos solo con las filas cuyo continente coincida con el seleccionado.
- Extraer solo la columna de países de esas filas filtradas.
- Actualizar el desplegable de países con esa lista filtrada.
- Limpiar la validación y contenido si el continente seleccionado queda vacío.
Veamos cómo hacer esto paso a paso en el editor de secuencias de comandos:
1. Abrimos el editor de secuencias de comandos con Extensiones > Apps Script y creamos un nuevo proyecto.
2. Definimos constantes para el nombre de la hoja donde se ingresan los datos y las columnas correspondientes:
const NOMBRE_HOJA_INGRESO = 'Ingreso de datos';
const COLUMNA_CONTINENTE = 3; // Por ejemplo, columna C
const COLUMNA_PAIS = 4; // Columna D donde irá el desplegable de países
const NOMBRE_HOJA_DATOS = 'Datos de referencia';3. Creamos la función principal que se ejecutará cada vez que editemos una celda (onEdit):
function onEdit(e) {
const hojaActiva = e.source.getActiveSheet();
const celdaActiva = e.range;
const valorSeleccionado = celdaActiva.getValue();
// Verificamos que estemos en la hoja correcta y en la columna del continente
if (hojaActiva.getName() !== NOMBRE_HOJA_INGRESO) return;
if (celdaActiva.getColumn() !== COLUMNA_CONTINENTE) return;
if (celdaActiva.getRow() <= 1) return; // Ignorar encabezados
// Si el valor está vacío, limpiamos validación y contenido de país
if (valorSeleccionado === '') {
hojaActiva.getRange(celdaActiva.getRow(), COLUMNA_PAIS).clearDataValidations().clearContent();
return;
}
// Traemos los datos de referencia
const hojaDatos = e.source.getSheetByName(NOMBRE_HOJA_DATOS);
const ultimaFila = hojaDatos.getLastRow();
const rangoDatos = hojaDatos.getRange(2, 1, ultimaFila -1, 2); // columnas continente y país
const datos = rangoDatos.getValues();
// Filtramos los países que correspondan al continente seleccionado
const paisesFiltrados = datos
.filter(fila => fila[0] === valorSeleccionado) // fila[0] es continente
.map(fila => fila[1]); // fila[1] es país
// Creamos la validación de datos para países
const reglaValidacion = SpreadsheetApp.newDataValidation()
.requireValueInList(paisesFiltrados, true)
.setAllowInvalid(false)
.build();
// Asignamos la validación a la celda correspondiente
hojaActiva.getRange(celdaActiva.getRow(), COLUMNA_PAIS).setDataValidation(reglaValidacion).clearContent();
}4. Guardamos el proyecto y autorizamos los permisos cuando el sistema lo solicite.
Con esto, cada vez que selecciones un continente en la columna correspondiente, la columna de países se actualizará automáticamente mostrando solo los países que pertenecen a ese continente.
Detalles y buenas prácticas del código
Algunos puntos importantes que debes tener en cuenta:
- Usamos constantes para los nombres de hojas y columnas para facilitar cambios futuros sin tener que buscar en todo el código.
- El filtro se hace con filter para seleccionar solo las filas que coinciden con el continente y luego con map extraemos solo la columna de países.
- Si la celda del continente queda vacía, limpiamos la validación y el contenido de la celda de país para evitar errores o datos inconsistentes.
- La validación de datos se crea con requireValueInList, que acepta una lista de elementos directamente, ideal para listas dinámicas.
- El código solo se ejecuta cuando editas la columna del continente y en la hoja correcta, para evitar que se dispare innecesariamente.
Cómo extenderlo a más niveles (ciudad, barrio, etc.)
La gran ventaja de esta estructura es que puedes añadir más niveles simplemente agregando más columnas a tu tabla de datos y replicando la lógica del script para cada nivel. Por ejemplo, para el nivel de ciudad, harías lo mismo: al seleccionar un país, filtras las ciudades correspondientes y actualizas el desplegable de ciudades.
Para eso, solo necesitas:
- Agregar constantes para las columnas de cada nivel.
- Modificar el script para detectar cambios en cada columna y actualizar el siguiente nivel.
- Filtrar el arreglo de datos usando las condiciones de todos los niveles anteriores (por ejemplo, continente y país para filtrar ciudades).
Esto hace que la solución sea muy flexible y escalable, ideal para bases de datos que crecen o que requieren múltiples niveles de selección.
Conclusión
En resumen, vimos cómo crear listados desplegables condicionales en Google Sheets usando Google Apps Script y la función FILTER de JavaScript para filtrar datos en arreglos. Este método es más sencillo y flexible que organizar los datos en múltiples pestañas o columnas con encabezados, y permite agregar fácilmente más niveles de jerarquía sin complicaciones. Además, el código se puede adaptar para limpiar validaciones y contenidos cuando las selecciones anteriores quedan vacías, evitando errores.
El siguiente paso puede ser extender este código para manejar más niveles como ciudades, barrios o cualquier otra categoría que necesites. Así podrás construir formularios o bases de datos dinámicas y escalables que se actualizan automáticamente según tus selecciones.
Si quieres aprender a usar Google Forms y Google Sheets desde cero, te invito a tomar un curso gratis de 2 horas.