Usando hiperenlaces y Apps Script para mejorar la trazabilidad en Google Sheets
Tra-za-bi-li-dad. ¡Qué bonita palabra! Una de esas que sin duda te hacen parecer más listo si la repites con la suficiente insistencia en tus reuniones familiares repletas de cuñados. Y ya no te digo en la barra de un bar, allí te va a garantizar atención VIP de manera inmediata.
Hoy me veo en la obligación de parar las máquinas de otro proyecto que tengo casi a punto de caramelo (sorpresa) para hablarte de la trazabilidad. Y de hojas de cálculo de Google. Y de Apps Script. Y encima se trata de un tinglado breve, práctico y no demasiado rebuscado (creo 😅). ¡Si es que no se puede pedir más!
La culpa de este interludio la ha tenido un indicador que utilizamos en el centro para medir la satisfacción de una de nuestras partes interesadas, probablemente la más importante: el alumnado.
El susodicho artefacto, «Indicador I09» de enigmático nombre, no es otra cosa que una hoja de cálculo de Google, a la que de hecho hacía algunas semanas que andaba pensando en darle un meneo para introducir ciertas mejoras estéticas y funcionales. Y el caso es que mientras finalmente lo hacía, se me ocurrió esta pequeñez que paso a contarte en las próximas 6.000 y poco palabras.
En este artículo vamos a implementar juntos diversos mecanismos para enlazar fácilmente la información relacionada que se encuentra en distintas tablas de tus hojas de cálculo.
TABLA DE CONTENIDOS
Relacionando la información en tus hojas de cálculo
Ya sabes que la mano que mece la cuna de los datos maneja el mundo. Y cuando esos datos se relacionan entre sí resulta mucho más fácil ascender en la pirámide del conocimiento hacia la cumbre de la sabiduría. Ya tienes otra cosa que decirle a tu bar(wo)man de confianza para que te sirva las birras antes que a nadie 🍺.
A diferencia de otras herramientas como Notion, Coda o AppSheet, Google Sheets no dispone de mecanismos nativos para establecer relaciones entre los datos que guardas en las incontables filas de sus tablas eternas.
Pero ahí reside posiblemente su encanto. Somos obreros de datos, pintores de celdas y fontaneros de fórmulas, nos encanta hacer las cosas desde abajo y dotar de significado a todas esas columnas con indescifrables códigos alfanuméricos, direcciones de correo o DNIs para establecer relaciones entre los fragmentos de la información que manejamos. Todo a nuestro modo.
Recapitulemos, Google Sheets nos ofrece múltiples posibilidades a la hora de analizar la información relacionada:
- Insertar fórmulas para buscar y filtrar la información atendiendo a diferentes criterios de coincidencia entre los valores almacenados en distintos conjuntos de datos. Aquí nos encontramos —seguro que me dejo alguna— con prácticas funciones como BUSCARX, QUERY, FILTER, o todas las variantes de las funciones de recuento y suma condicional, por ejemplo.
- Las im-pres-cin-di-bles tablas dinámicas, que facilitan la agrupación, agregación y filtrado de tus datos de mil maneras distintas.
- Ahora también, utilizando el nuevo formato de presentación como Tablas (las nombro así, con mayúscula inicial al menos una vez, dada su relevancia, aunque Google utiliza minúsculas, ellos sabrán). Estas nuevas «tablas» ofrecen numerosas ventajas, aunque también algún que otro inconveniente, por supuesto, como veremos más adelante. Su primera y principal ventaja, a mi modo de ver, reside en la gestión sencilla de vistas, que combinan la ordenación, el filtrado y (atención, esto es nuevo) la presentación agrupada de las filas atendiendo a los valores registrados en cualquiera de sus dimensiones (columnas).
Pero hoy no quiero hablarte de ninguna de estas características. Bueno, la verdad es que de esas Tablas —con la t mayúscula— puede que un poco.
Pero permíteme que ponga ahora mismo encima de la mesa el caso de uso que introducía hace unos instantes. Mi indicador I09 alberga numerosos conjuntos de datos, pero me centraré ahora en los almacenados en las pestañas Informes y Comentarios:
- Informes. Contiene, además de otros datos de contexto, las puntuaciones registradas en las encuestas que utilizamos para sondear la opinión del alumnado matriculado en el centro. Se trata de un conjunto de datos que refleja información principalmente cuantitativa.
- Comentarios. En ella se guardan los comentarios más destacados que hemos capturado en esos mismos cuestionarios de valoración, un elemento cualitativo extremadamente valioso a la hora de sostener la toma de decisiones con las que impulsar la mejora del centro.
El cómo llega toda esa información al indicador I09 no viene al caso ahora, pero seguramente podría dar para una serie de artículos 😁.
¿Cómo sabemos que un comentario ha sido emitido por los estudiantes cuyas encuestas han permitido elaborar un informe determinado? O lo que es lo mismo, ¿cómo se relaciona un comentario almacenado en cierta fila de la tabla de comentarios con su registro de encuestado correspondiente en la tabla de informes?
Muy fácil: a través del código de expediente del curso, algo como «CF-0924-194», un dato único para cada acción formativa que se almacena en sendas columnas de ambas tablas.
Evidentemente, podríamos añadir nuevas columnas calculadas en cualquier de estas tablas para mostrar información vinculada procedente de la otra gracias a la existencia de esa clave única común a informes y cursos, que relaciona ambas entidades de manera absolutamente inequívoca: el código de expediente del curso.
📝 Resumiendo filas relacionadas en las nuevas Tablas de Google Sheets (+ una extraña peculiaridad)
Nada nos impide por tanto añadir información unitaria que aporte contexto en cualquiera de ambos conjuntos de datos. O incluso resumirla (como explicaba en el artículo que como quien no quiere la cosa me acabo de sacar de la manga 🙏) tirando de algunas de las funciones que mencionaba con anterioridad.
Pero me pregunto si no te gustaría acceder a esos registros relacionados de otro modo.
Por ejemplo, haciendo clic en un enlace junto a cualquiera de los comentarios registrados para saltar inmediatamente ⚡ a la fila de la tabla en la que se encuentran todos los detalles del informe en cuyo contexto se capturaron.
De aquí a allá, en un clic
Veamos, el URL de cualquier hoja de cálculo de Google presenta una estructura como esta:
https://docs.google.com/spreadsheets/d/17GUUSytI7QOqkJE8xFKPdtc_RDRQkhu5ELWECtnunck/edit?gid=673982577#gid=67398257
Igual ya te sabes lo que sigue, pero mejor nos aseguramos:
- La larga secuencia alfanumérica tras
/d/es el ID de la hoja de cálculo, un número único de manera universal que la identifica y que es inmutable: va a ser siempre el mismo, sí, aunque le cambies el nombre. - El interrogante [
?] indica que lo que viene a continuación el primero de los llamados parámetros de consulta (o cadena de consulta) del URL. Se trata de un conjunto de secuencias de texto como valor=expresión, separadas por el delimitador «et» (ampersand) [&] cuando hay más de una. El uso de estos parámetros de consulta está muy extendido, pero su número y función son característicos de cada servicio web. En este caso, solo tenemos uno, gid, otro identificador, esta vez de la hoja (pestaña de la hoja de cálculo) que se abrirá al acceder al URL. - Finalmente, la almohadilla [
#] que viene a continuación es un marcador o ancla HTML convencional. En principio puede omitirse, pero reaparecerá automáticamente de manera insistente cuando lo hagas al cargar o recargar la hoja de cálculo, adoptando el mismo valor que el gid.
☝ Los URL de los servicios de Google admiten, en general, una gran cantidad de parámetros de consulta, algunos más oscuros, otros más conocidos. Hay tantos que me he tomado la molestia de construir un pequeño sitio web, googleurltricks.tk, para tener a mano los URL mágicos de Google más importantes. Un gran número de ellos están basados en el uso de parámetros de consulta, otros como los muy populares en el ámbito de Google Drive /copy, /preview o /export, utilizan en cambio los llamados parámetros de ruta. Mismo concepto, en cualquier caso.
Los parámetros de consulta son como una varita mágica 🪄 con la que puedes lograr cosas sorprendentes y a menudo bastante útiles. Echa un vistazo si no me crees a esta función HIPERVINCULO_HDC que forma parte de mi Kit de supervivencia de funciones con nombre para Google Sheets.
⚠️ Aprovecha estos URL especiales con precaución. A menudo no están oficialmente documentados ni soportados y nadie garantiza que vayan a ser eternos. Lo cierto es que muchos de ellos llevan funcionando desde hace eones sin problemas, aunque otros, muy extendidos también, lamentablemente han dejado de hacerlo recientemente para alegría del personal.
Dicho todo esto, debes saber que el URL de cualquier hoja de cálculo admite un parámetro de consulta adicional, menos evidente, denominado range. Por ejemplo, este URL...
https://docs.google.com/spreadsheets/d/17GUUSytI7QOqkJE8xFKPdtc_RDRQkhu5ELWECtnunck/edit?gid=673982577#gid=673982577&range=1579:1579...conduce directamente a la fila 1579 de la hoja de cálculo y hoja caracterizadas por sus identificadores únicos. En lugar de un intervalo abierto por la derecha de fila completa, puedes designar cualquier otro como A20:H50 o A2:A.
¿De qué chistera nos hemos sacado esto?
Estos enlaces dirigidos a un rango específico pueden obtenerse utilizando el comando Obtener enlace a este intervalo que aparece en el menú contextual que se despliega al hacer clic con el botón derecho del ratón sobre una celda o intervalo de celdas. Esto me hace pensar que su uso es razonablemente seguro.
De hecho, cuando lo que pretendemos es enlazar intervalos de datos internos, es decir, dentro de la misma hoja de cálculo, podemos omitir tanto la sección general del URL como el propio ID de la hoja de cálculo. De este modo, aunque a lo largo del artículo usaremos siempre URL completos, algo como esto funcionará perfectamente a la hora de insertar un enlace a cualquier otra celda o intervalo de celdas de la misma hoja de cálculo, bien sea de manera manual (Insertar → Enlace) o mediante fórmulas:
?gid=673982577#gid=673982577&range=1579:1579Pues ya lo tenemos, ¿no? Corramos entonces a montar algo con utilidad práctica ahora mismo.
Utilizaremos una hoja de cálculo simplificada, similar a mi indicador I09, aunque con datos de prueba ficticios y en menor cantidad. Así no hará falta que siga emborronando ni ampliando las capturas como hasta ahora, dado que contenían una gran cantidad de numeritos de procesos de encuestado reales.
Cuando la abras, comprobarás que he replicado las hojas Comentarios e Informes del indicador I09 previo. Como siempre, hazte una copia para destriparla con total libertad.
En la hoja de comentarios utilizaremos una expresión para conectarla (enlazarla) con la fila correspondiente en la hoja Informes que contiene las puntuaciones, así como otra información relativa al mismo curso.
La hoja de informes, por su parte, contiene algunos registros de prueba.
Verás que he usado, como en el indicador real, el nuevo formato de tablas. Sí, creo sinceramente que ha llegado para quedarse, así que más vale que te vayas acostumbrando a ellas, al menos de entrada.
La fórmula que necesitamos en la columna E de la hoja de comentarios es esta:
=LET(
urlHdc;"https://docs.google.com/spreadsheets/d/";
idHdc;"1CbVvKfkB8heqDzKq1bmHeXeVkOyK-coGpzrfBY6aOIs";
idHoja;"1939075467";
fila;1+COINCIDIR(B2;Informes[Código curso];0);
SI(
ESERROR(fila);
"Sin informe";
HIPERVINCULO(
urlHdc & idHdc & "/edit" &
"?gid=" & idHoja & "#gid=" & idHoja &
"&range=H" & fila;
"Ver informe curso"
)
)
)
He definido probablemente de manera innecesaria distintas variables por medio de la función LET para tratar de que el modo en que se construye el URL del hiperenlace resulte más evidente.
☝ Un consejo de amigo friqui de las hojas de cálculo: abusa de LET para hacer tus fórmulas más inteligibles. Tu yo futuro, probablemente tan friqui como yo, te lo agradecerá cuando tenga que volver sobre sus (tus) pasos.
Para identificar la fila en la que se encuentra el informe vinculado al comentario se utiliza la función COINCIDIR, que devuelve la posición relativa del elemento buscado en un intervalo, en este caso Informes[Código curso]. Sí, esta sintaxis tan molona es cosa de esas nuevas tablas. A ese valor le sumaremos 1, puesto que la tabla cuenta con una fila de encabezado que nos tenemos que saltar.
El resto es cosa de la función HIPERVINCULO y del operador & de concatenación de cadenas. Observa que he dirigido el enlace a la columna H de la hoja de informes. De ese modo, saltaremos directamente a la celda que contiene la puntuación global.
Pero atención, no solo eso, sino que al pasar el ratón sobre el texto del enlace aparecerá un pequeño panel emergente que nos adelantará tanto dónde vamos a saltar como el contenido de la celda destino. Es decir, obtenemos de rebote una vista previa de la puntuación antes de activar el enlace. Muy práctico, ¿no te parece?
Te decía más arriba que el marcador de posición #gid parece no ser necesario, pero como forma parte del URL que se obtiene utilizando el comando del menú contextual de Google Sheets he optado por incorporarlo tal cual en la fórmula, no vaya a ser que se nos escape algo.
Mucho cuidado con mover la columna que contiene las puntuaciones a otro lugar porque ya no saltaremos a ella. El parámetro de consulta que forma parte del URL se inserta como una cadena de texto, por esa razón la referencia al intervalo destino no se ajustará automáticamente del mismo modo que las referencias en una fórmula convencional.
Este es el resultado, en vivo y en directo:
¿Todo claro? Me alegro. No obstante, este tinglado esconde tres particularidades que creo que vas a querer conocer. Vamos con ellas.
- Si se omite la subcadena /edit al construir el URL pasará algo interesante. Por una parte, el pequeño panel flotante que se despliega al sobrevolar el texto del enlace con el ratón mostrará ahora otro aspecto, similar al de un enlace externo, y no incluirá el contenido de la celda destino. Además, al hacer clic sobre el enlace, este se abrirá en una nueva pestaña.
Omitir /edit en el URL tiene estas consecuencias. - Si el parámetro de consulta «&range» se inserta en el URL en primer lugar, justo antes la sección donde se indica el gid que determina la hoja, al saltar al intervalo destino resultará imposible salirse de él o editar otras celdas diferentes a las que quedan seleccionadas. Es como si la vista quedase bloqueada, lo que puede que te venga bien en determinadas situaciones. Por ejemplo (pruébalo tú mismo/a):
https://docs.google.com/spreadsheets/d/1CbVvKfkB8heqDzKq1bmHeXeVkOyK-coGpzrfBY6aOIs/edit?range=2:2&gid=1939075467#gid=1939075467
En este caso el enlace siempre se abrirá en nueva pestaña, con independencia de que hayamos omitido o no el /edit.
- La tercera y última particularidad tiene que ver la recién adquirida capacidad de agrupación de filas del nuevo formato de tablas. El problema es el comportamiento que exhibe la tabla al hacerlo, dado que las filas se renumeran, de un modo además un tanto tortuoso, debo decir 😨.
Te voy a mostrar dos tablas de comentarios simplificadas idénticas, la de la izquierda se muestra tal cual, en la de la derecha se ha aplicado una agrupación por los valores almacenados en la columna Analizar comentarios. Además, he coloreado en ambas la fila 2, qué aparentemente pasa a a ser la fila 3 cuando se agrupa.Lío, festival del humor (cuando agrupas)... llámalo como quieras, pero esto no me parece ni medio normal. En esta situación, un hiperenlace generado mediante una fórmula como la que estamos utilizando no funcionará correctamente. ¿No me crees? Fíjate pues en las fórmulas que he insertado en las celdas E1 de ambas hojas. El código CURS-110 claramente sigue encontrándose en la fila 1 de ambas tablas (sin tener en cuenta la de encabezado), pero la tabla de la derecha muestra el código CURS-110 en la fila 3, en tanto que CURS-104 aparece en la fila 2. Por si fuera poco el lío, una expresión como
=B2comprobamos que devuelve CURS-110 en la hoja de cálculo de la izquierda y CURS-104 en la de la derecha, que está agrupada. Puedes verificarlo en las hojas Test tabla y Test tabla agrupada.
Me pincho y no sangro 😵💫.
Está claro que el equipo que desarrolla Google Sheets no tenía mucho margen de maniobra para hacer que una herramienta que basa su funcionamiento en una rejilla interminable de celdas numeradas se comportase de manera consistente en estas circunstancias, como si es el caso de otras que fueron creadas libres de esta servidumbre (👋 Notion y Coda). Pero había que decirlo y se ha dicho. Será interesante sentarse a observar hacia dónde y de qué manera evoluciona el nuevo formato de tabla.
En cambio, si el intervalo que se enlaza está simplemente filtrado, aparecerá un informativo mensaje para indicárnoslo.Afortunadamente esto está mucho mejor resuelto.
Por otro lado —levantemos un poco la vista— en este caso de uso, que ha sido simplificado de manera deliberada, se ha utilizado una única columna (el código del curso) para establecer la coincidencia. ¿Pero y si necesitáramos hacerlo utilizando dos o más para encontrar el rango al que saltar?
Te muestro a continuación la fórmula, más complicada, que estoy usando realmente en mi indicador I09. En ella contemplo la posibilidad de se realicen varios procesos de encuestado a lo largo del desarrollo de un curso (encuestado inicial, intermedio y final), y por tanto pueda darse el caso de que existan varios informes con el mismo código del curso. Eso me obliga a caracterizar los comentarios recogidos no solo con el código de curso, sino también indicando el tipo de encuesta del proceso de encuestado / informe con el que están asociados.
=SI(
Y(D2="Encuesta";I2<>"Periódica");
SI(
ESBLANCO(F2);;
LET(
filaInforme;FILTER(
SEQUENCE(
FILAS(Informes[Expediente curso]);
1;
FILA(Informes[Expediente curso]));
Informes[Expediente curso]=F2;
Informes[Tipo encuesta]=I2);
SI(
ESERROR(filaInforme);
"¡Sin informe!";
HIPERVINCULO(
"https://docs.google.com/spreadsheets/d/
17GUUSytI7QOqkJE8xFKPdtc_XDRQkhk9poWECtnuncl/edit?
gid=673012577#gid=673012577&range=" & filaInforme &
":" & filaInforme;"Ir al informe"
)
)
)
);)
La idea feliz pasa por utilizar la función FILTER, que permite establecer múltiples condiciones de coincidencia de manera natural. No obstante, como esta no devuelve el número de fila en la que se produce la coincidencia, utilizamos la función SEQUENCE para generar un vector de índices numéricos crecientes, con tantas filas como informes hay en la tabla Informes, que nos permitan obtener su posición. Además, se tienen en cuenta otras circunstancias, como que no se haya introducido aún el código del curso en la fila del comentario o que el este se haya recogido durante un proceso de encuestado de un tipo determinado.
Pero no nos compliquemos la vida con esto, al menos hoy, y volvamos al caso propuesto inicialmente.
¡Bien, esto funciona de rechupete!
Sin embargo, la fórmula que hemos diseñado tiene un pequeño inconveniente: debemos insertar manualmente en la la expresión los identificadores tanto de la hoja de cálculo como de la hoja (pestaña) a la que deseamos saltar. Que sí, que ya sabemos que ambos son inmutables a lo largo de la vida de la hoja de cálculo y de las hojas que contiene, pero no deja de parecerme un aspecto mejorable.
¿Podemos automatizar esto? ¡Podemos ✊! ¡Que pase Apps Script!
Apps Script pone su granito de arena
Si sigues este espacio o mis redes sociales sabrás que llevo una buena temporada dando la tabarra con las funciones con nombre. Hablar de las cosas que te gustan no cuesta ❤️🔥, así que hoy me encantaría decirte que vamos a recurrir a ellas, tanto para encapsular y contener la complejidad de la expresión que hemos desarrollado en el apartado anterior, como para resolver el inconveniente del que te hablaba al final de él.
Pero no puedo hacerlo 🤷🏻.
Hay cosas que quedan más allá del alcance de las funciones con nombre, o lo que es lo mismo, del lenguaje de fórmulas de Google Sheets, que en definitiva constituye el motor que las propulsa.
Para mejorar la situación necesitamos un procedimiento que nos permita obtener en el contexto de una fórmula tanto el identificador de la hoja de cálculo actual como el de la pestaña a la que deseamos saltar, a partir de su nombre. Y no conozco manera de hacerlo recurriendo exclusivamente al motor de expresiones de Google Sheets.
Afortunadamente, podemos recurrir en su lugar a una función personalizada Apps Script, de las que he hablado también con frecuencia en este espacio. Además, mi complemento para hojas de cálculo HdC+ incluye un buen número de ellas, como comprobarás si examinas su documentación).
Y como verás, lo vamos a lograr de un modo esencialmente sencillo, aunque por aquello de hacer las cosas medio bien he optado por adornar un poco la solución. Calma, sera cosa de exactamente 41 líneas, de las cuales 15 son de comentarios y otras 9 están en blanco.
☝ Hay cosas que tampoco podremos lograr utilizando funciones personalizadas Apps Script, aunque algunos a veces nos obstinamos en lograrlo 🥵. Por esa razón, debemos conocer las capacidades de ambas, funciones con nombre y funciones personalizadas GAS, para escoger en cada momento la más adecuada o incluso utilizar ambas de manera complementaria en cada caso de uso específico.
La buena noticia es que podemos obtener los identificadores que necesitamos de modo absolutamente trivial gracias a Apps Script.
ID de la hoja de cálculo actual:
const hdc = SpreadsheetApp.getActive().getId();
URL completo de la hoja de cálculo actual (incluye el ID)
const hdc = SpreadsheetApp.getActive().getUrl();ID de la hoja (pestaña) a partir de su nombre almacenado en la variable nombreHoja.
const hoja = hdc.getSheets().find(hoja => hoja.getName() == nombreHoja);
=LET(
urlHdc;"https://docs.google.com/spreadsheets/d/";
idHdc;obtenerIdHdcGas();
idHoja;obtenerIdHojaGas("nombre_de_la_hoja_destino");
fila;1+COINCIDIR(B2;Informes[Código curso];0);
SI(
ESERROR(fila);
"Sin informe";
HIPERVINCULO(
urlHdc & idHdc & "/edit" &
"?gid=" & idHoja & "#gid=" & idHoja &
"&range=H" & fila;
"Ver informe curso"
)
)
)
Pero ya que estamos, vamos a montar una función personalizada que se encargará de construir totalmente el URL del enlace, contemplando además la posibilidad de bloquear el intervalo al saltar o escoger si el enlace se abrirá en una pestaña nueva o no dado que sabemos como hacerlo.
URL_HIPERVINCULO_INTERNO
(nombreHoja, rango, bloquearRango, abrirPestanya)
| Argumento | Descripción | Valor si se omite |
| nombreHoja | Nombre de la hoja en la que se encuentra el intervalo al que se desea saltar, entre comillas dobles [ "..."]. | La primera que se ha creado en la hoja de cálculo. |
| rango | Referencia a una celda o intervalo, sin el nombre de la hoja, entre comillas dobles [ "..."]. | "A1" |
| bloquearRango | Si es VERDADERO, no será posible seleccionar otras celdas diferentes al rango indicado como argumento. Fuerza la apertura del enlace en nueva pestaña. | FALSO |
abrirPestanya | Si es VERDADERO, el enlace se abrirá en otra pestaña. | FALSO |
Vamos ahora con la implementación:
/**
* Devuelve el URL que lleva al intervalo de datos de la hoja indicada en la hoja de cálculo actual.
*
* @param {"Informes"} nombreHoja Nombre de la hoja en la que se encuentra el intervalo al que se desea saltar, si se omite se utilizará la primera.
* @param {"C30:L30"} rango Referencia a una celda o intervalo, entre comillas dobles, sin el nombre de la hoja, "A1" si se omite.
* @param {VERDADERO} bloquearRango Si es VERDADERO, no será posible seleccionar otras celdas diferentes al rango indicado como argumento
* (fuerza la apertura del enlace en nueva pestaña), FALSO si se omite.
* @param {FALSO} abrirPestanya Si es VERDADERO, el enlace se abrirá en otra pestaña, FALSO si se omite.
* @returns Un URL como https://docs.google.com/spreadsheets/d/{ID_HDC}/edit?gid={ID_HOJA}#gid={ID_HOJA}}&range=C30:L30
*
* @customfunction
*/
function URL_HIPERVINCULO_INTERNO(nombreHoja, rango, bloquearRango, abrirPestanya) {
const hdc = SpreadsheetApp.getActive();
// Tratar argumentos, se comprueba si alguno se ha omitido y si el tipo es correcto, no se utilizan parámetros por
// defecto en la declaración de la función para tener mayor flexibilidad en su gestión aquí
nombreHoja = typeof nombreHoja != 'string' || nombreHoja == '' ? hdc.getSheets()[0].getName() : nombreHoja;
rango = typeof rango != 'string' || rango == '' ? 'A1' : rango;
bloquearRango = typeof bloquearRango != 'boolean' ? false : bloquearRango;
abrirPestanya = typeof abrirPestanya != 'boolean' ? false : abrirPestanya;
// Construir el URL
const hoja = hdc.getSheets().find(hoja => hoja.getName() == nombreHoja);
if (hoja) {
const cadenaBaseUrl = abrirPestanya ? hdc.getUrl().slice(0, -5) : hdc.getUrl();
let subcadena1Url = `gid=${hoja.getSheetId()}#gid=${hoja.getSheetId()}`;
let subcadena2Url = `range=${rango}`;
// Poner en primer lugar el parámetro de consulta que establece el rango objetivo si deseamos bloquearlo.
if (bloquearRango) [subcadena1Url, subcadena2Url] = [subcadena2Url, subcadena1Url];
// Devolver cadena final del URL
return `${cadenaBaseUrl}?${subcadena1Url}&${subcadena2Url}`;
} else throw 'La hoja indicada no existe en esta hoja de cálculo';
}
Algunos comentarios sobre la implementación de URL_HIPERVINCULO_INTERNO():
- Las líneas 1 - 13 constituyen la declaración de la función, que incluye el bloque JSDoc que la dota de la familiar ayuda contextual propia de las funciones nativas de Google Sheets.
- En la línea 15 se obtiene el objeto que caracteriza a la hoja de cálculo actial.
- En las líneas 17 - 22 se inspeccionan los argumentos que recibe la función. Algunos de ellos son opcionales y pueden saltarse introduciendo un punto y coma [
;] o simplemente omitirlo totalmente si no hay más parámetros a continuación. - Las líneas 24 - 37 son las que realmente ponen en su lugar las diferentes secciones de la cadena de texto que constituye el URL del hiperenlace, teniendo en cuenta los diferentes parámetros que se le pasan a la función, para finalmente devolver esta secuencia de texto como resultado.
- Finalmente, la línea 39 se encargará de emitir un mensaje de error cuando la hoja a la que se desea saltar no exista.
☝ No dejes de revisar mis artículos previos sobre la construcción de funciones personalizadas con Apps Script si estás interesado en conocer más detalles sobre sus capacidades y principios de diseño.
En la hoja de cálculo que te enlazaba en la sección anterior encontrarás un pequeño entorno de prueba (hoja fx GAS hiperenlaces internos) para que puedas probar cómodamente esta función y familiarizarte así con ella (ya sabes, hazte una copia para poder editarla, etc.).
Gracias a este diminuto artefacto Apps Script 💪, la expresión que necesitamos ahora para generar los enlaces en la pestaña Comentarios queda así de aseada :
=LET(
fila;1+COINCIDIR(B2;Informes[Código curso];0);
SI(
ESERROR(fila);
"Sin informe";
HIPERVINCULO(
URL_HIPERVINCULO_INTERNO("Informes";fila & ":" & fila);
"Ver informe curso"
)
)
)
En la hoja Comentarios encontrarás diversos ejemplos de uso de las expresiones que hemos desarrollado en este apartado y en el anterior, no dejes de echarles un vistazo:
- Hiperenlace básico (columna E).
- Hiperenlace que abre en una nueva pestaña (columna F).
- Hiperenlace con bloqueo de intervalo destino (columna G).
- Fórmula mejorada que integra la función personalizada GAS (columna H).
Ni tan mal 😏.
¿Y si quiero saltar a otras hojas de cálculo?
Buena pregunta.
Por supuesto, los URL con los que estamos trabajando tienen un ámbito de aplicación universal. Basta con utilizar el ID de una hoja de cálculo diferente a aquella sobre la que estamos trabajando para que todo funcione del modo descrito hasta el momento.
Lógicamente, en el caso de enlaces a otras hojas de cálculo ahora necesitaremos de manera ineludible el URL completo:
https://docs.google.com/spreadsheets/d/ID_HDC/edit?gid=ID_HOJA#gid={ID_HOJA}}&range=rango
Para explorar las posibilidades que tenemos en esta nueva circunstancia, he preparado una nueva hoja de cálculo independiente para almacenar únicamente los informes que contendrá exactamente la misma información (pero solo esa) que la registrada en la hoja Informes en la hoja de cálculo que hemos estado utilizando hasta el momento.
Lo primero que resulta evidente es que ahora necesitaremos encontrar la fila que contiene la clave única (código del curso) en un conjunto de datos que no se encuentra directamente almacenado en la hoja de cálculo sobre la que estamos trabajando.
No hay problema, para eso tenemos la potente función IMPORTRANGE, que afortunadamente ya admite el uso de la nuevas sintaxis de selección del intervalo a importar propia del nuevo formato de tabla. Y no sé si lo sabías, pero también acepta intervalos con nombre definidos en la hoja de cálculo de origen.
En principio podemos plantear una fórmula como esta para generar un enlace a la nueva e independiente hoja de cálculo:
=LET(
datosExternos;IMPORTRANGE("https://docs.google.com/spreadsheets/d/
1mu_wkJq6muxPXBMVAJuu2isUJGVKO6E4bo-IurZtVHo";"Informes[Código curso]");
urlHdc;"https://docs.google.com/spreadsheets/d/";
idHdc;"1mu_wkJq6muxPXBMVAJuu2isUJGVKO6E4bo-IurZtVHo";
idHoja;"510686424";
fila;1+COINCIDIR(B2;datosExternos;0);
SI(
ESERROR(fila);
"Sin informe";
HIPERVINCULO(
urlHdc & idHdc & "/edit" &
"?gid=" & idHoja & "#gid=" & idHoja &
"&range=H" & fila;
"Ver informe curso"
)
)
)
como ves, solo hemos necesitado definir la variable datosExternos dentro de la función LET para utilizarla a continuación en la búsqueda de coincidencias.
⚠️ Esto es funcional, pero no operativo. Nadie desea en sus hojas de cálculo una legión de cientos o miles de funciones IMPORTRANGE actuando en solitario pescando datos de manera simultánea de otra hoja de cálculo. No, tú tampoco. ¿El riesgo? Que tu hoja de cálculo no responda con fluidez, hasta el punto de bloquearse, o funcione de un modo inconsistente.
Estoy totalmente seguro de que los desarrolladores de Google Sheets se han afanado por incorporar mecanismos de caché para tratar de mejorar el rendimiento en situaciones como esta, pero personalmente no conozco su eficacia, así que mejor evitemos ponerlos a prueba de manera innecesaria 🙅🏻♂️, ¿no te parece?
¿Podemos realmente evitarlo? ¡Claro ✊!
Si decidimos utilizar una expresión matricial, por ejemplo basada en las funciones ARRAYFORMULA o BYROW, podemos aprovecharnos una vez más de la muy bendita función LET para asegurarnos de que la importación de los datos almacenados en otra hoja de cálculo se realice solo una vez, al menos por cada ocasión en la que se (re)evalúe la fórmula.
Mejoremos ahora mismo por tanto la expresión anterior:
=LET(
datosExternos;IMPORTRANGE("https://docs.google.com/spreadsheets/d/
1mu_wkJq6muxPXBMVAJuu2isUJGVKO6E4bo-IurZtVHo";"Informes[Código curso]");
urlHdc;"https://docs.google.com/spreadsheets/d/";
idHdc;"1mu_wkJq6muxPXBMVAJuu2isUJGVKO6E4bo-IurZtVHo";
idHoja;"510686424";
BYROW(B2:B;LAMBDA(código;SI(
ESBLANCO(código);;
LET(
fila;1+COINCIDIR(código;datosExternos;0);
SI(
ESERROR(fila);
"Sin informe";
HIPERVINCULO(
urlHdc & idHdc & "/edit" &
"?gid=" & idHoja & "#gid=" & idHoja &
"&range=H" & fila;
"Ver informe curso"
)
)
)
)))
)
Hemos mantenido dentro del LET() más externo tanto el IMPORTRANGE() como el resto de variables auxiliares que se mantienen inmutables cuando se generan los enlaces en cada fila de la tabla fuera del bucle que itera sobre ellas por medio de las funciones BYROW/LAMBDA.
No hay color ✌. Pero sí me quedan peros 😁.
El inconveniente reside ahora en que el nuevo formato de tablas no se lleva demasiado bien con expresiones matriciales como la anterior (no digas que no te lo advertí ya al inicio del artículo):
- No se admiten fórmulas matriciales —ni de ningún otro tipo— en el encabezado de las tablas, algo que constituye una práctica muy habitual mediante la que una expresión establece el texto del encabezado y, al mismo tiempo, calcula los valores de todas las celdas por debajo. Esto se hace cuando queremos evitar arrastrar una fórmula para rellenar múltiples celdas, pero deseamos al mismo tiempo conservar la posibilidad de ordenar los datos con normalidad (con ciertos matices).
- Si introducimos una fórmula matricial en la primera fila con datos de la tabla funcionará correctamente, pero al ordenar todo se irá probablemente al carajo porque la expresión única que rellena todas las celdas de la columna cambiará de fila, dejando todas las que quedan por encima vacías.
¿Y si entonces lo queremos todo? Hagamos recuento: formato de tabla aplicado sobre el conjunto de datos, hoja de cálculo independiente con la que enlazar y evaluación eficiente de la fórmula que genera los enlaces, evitando la evaluación de múltiples funciones IMPORTRANGE.
No pasa nada, tenemos soluciones para todo. Te propongo algo tan obvio como esto:
- Realiza un IMPORTRANGE() único del conjunto de datos que está almacenado en otra hoja de cálculo en una hoja auxiliar de la hoja de cálculo sobre la que estás trabajando, esa en la que deseas generar los enlaces. Ocúltala si prefieres no tenerla a la vista.
- Eejcuta todas las operaciones de búsqueda de coincidencias sobre la hoja local, visible o no, manteniendo lógicamente la generación de los hipervínculos de manera que apunten hacia la tabla de la hoja de cálculo independiente.
Las funciones personalizadas Apps Script solo pueden acceder por sí mismas a los datos almacenados en las hoja de cálculo en las que se utilizan. Esto hace que no tenga sentido plantearse la mejora de la función URL_HIPERVINCULO_INTERNO que hemos desarrollado en el apartado anterior, dado que en cualquier caso sería imprescindible indicarle manualmente los IDs requeridos de hoja de cálculo y pestaña objetivo.
🫵🏻 Sí podrías no obstante generalizarla para que soportara tanto enlaces internos, aplicando en ese caso una detección automática de los IDs, como externos, aunque fuera necesario indicárselos de de manera explícita al invocarla. Te lo dejo como ejercicio. Si te atascas, deja un comentario por aquí y te echo un cable.
No obstante, me resisto a finalizar este apartado sin diseñar una función con nombre. ¿Te parece si preparamos una precisamente para aplicarla en este caso? Es cierto que no aportará gran cosa sobre las fórmulas que ya hemos desarrollado, pero al menos así no tendrás que recordar las particularidades (rango, bloqueo, nueva pestaña) de los hiperenlaces que construimos.
HIPERVINCULO_INTERVALO(
id_hdc;
id_hoja;
rango;
ancla;
bloquear_rango;
abrir_pestaña)
=LET(
rango;SI(ESBLANCO(rango);"A1";rango);
ancla;SI(ESBLANCO(ancla);"Enlace";ancla);
bloquear_rango;SI(ESLOGICO(bloquear_rango);bloquear_rango;FALSO);
abrir_pestaña;SI(ESLOGICO(abrir_pestaña);abrir_pestaña;FALSO);
SI(
O(ESBLANCO(id_hdc);ESBLANCO(id_hoja));;
LET(
cadenaBaseUrl;SI(
abrir_pestaña;
"https://docs.google.com/spreadsheets/d/" & id_hdc;
"https://docs.google.com/spreadsheets/d/" & id_hdc & "/edit");
subcadenaUrl1;"gid=" & id_hoja & "#gid=" & id_hoja;
subcadenaUrl2;"range=" & rango;
HIPERVINCULO(SI(
bloquear_rango;
cadenaBaseUrl & "?" & subcadenaUrl2 & "&" & subcadenaUrl1;
cadenaBaseUrl & "?" & subcadenaUrl1 & "&" & subcadenaUrl2
);ancla))))
Te dejo en la hoja de cálculo inicialmente compartida una nueva zona de prueba (hoja fx hiperenlaces internos) para probar la función con nombre HIPERVINCULO_INTERVALO. Solo tiene que ir a Datos → Funciones con nombre para examinar la definición completa de esta función, que naturalmente puedes utilizar para generar hipervínculos tanto intra como inter hoja de cálculo.
En esa misma hoja de cálculo encontrarás dos pestañas adicionales en las que podrás destripar distintos ejemplos de cómo insertar hiperenlaces a una hoja de cálculo diferente mediante fórmulas, de acuerdo con el análisis que hemos desarrollado en este apartado.
En la pestaña Comentarios con informes externos, en la que los datos presentan el formato convencional (no se trata de una tabla) se hace de tres maneras distintas:
- Con IMPORTRANGE() y arrastrando la fórmula (columna E).
- Usando LET() + IMPORTRANGE() para optimizar la importación de datos, además de BYROW() para evitar arrastrar la fórmula (columna F).
- Del mismo modo que en la columna F, pero ahora sustituyendo la expresión que monta el URL por la función con nombre HIPERVINCULO_INTERVALO que acabamos de ver (columna G).
Por su parte, en la hoja Comentarios con informes externos | tabla verás otro ejemplo similar, pero en este caso con los datos en formato tabla y operando sobre filas con registros de informes que han sido previamente importados a esta hoja en la pestaña auxiliar Informes importados.
De esta manera esquivamos el uso de fórmulas matriciales en una tabla, tal y como te aconsejaba hace un momento, al tiempo que evitamos también el uso de una función IMPORTRANGE por fila de comentarios. Ha sido necesario replicar la fórmula de la celda E2 en toda la columna, algo que no obstante nos sugiere la propia tabla.
Por cierto, fíjate en esas columnas que contienen una imagen 🪬 en lugar del típico texto como ancla del enlace. Aunque la ayuda de la función HIPERVINCULO no lo menciona, en su segundo argumento (nombre_enlace) nadie nos impide utilizar directamente la función IMAGE, o bien una referencia a una celda de la hoja en la que se haya insertado previamente, para mostrar un elemento gráfico como ancla en lugar de texto. Por ejemplo:
=HIPERVINCULO("https://www.tictools.tk";IMAGE("https://cdn.bitrix24.com/b9316961/landing/027/027c19d37fefaaa64fc64510f40c2e60/Selecci_n_567.png"))Este pequeño truco, que seguro que ya conocías, me viene ahora de perilla para rescatar el dilema sobre el uso de funciones con nombre y funciones personalizadas GAS en la sección dedicada a Apps Script de este artículo. Me refiero a eso de cuándo usar unas u otras.
Personalmente, mi primera opción son en este momento las funciones con nombre. Creo que se integran de un modo más natural en Google Sheets y proporcionan un mejor rendimiento que las funciones personalizadas creadas con Apps Script, a pesar de sus flaquezas, que no son pocas (aún, espero):
- El lenguaje de fórmulas sigue siendo limitado, especialmente cuando se compara con el JavaScript de Apps Script.
- No disponen de un mecanismo específico similar al Urlfetch de Apps Script para realizar peticiones a APIs externas.
- No es posible insertar comentarios en las expresiones.
- No hay soporte nativo para argumentos opcionales.
- El editor no dispone de las mismas ayudas de las que sí disfrutamos al insertar fórmulas en la hoja (coloreado de sintaxis completo, emparejado de paréntesis, y sugerencias contextuales para los nombres de las funciones y sus argumentos).
- No cuentan con facilidades para la depuración de las expresiones, algo cada vez más necesario a medida que el lenguaje de fórmulas de Google Sheets adquiere mayor potencia y complejidad. Si es que ni siquiera es posible deshacer una acción dentro del cuadro de edición de la fórmula 🫨.
- No es posible manipularlas mediante Apps Script para facilitar su empaquetado y distribución.
Pero volviendo a sus fortalezas, por ejemplo, una función personalizada GAS no tiene manera de producir como resultado una imagen o una fórmula en estado funcional, como acabamos de hacer en la función con nombre HIPERVINCULO_INTERVALO. Tampoco es posible generar directamente con ella un hipervínculo, sino únicamente devolver su URL en el contexto de una fórmula nativa que lo envuelva amorosamente dentro de una función HIPERVINCULO para generarlo realmente.
A cada cosa, lo suyo. Al menos por el momento.
Comentarios finales y siguientes pasos
Mi relación con la complejidad, así en abstracto, ha tenido sus altibajos a lo largo del tiempo.
En líneas generales, me provoca una gran excitación resolver un problema técnicamente complejo. Y cuando eso ha supuesto además haber llevado las herramientas con las que he trabajado más allá de sus posibilidades aparentes, esa excitación se multiplica por dos 💪🏻. Como no podría ser de otro modo, me siento a la vez enormemente impresionado e inspirado por las proezas técnicas de otras personas cuando llegan a ese tipo de soluciones, sin importar lo costosas o rebuscadas que resulten.
Pero también valoro cada vez más las cosas sencillas e ingeniosas que resuelven problemas cotidianos. Los artefactos que construimos no tienen por qué ser necesariamente complejos para que resulten útiles. Y a veces, cuando chocamos con ese en ocasiones inevitable muro de complejidad, tal vez deberíamos pararnos a pensar si estamos empeñando, ofuscados, en tratar de sacar un clavo con un martillo, cuando tenemos una enorme caja de herramientas a nuestra disposición.
Keep it simple... o «lo bueno si breve, dos veces bueno». Lo sé, una equivalencia muy traída por los pelos 🫣.
Creo que este artículo, en el que honestamente no tenía demasiada fe hace unas horas cuando comenzaba a escribirlo 🙄, da visibilidad a una de esas soluciones simples que resuelven un pequeño problema. Un problema que no surge de un caso de uso sintético, sino de los procesos de análisis de datos que dan soporte a la mejora continua en el contexto de un centro educativo. Confío en que finalmente te sirva de inspiración y aporte cierto valor a tus flujos de trabajo con las hojas de cálculo de Google. Ojalá sea así 🙏.
Añadiré tanto la función personalizada Apps Script como la función con nombre que hemos desarrollado a mi complemento HdC+ y al Kit de funciones con nombre, respectivamente, para que las tengas a mano siempre que las necesites.
Una última cosa. Hoy nuestro objetivo ha sido mejorar la trazabilidad de los datos almacenados en nuestras hojas de cálculo usando exclusivamente el lenguaje de fórmulas de Google Sheets para ligarlos en tiempo real. Pero hay otras estrategias alternativas, con sus ventajas e inconvenientes. Podríamos haber diseñado un script GAS, ejecutado manualmente o por medio de un activador por tiempo, que realizase un proceso análogo, una estrategia que no hemos explorado y cuya implementación te propongo como reto.
Si te atascas y prefieres que te ayude, o simplemente te parece una idea interesante, házmelo saber en los comentarios de este artículo para que publique una segunda parte desarrollando esta solución alternativa.
¡Que las hojas de cálculo de Google te sean propicias!