Lista Desplegable Condicional en Varias Filas – Google Sheets + Apps Script

Lista desplegable condicional en varias filas en Google Sheets

En este artículo vemos como crear una lista desplegable que dependa de un valor de otra lista, y que se pueda “arrastrar” o repetir en múltiples filas.

Aquí pueden ver el tutorial en video.

Qué es una lista desplegable

Una lista desplegable es una ayuda a un usuario, para que, al dar clic le aparezca una lista de las posibles opciones. Como esto:

Una lista desplegable es muy util por dos razones:

  1. Le hace la vida más fácil (y más rápida) a los usuarios o a las personas que llenan la base, ya que no tienen que escribir nada, y la pueden llenar más rápido. También les autocompleta o les sugiere escribiendo solo las primeras letras
  2. Mantiene nuestra base más “limpia” y robusta, ya que nos aseguramos de que solo pueda escoger de entre los datos que queremos.

Insertar una lista desplegable es una forma de validación de datos y es sencillo de hacer. Aquí pueden ver la guía para insertar listas desplegables.

Qué es una lista desplegable condicional

Las listas desplegables nos abren un mundo de posibilidades, y podemos empezar a pensar en múltiples listas.

Miremos el siguiente caso: Quiero que mi usuario pueda escoger país y también pueda escoger ciudad. Con listas desplegables simples quedaría así.

Pero sería muy bueno (y muy práctico) que la lista de ciudades se filtrara de alguna manera, dependiendo del país que escoja.

A esto es a lo que me refiero con una lista desplegable condicional o dependiente.

Dependiendo del país, la lista desplegable de la ciudad debería cambiar.

Pero ese tampoco es el tema de este artículo.

Aquí pueden ver un artículo completo de listas desplegables condicionales

Listas desplegables en varias filas

Lo que queremos ver en este artículo 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 tienen sus datos para que no pierdan 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
  }
}

Y ya esta!

Conclusión

Como ves, con un código de complejidad media podemos crear unos desplegables muy interesantes en todas las filas de nuestra hoja.

63 comentarios en “Lista Desplegable Condicional en Varias Filas – Google Sheets + Apps Script”

  1. Hola, me gustaría saber cómo agregar una línea de código que deje la celda en blanco cuando se elimina el contenido de mi primera fila, gracias!

  2. Hola, muchas gracias, ha sido muy útil. Tengo una duda.

    ¿Es posible acomodar la información de la siguiente manera?:
    Mexico | Cd. Mexico | Guadalajara | Monterrey
    Colombia | Bogotá | Medellín | Barranquilla

    En lugar de:
    Mexico | Colombia
    Cd. Mexico |Bogotá
    Guadalajara |Medellín
    Monterrey |Barranquilla

  3. Buenas tardes, muchísimas gracias primero que todo.
    Estoy ejecutando todo, pero no se me genera la lista desplegable en la columna ciudades. Me podrás ayuda? Muchas gracias.
    Este es mi código:

    function onEdit() {
    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 de país
    var indice = paises[0].indexOf(pais);
    //Obtengo rango de mensaje
    var rangoValidacion =
    hojaLista.getRange(3,indice+1,hojaLista.getLastRow());

    //Vuelvo el rango una validacion

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

    //Asigno la validacion a la celda especifica
    celdaActiva.offset(0,1).setDataValidation(reglaValidacion);

    }

    }

      1. Yo hice esta modificación para que me funcionara:

        //Acciones para la columna 1
        if(filaActiva>1 && columnaActiva==1 && hojaDatos.getName()==”Datos”)

        Abrazo!!

  4. Hola Juan Muchas gracias por tu video, me ayudo bastante.

    Pero me gustaría saber si hay alguna manera de que el mismo código que tienes sirva para que cuando yo copie un país ya agregado y lo pegue en 3 celdas más que se encuentren abajado del país agregado, me traiga los datos de las ciudades en las 3 celdas pegadas. Ya que cuando realizo eso solo me llega a traer la ciudad de la primer celda que esta seleccionada de las 3 y ya las dos faltantes no trae nada en la columna de ciudades.

    ejemplo de código.

    function onEdit() {
    var archivo = SpreadsheetApp.getActiveSpreadsheet();
    var hojaDatos = archivo.getSheetByName(“Carga”);
    var pais = hojaDatos.getActiveCell().getValue();
    var filaActiva = hojaDatos.getActiveCell().getRow();
    var columnaActiva = hojaDatos.getActiveCell().getColumn();

    var hojaLista = archivo.getSheetByName(“Pais”);
    var paises = hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

    if(filaActiva>1 && columnaActiva==1){
    var indice = paises[0].indexOf(pais)+1;
    var rangoValidacion = hojaLista.getRange(3,indice,hojaLista.getLastRow());
    var reglaValidacion = SpreadsheetApp.newDataValidation().requireValueInRange(rangoValidacion).build();
    hojaDatos.getActiveCell().offset(0, 1).setDataValidation(reglaValidacion);
    }
    }

  5. Hola!, muchas gracias por la info..
    tengo una consulta.. no sé que estoy poniendo mal de la celda activa, que cuando toco cualquier celda pone validación de datos..

    dejo el código
    function onEdit() {

    // Conexión de datos
    let archivo = SpreadsheetApp.getActiveSpreadsheet();
    let hojaPLANIFICACIon = archivo.getSheetByName(‘PLANIFICACION DE EQUIPOS’);
    let celdaActiva = hojaPLANIFICACIon.getActiveCell();
    let hojaAUX = archivo.getSheetByName(“AUX”);

    //Datos de la celda Activa
    let profesionBuscada = celdaActiva.getValue();
    Logger.log(profesionBuscada)

    let filaActiva = celdaActiva.getRow();
    Logger.log(filaActiva)

    let columnaActiva = celdaActiva.getColumn();
    Logger.log(columnaActiva)

    if (filaActiva =>5 && columnaActiva ==6 && celdaActiva.getName() ===’PLANIFICACION DE EQUIPOS’){

    let profesiones = hojaAUX.getRange(1,2,1,hojaAUX.getLastColumn()).getValues();
    Logger.log(profesiones)

    let indice = profesiones[0].lastIndexOf(profesionBuscada)+2;
    Logger.log(indice)

    let rangoValidacion = hojaAUX.getRange(2,indice,16).getValues();
    Logger.log(rangoValidacion)

    if (filaActiva =>5 && columnaActiva ==6 && celdaActiva.getName() ===’PLANIFICACION DE EQUIPOS’){

    let reglaValidacion = SpreadsheetApp.newDataValidation().requireValueInList(rangoValidacion).build();
    //Asigno la validación a la celda específica
    celdaActiva.offset(0,1).setDataValidation(reglaValidacion);
    }
    }
    };

  6. Excelente posteo.
    Creo que estoy muy cerca de lograrlo pero me sale el error: Exception: The starting column of the range is too small.
    onEdit @ Sin título.gs:16

    Estoy convencido que debe ser algun error facil, pero no logro dar con el. Mi codigo es el siguiente :

    function onEdit() {
    var archivo = SpreadsheetApp.getActiveSpreadsheet();
    var hojaRecepcion = archivo.getSheetByName(“Recepcion”);
    var campos = hojaRecepcion.getActiveCell().getValue();
    var filaActiva = hojaRecepcion.getActiveCell().getRow();
    var columnaActiva = hojaRecepcion.getActiveCell().getColumn();

    var hojaCampos = archivo.getSheetByName(“Campos”);
    var campos = hojaCampos.getRange(1,1,hojaCampos.getLastColumn()).getValues();

    if(filaActiva>2 && columnaActiva ==3){
    var indice = campos [0] .indexOf (campos)+1;
    var rangoValidacion = hojaCampos .getRange(2,indice,hojaCampos .getLastRow());
    var reglaValidacion = SpreadsheetApp.newDataValidation().requireValueInRange(rangoValidacion).build();
    hojaRecepcion .getActiveCell() .offset(0,1) .setDataValidation(reglaValidacion);
    }
    }

  7. Hola Juan,

    Excelente trabajo el que realizas cada día. Enorme profesional.

    Me gustaría compartir mi código contigo por si me pudieras ayudar, ya que lo hice hace tiempo con tu tutorial y hasta hace poco me funcionaba correctamente, pero de buenas a primeras ha dejado de ir bien. Se crea el desplegable correctamente, pero los datos que deben cargarse no se cargan, se queda el desplegable de forma continua en “cargando…”.

    ¿Sabrías decirme porqué?

    Mi código

    function onEdit() {
    var archivo = SpreadsheetApp.getActiveSpreadsheet();
    var hojaDatos = archivo.getSheetByName(“Datos”);
    var hojaLista = archivo.getSheetByName(“Listas”);
    var PrimerDatoElegido = hojaDatos.getActiveCell().getValue();
    var filaActiva = hojaDatos.getActiveCell().getRow();
    var columnaActiva = hojaDatos.getActiveCell().getColumn();
    var encabezados =hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();
    if(filaActiva>1 && columnaActiva==2){
    var Columna = encabezados[0].indexOf(PrimerDatoElegido)+1;
    var RangoValidacion = hojaLista.getRange(3,Columna,hojaLista.getLastRow());
    var ReglaValidacion = SpreadsheetApp.newDataValidation().requireValueInRange(RangoValidacion).build();
    hojaDatos.getActiveCell().offset(0, 1).setDataValidation(ReglaValidacion);
    }
    }
    Mil gracias de antemano.
    Un saludo.

    1. Hola Raul

      Incluye esta linea

      if(filaActiva>1 && columnaActiva==2){
      var Columna = encabezados[0].indexOf(PrimerDatoElegido)+1;
      var RangoValidacion = hojaLista.getRange(3,Columna,hojaLista.getLastRow());
      Logger.log(RangoValidacion.getValues())
      var ReglaValidacion = SpreadsheetApp.newDataValidation().requireValueInRange(RangoValidacion).build();
      hojaDatos.getActiveCell().offset(0, 1).setDataValidation(ReglaValidacion);
      }

      Pruébalo desde el editor y cuéntame que te arrojó el logger

      Un gran saludo!

  8. Buen dia; estoy tratando de ejecutar la accion siguiendo los pasos y me arroja el siente error “Error Se ha intentado ejecutar desplegable, pero no se ha podido guardar el proyecto.”

    Error de sintaxis: SyntaxError: missing ) after argument list, línea: 18, archivo: Código.gs

    function desplegable() {
    var encabezado=1;
    var coldesplegable=16;
    var colsegundodesplegable=17;
    var nombredesplegable = “Consolidado”;
    var nombredatos = “hoja 1”;
    var libro = SpreadsheetApp.getActiveSpreadsheet();
    var hojaActiva = libro.getActiveSheet();
    var celdaActiva = hojaActiva.getActiveCell();
    var filaActiva = celdaActiva.getRow();
    var ColActiva = celdaActiva.getColumn();
    var valorActivo = celdaActiva.getValue();

    if(hojaActiva.getName()==nombredesplegable && filaActiva > encabezado && ColActiva ==coldesplegable) {
    var hojadatos = libro.getSheetByName(nombredatos);
    var rangodatos = hojadatos.getRange(2,1, hojadatos.getLastRow()-1, 2).getValues();
    Logger.log(rangodatos);
    var arreglofiltrado = rangodatos.filter(funtion(fila){
    return fila[0]==valorActivo;
    });
    Logger.log(arreglofiltrado);
    var arregloparadesplegable = arreglofiltrado.map(funtion(fila){
    return fila[1];
    }) ;

    VAR validacion = SpreadsheetApp.newDataValidation().requireValueInList(arregloparadesplegable).build();
    hojaActiva.getRange(filaActiva, colsegundodesplegable).setDataValidation(validacion);
    }
    }

    Me podria ayudar indicandome que estoy haciendo mal

    1. Hola Luis

      Tienes varios errores

      Lo primero que tienes que hacer es cambiar funtion por function en todo el código

      Soluciona eso y mira que error te sigue saliendo
      Saludos!

  9. Hola hay muchas gracias me ayudaste en mucho!! pero hay alguna manera de acomodar la lista en un orden, es que mi lista es una serie de dígitos ejemplo (1.2.4, 12.4.6) que están en otra hoja y la lista me la muestra del primer registro al ultimo y me gustaría que fuera del último registro al primero

    1. Hola Roberto

      Disculpa la demora

      La verdad es que este tema de ordenar no es tan sencillo, porque depende de números y textos y más. La otra semana sale un video en el canal en el que ordeno desde código que seguro podrás aplicar a tu código de listas desplegables

      Un gran saludo!

  10. Hola Juan, muy bueno todo lo que haces. Te pregunto lo siguiente ya que me tira este error….TypeError: Cannot read property ‘getActiveCell’ of null (línea 4, archivo “Código”)

    que tengo mal?

    function onEdit() {
    var archivo = SpreadsheetApp.getActiveSpreadsheet();
    var hojaNomina = archivo.getSheetByName(“Nomina”);
    var residencia = hojaNomina.getActiveCell().getValue();
    var filaActiva = hojaNomina.getActiveCell().getRow();
    var columnaActiva = hojaNomina.getActiveCell().getColumn();

    var hojaValidaciones = archivo.getSheetByName(“Validaciones”);
    var residencias = hojaValidaciones.getRange(2,1,1,3).getValues();

    if(filaActiva>2 && columnaActiva ==12){
    var indice = residencias[0].indexOf(residencia)+1;
    var rangoValidacion = hojaValidaciones.getRange(5,indice,hojaValidaciones.getLastRow());
    var reglaValidacion = spreadSheetApp.newDataValidation().requireValueRange(rangoValidacion).build();
    hojaNomina.getActiveCell().offset(0,1).setDataValidation(reglaValidacion);

    }
    }

  11. Mil gracias por tus videos 😀 son muy útiles, prácticos, entendibles y geniales!

    Realicé toso el código con una hoja “limpia” para hacerlo conforme al ejemplo, sin embargo al suplir la información para que se corra en una hoja que se llama “Alta Socios”, columna 30 (validación simple) y 31 (validación condicionada a la anterior), fila 228. Me sale el siguiente error
    TypeError: Cannot read property ‘getActiveCell’ of null (línea 6, archivo “Código”)

    Te copio mi código completo:
    //Funcion para tener validacion de datos condicional en toda una columna
    function onEdit() {
    //conexion de datos
    var archivo = SpreadsheetApp.getActiveSpreadsheet();
    var hojaDatos = archivo.getSheetByName(“AltaSocios”);
    var celdaActiva = hojaDatos.getActiveCell();
    var hojaListas = archivo.getSheetByName(“Listas”);

    //Datos de la celda activa
    var region = celdaActiva.getValue();
    var filaActiva = celdaActiva.getRow();
    var columnaActiva = celdaActiva.getColumn();

    //Lista de Regiones
    var regiones = hojaListas.getRange(1,1,1,hojaListas.getLastColumn()).getValues();

    //Acciones para la columna 1
    if(filaActiva>228 && columnaActiva ==30 && archivo.getActiveSheet.getName == “Alta Socios”){
    celdaActiva.offset(0,1).clearContent().clearDataValidations();

    if(region!=””){
    //Encuentro columna del pais
    var indice = regiones[0].indexOf(region)+1;
    //obtengo rango de ciudades
    var rangoValidacion = hojaListas.getRange(3,indice,hojaListas.getLastRow());
    //vuelvo el rango una validacion
    var reglaValidacion = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInRange(rangoValidacion).build();
    //Asigno la validacion a la celda especifica
    celdaActiva.offset(0,1).setDataValidation(reglaValidacion);
    }

    }
    }

    Y una última pregunta, yo debo ser la dueña de la hoja de cálculo para que se corran las macros verdad? o puedo hacerlo sólo siendo editora?

    Saludos, mil gracias de antemano por todo tu apoyo 😀

    1. Ya vi que me faltaba el espacio en el nombre de mi hoja “Alta Socios” (lo había puesto junto), ahora ya no me sale el error pero no funciona en la hoja 🙁
      Dejo el código completo nuevamente:
      //Funcion para tener validacion de datos condicional en toda una columna
      function onEdit() {
      //conexion de datos
      var archivo = SpreadsheetApp.getActiveSpreadsheet();
      var hojaDatos = archivo.getSheetByName(“Alta Socios”);
      var celdaActiva = hojaDatos.getActiveCell();
      var hojaListas = archivo.getSheetByName(“Listas”);

      //Datos de la celda activa
      var region = celdaActiva.getValue();
      var filaActiva = celdaActiva.getRow();
      var columnaActiva = celdaActiva.getColumn();

      //Lista de Regiones
      var regiones = hojaListas.getRange(1,1,1,hojaListas.getLastColumn()).getValues();

      //Acciones para la columna 30
      if(filaActiva>227 && columnaActiva ==30 && archivo.getActiveSheet.getName ==”Alta Socios”){
      celdaActiva.offset(0,1).clearContent().clearDataValidations();

      if(region!=””){
      //Encuentro columna de la region
      var indice = regiones[0].indexOf(region)+1;
      //obtengo rango de tribus
      var rangoValidacion = hojaListas.getRange(3,indice,hojaListas.getLastRow());
      //vuelvo el rango una validacion
      var reglaValidacion = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInRange(rangoValidacion).build();
      //Asigno la validacion a la celda especifica
      celdaActiva.offset(0,1).setDataValidation(reglaValidacion);
      }

      }
      }

      1. Hola!

        Cambia esto

        //Acciones para la columna 30
        if(filaActiva>227 && columnaActiva ==30 && archivo.getActiveSheet.getName ==»Alta Socios»)

        Por esto

        //Acciones para la columna 30
        if(filaActiva>227 && columnaActiva ==30 && archivo.getActiveSheet.getName() ==»Alta Socios»)

        Me cuentas!

  12. Hola Juan muchas gracias por el código y el paso a paso, aunque tengo un archivo donde quiero que tenga la misma funcionalidad pero que empiece a correr el codigo desde las celdas (H,y en I) donde (H) es responsable y (I) es causa raíz, no se donde debo cambiar por fa me podrias ayudar?

    1. Hola Jaime

      Solo cambia en esta linea

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

      Un gran saludo!

  13. Hola, ya hice mi código, pero intento colocar onEdit y no funciona, este es mi código completo:

    //Variables de Configuración
    var encabezados = 1;
    var columnasDesplegables = [5, 6, 7]
    var nombreDesplegable = “Daciuniversity”;
    var nombreDatos = “Intervalosuniversity”;

    function desplegablesUniversity() {
    //Conectarme con mi celda activa
    var libro = SpreadsheetApp.getActiveSpreadsheet();
    var hojaActiva = libro.getActiveSheet();
    var celdaActiva = hojaActiva.getActiveCell();
    var filaActiva = celdaActiva.getRow();
    var colActiva = celdaActiva.getColumn();
    var valorActivo = celdaActiva.getValue();

    if (hojaActiva.getName() == nombreDesplegable && filaActiva > encabezados && (colActiva == columnasDesplegables[0] || colActiva == columnasDesplegables[1])) {
    //Datos para armar los desplegables
    var hojaDatos = libro.getSheetByName(nombreDatos);
    var rangoDatos = hojaDatos.getRange(2, 1, hojaDatos.getLastRow() – 1, 3).getValues();
    //Primer desplegable condicional
    if (colActiva == columnasDesplegables[0]) {
    hojaActiva.getRange(filaActiva, columnasDesplegables[1]).clearContent()
    hojaActiva.getRange(filaActiva, columnasDesplegables[2]).clearContent()
    hojaActiva.getRange(filaActiva, columnasDesplegables[2]).clearDataValidations();
    if (valorActivo == ”) {
    hojaActiva.getRange(filaActiva, columnasDesplegables[1]).clearDataValidations();
    hojaActiva.getRange(filaActiva, columnasDesplegables[2]).clearDataValidations();
    }
    else {
    crearDesplegable(hojaActiva,filaActiva,columnasDesplegables[1],rangoDatos,valorActivo,0,1)
    }
    }
    //]Segundo desplegable condicional
    else if (colActiva == columnasDesplegables[1]) {
    hojaActiva.getRange(filaActiva, columnasDesplegables[2]).clearContent();
    if (valorActivo == ”) {
    hojaActiva.getRange(filaActiva, columnasDesplegables[2]).clearDataValidations();
    }
    crearDesplegable(hojaActiva,filaActiva,columnasDesplegables[2],rangoDatos,valorActivo,1,2)

    }
    }
    }

    function crearDesplegable(hojaActiva,fila,columnaDependiente,rangoDatos, valorActivo,columnaFiltro,colValor) {
    var arregloFiltrado = rangoDatos.filter(fila => fila[columnaFiltro] == valorActivo);
    var arregloParaDesplegable = arregloFiltrado.map(fila => fila[colValor]);
    var validacion = SpreadsheetApp.newDataValidation().requireValueInList(arregloParaDesplegable).build();
    hojaActiva.getRange(fila, columnaDependiente).setDataValidation(validacion);

    }

  14. Luis Gabriel Garces

    Excelente Juan, funciono perfecto para lo que tenia pensado, aplicación en creacion de reportes de Información Exogena.

    Muchas Gracias por compartir tu conocimiento

  15. Hola. Muchas gracias por la información. Estoy teniendo problemas con la formular, en particular me die que existe un error en la linea 16 (If(hojaActiva.getName()==nombreDesplegable && filaActiva >encabezados && colActiva==colDesplegable){)
    El error que indica es el siguiente: Error Se ha intentado ejecutar desplegables, pero no se ha podido guardar el proyecto.

    function desplegables(){
    //Variables de Configuración
    var encabezados=1;
    var colDesplegable =3;
    var nombreDesplegable = “Ingreso”;
    var nombreDatos = “Datos”;

    //Conectarme celda activa
    var libro = SpreadsheetApp.getActiveSpreadsheet();
    var hojaActiva = libro.getActiveSheet();
    var celdaActiva = hojaActiva.getActiveCell();
    var filaActiva =celdaActiva.getRow();
    var colActiva =celdaActiva.getColumn();
    var valorActivo = celdaActiva.getValue();

    If(hojaActiva.getName()==nombreDesplegable && filaActiva >encabezados && colActiva==colDesplegable){
    var hojaDatos = libro.getSheetByName(nombreDatos);
    var rangoDatos = hojaDatos.getRange(2, 1, hojaDatos.getLastRow()-1,2).getValues();
    Logger.log(rangoDatos);
    }

    }

  16. Hola. Muchas gracias por la informacion. Aunque esta vaina no se por que no me sirve, lo siento soy algo nuevo en el tema.

    Resulta que estoy haciendo este mismo proceso pero en una celda O para que aparezcan en P, pero no se por que no me reconoce la celda activa, pues la lista si lo reconoce.

    // 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(“Categorizacion”);
    var celdaActiva = hojaDatos.getActiveCell();
    var hojaLista = archivo.getSheetByName(“Lista de apoyo Programacion”);

    //Datos de la celda Activa
    var Categoria_grande = celdaActiva.getValue();
    var filaActiva = celdaActiva.getRow();
    var columnaActiva = celdaActiva.getColumn();
    Logger.log (Categoria_grande)

    //Lista de Países
    var Categorias_Grandes =hojaLista.getRange(1,1,6,hojaLista.getLastColumn()).getValues();
    Logger.log (Categorias_Grandes)

    //Acciones para la columna 1
    if(filaActiva>1 && columnaActiva ==1){

    //Encuentro columna del país
    var indice = Categorias_Grandes[0].indexOf(Categoria_grande);

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

    Gracias :3

    1. Hola!

      Algunas recomendaciones

      1. Es bueno no usar variables que empiezen por mayusculas

      cambia Categoria_Grande por categoriaGrande (Clic derecho y dale clic en cambiar simbolo para q no tengas q cambiar uno por uno)

      Lo mismo para Categorias_Grandes

      De resto lo veo bien

      Hazle un logger log a índice para ver si si te esta trayendo el numero

      Saludos!

  17. Me sigue sin funcionar.
    El error me lo sigue diciendo en la linea 18 en getRanger.

    Porque puede ser?

    Muchas gracias, y perdona tantas molestias ocasionadas.

        1. Hola!

          Cambiaste esta linea

          var indice = productos[0].indexOf(productos);

          por esta?

          var indice = productos[0].indexOf(producto);

          Me cuentas!

  18. Hola buenas, muy bueno el tutorial,
    Tengo una cosnulta, todo el rato me sale el mismo error y no se como subsanarlo.
    No me llega a funcionar la función:

    Este es el error que me marca
    Error
    TypeError: Cannot read property ‘getRange’ of null
    onEdit @ Código.gs:18

    Es en la siguiente linea:

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

    Porque puede ser?
    Que es lo que estoy haciendo mal?

    Lo estoy intentando hacer con tu ejemplo para poder aplicarlo a un listado de pedidos donde vinculo el producto con el tipo de trabajo, que esa serian mis dos columnas. pero tampoco me funciona.

    Alguna ayudita.

    Muchisimas gracias

      1. Hola.

        Este es el código entero que tengo:

        // 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 productos = celdaActiva.getValue();
        var filaActiva = celdaActiva.getRow();
        var columnaActiva = celdaActiva.getColumn();

        //Lista de productos
        var productos = hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

        //Acciones para la columna 1
        if(filaActiva>1 && columnaActiva ==1){

        //Encuentro columna de productos
        var indice = productos[0].indexOf(productos);

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

        }

        Mis pestañas ahora son DATOS y Listas pero en verdad deberían ser Productos (donde datos) y Pedidos 2021 (donde lista)

        Muchisimas Gracias.

          1. He puesto tal cual tu código, haciendo el mismo ejemplo para ver si me salia bien este.
            Pero me pone el mismo error:

            “TypeError: Cannot read property ‘getRange’ of null
            onEdit @ Código.gs:18”

            18 – var paises = hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

            describo el 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 producto = celdaActiva.getValue();
            var filaActiva = celdaActiva.getRow();
            var columnaActiva = celdaActiva.getColumn();

            //Lista de productos
            var productos = hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValues();

            //Acciones para la columna 1
            if(filaActiva>1 && columnaActiva ==1){

            //Encuentro columna de productos
            var indice = productos[0].indexOf(productos);

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

            }

            Ya no se que hacer.
            Muchicicimas gracias

          2. Hola!

            Creo que fue un error mio

            Cambia esto

            //Encuentro columna de productos
            var indice = productos[0].indexOf(productos);

            Por esto

            //Encuentro columna de productos
            var indice = productos[0].indexOf(producto);

            Saludos!

  19. Gracias por tu aportación, es muy clara y fácil de entender.

    Sólo me queda una duda, ¿tienes la forma de activar en la validación que solo se puedan ingresar los valor que aparecen y no otra entrada?

    De antemano gracias por la respuesta

    1. Hola Ulises. Mil gracias por el comentario!

      Cambia esta linea

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

      por

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

      Me cuentas si te sirve

      Saludos!

  20. Hola! muy bueno el codigo!
    Tengo un problema; sólo me funciona si armo el desplegable de paises en la celda A1 (y B1 las ciudades) de la hoja de datos.
    Necesito que esté en la D5 y en E5 respectivamente y no puedo cambiarlos.

    1. Hola Martina

      Solo debes cambiar la columna en el condicional por el numero de columna donde esten tus datos. En tu caso, cambia el 1 por 4

      Saludos!

  21. Hola Juan. Muchas gracias por el tutorial, seguí los pasos y me funcionó bien. El problema que encuentro, que tal vez no tiene solución, es que estoy trabajando en una planilla que debe ser compartida con mucha personas, y cuando ellas la usan, no corre.

    ¿Hay forma de solucionar esto sin que ellos tengan que seguir los mismos pasos que yo con el Apps Scrip?

    1. Hola Violeta

      En estos scripts siempre hay que dar permisos la primera vez y onEdit funciona “detrás de cámaras”. Una opción es habilitar un botón o menú para que la primera vez haya que presionar el botón para que pida los permisos

      Voy a ponerlo en mi lista de videos por hacer

  22. Hola. Muchas gracias por la data! es excelente. Te hago una consulta. Tengo una base de datos con varias variables. Se puede aplicar este codigo varias veces para distintas variables? por ej. provincia y ciudad de origen; provincia y ciudad actual. Yo duplique los codigos y les cambie las columnas, funciona pero el ultimo codigo que ejecuto, no todos juntos… hay un modo de hacer que funcionen todos a la vez? gracias!

    1. Hola!

      Esta muy buena tu pregunta

      No la había pensado

      En teoría no debe cambiar mucho pero no es tan sencillo,

      Está muy bueno para un video

      Un gran saludo!

  23. Juan hola,

    de casualidad tienes una replicación o tutorial del código pero para el segundo método, es decir, “filter”.

    Quedo atento y mil gracias.

  24. Hola Juan, muchas gracias por los videos y toda la información detallada, de verdad he aprendido muchísimo contigo.

    Tengo un problema, seguí todos los pasos descritos en el video para realizar una base de datos de contactos de artistas musicales; quiero utilizar listas desplegables condicionales para poder seleccionar el género musical principal del artista (MAIN GENRE, que en tu caso sería equivalente a PAÍS) y tener otra columna condicionada para subgénero (SUBGENRE, que sería equivalente a la CIUDAD), por ejemplo, para un artista que su género principal sea rock, luego poder seleccionar como subgénero punk rock.

    Todo me funcionó perfecto, pero luego de un par de días quise agregar otra opción como género principal en mi hoja de Lists, sin embargo por alguna razón parece no actualizarse en la hoja de datos, es decir, este nuevo género no aparece en la lista desplegable bajo MAIN GENRE en mi hoja de datos. No entiendo por qué, ya que cuando hago el comando Logger.log(genres); este nuevo género aparece en los LOGS. Tienes alguna idea de qué pueda estar sucediendo?

    Nuevamente, muchas gracias por tu ayuda!

    Saludos desde Francia!

    1. Hola Alfredo

      Mil gracias por escribir

      Intenta esto: agrega otro genero y cuentame que pasa

      Y vamos mirandpo.

      O si quieres enviame una copia del archivo y lo veo rápido

      Saludos

  25. Muchas gracias por los videos juan, ayudan mucho a quienes vamos aprendiendo.

    tengo una gran duda de como podría hacer para tener listas desplegables condicionales como las que muestras aquí, pero para 2 hojas (INGRESOS y SALIDAS de un inventario, para que sean fácilmente rellenables y que los datos para ambas hojas salen de intervalos nombrados de una tercera hoja llamada INVENTARIO.

    Gracias de antemano, mucha suerte 🙂

Deja un comentario

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