Como hacer listas desplegables con dependencia Google Spreadsheet

Muchas veces queremos hacer mas de una lista desplegable pero googles spreadsheeet no permite relacionar listas con dependencia.
Hoy gracias a google script, podremos crear listas mas eficientes.
Aqui van los pasos:





1) Creamos una lista pivot con todos los primeros campos y sus dependencias.




2)Nombramos las listas de dependencia como rangos



3) Creamos el Panel y agregamos una validación de datos para el campo no dependiente (en este caso País, ya que no depende de otra selección)



4)Para el desplegable con dependencia, debemos crear 2 celdas "condicionales" con esta formula.

a) Condicional1

=IFERROR(OFFSET('Panel'!(rango donde esta el primer desplegable);MATCH(MAX('Panel'!(celda/rango pivot));'Panel'!(celda/rango pivot);)-1;);)

En nuestro caso que usaremos la columna D como pivot y hemos puesto el desplegable en a2 - ambos de la hoja "panel" quedaría así:

Ojo: Arrancamos en D2 - no en D1

=IFERROR(OFFSET(Panel!A2;MATCH(MAX(Panel!D2);Panel!D2;)-1;);)

b)Concicional2


=ARRAYFORMULA(IFERROR(Indirect(Condicional1)))

En nuestro caso que hemos puesto el condicional en F2 quedaría asi:

=ARRAYFORMULA(IFERROR(Indirect(f2)))





5) Ahora vamos a utilizar el siguiente Script.
Lo dejo armado por si quieren hacer listas con 3 condiciones. En este caso solo usare 2.

SCRIPT

function onEdit(event) {
var lcol = 1; //Variable 1 (numero de columnas del primer desplegable: a=1 , b=2,etc)
var rcol = 3;   //Variable 2 (numero de columnas del segundo desplegable: a=1 , b=2,etc)
var tcol = 4;  //fecha hora del script
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Panel" ) //Nombre de la hoja
var r = event.source.getActiveRange();
var scol = r.getColumn();
if (scol >= lcol && scol <= rcol) {
s.getRange(r.getRow(), tcol).setValue(new Date());
}
}

Y como pegamos el script? Asi:



Va a pedirte acceso, dale ok, colocale un nombre al proyecto y cuando salte el error presiona "ignorar"

6) Ultimo paso, creamos la lista dependiente con el segundo condicional y desaparecemos el pivot




Dudas o consulas en Comentarios.








Pablo

Author & Editor

Soy Pablo, especialista en Inteligencia comercial de profesión y geek por pasión. Trabajo como consultor de Business Intelligence para Pymes y puedo ayudarte.