Listas desplegables condicionales en múltiples filas con Google Sheets y Apps Script

En este tutorial aprenderás cómo crear listas desplegables condicionales en múltiples filas usando Google Sheets y Google Apps Script. Esta técnica te permitirá que una lista desplegable dependa de la selección hecha en otra columna, y que funcione replicada en varias filas, agilizando la captura de datos y mejorando la experiencia de usuario en tus hojas de cálculo.

Si prefieres ver el tutorial en video, te lo dejo arriba para que puedas seguirlo paso a paso.

Materiales y requisitos previos

  • Una cuenta Google con acceso a Google Sheets.
  • Conocimiento básico de hojas de cálculo.
  • Datos organizados en la hoja para crear las listas desplegables.

Preparando los datos para las listas desplegables condicionales

El primer paso es organizar tus datos. Vamos a trabajar con dos pestañas principales: una llamada Listas donde tendrás los datos base, y otra llamada Datos donde se crearán las listas desplegables.

Paso 1: Crear listado de países

En la pestaña Listas, escribe en la primera fila los nombres de los países, cada uno en una columna diferente. Por ejemplo:

Columna AColumna BColumna C
ColombiaMéxicoArgentina

Paso 2: Crear la columna de países con lista desplegable en «Datos»

En la pestaña Datos:

  • Escribe País en la celda A1.
  • Selecciona desde la celda A2 hasta la última fila donde quieres que aparezca la lista desplegable.
  • Haz clic derecho y selecciona Validación de datos.
  • En Criterios, escoge Lista a partir de un intervalo y selecciona el rango de países en la fila 1 de la pestaña Listas.
  • Haz clic en Guardar.

Paso 3: Crear listado de ciudades por país en «Listas»

En la pestaña Listas, debajo de cada país, escribe las ciudades correspondientes. Por ejemplo, debajo de «Colombia» escribe Bogotá, Medellín, Cali, etc. Así para cada país.

Creando el script para listas desplegables condicionales en múltiples filas

Para que la lista desplegable de ciudades cambie según el país seleccionado en cada fila, usaremos Google Apps Script con la función onEdit(). Este script detecta cuando cambias un país y actualiza la lista de ciudades correspondiente en la columna siguiente.

Pseudoalgoritmo del script

La lógica que sigue el script es:

  • Detectar la celda activa donde el usuario hizo un cambio.
  • Verificar que el cambio ocurrió en la columna del país y en la hoja correcta.
  • Buscar la posición del país seleccionado en la fila de países de la pestaña Listas.
  • Obtener el rango de ciudades correspondiente a ese país.
  • Crear la validación de datos para la celda de la ciudad en la misma fila.
function onEdit() {
  var archivo = SpreadsheetApp.getActiveSpreadsheet();
  var hojaDatos = archivo.getSheetByName("Datos");
  var celdaActiva = hojaDatos.getActiveCell();
  var filaActiva = celdaActiva.getRow();
  var columnaActiva = celdaActiva.getColumn();
  var pais = celdaActiva.getValue();

  // Solo si estamos en la hoja "Datos", en la columna 1 (País) y fila > 1 (sin encabezado)
  if(filaActiva > 1 && columnaActiva == 1 && hojaDatos.getName() == "Datos") {
    var hojaLista = archivo.getSheetByName("Listas");
    var paises = hojaLista.getRange(1, 1, 1, hojaLista.getLastColumn()).getValues();
    var indice = paises[0].indexOf(pais);
    if(indice != -1) {
      var rangoValidacion = hojaLista.getRange(2, indice + 1, hojaLista.getLastRow() - 1);
      var reglaValidacion = SpreadsheetApp.newDataValidation()
        .requireValueInRange(rangoValidacion)
        .setAllowInvalid(false)
        .build();
      celdaActiva.offset(0, 1).setDataValidation(reglaValidacion);
      celdaActiva.offset(0, 1).clearContent(); // Limpia contenido anterior si cambia el país
    } else {
      celdaActiva.offset(0, 1).clearDataValidations();
      celdaActiva.offset(0, 1).clearContent();
    }
  }
}

Este código asigna la lista de ciudades correspondiente al país que elijas en cada fila, y limpia la ciudad si cambias el país para evitar inconsistencias.

Método alternativo usando intervalos con nombre

Otra forma muy práctica es nombrar los rangos de ciudades con el mismo nombre que el país. Por ejemplo, el rango de ciudades de «Colombia» se nombra «Colombia». Así, el script puede buscar directamente el rango por nombre y asignar la validación.

function onEdit() {
  var archivo = SpreadsheetApp.getActiveSpreadsheet();
  var hojaDatos = archivo.getSheetByName("Datos");
  var celdaActiva = hojaDatos.getActiveCell();
  var filaActiva = celdaActiva.getRow();
  var columnaActiva = celdaActiva.getColumn();
  var pais = celdaActiva.getValue();

  if(filaActiva > 1 && columnaActiva == 1 && hojaDatos.getName() == "Datos") {
    try {
      var rango = archivo.getRangeByName(pais);
      if(rango) {
        var validacion = SpreadsheetApp.newDataValidation()
          .requireValueInRange(rango)
          .setAllowInvalid(false)
          .build();
        celdaActiva.offset(0, 1).setDataValidation(validacion);
        celdaActiva.offset(0, 1).clearContent();
      } else {
        celdaActiva.offset(0, 1).clearDataValidations();
        celdaActiva.offset(0, 1).clearContent();
      }
    } catch(e) {
      celdaActiva.offset(0, 1).clearDataValidations();
      celdaActiva.offset(0, 1).clearContent();
    }
  }
}

Este método es muy flexible y fácil de mantener, pues solo debes crear los intervalos con nombre en la pestaña de listas.

Consejos prácticos y precauciones

  • Organiza bien tus datos: Mantén las listas en una pestaña dedicada para facilitar el mantenimiento.
  • Revisa nombres exactos: Los nombres de los países y los intervalos deben coincidir exactamente para que el script funcione.
  • Guarda y autoriza el script: La primera vez que ejecutes el script, deberás otorgar permisos.
  • Evita duplicados: No repitas nombres en la fila de países ni en los rangos con nombre.
  • Prueba en pocas filas: Antes de aplicarlo a toda la hoja, prueba en unas pocas filas para verificar que funciona.

Solución a problemas comunes

En los comentarios del artículo y videos, varios usuarios han reportado problemas frecuentes que te resumo aquí para ayudarte:

  • Error “Cannot read property ‘getRange’ of null”: Esto pasa cuando el nombre de la hoja en el script no coincide exactamente con la hoja en el archivo. Revisa mayúsculas, espacios y tildes.
  • Listas que no se actualizan al agregar nuevos países o ciudades: Asegúrate de que el rango de la lista en la validación de datos incluye las nuevas filas o usa intervalos con nombre para mayor flexibilidad.
  • Validación que permite valores no listados: Cambia la regla para .setAllowInvalid(false) para evitar entradas fuera de la lista.
  • El script no funciona para varias hojas o columnas: Puedes modificar el script para que valide varias columnas o varias hojas usando condiciones y arrays con nombres de hojas o columnas.

Si quieres un ejemplo para validar en varias columnas, te recomiendo revisar el código que compartí en la academia, donde manejo listas dependientes para múltiples columnas con limpieza de contenido y validaciones dinámicas.

Conclusión

Crear listas desplegables condicionales en varias filas con Google Sheets y Apps Script es una forma poderosa de hacer tus hojas más dinámicas y fáciles de usar. Con los métodos que vimos, puedes adaptar el sistema a tus necesidades, ya sea usando rangos directos o intervalos con nombre. Además, el script puede extenderse para múltiples columnas y hojas con un poco de personalización.

Te recomiendo practicar con los ejemplos y adaptar el código a tus datos. Así lograrás automatizar y simplificar la captura de datos en tus proyectos o negocio.

Si quieres aprender a usar Google Forms y Google Sheets desde cero, te invito a tomar un curso gratis de 2 horas.

69 comentarios en “Listas desplegables condicionales en múltiples filas con Google Sheets y Apps Script”

  1. Jarvin

    buen día, estaba probando el código y lo implemente tal como en el video solo cambie las variables de país y países por distribuidor y sucursal pero me funciona a media al momento de ingresar el distribuidor la columna de sucursal no sufre cambios a menos que borre el distribuidor me habilita la lista en sucursal pero esta a su ves solo me muestra las sucursales de la primera columna.

    function onEdit(){
    var archivo = SpreadsheetApp.getActiveSpreadsheet();
    var hojaDatos = archivo.getSheetByName(«Datos»);
    var distribuidor = hojaDatos.getActiveCell().getValue();
    var filaActiva = hojaDatos.getActiveCell().getRow();
    var columnaActiva = hojaDatos.getActiveCell().getColumn();

    var hojaLista = archivo.getSheetByName(«Listas»);
    var sucursal = hojaLista.getRange(1,1,1,hojaLista.getLastColumn()).getValue();

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

    Responder
  2. Osvaldo M

    Hola, me gustaria hacer una lista con datos de estudiantes con su informacion personal y que al yo selecionar un estudiante por lista desplegable me salga toda su informacion en la fila.

    Responder
  3. Luis Caicedo

    Hola excelente trabajo el que realizas
    Por favor ayudame, como seria el proceso para cuando se require realizar esas listas desplegables dependientes en varios hojas?.
    por ej si tuviera Hija1, Hoja2, Hoja3, hoja4
    Mil gracias

    Responder
    1. Juan Sebastian

      HOla!
      Algo asi
      const HOJAS=[«Hoja1″,»Hoja2»] //Agrega todas las hojas que quieras separadas por comas
      if(HOJAS.indexOf(nombreHojaActiva)!=-1 && …)

      Responder
  4. Marlene

    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!

    Responder
  5. Hil

    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

    Responder
  6. Mauricio

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

    }

    }

    Responder
      1. Daniel

        Yo hice esta modificación para que me funcionara:

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

        Abrazo!!

        Responder
  7. Juan Francisco

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

    Responder
  8. Agustina

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

    Responder
  9. juan pablo

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

    Responder
  10. Raúl

    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.

    Responder
    1. jsguzmanb

      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!

      Responder
  11. Luis

    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

    Responder
    1. jsguzmanb

      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!

      Responder
  12. Roberto Dolz

    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

    Responder
    1. jsguzmanb

      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!

      Responder
  13. Facundo

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

    }
    }

    Responder
  14. Diana Santacruz

    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 😀

    Responder
    1. Diana Santacruz

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

      }
      }

      Responder
      1. jsguzmanb

        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!

        Responder
  15. Jaime Pinzon

    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?

    Responder
    1. jsguzmanb

      Hola Jaime

      Solo cambia en esta linea

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

      Un gran saludo!

      Responder
  16. Araceli Sánchez

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

    }

    Responder
  17. 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

    Responder
  18. Sebastian

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

    }

    Responder
  19. Luis Ortiz

    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

    Responder
    1. jsguzmanb

      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!

      Responder
  20. Viviana Laguna

    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.

    Responder
        1. jsguzmanb

          Hola!

          Cambiaste esta linea

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

          por esta?

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

          Me cuentas!

          Responder
  21. Viviana Laguna

    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

    Responder
      1. Viviana Laguna

        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.

        Responder
          1. Viviana Laguna

            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. jsguzmanb

            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!

  22. Ulises Cruz

    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

    Responder
    1. jsguzmanb

      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!

      Responder
  23. Martina

    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.

    Responder
    1. jsguzmanb

      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!

      Responder
  24. Violeta

    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?

    Responder
    1. jsguzmanb

      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

      Responder
  25. Flor

    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!

    Responder
    1. jsguzmanb

      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!

      Responder
  27. Pedro Rubiano

    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.

    Responder
  28. Alfredo Lunar

    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!

    Responder
    1. jsguzmanb

      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

      Responder
  29. Leonel

    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 🙂

    Responder

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.

JuanSGuzman
Powered by  GDPR Cookie Compliance (se abre en una nueva pestaña)
Resumen de privacidad

Esta web utiliza cookies para que podamos ofrecerte la mejor experiencia de usuario posible. La información de las cookies se almacena en tu navegador y realiza funciones tales como reconocerte cuando vuelves a nuestra web o ayudar a nuestro equipo a comprender qué secciones de la web encuentras más interesantes y útiles.