Tutorial de Google Sheets: Cómo habilitar la validación de datos en selección múltiple usando Apps Script

Original article: Google Sheets Tutorial – How to Enable Multiple Selection Data Validation Using Apps Script

En este artículo, te mostraré cómo permitir que se seleccionen varios elementos mediante la función de validación de datos de lista desplegable en Hojas de cálculo de Google.

Aquí está la Hoja de cálculo de Google que usaremos para el ejemplo. Puedes hacer una copia para editarla tú mismo haciendo clic en Archivo -> Hacer una copia.

Al final del artículo hay un tutorial en video que grabé para este artículo. ?

El problema ?

Mi hijo de cinco años planteó una pregunta que desencadenó una inmersión profunda en Google Apps Script. Quería tener varios elementos seleccionados de una lista desplegable de validación de datos.

¡Deja que un niño de cinco años me envíe a Google, YouTube y más allá en busca de una solución para la hoja de cálculo! ?

michelangelo — Gif de Michelangelo de Las Mutantes Tortugas Ninja

Habíamos creado una hoja de cálculo que mostraba información sobre las Tortugas Ninja mutantes. Nombres, cumpleaños, edades, colores favoritos...

Estaba destacando el increíble poder de las hojas de cálculo para organizar, calcular y visualizar información. Cosas típicas de la educación de niños de cinco años.

Para la columna de color favorito, usamos una lista desplegable para seleccionar de una lista de colores.

Esta es una característica bastante simple de usar en Hojas de cálculo de Google. Para crear una lista desplegable, seleccione Datos -> Validación de datos del menú:

Captura de pantalla del menú de datos de las Hojas de Cálculo de Google

Actualización: la misma función ahora también está disponible cuando haces clic con el botón derecho en una celda:

Captura de pantalla del menú contextual en Hojas de cálculo de Google

En cualquier caso, aparecerá un menú de validación de datos donde podrás establecer tus condiciones.

Teníamos nuestra lista de colores en las celdas, H2:H9 así que seleccionamos Menú Desplegable (desde un intervalo)en la sección Criterios y luego ingresamos ese rango.

Queríamos que se copiara a otras celdas sin afectar a ese rango, así que lo bloqueamos, usando los signos $: =$H$2:$H$9.

Esto permite que la validación se copie a otras celdas mientras se conservan esas referencias de celda para la lista de valores de color.

Captura de pantalla del menú de opciones de validación de datos de Hoja de Cálculo de Google

Hojas de cálculo de Google permite una sola selección ?

El problema es que las Hojas de Cálculo de Google solo permiten una única selección. ¡Queríamos que Leonardo tuviera varios colores favoritos!

Por suerte, Google Apps Script permite escribir código personalizado dentro de las Hojas de cálculo de Google y así pudimos resolver nuestro problema.

Encontré el código para hacerlo en un vídeo de YouTube de Alexander Ivanov y me dispuse a actualizar la información con un vídeo más claro y una explicación propia.

Cómo usar Apps Script?‍?

Abre la pantalla de Apps Script seleccionando Extensiones -> Apps Script desde la barra de menús.

Podrás crear archivos usando el ícono más +. Para este proyecto necesitamos un archivoCode.gs y un archivoPage.html.

El archivo Code.gs

Comienza en el archivo Code.gs.

Lo primero que queremos es un menú desplegable en la barra de herramientas de la Hoja de cálculo de Google para ejecutar nuestro código.

Captura de pantalla de la barra desplegable personalizada de Google Sheets

Usamos el siguiente código para agregar la opción de interfaz de usuario Show dialog en la barra de menú. Al hacer clic, ejecutará la función showDialog. Al envolver la llamada a estos métodos en la función incorporada onOpen, este menú se añade en la barra de herramientas en cuanto abrimos la hoja de cálculo.

function onOpen(e) {								
	SpreadsheetApp.getUi()								
	.createMenu('Multiple Select Data Validation')
	.addItem('Show dialog', 'showDialog')								
	.addToUi();								
}

A veces tarda un poco antes de que los menús personalizados estén visibles en el menú. Dale unos segundos y aparecerá.

La funciónshowDialog creará una variable HTML a partir de una plantilla que crearemos en un momento. Luego usa el método incorporado .showSidebarpara crear una barra lateral con ese html.

function showDialog() {								
	var html = HtmlService.createTemplateFromFile('Page').evaluate();
	SpreadsheetApp.getUi().showSidebar(html);								
}

Luego tenemos otra función, valid, que verificará la celda actual en busca de cualquier criterio de validación de datos y devolverá esos valores en un arreglo bidimensional.

var valid = function(){								
	try{								
		return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();								
    }catch(e){								
        return null								
    }								
}

Y nuestra función final, fillCell creará un arreglo para contener la lista resultante de valores que queremos que contenga la celda. Luego guarda estos valores en el arreglo y los separa por comas.

Por último, utiliza el método incorporado setValuepara llenar la celda actual con los valores separados por comas (nuestros colores favoritos).

(fillCell y valid se llaman en el código Page.html que estamos a punto de ver).

function fillCell(e){								
	var s = [];								
	for(var i in e){								
		if(i.substr(0, 2) == 'ch') s.push(e[i]);								
}								
    if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', '));
}

Aquí está el código completo para el archivo Code.gs:

function onOpen(e) {								
	SpreadsheetApp.getUi()								
	.createMenu('Multiple Select Data Validation')
	.addItem('Show dialog', 'showDialog')								
	.addToUi();								
}								
function showDialog() {								
	var html = HtmlService.createTemplateFromFile('Page').evaluate();
	SpreadsheetApp.getUi().showSidebar(html);								
}								
var valid = function(){								
	try{								
		return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();								
    }catch(e){								
        return null								
    }								
}								
function fillCell(e){								
	var s = [];								
	for(var i in e){								
		if(i.substr(0, 2) == 'ch') s.push(e[i]);								
}								
    if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', '));
}

El archivo Page.html

Ahora crea y abre un archivo llamado Page.html.

Esto controlará un recuadro emergente donde manejaremos nuestras selecciones múltiples y contendrá:

un formulario con casillas de verificación junto a cada opción
un botón para rellenar la celda actual
un botón para obtener la validación de la celda actual

Captura de pantalla de nuestra barra lateral html personalizada de la Hoja de Cálculo de Google

Usaremos una plantilla HTML para nuestro ejemplo. Primero, crea una variable, data llamando a la función valid() que creamos arriba. Usamos la sintaxis <? CODIGO_VA_AQUI ?> para escribir el código dentro de la plantilla html.

<? var data = valid(); ?>

Luego creamos un formulario para albergar todos los datos extraídos de la función valid():

<form id="form" name="form">
</form>

Y ejecutaremos un código para crear nuestra lista de casillas de verificación dentro de este formulario. Primero, verificamos si los datos en la celda son de tipo [object Array].

<? if(Object.prototype.toString.call(data) === '[object Array]') { ?>

valid()devuelve un arreglo bidimensional porque está usando el método incorporado getValues(). Esto devuelve un arreglo de arreglos, cada una de las cuales es uno de los colores favoritos:

//A modo de ilustración; este es el tipo de arreglo 2D que devolverá valid()

favoriteColors = [
    ["purple"],
    ["red"],
    ["white"],
    ["black"]
]

Esto ayudará para cuando veamos el siguiente fragmento de código que puede parecer abrumador.

Necesitamos acceder a cada color: las cadenas de caracteres. Hacemos esto anidando bucles confor. El primer ciclo itera a través de cada posición en el arreglo de colores favoritos.

<? for (var i = 0; i < data.length; i++) { ?>

El segundo ciclo itera a través de cada elemento en las matrices interiores. En nuestro caso, este siempre será un elemento, ya que cada una de estas matrices tiene una longitud de 1.

<? for (var j = 0; j < data[i].length; j++) { ?>

Recuerde, ["purple"]es un arreglo de longitud 1. El bucle j no cuenta las letras de la cadena dentro del arreglo, solo estamos contando la longitud del arreglo.

Entonces, estamos recorriendo cada elemento en el arreglo 2D y creando una casilla de verificación para cada uno:

<input type="checkbox" id="ch<?= '' + i + j ?>" name="ch<?= '' + i + j ?>" value="<?= data[i][j] ?>"><?= data[i][j] ?><br>

Esto agrega un id y name que comienza con "ch" y luego agrega la posición del arreglo del elemento. También extrae el valor value(el color) además del texto mostrado.

Estas capturas de pantalla pueden ayudar a visualizarlo:

captura de pantalla del elemento inspeccionado: la entrada(input) con una identificación(id)

captura de pantalla del elemento inspeccionado: el formulario(form) y los elementos de entrada(input)

Si nuestra condición inicial if falla, mostraremos un mensaje <p> que indica que tal vez no haya reglas de validación de datos en esa celda mientras se vincula a un artículo de soporte que muestra cómo crear una lista desplegable en la celda.

else { ?>								
	<p>Maybe current cell doesn't have <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation...</a></p>								
<? } ?>

Cómo programar los botones

Luego necesitamos nuestros dos botones.

El primer botón extrae la validación de datos de la celda. Ejecuta la función showDialog() que creamos en Code.gs y crea el formulario de casillas de verificación si existen valores de validación de datos.

// obtiene la validación de la celda actual
<input type="button" value="get validation from current" onclick="google.script.run.showDialog()" />

El segundo botón rellena los valores seleccionados en la celda actual. ¡Lo que buscábamos todo el tiempo! Esto ejecuta la función fillCell de Code.gs.

// Rellena los valores de la celda actual 
<input type="button" value="fill current" onclick="google.script.run.fillCell(this.parentNode)" />

Aquí está el código completo de Page.html:

<div>								
<? var data = valid(); ?>								
<form id="form" name="form">								
<? if(Object.prototype.toString.call(data) === '[object Array]') { ?>								
<? for (var i = 0; i < data.length; i++) { ?>								
    <? for (var j = 0; j < data[i].length; j++) { ?>								
        <input type="checkbox" id="ch<?= '' + i + j ?>" name="ch<?= '' + i + j ?>" value="<?= data[i][j] ?>"><?= data[i][j] ?><br>								
    <? } ?>								
<? } ?>								
<? } else { ?>								
        <p>Maybe current cell doesn't have <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation...</a></p>								
<? } ?>								
    <input type="button" value="fill current" onclick="google.script.run.fillCell(this.parentNode)" />								
    <input type="button" value="get validation from current" onclick="google.script.run.showDialog()" />								
</form>								
</div>

Resumen

Sí, es una solución bastante engorrosa para algo relativamente simple. Tal vez Google agregue esta funcionalidad en algún momento. Recientemente, actualizaron la Validación de datos para incluir una funcionalidad más moderna y fácil de usar, por eso no lo descarto.

Hasta ahora, esta ha sido una gran solución para mí y estoy agradecido a Alexander por su código inicial.

¡Espero que este artículo te haya ayudado a comprender un poco mejor el código y que te capacite para crear tus propias soluciones personalizadas con Hojas de cálculo de Google!

Como prometí, aquí está mi video tutorial:

Videotutorial ?️ (en inglés)

¡Gracias!

¡Gracias por leer! Si esto te resultó útil, me encantaría que me siguieras y dijeras hola ? en LinkedIn y YouTube, donde puedes encontrar más contenido como este.

¡Mis mejores deseos!