Use Google Apps Script To Add Dependent Drop Down Options

Summary

Este video explica cómo crear menús desplegables dependientes en Google Sheets usando Google Apps Script. El proceso permite que las opciones de un desplegable cambien dinámicamente según las selecciones previas del usuario, por ejemplo, al elegir un departamento, las opciones de equipo se actualizan automáticamente. Se utiliza un script que detecta los cambios mediante el evento onEdit, filtra los datos en función de las selecciones y actualiza las reglas de validación de las celdas. Además, se incluyen mejoras visuales para indicar cuando los datos están siendo cargados y mejorar la experiencia del usuario.

Takeaways

• 😀 La limpieza de datos en hojas de cálculo puede ser difícil, especialmente cuando se trabaja con múltiples colaboradores.
• 😀 Los menús desplegables son una herramienta útil para normalizar los datos en una hoja de cálculo.
• 😀 Los menús desplegables dependientes permiten que las opciones de un desplegable cambien según la selección realizada en otro desplegable.
• 😀 En lugar de usar la fórmula INDIRECTA para menús desplegables, se recomienda utilizar Google Apps Script para manejar múltiples menús dependientes.
• 😀 Un desencadenador (trigger) de tipo 'on edit' en Google Apps Script puede escuchar cambios y actualizar las opciones de un menú desplegable en función de la selección anterior.
• 😀 Es necesario tener un conjunto de datos que mapee las opciones de los menús desplegables, estructado en un formato adecuado, como una tabla.
• 😀 El uso de 'new Set()' en el script ayuda a eliminar valores duplicados al crear una lista única de opciones para los desplegables.
• 😀 Para actualizar la validación de los menús desplegables, se utiliza 'SpreadsheetApp.newDataValidation()' en Google Apps Script.
• 😀 Para mejorar la experiencia del usuario, se recomienda proporcionar retroalimentación visual mientras los datos están siendo cargados, como cambiar el color de fondo de la celda.
• 😀 La creación de funciones como 'setCellState()' puede permitir limpiar el contenido de las celdas y marcar la celda como en espera mientras se cargan las nuevas opciones.
• 😀 Reutilizar funciones como 'filterTeams' y 'getRoles' ayuda a crear menús desplegables dependientes para múltiples niveles (departamento, equipo, rol).

Q & A

¿Qué son los 'drop-downs' y por qué son útiles en las hojas de cálculo?

Los 'drop-downs' son listas desplegables que permiten a los usuarios seleccionar opciones predefinidas. Son útiles para mantener la consistencia en los datos ingresados, evitando errores y asegurando que las entradas sean siempre válidas y uniformes.

¿Qué es un 'drop-down dependiente' y cómo funciona?

Un 'drop-down dependiente' es un tipo de lista desplegable cuya selección depende de la opción elegida en otro 'drop-down'. Por ejemplo, si seleccionas un departamento, los equipos disponibles se actualizan automáticamente en función de esa selección.

¿Cómo se estructura un 'drop-down dependiente' en tres niveles?

En un 'drop-down dependiente' de tres niveles, la selección en el primer nivel (por ejemplo, departamento) influye en las opciones del segundo nivel (por ejemplo, equipo), y la selección en el segundo nivel afecta las opciones del tercer nivel (por ejemplo, roles o funciones).

¿Cuál es la diferencia entre usar la fórmula INDIRECTA y Google Apps Script para 'drop-downs dependientes'?

La fórmula INDIRECTA solo funciona para un único 'drop-down' a la vez, mientras que Google Apps Script permite manejar múltiples 'drop-downs' dependientes, actualizando dinámicamente las opciones basadas en las selecciones anteriores.

¿Qué hace la función 'onEdit' en Google Apps Script?

La función 'onEdit' es un evento activado automáticamente cuando un usuario edita una celda en la hoja de cálculo. Permite ejecutar acciones, como actualizar opciones de 'drop-down', basándose en los cambios realizados por el usuario.

¿Cómo se implementa la validación de datos dinámica en Google Sheets?

La validación de datos dinámica se implementa utilizando Google Apps Script. Se configura la validación para que se actualice automáticamente cada vez que el usuario realice una selección en un 'drop-down', filtrando las opciones disponibles según las selecciones previas.

¿Qué es una 'named range' y cómo se utiliza en este contexto?

Una 'named range' es un rango de celdas al que se le asigna un nombre para facilitar su referencia en fórmulas o scripts. En este contexto, se utiliza para organizar y acceder fácilmente a los datos que se utilizarán en los 'drop-downs dependientes'.

¿Cómo se maneja la eliminación de entradas en las celdas cuando se cambian las opciones en los 'drop-downs'?

Para evitar errores cuando se cambian las opciones, se puede eliminar el valor previamente seleccionado y establecer un estado visual (como un color gris) para indicar que los nuevos datos están siendo cargados. Una vez cargados, el color de fondo vuelve a blanco y el nuevo valor se muestra.

¿Qué función tiene el método 'setDataValidation' en Google Apps Script?

El método 'setDataValidation' se utiliza para aplicar reglas de validación a una celda, en este caso, configurando los 'drop-downs' dependientes y actualizando las opciones disponibles según las selecciones previas en otros 'drop-downs'.

¿Cómo se mejora la experiencia de usuario al utilizar 'drop-downs dependientes' en Google Sheets?

La experiencia de usuario se mejora al añadir funciones como el cambio de color de las celdas para indicar que se está procesando una actualización, y al eliminar las entradas previas para evitar confusión. Esto asegura que el usuario sepa cuándo la información está siendo recargada y cuándo está lista para seleccionar.