3 Listas desplegables dependientes en todas mis filas

3 listas desplegables dependientes en todas las filas en google sheets
— by

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.

,

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de Marta
    Marta

    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.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola MArta

      Pudiste solucionar?

      Un gran saludo!

  2. Avatar de Daniel González
    Daniel González

    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

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Daniel

      Aquì te dejo un mejor mètodo

      https://youtu.be/1p5cZ9l_Oqw

      Saludos!

  3. Avatar de Javier
    Javier

    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);

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Intenta cambiar este

      var nombreHojaCont = celdaActiva.offset(0,-3).getValue();

      por -2

      Puede que sea eso

      Saludos!

  4. Avatar de maria
    maria

    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

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Maria

      Desafortunadamente Google Forms no deja hacer esto ni siquiera con código

      Un gran saludo!

  5. Avatar de Monse
    Monse

    Cómo se haría el script si los datos se encuentra en una hoja?

    1. Avatar de jsguzmanb
      jsguzmanb

      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!

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