3 Listas desplegables dependientes en todas mis filas

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:

1. Definir como estructurar y organizar los datos
2. Crear la primera lista
3. Crear la segunda lista
4. Crear la tercera lista
5. 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:

1. Traer los datos de la celda donde se ha escogido el continente
2. Asegurarnos que estemos donde el código se debe ejecutar:
   1. Asegurarnos que estemos en la hoja correcta
   2. Asegurarnos que estemos en filas no correspondientes a encabezados
   3. Asegurarnos que estemos en la columna del primer desplegable
3. Buscar la pestaña donde está la lista de países que corresponde a ese continente.
4. 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.