En este artículo veremos como tener 3 listas desplegables dependientes en todas las filas de un archivo de Google Sheets, cada una con un listado desplegable dependiente de la respuesta de la columna anterior.
Si prefieren verlo en video, aquí se los dejo:
Introducción
Ya hemos visto como crear listados desplegables básicos.
También como hacer listados desplegables dependientes o condicionales, para una sola vez.
Hasta vimos como hacer estos mismos listados desplegables pero en varias filas.
Hoy vamos a ampliar este último concepto para poder incluir más de una columna. Es decir, para tener más de dos listados desplegables, siempre dependiendo del anterior.
Lo que quiero lograr es lo siguiente:

Esto no se puede lograr con funcionalidades básicas de Sheets sino que me toca recurrir a código de Google Apps Script.
Estos son los pasos que voy a seguir:
- Definir como estructurar y organizar los datos
- Crear la primera lista
- Crear la segunda lista
- Crear la tercera lista
- Borrar los datos cuando escoja otra categoría diferente
1. Definir la estructura de los datos
Esto es bien importante, ya que esto va a cambiar mi código. Yo veo dos maneras de hacerlo.
La primera es con una sola pestaña con todos mis datos en 3 columnas, así:

La segunda es organizarlo por pestañas, donde cada pestaña es una categoría, cada columna es un país, y en las filas estarían las ciudades, así:

Hoy vamos a trabajar con el segundo orden, es decir con pestañas para cada categoría
2. Crear la primera lista
Para esto simplemente vamos a escoger la primera columna y vamos a crear una validación de datos. Como mi primera categoría está en los nombres de las pestañas, debo crear una nueva lista para esta validación, puede ser en una pestaña adicional llamada «Listas».
Simplemente asegurémonos que seleccionamos la opción «Rechazar entrada»

Segunda lista
Para esta segunda lista ya necesitamos código.
Básicamente lo que queremos hacer es que cada vez que el usuario escoja un continente (en la columna A) busquemos la pestaña que corresponda a ese continente y luego mostrar esos países en una nueva lista desplegable.
Lo que nuestro código debe hacer entonces es:
- Traer los datos de la celda donde se ha escogido el continente
- Asegurarnos que estemos donde el código se debe ejecutar:
- Asegurarnos que estemos en la hoja correcta
- Asegurarnos que estemos en filas no correspondientes a encabezados
- Asegurarnos que estemos en la columna del primer desplegable
- Buscar la pestaña donde está la lista de países que corresponde a ese continente.
- Asignar la validación de datos.
Traer los datos de la celda actual
Los datos que vamos a necesitar son:
La hoja actual
var ss = SpreadsheetApp.getActiveSpreadsheet();
var hojaActual = ss.getActiveSheet();
El nombre de la hoja actual
var nombreActual = hojaActual.getName();
La celda actual
var celdaActiva = hojaActual.getActiveCell();
La fila y columna de la celda actual
var filaActiva = celdaActiva.getRow();
var colActiva = celdaActiva.getColumn();
El valor de la celda actual
var valorActiva = celdaActiva.getValue();
Con esto ya tenemos todo lo que necesitamos
Asegurarnos que solo se ejecute en donde queremos
La hoja
Primero debemos asegurarnos que solo se ejecute en la hoja que queremos. En nuestro caso esta hoja se llama «Varias filas», entonces guardemos este valor.
const nombreHojaP = "Varias filas";
Ahora creamos un condicional donde se asegure que estemos en la hoja principal
if(nombreActual==nombreHojaP){
//Aqui ejecutamos nuestro cósigo
}
La fila
Ahora asegurémonos que el código no toque los encabezados. Normalmente solo tendremos un encabezado, pero en el caso que no debemos decirle a Sheets en que fila comienzan los datos. Creemos entonces una constante que diga la fila donde debería empezar a funcionar nuestro código:
const filaInicio = 2;
Ahora podemos comparar con nuestra fila actual
if(filaActiva>=filaInicio){
//Aquí si ejecutamos nuestro código
Inclusive, si queremos, podríamos juntar las dos condiciones anteriores (Hoja y fila) en una sola:
if(nombreActual==nombreHojaP && filaActiva>=filaInicio){
//Aqui ejecutamos nuestro cósigo
}
La columna
Como estamos hablando del primer desplegable, necesitamos saber en que columna va a estar ese primer desplegable. En nuestro caso es la 1, pero podría ser otro. Por eso es bueno crear otra constante para la columna donde se encuentra nuestra primera lista:
const colPrimerDesplegable = 1;
Ahora, volvemos a comparar nuestra columna actual para ver si coincide con la columna de mi primer desplegable
if (colActiva==colPrimerDesplegable){
//Aqui va el código para crear el primer desplegable
}
¡Y listo! Ahora si podemos proceder a crear nuestro desplegable
Buscar los datos de nuestra lista
Como ya tenemos el valor de la celda, y como estamos seguros de que el valor de esa celda va a coincidir con alguna de las pestañas de la primera categoría (en nuestro caso continentes), simplemente podemos «llamar» a la pestaña que tiene el mismo nombre que nuestra celda.
var hojaDatos = ss.getSheetByName(valorActiva);
Una vez tengamos acceso a la pestaña, vamos a guardar la primera fila (donde están los países) en una variable.
var listaPaises = hojaDatos.getRange(1,1,1,hojaDatos.getLastColumn());
Asignar la validacion de datos
Vamos ahora a tomar ese rango e incrustarlo dentro de una validación de datos de lista que vamos a crear.
var validacionPaises = SpreadsheetApp.newDataValidation().requireValueInRange(listaPaises).build();
Por último asignamos esta validación de datos a la celda que está una columna a la derecha de la celda donde esta nuestro primer desplegable (Es decir, donde se va a ubicar nuestro segundo desplegable).
celdaActiva.offset(0,1).setDataValidation(validacionPaises);
Nota: Esto es en el caso del ejemplo, pero puede presentarse el caso que el segundo desplegable no necesariamente está una columna a la derecha sino varias columnas a la derecha, o inclusive, a la izquierda. Aquí simplemente cambian el número del offset
Tercera lista
Con lo que ya hicimos para la segunda lista, tenemos la mitad del camino recorrido. Solo tenemos que hacer algunos ajustes.
Primero vamos a asignar la columna donde está la segunda lista, en nuestro caso la columna 2
const colSegundoDesplegable = 2;
Ahora vamos a crear el condicional donde comparamos si la columna donde estamos corresponde al segundo desplegable
else if(colActiva==colSegundoDesplegable){
//Aquí va el código para crear el tercer desplegable
}
Vamos ahora a ver que continente se ha escogido y vamos a seleccionar la pestaña correspondiente a ese continente
var nombreHojaCont = celdaActiva.offset(0,-1).getValue();
var hojaDatos = ss.getSheetByName(nombreHojaCont);
Ahora vamos a traer la lista de países pero a un arreglo
var listaPaises = hojaDatos.getRange(1,1,1,hojaDatos.getLastColumn()).getValues();
En el arreglo de países vamos a ubicar donde está el país escogido
var indice = listaPaises[0].indexOf(valorActiva)+1;
Vamos a traer el rango de las ciudades de ese país
var listaCiudades = hojaDatos.getRange(3,indice,hojaDatos.getLastRow())
Y al igual que en el anterior caso vamos a crear una validación de datos con ese rango y asignársela a la columna de las ciudades
var validacionCiudades = SpreadsheetApp.newDataValidation().requireValueInRange(listaCiudades).build();
celdaActiva.offset(0,1).setDataValidation(validacionCiudades);
Limpiar los datos
Lo último que vamos a hacer es asegurarnos que cuando yo escoja otro país o borre un dato, las validaciones se eliminen.
Para esto, en el caso del primer desplegable (Adentro del primer condicional) vamos a borrar los datos y las validaciones de la columna 2 y 3
celdaActiva.offset(0,1).clearContent().clearDataValidations();
celdaActiva.offset(0,2).clearContent().clearDataValidations();
Y en el caso del segundo desplegable vamos a borrar solo el tercero
celdaActiva.offset(0,1).clearContent().clearDataValidations();
Y listo!
Lo último que debemos hacer es incluir nuestra función dentro de un método onEdit() para asegurarnos que la función corra cada vez que cambiemos alguna de nuestras celdas del archivo
function onEdit(){
desplegables();
}
Código completo
function onEdit(){
desplegables();
}
function desplegables() {
// Constantes iniciales de configuracion
const nombreHojaP = "Varias filas";
const filaInicio = 2;
const colPrimerDesplegable = 1;
const colSegundoDesplegable = 2;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var hojaActual = ss.getActiveSheet();
if(hojaActual.getName()==nombreHojaP){
var celdaActiva = hojaActual.getActiveCell();
var filaActiva = celdaActiva.getRow();
var colActiva = celdaActiva.getColumn();
var valorActiva = celdaActiva.getValue();
if(filaActiva>=filaInicio){
if (colActiva==colPrimerDesplegable){
celdaActiva.offset(0,1).clearContent().clearDataValidations();
celdaActiva.offset(0,2).clearContent().clearDataValidations();
var hojaDatos = ss.getSheetByName(valorActiva);
var listaPaises = hojaDatos.getRange(1,1,1,hojaDatos.getLastColumn());
var validacionPaises = SpreadsheetApp.newDataValidation().requireValueInRange(listaPaises).build();
celdaActiva.offset(0,1).setDataValidation(validacionPaises);
}
else if(colActiva==colSegundoDesplegable){
celdaActiva.offset(0,1).clearContent().clearDataValidations();
var nombreHojaCont = celdaActiva.offset(0,-1).getValue();
var hojaDatos = ss.getSheetByName(nombreHojaCont);
var listaPaises = hojaDatos.getRange(1,1,1,hojaDatos.getLastColumn()).getValues();
var indice = listaPaises[0].indexOf(valorActiva)+1;
var listaCiudades = hojaDatos.getRange(3,indice,hojaDatos.getLastRow())
var validacionCiudades = SpreadsheetApp.newDataValidation().requireValueInRange(listaCiudades).build();
celdaActiva.offset(0,1).setDataValidation(validacionCiudades);
}
}
}
}
Conclusión y próximos pasos
Hemos construido tres listas desplegables, cada una dependiente de lo escogido en la lista anterior. Podríamos usar la misma lógica para construir 4, 5 o las que queramos.
Cuéntenme si les funcionó y que modificaciones le han hecho o que sugerencias para mejorarlo.
Hola buenas! Mil gracias por el contenido, me estaba volviendo loca buscando la manera de hacerlo.
Tengo una pregunta, el código se me ejecuta perfecto para la primera fila, pero cuando bajo a la segunda y la tercera ya no se me ejecuta, sino que para los valores de la segunda drop list sólo me deja seleccionar las mismas opciones que ya están marcadas en la misma columna.
Hola MArta
Pudiste solucionar?
Un gran saludo!
disculpa en caso de tener todos los datos en una sola hoja ¿Cómo seria?
me agrado el modo que utilizaste sin embargo como son muchos datos me complicaría tener tantas hojas. y siento que tardaría mas en ejecutar el script.
de antemano agradezco tu atención
Hola Daniel
Aquì te dejo un mejor mètodo
https://youtu.be/1p5cZ9l_Oqw
Saludos!
Buenas, he intentado hacerlo una segunda vez pero me sigue cogiendo los valores de la anterior condición. Espero que puedas ayudarme.
else if(colActiva==colTercerDesplegable){
celdaActiva.offset(0,1).clearContent().clearDataValidations();
var nombreHojaCont = celdaActiva.offset(0,-3).getValue();
var hojaDatos = ss.getSheetByName(nombreHojaCont);
var listacontenidos = hojaDatos.getRange(1,11,1,hojaDatos.getLastColumn()).getValues();
var indice_2 = listacontenidos[0].indexOf(valorActiva)+1;
var conjuntoContenidos_2 = hojaDatos.getRange(2,indice_2,hojaDatos.getLastRow())
var validacionContenidos_2 = SpreadsheetApp.newDataValidation().requireValueInRange(conjuntoContenidos_2).build();
celdaActiva.offset(0,1).setDataValidation(validacionContenidos_2);
Hola!
Intenta cambiar este
var nombreHojaCont = celdaActiva.offset(0,-3).getValue();
por -2
Puede que sea eso
Saludos!
Hola, es posible contar con esta funcionalidad pero desde un google forms? es decir que la lea las alternativas de respuesta desde la base de sheet con tres o más niveles de dependencia
Hola Maria
Desafortunadamente Google Forms no deja hacer esto ni siquiera con código
Un gran saludo!
Cómo se haría el script si los datos se encuentra en una hoja?
Hola!
Es parecido, pero ya no tenemos que hacer el indexOf
ahi tenemos que usar la función filter de JAvascript
Te dejo un script que hice personal mientras hago un video del tema
if(filaActiva>encabezados && columnaActiva ==colDesplegable && archivo.getActiveSheet().getName()==nombreHojaValidacion){
celdaActiva.offset(0,1).clearContent().clearDataValidations();
Logger.log(«Hola»);
if(condicional!=»){
//Filtro mi base por la condicion que se escogío «condicional»
var categoriasFiltradas = categorias.filter(function(fila){
return fila[0]===condicional;
})
//Traigo el rango para el segundo desplegable
var subcats = categoriasFiltradas.map(function(fila){
return fila[1];
})
//Obtengo rango
var rangoValidacion = subcats;
//Vuelvo el rango una validación
var reglaValidacion = SpreadsheetApp.newDataValidation().requireValueInList(rangoValidacion).build();
//Asigno la validación a la celda específica
celdaActiva.offset(0, 1).setDataValidation(reglaValidacion);
}
Saludos!