Lista desplegable condicional o dependiente en Google Sheets

Desplegables dependientes en Google Sheets
— by

Las listas desplegables son muy útiles en Google Sheets. Sin embargo cuando tenemos muchos datos, pueden llegar a ser difíciles de manejar

Aquí es donde entran las listas desplegables dependientes o condicionales.

Es una forma de acotar o clasificar los datos en categorías

En esta entrada vemos como crear una lista desplegable condicional en Google Sheets. Es decir una lista desplegable que cambie dependiendo de lo que se escoja en otra lista.

Qué es una lista desplegable

Una lista desplegable es una forma muy fácil de limitar las opciones que tiene un usuario de nuestra hoja de cálculo, para escoger entre una serie de opciones que le presentamos.

Es muy útil por ejemplo, para informes o dashboards dinámicos, donde dependiendo de la opción que el usuario escoja puede ver información condicional con la ayuda de funciones como BUSCARV, SUMAR.SI, CONTAR.SI, FILTER, y QUERY, entre otras.

Aquí podemos ver todo acerca de como crear listas desplegables en Google Sheets .

Qué es una lista desplegable condicional.

Un problema un poco más avanzado que nos podemos encontrar es cuando tenemos dos listas desplegables. Veámoslo de manera más clara con un ejemplo.

Digamos que tenemos una base de población por continente y país.

Por otro lado tenemos un informe donde queremos permitirle al usuario escoger el país, para poder ver la población del que elija.

Hasta ahora no habría problema. Sería simplemente crear una lista desplegable con los países.

Sin embargo si son muchos países, sería mucho más fácil tener dos menús desplegables, uno de continente y uno de país

La dificultad incrementa porque ahora tenemos que «conectar» esas dos listas de alguna manera, para que cuando escojamos un continente, la lista de países se actualice y se «filtre» con solo los países de ese continente.

Hay varios métodos para hacer esto, pero todos empiezan igual: con nuestra primera lista desplegable, la cual construimos con una simple validación de datos.

Encontrarás un curso completo de listas desplegables condicionales en la academia de Sheets JuanSGuzman

Creando la primera lista desplegable

Para empezar vamos a crear una lista para los continentes.

Ya vimos en este artículo como crear una lista desplegable, pero volvámoslo a ver rápidamente:

  • Primero tenemos que escoger la celda donde vamos a querer insertar la lista.
  • Después nos vamos a «Validación de Datos» (con clic derecho, o en «Datos» > «Validación de Datos»)
  • En la sección «Criterios» dejamos escogida la opción «Lista a partir de un intervalo» y escogemos el rango donde están nuestras categorías.

Pero antes de hacerlo tenemos que decidir como tenemos nuestros datos ordenados, porque de eso va a depender no solo el método que usemos para nuestra segunda lista, sino donde estarán los datos para nuestra primera lista.

Método 1: Lista ordenada por categorías

La primera opción es que ordenemos nuestros países debajo de cada continente así:

Esto quiere decir que nuestra primera categoría (continentes) queda en la primera fila

Y las subcategorías quedan en cada columna debajo de su respectiva categoría (continente)

Entonces cuando vayamos a hacer nuestra primera validación de datos, escogemos como lista la primera fila, algo así como A1:H1 en nuestro caso (O A1:M1 para dejar espacio para más categorías en el futuro)

Método 2: Lista tipo Base de Datos

La segunda opción es que nuestros datos no esten tan ordenados (porque no queremos o no podemos, y simplemente esten en una tabla común y corriente, como en el siguiente ejemplo:

En este caso nuestra primera lista seria la primera columna entera. Afortunadamente la validación de datos de Sheets es muy inteligente y si le «damos» un listado con ítems repetidos, me elimina automáticamente los repetidos, entonces no tengo que hacer nada adicional.

Sin importar el método, entonces nuestra primera lista quedaría así:

Creando la segunda lista desplegable

Ahora para los países, lo primero que se nos ocurriría es hacer exactamente lo mismo que para los continentes. Hagámoslo:

En el caso de las listas ordenadas, escogemos todo nuestro rango de países:

En el caso de la tabla, simplemente escogemos la segunda columna

El resultado sería similar al del continente.

Esto funcionaria.

Pero quiero ir un paso más allá. Quiero hacerle la vida más fácil al usuario (así el usuario sea yo). Quiero que cuando el usuario escoja un continente, solo le aparezcan los países correspondientes a ese continente.

A diferencia de Excel, donde esto lo podemos hacer directamente en la validación con la ayuda de la función INDIRECTO, aquí tenemos que ser más creativos y crear una lista auxiliar. Esta lista la podemos crear de varias maneras. Hoy vamos a ver tres maneras diferentes de hacer lo mismo, todo depende de como tengan organizados sus datos.

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Método 1: Función INDIRECTO

Una primera forma de hacerlo, es copiando, o mejor, imitando a como normalmente lo hacemos en Excel, con la ayuda de la función INDIRECTO y de los Intervalos con Nombre.

En pocas palabras, lo que hace la función INDIRECTO es tratar una referencia como un texto, esto es buenísimo para usar nombres de pestañas como variables, o Intervalos con Nombre. Aquí puedes ver más detalles acerca de la función INDIRECTO.

El Intervalo con Nombre, por su lado es una forma de nombrar rangos o referencias, y hacerlos más fáciles de usar en funciones o validaciones. Aquí puedes ver más detalles acerca de los Intervalos con Nombre.

Este método funciona muy bien para cuando tenemos organizadas nuestras categorías y subcategorías en listas en una hoja aparte y donde el encabezado es el nombre de la categoría y debajo de este están los listados condicionales, como ya lo vimos en nuestro primer ejemplo, así:

Lo primero que tenemos que hacer es asignarle a cada lista de ciudades el Nombre del país, y lo más importante es que debe ser exactamente igual. Si mi país es «Perú», mi intervalo con Nombre tiene que llamarse «Perú». Si no, no va a funcionar.

Esta es la parte «aburrida» de este método: tenemos que coger cada lista de países, darle clic derecho ir a Intervalo con Nombre y asignarle el nombre del continente correspondiente.

Lo que ahora tenemos que hacer es buscar un lugar donde vayamos a crear nuestra lista auxiliar. Para el ejemplo puede ser en la misma hoja donde están las listas desplegables, pero para evitarnos problemas y daños es preferible tenerla en una hoja aparte.

Esta lista auxiliar la vamos a construir con la siguiente fórmula:

=INDIRECTO(B1)

Y B1 es donde esta el valor de mi categoría, en nuestro caso, el continente.

Y no es más.

Lo que hace la función INDIRECTO entonces, es ir a buscar el intervalo con nombre que tenga este nombre exacto.

Lo último que tenemos que hacer es asignarle a la segunda lista desplegable nuestra lista auxiliar, siempre dejando espacio suficiente para nuestra lista de países más larga. En nuestro ejemplo, África es el continente con más países (57), entonces nos debemos asegurar que nuestra lista auxiliar por lo menos sea de 57 filas, como empieza desde la fila 2, sería D2:D58.

Encontrarás un curso completo de listas desplegables condicionales en la academia de Sheets JuanSGuzman

Método 2: Introduciendo la función FILTER

Si nuestros datos estan ordenados en una tabla como en el ejemplo 2 que ya vimos, no podemos usar el anterior método:

En este caso, otra forma de crear la lista auxiliar, es con la ayuda de la función FILTER.

FILTER es una función exclusiva de Sheets, que hace lo mismo que haría cualquier filtro, pero desde una función. Es decir, que puedo programáticamente crear un filtro dinámico.

La función FILTER acepta mínimo dos argumentos: el rango, y la condición. Digo mínimo, porque pueden ser múltiples condiciones. Para el ejemplo solo necesitamos uno: que el continente sea el que el usuario ha escogido.

Por ejemplo digamos que queremos filtrar todas los países de Suramerica.

Entonces el rango que quiero filtrar es el de los países (Columna B), ya que solo quiero que me liste estos países

Y la condición sería un continente especifico, es decir que la columna A sea igual a un continente. Para ver el ejemplo más fácil, pongamos la constante «Suramerica»

Para esto usaría la función:

=FILTER(B2:B236;A2:A236=»Suramerica»)

Esto me da:

Ahora entonces solo me faltaría vincular la categoría (continente) a la celda donde está la lista desplegable del continente, y arreglar la función FILTER para que traiga los datos de la pestaña donde están los continentes y los países (En nuestro caso «Datos»)

Entonces modificaríamos la función FILTER así

=FILTER(Datos!B2:B236;Datos!A2:A236=B1)

Y, por último, hacemos la validación de datos con la lista auxiliar, de la misma manera que lo hicimos con el método indirecto.

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Listas desplegables en varias filas

Ahora, lo que queremos ver es como podemos replicar los desplegables de país y ciudad pero en varias filas. Algo como esto:

Desafortunadamente (o afortunadamente, para los que nos gustan los retos), esto no se puede hacer con funciones en Google Sheets.

Para este caso, tenemos que trabajar con código de Google Apps Script, o lo que (mal) se conoce como Macros de Google Sheets.

En otras palabras tenemos que ensuciarnos las manos codificando en Apps Script. Aquí les dejo una introducción a programar en Google Apps Script.

Esto se puede hacer de varias maneras. En esta ocasión vamos a ver 2 formas diferentes, que dependen de como tenemos estructurados nuestros datos base.

El método 1 implica que mis datos esten estructurados de la siguiente manerta:

Es decir que las categorias estan en la primera fila, y debajo de cada categoria, estarian sus correspondientes subcategorias

Esto es muy util para cuando solo hay 2 niveles, es decir solo categoria y subcategoria, y no hay subsubcategoria.

Una desventaja es que si tengo muchas categorias se me puede volver una tabla muy grande y dificil de revisar o editar, pero se que en muchos casos puede ser la mejor opción

El otro método, en mi opinión es más flexible ya que sirve para muchas categorías y subcategorías y es muy fácil agregar nuevas subcategorías.

En este segundo metodo los datos deberian estar estructurados de la siguiente manera

Es decir, los valores de las categorías en una primera columna, y los valores de las subcategorías correspondientes en una segunda columna. No importa que se repitan los valores de las categorías

Lo interesante de este método es que es muy fácil agregar sub-sub-categorías así

Y ni siquiera tienen que estar en orden; si se me ocurrió una nueva ciudad de Chile, simplemente la puedo poner al final de todos mis datos

Vamos a ver los dos métodos en detalle, pero es importante escoger como tienes tus datos para que no pierdas tiempo viendo el método que no es

Método 1: Subcategoría debajo de las categorias

Vamos a ver el paso a paso de lo que queremos hacer.

  1. Crear un listado de países (en una fila)
  2. Crear una lista desplegable simple para los países (usando el listado de países) en la columna A.
  3. Crear un listado de ciudades debajo de cada país
  4. Buscar el país escogido en la lista de países
  5. Asignar las ciudades de ese país a un rango de validación
  6. Asignar ese rango de validación a la celda de la ciudad

Vamos entonces a mirar en detalle el proceso y el código:

1. Crear un listado de países

i – Crear una nueva pestaña y llamarla «Listas».

ii – En la primera fila creamos nuestra lista de países.

2. Crear la columna de países con la lista desplegable de países

i – Crear una nueva pestaña y llamarla «Datos»

ii – Escribimos el encabezado «País» en la celda A1

iii – Seleccionamos la celda A2 y presionamos «Ctrl» + «Shift» + «Abajo» para seleccionar toda la columna A desde la fila 2.

iv – Clic derecho, y escoger «Validación de datos»

v – En «Criterios» escogemos «Lista a partir de un intervalo» y en el intervalo escogemos la primera fila de la pestaña «Listas» (Mejor escogerla completa para adelantarnos a la posibilidad de que hayan muchos países)

vi – Dar click en «Guardar»

3. Crear el listado de ciudades de cada país

i – En la pestaña «Listas», vamos a insertar las ciudades debajo de cada país. (Dejo una fila entre país y ciudades más por orden que cualquier otra cosa).

ii – Crear el encabezado de ciudades en la pestaña «Datos»

4. Buscar el país en nuestra lista de países

i – Vamos a «Herramientas» > «Editor de Secuencia de Comandos»

ii – Vamos a llamar a nuestra función «onEdit()»

iii – Vamos a «conectarnos» con nuestro archivo, nuestra pestaña y la celda activa

iv – Vamos a guardar en una variable el país escogido

function onEdit() {

/*Nos conectamos a Google Sheets, a la pestaña "Datos" y a la celda en la que el usuario esta "parado" */
var archivo = SpreadsheetApp.getActiveSpreadsheet();
var hojaDatos = archivo.getSheetByName("Datos");
var celdaActiva = hojaDatos.getActiveCell();

//Este es el país escogido
var pais = celdaActiva.getValue();
}

v – Vamos a guardar en una variable la fila de la celda activa, y en otra variable la columna la columna

vi – Nos «conectamos» con la hoja donde están los países.

vii – Guardamos en un arreglo los países

var filaActiva = celdaActiva.getRow();
var columnaActiva = celdaActiva.getColumn();

//Conectarse con la hoja de listas
var hojaLista = archivo.getSheetByName("Listas");

//Lista de Países
var paises =hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

viii – nos aseguramos que el código solo se ejecute cuando se modifiquen las celdas donde hay países (Columna 1, Fila de la 2 en adelante). Ya que estamos aquí tambien vamos a incluir un condicional de la hoja donde quiero que se muestren los desplegables. Esto lo hacemos con un condicional «If»

  //Acciones para la columna 1
  if(filaActiva>1 && columnaActiva ==1 && celdaActiva.getName()=="Datos"){

  }

ix – Buscamos en el arreglo la columna del país actual país actual (Dentro del if)

//Encontrar la columna en la que esta el país (y por consiguiente, las ciudades)
  var indice = paises[0].indexOf(pais);

5. Asignar las ciudades de ese país a un rango de validación

i – Una vez tenemos la columna del país, podemos usar este para extraer el rango de ciudades (Dentro del if)

var rangoValidacion = hojaLista.getRange(3,indice+1,hojaLista.getLastRow());

ii – Creamos la regla de validación con el rango anterior

var reglaValidacion = SpreadsheetApp.newDataValidation().requireValueInRange(rangoValidacion).build();

iii – Asignamos la regla de validación a la celda de la ciudad (la celda que esta una columna a la derecha de la celda activa.

celdaActiva.offset(0, 1).setDataValidation(reglaValidacion);

Y listo!

Código completo

// Función para tener validacion de datos condicional en toda una columna
function onEdit() {
  // Conexión de datos
  var archivo = SpreadsheetApp.getActiveSpreadsheet();
  var hojaDatos = archivo.getSheetByName("Datos");
  var celdaActiva = hojaDatos.getActiveCell();
  var hojaLista = archivo.getSheetByName("Listas");
  
  //Datos de la celda Activa
  var pais = celdaActiva.getValue();
  var filaActiva = celdaActiva.getRow();
  var columnaActiva = celdaActiva.getColumn();

  //Acciones para la columna 1
  if(filaActiva>1 && columnaActiva==1  && celdaActiva.getName()=="Datos"){
    
//Lista de Países
  var paises =hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

    //Encuentro columna del país
      var indice = paises[0].indexOf(pais);
    //Obtengo rango de ciudades
      var rangoValidacion = hojaLista.getRange(3,indice+1,hojaLista.getLastRow());
    //Vuelvo el rango una validación
      var reglaValidacion = 
            SpreadsheetApp.newDataValidation().requireValueInRange(rangoValidacion).build();
    //Asigno la validación a la celda específica
      celdaActiva.offset(0, 1).setDataValidation(reglaValidacion);
  }
}

Así quedarían nuestras listas:

Método 2: Usando Filtros

Ya establecimos que en este segundo método nuestras categorías y subcategorías están organizadas en «modo tabla»

Sin embargo no tenemos que empezar de cero el código: podemos tomar toda la parte del condicional y partir de ahí

// Función para tener validación de datos condicional en toda una columna
function onEdit() {
  // Conexión de datos
  var archivo = SpreadsheetApp.getActiveSpreadsheet();
  var hojaDatos = archivo.getSheetByName("Datos");
  var celdaActiva = hojaDatos.getActiveCell();
  var hojaLista = archivo.getSheetByName("Listas");
  
  //Datos de la celda Activa
  var pais = celdaActiva.getValue();
  var filaActiva = celdaActiva.getRow();
  var columnaActiva = celdaActiva.getColumn();

  //Acciones para la columna 1
  if(filaActiva>1 && columnaActiva ==1 && celdaActiva.getName()=="Datos"){
    //Aquí vamos a insertar la validación
  }
}

Listas desplegables en varias filas

Ahora, vamos a llevar esto al siguiente nivel.

Lo que queremos ver es como podemos replicar los desplegables de país y ciudad pero en varias filas. Algo como esto:

Desafortunadamente (o afortunadamente, para los que nos gustan los retos), esto no se puede hacer con funciones en Google Sheets.

Para este caso, tenemos que trabajar con código de Google Apps Script, o lo que (mal) se conoce como Macros de Google Sheets.

En otras palabras tenemos que «ensuciarnos las manos» codificando en Apps Script. Aquí les dejo una introducción a programar en Google Apps Script.

Así como lo vimos en los condicionales de una sola fila, esto se puede hacer de varias maneras que dependen de como tenemos estructurados nuestros datos base.

El primer método implica que mis datos estén estructurados «tipo listas» de la siguiente manera:

Es decir que las categorias estan en la primera fila, y debajo de cada categoria, estarian sus correspondientes subcategorias

Esto es muy util para cuando solo hay 2 niveles, es decir solo categoria y subcategoria, y no hay subsubcategoria.

Una desventaja es que si tengo muchas categorias se me puede volver una tabla muy grande y dificil de revisar o editar, pero se que en muchos casos puede ser la mejor opción

El otro método, en mi opinión es más flexible ya que sirve para muchas categorías y subcategorías y es muy fácil agregar nuevas subcategorías.

En este segundo metodo los datos deberian estar estructurados de la siguiente manera

Es decir, los valores de las categorías en una primera columna, y los valores de las subcategorías correspondientes en una segunda columna. No importa que se repitan los valores de las categorías

Lo interesante de este método es que es muy fácil agregar sub-sub-categorías así

Y ni siquiera tienen que estar en orden; si se me ocurrió una nueva ciudad de Chile, simplemente la puedo poner al final de todos mis datos

Vamos a ver los dos métodos en detalle, pero es importante escoger como tienes tus datos para que no pierdas tiempo viendo el método que no es

Método 1: Subcategoría debajo de las categorias

Vamos a ver el paso a paso de lo que queremos hacer.

  1. Crear un listado de países (en una fila)
  2. Crear una lista desplegable simple para los países (usando el listado de países) en la columna A.
  3. Crear un listado de ciudades debajo de cada país
  4. Buscar el país escogido en la lista de países
  5. Asignar las ciudades de ese país a un rango de validación
  6. Asignar ese rango de validación a la celda de la ciudad

Vamos entonces a mirar en detalle el proceso y el código:

1. Crear un listado de países

i – Crear una nueva pestaña y llamarla «Listas».

ii – En la primera fila creamos nuestra lista de países.

2. Crear la columna de países con la lista desplegable de países

i – Crear una nueva pestaña y llamarla «Datos»

ii – Escribimos el encabezado «País» en la celda A1

iii – Seleccionamos la celda A2 y presionamos «Ctrl» + «Shift» + «Abajo» para seleccionar toda la columna A desde la fila 2.

iv – Clic derecho, y escoger «Validación de datos»

v – En «Criterios» escogemos «Lista a partir de un intervalo» y en el intervalo escogemos la primera fila de la pestaña «Listas» (Mejor escogerla completa para adelantarnos a la posibilidad de que hayan muchos países)

vi – Dar click en «Guardar»

3. Crear el listado de ciudades de cada país

i – En la pestaña «Listas», vamos a insertar las ciudades debajo de cada país. (Dejo una fila entre país y ciudades más por orden que cualquier otra cosa).

ii – Crear el encabezado de ciudades en la pestaña «Datos»

4. Buscar el país en nuestra lista de países

i – Vamos a «Herramientas» > «Editor de Secuencia de Comandos»

ii – Vamos a llamar a nuestra función «onEdit()»

iii – Vamos a «conectarnos» con nuestro archivo, nuestra pestaña y la celda activa

iv – Vamos a guardar en una variable el país escogido

function onEdit() {

/*Nos conectamos a Google Sheets, a la pestaña "Datos" y a la celda en la que el usuario esta "parado" */
var archivo = SpreadsheetApp.getActiveSpreadsheet();
var hojaDatos = archivo.getSheetByName("Datos");
var celdaActiva = hojaDatos.getActiveCell();

//Este es el país escogido
var pais = celdaActiva.getValue();
}

v – Vamos a guardar en una variable la fila de la celda activa, y en otra variable la columna la columna

vi – Nos «conectamos» con la hoja donde están los países.

vii – Guardamos en un arreglo los países

var filaActiva = celdaActiva.getRow();
var columnaActiva = celdaActiva.getColumn();

//Conectarse con la hoja de listas
var hojaLista = archivo.getSheetByName("Listas");

//Lista de Países
var paises =hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

viii – nos aseguramos que el código solo se ejecute cuando se modifiquen las celdas donde hay países (Columna 1, Fila de la 2 en adelante). Ya que estamos aquí tambien vamos a incluir un condicional de la hoja donde quiero que se muestren los desplegables. Esto lo hacemos con un condicional «If»

  //Acciones para la columna 1
  if(filaActiva>1 && columnaActiva ==1 && celdaActiva.getName()=="Datos"){

  }

ix – Buscamos en el arreglo la columna del país actual país actual (Dentro del if)

//Encontrar la columna en la que esta el país (y por consiguiente, las ciudades)
  var indice = paises[0].indexOf(pais);

5. Asignar las ciudades de ese país a un rango de validación

i – Una vez tenemos la columna del país, podemos usar este para extraer el rango de ciudades (Dentro del if)

var rangoValidacion = hojaLista.getRange(3,indice+1,hojaLista.getLastRow());

ii – Creamos la regla de validación con el rango anterior

var reglaValidacion = SpreadsheetApp.newDataValidation().requireValueInRange(rangoValidacion).build();

iii – Asignamos la regla de validación a la celda de la ciudad (la celda que esta una columna a la derecha de la celda activa.

celdaActiva.offset(0, 1).setDataValidation(reglaValidacion);

Y listo!

Código completo

// Función para tener validacion de datos condicional en toda una columna
function onEdit() {
  // Conexión de datos
  var archivo = SpreadsheetApp.getActiveSpreadsheet();
  var hojaDatos = archivo.getSheetByName("Datos");
  var celdaActiva = hojaDatos.getActiveCell();
  var hojaLista = archivo.getSheetByName("Listas");
  
  //Datos de la celda Activa
  var pais = celdaActiva.getValue();
  var filaActiva = celdaActiva.getRow();
  var columnaActiva = celdaActiva.getColumn();

  //Acciones para la columna 1
  if(filaActiva>1 && columnaActiva==1  && celdaActiva.getName()=="Datos"){
    
//Lista de Países
  var paises =hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

    //Encuentro columna del país
      var indice = paises[0].indexOf(pais);
    //Obtengo rango de ciudades
      var rangoValidacion = hojaLista.getRange(3,indice+1,hojaLista.getLastRow());
    //Vuelvo el rango una validación
      var reglaValidacion = 
            SpreadsheetApp.newDataValidation().requireValueInRange(rangoValidacion).build();
    //Asigno la validación a la celda específica
      celdaActiva.offset(0, 1).setDataValidation(reglaValidacion);
  }
}

Así quedarían nuestras listas:

Método 2: Usando Filtros

Ya establecimos que en este segundo método nuestras categorías y subcategorías están organizadas en «modo tabla»

Sin embargo no tenemos que empezar de cero el código: podemos tomar toda la parte del condicional y partir de ahí

// Función para tener validación de datos condicional en toda una columna
function onEdit() {
  // Conexión de datos
  var archivo = SpreadsheetApp.getActiveSpreadsheet();
  var hojaDatos = archivo.getSheetByName("Datos");
  var celdaActiva = hojaDatos.getActiveCell();
  var hojaLista = archivo.getSheetByName("Listas");
  
  //Datos de la celda Activa
  var pais = celdaActiva.getValue();
  var filaActiva = celdaActiva.getRow();
  var columnaActiva = celdaActiva.getColumn();

  //Acciones para la columna 1
  if(filaActiva>1 && columnaActiva ==1 && celdaActiva.getName()=="Datos"){
    //Aquí vamos a insertar la validación
  }
}

Conclusión

Como ves, no es difícil hacer una lista desplegable dependiente o condicional en Sheets. Lo más importante es ver como tenemos nuestros datos ordenados, y dependiendo de esto escoger la metodología que se ajuste a ellos.

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de Nicolas
    Nicolas

    No quería hacer precisamente lo mismo que explicaste, pero si necesitaba de las herramientas y métodos, y fue solo gracias a lo excelente que esta tanto explicado como ejecutado todo, que pude hacer lo que buscaba. Mil gracias por todo tu tiempo.

    1. Avatar de jsguzmanb
      jsguzmanb

      Mi gracias a ti por las palabras!
      Saludos!

  2. Avatar de Nestor
    Nestor

    Wow! Que artículo tan fascinante. Me ayudó muchísimo en el problema que quería solucionar. Gracias.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Que amables palabras! Mil gracias!

      Saludos!

  3. Avatar de Mario
    Mario

    Buenas noches esta buenísimo todo, una consulta por favor, tengo un código que hice en una hoja y me va súper bien
    Pero al momento de duplicar el archivo ese mismo código deja de funcionar,, me parece súper raro, espero que me puedan ayudar, gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Puede ser que hayas creado un activador? Estos se eliminan al copiar los archivos. Saludos!

  4. Avatar de Jorge
    Jorge

    Buenas tardes, como puedo hacer una lista desplegable, que al seleccionar cualquiera de los datos de la lista, automaticamente me lleve a una hoja de informacion, del libro que estoy utilizando en google drive hoja de calculo????

    1. Avatar de jsguzmanb
      jsguzmanb

      Hmmm
      No se si se pueda
      Saludos!

  5. Avatar de Juan Pablo
    Juan Pablo

    Hola, quiero hacer una lista desplegable en formularios y al momento de seleccionar uno, se llenen cuatro casillas mas, como le puedo hacer? Vi un video de usted, pero era con opcion multiple.

    Gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Te refieres a formularios en Google Sheets o formularios de Google Forms?

      Saludos!

  6. Avatar de juan
    juan

    Llevaba tiempo buscando un tutorial sobre esto y no conseguía encontrar nadie que explicase lo que buscaba. Porfiiin.

    En mi caso necesito modificar el código, he realizado todos los paso pero no explicas como implementarlo. Aplicación web, complemento, biblioteca, ejecutable api…. y qué hacer con esa dirección que te proporcionan.

    muchas gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Juan

      Gracias por la sugerencia

      La verdad es que si tienes el onEdit, no hay necesidad de hacer nada mas

      Saludos!

  7. Avatar de Eduardo
    Eduardo

    Hola, tengo una duda

    Se pueden hacer listas desplegables mostrando informacion dependiendo de la persona que ingrese al documento, mas bien dependiendo de la cuenta de la persona que ingrese al documento?

    1. Avatar de jsguzmanb
      jsguzmanb

      Si estas en workspace si
      Si son cuentas gratuitas de gmail es dificl que funciones por el tema de permisos
      Saludos!

  8. Avatar de Gaston
    Gaston

    Hola! Quisiera hacer lo de la lista desplegable dependiendo de otra lista desplegable pero en varias filas. Es decir que la lista desplegable madre, va ir variando fila a fila. Una fila será Vialidad, otra será Arquitectura, otra Hidráulica y así sucesivamente. El tema es que la lista desplegable dependiente me termina trayendo TODAS las opciones. No solo la del área correspondiente.

    Saludos.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Aquí te dejo un tutorial

      https://youtu.be/gwju78H3kW4

      Saludos!

  9. Avatar de Susana
    Susana

    Hola! Una consulta, vi unos videos tuyos donde creas listas de tareas, y en un campo queda registrada la hora de la acción realizada. Es posible que quede registrado de igual forma el usuario que la realiza?

    De antemano muchas gracias!

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Susana

      Si se podria

      Hay un metodo similar a getUser que podria «imprimir» el usuario que hace alguna accion

      Saludos!

  10. Avatar de Aldo
    Aldo

    Hola.
    Tengo una pregunta o sugerencia de tema respecto a las listas desplegables.
    Actualmente estoy trabajando con una base de datos en la que se asigna un código / nombre + un folio ¿hay forma de preestablecer los datos y que al mismo tiempo sin importar cuál escojas en la lista desplegable el folio cambie automáticamente al número consecutivo?

    1. Avatar de Aldo
      Aldo

      Ejemplo:
      RUS_21_001
      POST_21_002
      NEG_21_003
      POST_21_004

    2. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Creo que no te entiendo bien. Te refieres como a un buscarv?

      Saludos!

  11. Avatar de Moi
    Moi

    Hola tengo el problema de no ver los elementos repetidos en la lista… por ejemplo un id de una persona que asiste por ejemplo 2 veces… la variante es la fecha en que asiste… hay alguna forma de ingresar el id y que salgan los resultados en la lista desplegable??

  12. Avatar de Alejandro Rivas
    Alejandro Rivas

    Hola buenas, la verdad me sirvió muchisimo.
    El tema es el siguiente
    Creé una lista de teléfonos para trabajar con una lista desplegable. y la comparto con mis clientes para que puedan buscar un celular con sus precios para los distintos servicios y a ellos, no le salen la lista desplegable.
    Estoy usando BUSCARV y he creado la lista y todo, pero a ellos no se le despliega la lista de modelos.
    Ellos deben instalar algo adicional o yo estoy haciendo algo mal?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Alejandro

      Dos preguntas:

      1. La lista la traes del mismo archivo?
      2. Es en una sola celda el desplegable, o en varias?

      Saludos!

    2. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Asi es, tienen que autorizar la primera vez que corre el script

      De pronto les puedes poner una opción o un boton para que corra el script con un comando y les pida la autorización, sin tener que entrar al editor de scripts

      Un gran saludo

  13. Avatar de Pedro Rubiano
    Pedro Rubiano

    Cordial saludo jsguzmanb,

    Quisiera preguntarte, si existe una manera practica de generalizar el segundo método que explicaste pero para una tabla, es decir, tengo que generar una tabla en la que en cada nuevo registro debe contener esa misma posibilidad de lista desplegables condicionales, pero no veo tan claro cual seria la manera más practica y si realmente es posible, considerando que no puedo generar igual numero de listas auxiliares que numero de registros que le incluya a la tabla.

    Agradezco tu ayuda inmensamente.

  14. Avatar de George

    Hola muy buenas, me gustaría ir un paso más allá, no se si será posible de forma nativa. Les explico:

    Tengo un proyecto enfocado al entrenamiento y nutrición y lo que he creado son listas desplegables dependientes, es decir, al elegir el nivel y el grupo muscular, solo aparecerán los ejercicios que se consideren de ese nivel y de ese músculo en concreto.

    Ahora bien, lo que me gustaría añadir como función automática sería que una vez elegido el ejercicio (digamos que elijo «fondos en el suelo»), se añada un link con el correspondiente vídeo de ese mismo ejercicio (ya sea en una celda cercana o en el propio nombre del ejercicio.

    Muchas gracias de antemano!

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola George,

      Pues la verdad, lo que se me ocurre es un simple buscarv.

      Que tu tengas una lista de referencia con todos los ejercicios y en la columna de al lado su link de video correspondiente

      En la otra hoja, al lado del desplegable un buscarv con el link

      Que te parece

      cuéntame si te entendí bien

      Saludos!

  15. Avatar de mariano
    mariano

    hola juan, me acaban de enviar tu blog y me parece increible.
    Actualmente tomo unos cursos en udemy y tus videos son mucho mejores y mas pragmaticoas.
    TEngo una duda con listas desplegables condicionales. Si yo quisiera que se repitieran las celdas. Como le tendria que hacer? tendria que crear 100 columnas en otra hoja para que pudiera trabajar o hay otra forma? Para 1 celda puedo hacerlo pero si fueran 100 como seria?
    me explique bien?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Mariano

      Muchas gracias por tus comentarios!

      Tambien tengo unos cursos en Udemy (ojala no sean los mios los que hayas visto 🤦‍♂️)

      Pero la verdad tienes razón, no se si cursos en Udemy sean la mejor solución para sheets, jaja, me estoy autosaboteando.

      Volviendo al tema, revisate este artículo para hacerlo. Lo único, es que a diferencia de Excel no se puede solo con formulas, nos toca con código, pero no es tan complicado

      https://juansguzman.com/lista-desplegable-condicional-en-varias-filas-google-sheets-apps-script/

      Creo que te va a responder tus preguntas, pero si no quedo pendiente!

      Saludos!

  16. Avatar de HECTOR FAJARDO CHAVEZ
    HECTOR FAJARDO CHAVEZ

    SI DESEO HACER QUE LA LISTA DEPENDIENTE SEA PARA VARIAS FILAS COMO SE PODRIA HACER

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Hector

      Revísate este artículo, que creo que es lo que estas buscando.

      https://juansguzman.com/lista-desplegable-condicional-en-varias-filas-google-sheets-apps-script/

      Un gran saludo!

  17. Avatar de Juan Camilo Rey Pava
    Juan Camilo Rey Pava

    Hola feliz noche, queria saber si sabes cómo es posible lograr que en google sheets generar una lista de validación, pero en esta dejar un espacio para que la persona pueda escribir lo que sea, es decir tener la opción de elegir entre los datos que yo le programe, pero además dar la posibilidad de que también pueda escribir sin que salte el error de validación, en Excel esto se hace generando la lista y dejando una celda en blanco, sin embargo en google sheets al hacer eso el programa simplemente ignora esos espacios en blanco y solo muestra los datos escritos de esta lista, esta necesidad surge ya que estoy programando una hoja de mantenimiento en donde existen muchas tareas que se pueden generar en lista, sin embargo existen imprevisto que no se pueden poner en una lista dado que son imprevistos y es por ello que necesito poder tener la posibilidad de un espacio en blanco. Gracias

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Juan Camilo

      Tienes razón. En Sheets no se puede.

      Lo que en principio es una ventaja, porque se salta el blanco, en tu caso no funciona

      Se me ocurren varias formas de enfrentarlo.

      1. Usar Formularios de Google Forms vinculados con tu Sheets
      2. Dejar la validación de lista solo generando alerta, sino rechazar la entrada
      3. Dejar la opcion de «otros» como una celda aparte

      No se si alguna de estas te suene.

      Un gran saludo!

  18. Avatar de Miguel B.
    Miguel B.

    Hola! Me encuentro buscando realizar esta misma acción pero en Excel. Tal cual como en la parte 2 de su explicación, donde hay un listado desordenado de datos donde están algunos que coinciden con el condicionante. Pero no consigo cómo filtrar los datos para poder reducir la lista desplegable en función a esa condición.

    Si tiene alguna idea o sugerencia de como realizarlo, muchas gracias.

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Miguel

      Aunque cada vez me alejo más de Excel, creo que la función FILTER ya funciona en EXCEL

      Ya intentaste con esta?

      Saludos!

      1. Avatar de Miguel B.
        Miguel B.

        Hola! desde que le escribí hasta ahora he logrado trabajarlo con una fórmula que incluye Indice y K.esimo, pero al momento de ingresar la formula en la lista desplegable me manda error.

        Desconocía totalmente la existencia de Filter en Excel, leí que está desde la versión 2020… Y es exactamente lo que necesito! (debí haber leído antes jaja )

        Voy a descargarlo de inmediato y empezaré de cero con esa función.
        Muchas gracias!

        1. Avatar de jsguzmanb
          jsguzmanb

          Excelente Miguel

          Estoy precisamente haciendo un artículo en estos momentos de FILTER

          Por ahora te dejo un par de videos del canal explicandote FILTER

          https://www.youtube.com/watch?v=88EuAIaxClw

          https://www.youtube.com/watch?v=grjWtzWbPg8

          Un gran saludo!

          Yo creo que funcionará igual o sino muy parecido en Excel

  19. Avatar de Stefany Téllez
    Stefany Téllez

    Hola. Hice una lista desplegable dependiente en excel y quedó perfecta para 1000 casillas, cuando la subí al Drive la función no se reconoce. Teniendo en cuenta la cantidad de datos necesarios y que debe estar en drive ¿qué formula debería usar?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Stefany

      Desafortunadamente una de las cosas que no migra entre Excel y Sheets es la validación de datos con fórmulas, por lo que las listas desplegablescondicionales de varias filas no te van a coger en Sheets, tal como se construyen en Excel (con INDIRECTO)

      Te toca construirlas «de cero» con una macro.

      No es tan complicado. Aquí te dejo el tutorial de Listas Desplegables Condicionales en varias filas.

      Me cuentas si te sirvió.
      Saludos!

  20. Avatar de Carolina Gómez Correa

    Quiero darte las gracias por tus tutoriales. Me han servido demasiado!!! estoy súper feliz. Son fáciles, paso a paso y justo con lo que necesito. Tienes redes para seguirte?? ya me suscribí a tu canal… un abrazo

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Carolina

      Gracias a ti!

      Por ahora mi única red es YouTube. Todos los días encontrarás un nuevo video.

      Cualquier sugerencia de temas es muy bienvenida.

      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