Crea una función original en Hojas de Cálculo (Google Sheets)

¡Utiliza JavaScript para optimizar tus cómputos!

Objetivo

Probablemente ya conoces funciones como SUMA (SUM en inglés) y PROMEDIO pues son muy útiles para hacer matemáticas. ¿Has tenido curiosidad de inventar o crear la tuya propia?

En este artículo exploramos como crear la función que llamaremos COMUN. El objetivo es que la función COMUN nos permita encontrar qué valores son idénticos en una selección de celdas arbitraria.

¿Cúales son los requisitos?

Los requisitos dependen de tus objetivos:

1. Para completar el articulo sólo necesitas saber copiar y pegar (copy & paste)

2. Para elaborar con tus propias funciones, no descritas en este artículo, necesitas saber codificar en el lenguaje de programación JavaScript.

¡Comencemos!

Google nos provee una herramienta muy útil para lograr nuestro objetivo: Editor de secuencias de comandos (script editor en inglés). Abre una nueva hoja y haz click en el editor de secuencias de comandos.

Luego tendrás acceso a la siguiente pantalla:

Aquí es que la codificación de nuestra función COMUN ocurrirá. Una vez dentro del Editor de secuencias de comandos, podemos ver Google nos deja definir funciones en JavaScript.

API

Aprendamos un poco las reglas del API de Hojas de Cálculo (Google Sheets). Observemos algunas de las reglas que el API nos impone.

1. El nombre de la función original no debe terminar en guion bajo ( _ ) o de lo contrario será privada.

2. El nombre de la función original debe ser distinto a las funciones existentes.

3. Si la función devuelve sólo un valor, se coloca en la celda donde fue invocada la función. Por otro lado, si la función devuelve múltiples valores, el resultado se coloca en la celda donde se invocó hacia abajo hasta que terminen los valores. Por último, si se devuelve una matriz, el resultado ocupará el espacio de la matriz comenzando en la celda donde se invocó la función.

4. Las celdas que seleccionemos en nuestra hoja de cálculo serán enviadas a nuestra función como argumentos. Específicamente, si la selección contiene sólo una fila o sólo una columna, el argumento que recibe nuestra función será un arreglo de una dimensión. De lo contrario, si nuestra selección contiene filas y columnas, la función recibirá como argumento un arreglo bi-dimensional con los valores. Éste detalle es muy importante a la hora de implementar nuestra función original.

   

Código

Comenzaremos escribiendo dos funciones privadas que utilizaremos en lo que será nuestra función pública COMUN. La primera función computará el numero de ocurrencias de cada valor en una fila o columna, es decir en un arreglo unidimensional. La función, crea una estructura de Map y lo utiliza para contabilizar el número de ocurrencias de cada valor visto al iterar por cada valor del arreglo. Ignoraremos valores vacíos.

	// ------------------------------------------------------------------
	// Funciones privadas terminan en simbolo: _
	// https://developers.google.com/apps-script/guides/sheets/functions
	// ------------------------------------------------------------------
	// Computa el número de repeticiones de cada celda dado una
	// selección horizontal y/o vertical de una dimensión
	// i.e. {perro: 2, gato: 1}
	function getWordCountInArray_(rows) {
	const m = new Map()
	for (let row = 0; row < rows.length; row++) {
	if (matrix[row] === '') {
	continue
	}
	const key = typeof matrix[row] === 'string'
	? matrix[row].trim()
	: matrix[row]
	m.set(key, m.get(key) != null ? m.get(key) + 1 : 1)
	}
	return m
	}

view raw getWordCountInArray_.js hosted with ❤ by GitHub

Dado a que JavaScript nos permite mezclar tipos de valores en nuestro Map, tomamos precauciones a la hora de eliminar espacio en blanco innecesario utilizando la función trim() que opera solamente en valores de tipo string. Pudiéramos haber convertido todos los valores a tipo string pero eso causa problemas de interoperabilidad de nuestros valores en la Hoja de Cómputos con funciones existentes como SUMA.

Nuestra segunda función privada hace exactamente lo mismo, pero para valores encontrados en un arreglo bi-dimensional, o matriz.

	// Computa el número de repeticiones de cada celda dado una
	// selección bi-dimensional, entiendase una matriz
	// i.e. {perro: 2, gato: 1}
	function getWordCountInMatrix_(matrix) {
	const m = new Map()
	for (let row = 0; row < matrix.length; row++) {
	for (let col = 0; col < matrix[0].length; col++) {
	if (matrix[row][col] === '') {
	continue
	}
	const key = typeof matrix[row][col] === 'string'
	? matrix[row][col].trim()
	: matrix[row][col]
	m.set(key, m.get(key) != null ? m.get(key) + 1 : 1)
	}
	}
	return m
	}

view raw getWordCountInMatrix_.js hosted with ❤ by GitHub

Ahora por fin escribimos nuestra función pública. Declaramos una función con el nombre en todas mayúsculas COMUN. Aquí simplemente adquirimos el Map con nuestros valores y el número de repeticiones. Él mismo, lo adquirimos delegando a la función privada adecuada, dependiendo si la selección del usuario fue de una o dos dimensiones. Por último, filtramos nuestros resultados retornando sólo aquellos valores cuya ocurrencia sea más de uno.

	// ------------------------------------------------------------------
	// Funcion publica que calcula elementos en comun dado una selection
	// de celdas
	// https://developers.google.com/apps-script/guides/sheets/functions
	// ------------------------------------------------------------------
	function COMUN(arr) {
	// De acuerdo al API siempre la selection es un arreglo
	if (!Array.isArray(arr) || arr.length === 0) {
	return 'Selección inválida. Escoja un rango válido e.g. A2:E30'
	}
	// Determinar si la selección es una fila/columna o una matriz.
	// Delegamos la computacion de repeticiones a la funcion adecuada.
	const wordCount = Array.isArray(arr[0])
	? getWordCountInMatrix_(arr)
	: getWordCountInArray_(arr)
	// Capturamos celdas con ocurrencias que tengan mas de 1 repetición
	const result = []
	for (const entry of wordCount.entries()) {
	const [key, count] = entry;
	if (count > 1) {
	result.push(key)
	}
	}
	// Debemos devolver un arreglo con multiples valores o sólo un valor
	// para colocar en la celda.
	return result.length > 0
	? result
	: 'No hay valores en común'
	}

view raw COMUN.js hosted with ❤ by GitHub

Escoge Guardar proyecto

¡Listo!

Demo

Vólvamos a nuestra hoja de cálculo, entremos los siguientes valores e invoquemos nuestra función.

Intentemos una matriz de números. ¿Qué sucede cuando más de un valor se repite? - Exactamente lo que el API nos dice que sucedería, los resultados se ilustran verticalmente desde la celda donde invocamos la función.

Con esto concluimos nuestro artículo. Esperamos que les haya gustado. Si quieren recibir más contenido de codificación o temas parecidos déjen un comentario.

¡Gracias!