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:
- 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
- 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.
- Crear un listado de países (en una fila)
- Crear una lista desplegable simple para los países (usando el listado de países) en la columna A.
- Crear un listado de ciudades debajo de cada país
- Buscar el país escogido en la lista de países
- Asignar las ciudades de ese país a un rango de validación
- 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.
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!
Hola! No entiendo bien. Deje cual celda en blanco
Saludos!
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
Hola!
Habría que modificar un poco el código, pero si se debe poder
Saludos!
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);
}
}
Hola!
Cambia esto
var indice = paises[0].indexOf(pais);
por esto
var indice = paises.indexOf(pais);
Saludos!
Yo hice esta modificación para que me funcionara:
//Acciones para la columna 1
if(filaActiva>1 && columnaActiva==1 && hojaDatos.getName()==»Datos»)
Abrazo!!
Excelente!
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);
}
}
Hola Juan
Te dejo este video, puede que te sirva
https://youtu.be/8Utinr3rNI8
Saludos!
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);
}
}
};
Hola!
Tienes repetida esta linea
if (filaActiva =>5 && columnaActiva ==6 && celdaActiva.getName() ===’PLANIFICACION DE EQUIPOS’){
Saludos!
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);
}
}
HOla!
Mil gracias por tus palabras!
Creo que el problema está en tu indice
Hazle un logger.log a ver que te da
Saludos!
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.
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!
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
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!
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
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!
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);
}
}
Hola! confirmaste que el nombre de la hoja este exacto? Que no tenga tildes?
Saludos!
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 😀
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);
}
}
}
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!
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?
Hola Jaime
Solo cambia en esta linea
//Acciones para la columna 1
if(filaActiva>1 && columnaActiva==8 && celdaActiva.getName()==»Datos»){
Un gran saludo!
Juan agradezco mucho tu ayuda!! si me sirvio la corrección y tu apoyo!! bendiciones!!
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);
}
Hola Aracelli
Te sale algún error?
Un gran saludo!
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
Gracias a ti Luis!
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);
}
}
Error Se ha intentado ejecutar desplegables, pero no se ha podido guardar el proyecto.
Error de sintaxis: SyntaxError: Unexpected token ‘{‘, línea: 16, archivo: Código.gs
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
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!
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.
No te preocupes
Dejame ver como tienes el codigo actualmente
Saludos
ya sabemos porque no me funciona??
He seguido probando cosas y nada.
Muchas gracias
Hola!
Cambiaste esta linea
var indice = productos[0].indexOf(productos);
por esta?
var indice = productos[0].indexOf(producto);
Me cuentas!
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
Hola Viviana
El error debe estar en hojaLista
Déjame ver las líneas anteriores donde defines hojaLista
Saludos!
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.
Hola!
Tienes dos variables repetidas
2 variables llamadas productos
cambiale el nombre a la primera
Me cuentas
Saludos!
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
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!
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
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!
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.
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!
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?
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
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!
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!
Puedes poner el bucle para no tener que ir celda a celda ejecutando el script?
Hola! Te refieres a actualizar los desplegables pasaods? los de arriba?
Saludos!
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.
Hola Pedro
No tengo artículo aún pero si un video
Aquí te lo dejo.
https://youtu.be/gwju78H3kW4
Saludos!
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!
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
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 🙂
Hola Leonel,
Mil gracias por esscribir.
No se si has visto los artículos (y videos) de inventario que tengo.
De pronto te pueden dar ideas
https://juansguzman.com/gestion-de-inventarios-en-google-sheets/
Me cuentas