Crear listas desplegables dependientes infinitas en Google Sheets con Apps Script

En este tutorial, aprenderás a crear listas desplegables dependientes infinitas en Google Sheets usando Google Apps Script. Con este método, podrás generar tantos niveles de desplegables como necesites, de forma automática y escalable, ideal para manejar datos jerárquicos como Continente, País, Estado, Ciudad y más. Así podrás ahorrar tiempo y evitar errores manuales al actualizar tus listas.

Si prefieres ver este tutorial en video, te los dejo a continuación para que puedas seguir el paso a paso visualmente y con explicaciones adicionales.

Requisitos previos

1. Google Sheets con tus datos organizados en columnas consecutivas y sin celdas combinadas.
2. Google Apps Script habilitado en tu hoja de cálculo para añadir el código que generará los desplegables dependientes.
3. Una estructura jerárquica de datos donde cada nivel dependa del anterior (por ejemplo: Continente > País > Estado > Ciudad).

Paso 1: Organiza tus datos en la hoja de cálculo

Lo primero es tener tus datos bien ordenados en Google Sheets. Cada nivel de dependencia debe estar en columnas consecutivas y en el mismo orden que quieres que aparezcan en los desplegables. Esto es fundamental para que el script funcione correctamente.

Ejemplo práctico de cómo organizar los datos:

Continente	País	Estado	Ciudad
América	EE.UU.	California	Los Ángeles
América	EE.UU.	Nueva York	Buffalo
Europa	Italia	Toscana	Florencia

Recuerda que no debe haber filas con datos combinados y cada fila debe ser única para evitar conflictos en las listas desplegables.

Paso 2: Crea manualmente el primer desplegable

1. Selecciona la celda donde quieres que aparezca el primer desplegable (por ejemplo, la celda B2).
2. Ve al menú Datos > Validación de datos.
3. En «Criterios», elige Lista de un rango y selecciona el rango donde están los valores del primer nivel (por ejemplo, la columna de Continentes).
4. Marca la opción «Rechazar entrada de datos no válidos» para evitar errores.
5. Guarda los cambios y verifica que el desplegable funcione correctamente.

Paso 3: Escribe y configura el código en Apps Script

Ahora vamos a crear el script que actualizará automáticamente los desplegables dependientes según la selección del usuario. Esto hará que cada vez que cambies un valor, se ajusten los siguientes desplegables sin que tengas que hacerlo manualmente.

1. Abre el editor de Apps Script desde Extensiones > Apps Script.
2. Elimina cualquier código que tengas y pega el siguiente script:

function onEdit(e) {
  var hojaDatosNombre = "Base de Datos";  // Hoja con los datos originales
  var hojaListasNombre = "Listas";       // Hoja donde están las listas para los desplegables
  var rangoEditado = e.range;
  var hojaActiva = rangoEditado.getSheet();
  var nombreHojaActiva = hojaActiva.getName();

  var columnasDesplegables = {
    inicio: 2,  // Columna B (2)
    fin: 5      // Columna E (5)
  };

  var desfaseColumnas = -1;  // Ajusta según la estructura de tus datos

  // Solo actúa si estamos en la hoja de datos, fila mayor a 1 y en las columnas de desplegables
  if (nombreHojaActiva === hojaDatosNombre &&
      rangoEditado.getRow() > 1 &&
      rangoEditado.getColumn() >= columnasDesplegables.inicio &&
      rangoEditado.getColumn() <= columnasDesplegables.fin) {

    var valorSeleccionado = rangoEditado.getValue();
    var hojaListas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(hojaListasNombre);
    var datosListas = hojaListas.getDataRange().getValues();

    // Filtrar las opciones que correspondan al valor seleccionado en el nivel anterior
    var opciones = datosListas.filter(function(fila) {
      return fila[rangoEditado.getColumn() + desfaseColumnas - 1] === valorSeleccionado;
    }).map(function(fila) {
      return fila[rangoEditado.getColumn() + desfaseColumnas];
    });

    // Crear la regla de validación con las opciones filtradas
    var regla = SpreadsheetApp.newDataValidation()
      .requireValueInList(opciones, true)
      .build();

    // Asignar la validación a la celda siguiente a la derecha
    rangoEditado.offset(0, 1).setDataValidation(regla);

    // Limpiar las validaciones y valores de las celdas a la derecha para evitar inconsistencias
    var hojaDatos = hojaActiva;
    var filaActiva = rangoEditado.getRow();
    var columnaActiva = rangoEditado.getColumn();
    var COLUMNA_FINAL = columnasDesplegables.fin;
    var rangoALaDerecha = hojaDatos.getRange(filaActiva, columnaActiva + 2, 1, COLUMNA_FINAL - columnaActiva - 1);
    rangoALaDerecha.clearContent();
    rangoALaDerecha.clearDataValidations();
  }
}

3. Guarda el proyecto con un nombre descriptivo, como "Desplegables Infinitos".
4. Regresa a tu hoja de cálculo y prueba el script seleccionando un valor en el primer desplegable. Observa cómo los desplegables siguientes se actualizan automáticamente.

Paso 4: Asegura la ejecución automática del script

El script usa la función especial onEdit, que se ejecuta automáticamente cada vez que editas una celda. No necesitas configurar nada extra para que funcione, solo asegúrate de que el script esté guardado y activo.

Consejos prácticos y advertencias

• Orden y consecutividad: Las columnas con los niveles de desplegables deben estar ordenadas y ser consecutivas, ya que el script depende de este orden para funcionar correctamente.
• Desfase de columnas: El parámetro desfaseColumnas es clave para que el script encuentre las opciones correctas. Ajusta este valor si cambias la estructura de tus datos.
• Datos únicos y sin combinaciones: Cada fila debe ser única y no debe contener celdas combinadas para evitar errores en la validación.
• Al modificar el valor del primer desplegable: El script limpia automáticamente los valores y validaciones en las columnas a la derecha para evitar datos inconsistentes, tal como me comentó un usuario en el blog.

Conclusión

Con este método puedes crear listas desplegables dependientes infinitas en Google Sheets, que se actualizan automáticamente con cada selección. Esto es perfecto para manejar bases de datos jerárquicas complejas y mantener tus hojas limpias y ordenadas. Recuerda siempre mantener la estructura de datos clara y el código ajustado a tus columnas.

Si quieres profundizar más en automatizaciones con Google Apps Script y Google Sheets, te recomiendo inscribirte en mi academia donde tenemos cursos completos y soporte para que lleves tus habilidades al siguiente nivel.

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