Cómo extraer los URL de los enlaces múltiples en las hojas de cálculo de Google con Apps Script

A todos nos gustan las hojas de cálculo. Y las casillas de verificación (ya lo sabemos). Y por eso hoy volvemos a hablar de las primeras... aunque no de las segundas.

Me gustaría contarte en esta ocasión algunas cosillas relacionadas con los hiperenlaces, hipervínculos, o como más rabia te dé llamarlos, en el ámbito de las hojas de cálculo de Google y, más concretamente, con el modo en que pueden ser extraídos de las celdas en las que aparecen.

¿Extraídos? ¿Pero de qué hablas? ¿Qué demonios hay que extraer? Bueno, vamos por partes...

Hiperenlaces y hojas de cálculo

De entrada, y con tu permiso, en lo que sigue voy a utilizar el termino enlaces para referirme a esos hiperenlaces o hipervínculos. No es que a mi teclado le falten la hache, la pe y la erre, simplemente me apetece acortar.

Seguramente ya sabes (¡vivan las obviedades!) que puedes escribir, así a lo loco, un URL en el interior de cualquier celda de una hoja de cálculo.

Al pasar la rata por encima, además, se nos mostrará graciosamente una vista previa más o menos completa de lo que hay más allá de él.

Y es que a las hojas de cálculo (de Google siempre, por favor) les gustan 💙 los enlaces. Tanto que incluso nos dejan construir con ellos pequeños elementos para facilitar la navegación interna, sin fórmulas ni código, como explica aquí tan bien Ben Collins en este mini-hilo-tutorial. All the world's a spreadsheet, my friend.

También podemos insertar enlaces utilizando la función HIPERVINCULO:

El resultado podríamos decir que es mejor, desde un punto de vista visual, dado que ahora no hay ni rastro del probablemente largo y tal vez poco significativo URL. Solo veremos en la celda el texto de anclaje que lo activa, establecido por medio del segundo parámetro (nombre_enlace) de la función, que en todo caso es opcional.

Pero es que además también podemos insertar enlaces usando:

• El comando de menú Insertar ⇒ Enlace.
• El botón 🔗 en la barra de herramienta
• El comando del menú contextual emergente que aparece al hacer clic con el botón derecho de la rata sobre cualquier celda.

Por opciones no será, desde luego.

Al seguir cualquiera de estos tres procedimientos para insertar un enlace...

... la cosa queda así, justo del mismo modo que al utilizar la función HIPERVINCULO:

Y aquí es donde las cosas empiezan a ponerse interesantes, porque resulta que desde algo así como mayo de 2020 🤔, el modo en que se gestionan los enlaces dentro de las celdas permite ciertas filigranas:

• Crear un enlace usando como ancla tan solo una parte del texto de la celda.
• Introducir varios enlaces dentro de una misma celda.

Para ello basta con:

1. Hacer doble clic sobre una celda para editar el texto en su interior.
2. Seleccionar el fragmento de texto que te dé más rabia.
3. Utilizar el botoncito 🔗 que permite insertar enlaces (no, con el menú no hay tu tía) o el atajo de teclado CTRL+K.

¡Quiero mis enlaces de vuelta!

Vale, pues ya sabemos insertar enlaces de mil maneras en esas hojas de cálculo de nuestras entretelas.

Pertrechados con esta capacidad podemos crear largas tablas en las que atesorar nuestros enlaces favoritos usando un texto descriptivo en lugar de esos URL feotes.

Todo bien.

Pero ¿qué pasaría si en algún momento necesitáramos acceder al URL de todos esos bonitos enlaces para hacer cosillas con ellos? Se me ocurren, sin pensar mucho, dos o tres situaciones:

1. Queremos generar un lindo informe de Data Studio.
2. Necesitamos enviar por correo electrónico la información contenida en una serie de filas de la tabla, incluyendo los enlaces, claro, usando un complemento.
3. Pretendemos realizar alguna automatización con Apps Script que precise recuperar los URL de los enlaces de las celdas con algún objetivo, por ejemplo almacenarlos en una base de datos o en otra hoja de cálculo.

Te muestro el fatal desenlace en los dos primeros casos y ya dejo a tu seguro que acertada imaginación el del tercero.

1️⃣ Veamos primero qué pasa con Data Studio:

Pues la primera en la frente, nuestros valiosos URL no aparecen por ninguna parte, solo el texto de referencia.

2️⃣ ¿Y si tiramos del estupendo complemento Form Mule? ¿O del aún más esplendoroso si cabe Autocrat?

Pues tampoco.

Vamos a ver, ¿y esto tiene solución?

Afortunadamente sí, además una relativamente sencilla (sin Apps Script, osea sin GAS 🤡), y otra en la que sí tendremos que arremangarnos y programar un poquillo.

¿Conoces la función FORMULATEXT?

Pues deberías, mola mucho, sobretodo cuando prepararas plantillas de hojas de cálculo para formaciones, infografías o GIFs animados divulgativos.

FORMULATEXT recibe un único parámetro, la referencia a una celda, que debe contener una fórmula, y nos devuelve a cambio esa fórmula como una cadena de texto (o un contundente mensaje de error si lo que había en la celda resulta que no era una fórmula).

Dos consideraciones:

• La cadena de texto que se obtiene como resultado muestra las fórmulas en inglés. Por ejemplo, HIPERVINCULO se transforma en HYPERLINK. Qué moderno, oiga.
• FORMULATEXT no admite un argumento matricial. Toca arrastrar para aplicarla sobre un conjunto de celdas con enlaces.

Vamos a ver si esta simpática función nos puede echar una mano a la hora de recuperar esos preciados URL ocultos. Para ello comprobaremos cuál es el resultado cuando la utilizamos sobre una celda que contiene:

1. Un enlace insertado por medio de la función HIPERVINCULO.
2. Un solo enlace insertado con el botón 🔗 de la barra de herramientas.
3. Varios enlaces insertado con el botón 🔗 de la barra de herramientas.

Al lío:

Como puedes ver, solo obtendremos un resultado distinto de una condición de error en el caso (A), en el que la celda de origen contiene un enlace insertado mediante la consabida función HIPERVINCULO.

Pero al mal tiempo, buena cara.

Ya sabemos que FORMULATEXT nos proporciona, exclusivamente en un caso determinado, una cadena de texto que contiene el URL que perseguimos. Por tanto, solo tenemos que echar mano de la función REGEXEXTRACT y de una expresión regular adecuada para obtenerlo (puedes usar RegExr para ayudarte a construirla).

🤷‍♂️ En ocasiones, al abrir RegExr con una expresión regular precargada (por ejemplo, si haces clic en el enlace del pie de imagen anterior) es posible que se muestre un llamativo mensaje de error. Simplemente modifica algún carácter en el texto de prueba, bajo la expresión regular, para que todo vuelva a funcionar como debe.

Por tanto, la fórmula que necesitaremos es esta:

=REGEXEXTRACT( 
    FORMULATEXT(celda);
    "\(" & CARACTER(34) & "(.+)" & CARACTER(34) & ";.+"
)

👇 Miniexplicación 👇

• Partimos de una celda en la hoja de cálculo que contiene una fórmula similar a =HIPERVINCULO("URL";"TEXTO_ANCLAJE"), de la que pretendemos extraer únicamente la cadena de texto correspondiente al elemento URL.
• Uso un grupo de captura (.+) para extraer solo la parte del texto de la fórmula que me interesa, desde las comillas de apertura justo detrás del primer paréntesis hasta las de cierre que delimitan el URL por la derecha, sin incluirlas, claro está.
• Como REGEXEXTRACT necesita que la expresión regular vaya también entre comillas dobles, 💡 no tengo más remedio que construirla usando el viejo truco de representar las comillas de apertura y cierre del URL con la función CARACTER y su código UNICODE (34) y concatenarlas al resto de la cadena que constituye la expresión regular utilizando el operador &.
• A continuación, concateno también el resto del patrón de la expresión regular (;.+), que representa el punto y coma separador de los parámetros de la función HIPERVINCULO y todo lo que viene detrás.

Y con esto, amigas y amigos, tenemos solucionado uno de los tres casos posibles. ¿Pero que pasa con el resto?

Para todo lo demás... ¡Apps Script!

La GAS-estrategia

Tratemos ahora de dar con una solución más general.

Veamos, la clase RichTextValue del servicio Apps Script de hojas de cálculo cuenta con el método getLinkUrl(), que parece ser la respuesta a nuestras plegarias:

Con él podemos obtener el URL del enlace enterrado en una celda. Y también el aplicado sobre cualquier subcadena del texto en su interior, así que en principio ya deberíamos estar en disposición de resolver también los casos (B) y (C) de los que hablábamos en el apartado anterior.

Pero eso de tener que averiguar la posición de inicio y fin de cada posible subcadena de texto a la que está anclado un enlace no parece muy práctico, ¿verdad?

☝ Uno de los secretos para cocinar un buen plato es conocer bien los ingredientes que podemos echar en la cazuela. Y los ingredientes de un script GAS son los métodos de los distintos servicios que tenemos a nuestra disposición. Más vale que los revises bien, para conocer de qué son capaces, antes de lanzarte a picar código y darte cuenta posteriormente, con pesar, de que todo era mucho más sencillo (o complicado, que a veces también pasa) de lo que pensabas. Y te digo esto porque lo he sufrido en mis carnes... más de una vez.

Por tanto, sigamos examinando la lista de métodos. Verás que justo debajo tenemos el intrigante, pero muy oportuno, getRuns().

Este método se va a encargar de trocear para nosotros nosotros ese texto rico (o mejor, enriquecido) dentro de la celda, devolviendo un vector con tantos elementos de tipo RichTextValue como sean necesarios para segmentar las subcadenas de texto continuas que tienen exactamente el mismo formato.

Aclaro: cuando hablo de formato me refiero a todo eso de negrita, cursiva, subrayado, tipo de letra, cuerpo... y también a la presencia o no de enlaces internos, por supuesto, que son nuestro objetivo, en definitiva.

Y como cada una de esas misteriosas entidades demominada run no es otra cosa que un objeto de tipo RichTextValue, solo tendremos que invocar el método getLinkUrl() para obtener el URL que contiene.

Pues como ya lo tenemos todo, vamos con la implementación, que se hará script en forma de función personalizada para hojas de cálculo. De ese modo la podremos utilizar en nuestras fórmulas como una más cuando la necesitemos, sin menús ni zarandajas.

Con todos ustedes, la función personalizada OBTENERENLACES

Sí, con -s, porque los queremos todos (si los hay, por supuesto).

La función exige un parámetro (intervalo) y admite opcionalmente otros dos (todos y separador), que permiten indicar si se desean extraer todos los enlaces o solo el primero, así como el carácter separador que se utilizará, en su caso, para devolver un resultado múltiple cuando se dé esta situación en una celda. De manera predeterminada se asumirá que solo se desea obtener el primer enlace que aparece dentro de una celda y se utilizará como secuencia separadora coma + espacio.

Empecemos con la cabecera, con todos sus marcadores JSDoc necesarios para que la función disponga de la siempre bienvenida ayuda contextual.

☝ En esta sección de la documentación técnica que acompaña a un artículo previo, en el que también presento una función personalizada, comento cosas relativas a esta ayuda contextual, échale un vistazo si te apetece.

/**
 * Devuelve los URL de los enlaces encontrados en el interior de las celdas del intervalo que se pasa como parámetro.
 * 
 * Admite:
 *  (+) Celdas en las que se ha utilizado la función HIPERENLACE().
 *  (+) Celdas en las que se han generado 1 o varios enlaces usando Insertar → Enlace o el botón 🔗.
 * 
 * Limitaciones:
 *  (-) Solo soporta referencias a celdas o intervalos en formato A1 estricto (nada de composición matricial).
 *  (-) Si el contenido de la celda es un número, su enlace no será recuperado (basta con aplicarle formato de texto para que sí lo sea).
 * 
 * @param {A1:A10}      intervalo   Intervalo de datos.
 * @param {VERDADERO}   todos       VERDADERO si se desean extraer todos los URL, FALSO si se omite.
 * @param {";"}         separador   Secuencia de caracteres a utilizar para separar los URL extraídos de una misma celda, ", " si se omite.
 *
 * @return Intervalo de URLs, como cadenas de texto.
 * 
 * @customfunction
 */
function OBTENERENLACES(intervalo, todos = false, separador = ', ') {

Debes saber que las funciones personalizadas para hojas de cálculo desarrolladas con Apps Script, al ser utilizadas dentro de una fórmula, reciben directamente los valores contenidos en las celdas referenciadas por las expresiones de rango que se usan como parámetros.

Por ejemplo, si uso como parámetro el rango A1:A10, lo que recibirá la función (y podrá manipular) serán los valores contenidos en esas celdas, pero en ningún caso sabrá de dónde han salido. Esto, en principio, nos pone unas cuantas piedras en el camino.

¿Por qué razón?

Porque al invocar a la función usando como parámetro un rango de celdas que contienen enlaces internos, lo que le llegará únicamente a la función es... lo has adivinado, el texto al que se anclan los enlaces, pero desprovisto de cualquier URL.

Por ese motivo no nos queda otra que saltarnos el mecanismo convencional de pase de parámetros para averiguar cuál es la expresión de rango que identifica a las celdas de las que queremos obtener los URL y poder así extraer de ellas la información que necesitamos utilizando el servicio Apps Script de hojas de cálculo directamente. Y es que, afortunadamente, una función personalizada tiene barra libre de acceso (ojo, solo en lectura) a la hoja de cálculo donde se encuentra. ¡Piedras fuera!

Y lo anterior puede conseguirse con este hack:

  const hdc = SpreadsheetApp.getActiveSheet();

  // Truco: se obtiene el primer parámetro (literal de la referencia al intervalo) parseando el valor de la celda que contiene esta fórmula
  const referencia = hdc.getActiveCell().getFormula().match(/\(([A-Za-z0-9:'! ]+)/)?.[1];
  if (!referencia) throw 'Especificación de rango no soportada.';

La idea feliz se apoya en dos astucias (bueno, realmente la astucia más astuta es la primera, la verdad).

Astucia 1️⃣:

Para una función personalizada, la hoja y celda activas son aquellas en la que reside la fórmula con la que se la ha invocado. Siempre y para cada una de sus posibles instancias. Por tanto, al usar getActiveCell().getFormula() sobre la hoja activa lo que estamos haciendo es obtener la propia fórmula que hay en la celda como cadena de texto. Muy meta, ¿verdad?

Astucia 2️⃣:

Mediante el método JavaScript String.prototype.match() se extrae (¡otra vez esas dichosas expresiones regulares!) el primer parámetro de la función, que es precisamente la expresión de rango buscada, esto es, el intervalo de celdas sobre el que la función debe operar. Nuevamente, juega si te place con la expresión regular que he usado, que en mi opinión no es perfecta ni la única posible, aunque al menos sí debería funcionar con independencia de la configuración regional de la hoja de cálculo.

El inconveniente (no hay idea feliz sin el suyo) es que no podemos construir intervalos utilizando los consabidos operadores de composición matricial { \ ; } para especificar las celdas sobre las que deseamos que la función trabaje. Solo se admiten expresiones como A2, A2:A, A2:B10, A2:B, etc... que tampoco está tan mal.

Y lo que queda es ya casi historia...

  if (intervalo.map) {
    // [A] Procesar intervalo de celdas
    const rtvs = hdc.getRange(referencia).getRichTextValues();
    // getRichTextValues() siempre devuelve un [[]], pero todos sus elementos son de tipo getRichTextValue, aún cuando la celda no contiene texto
    // https://twitter.com/pfelipm/status/1459949065089789954
    if (todos) {
      return rtvs.map(rtvFila => rtvFila.map(rtvCelda => rtvCelda.getRuns().filter(run => run.getLinkUrl()).map(run => run.getLinkUrl()).join(separador)));
    } else {
      return rtvs.map(rtvFila => rtvFila.map(rtvCelda => rtvCelda.getRuns().find(run => run.getLinkUrl())?.getLinkUrl()));
    }
  } else {
    // [B] Procesar celda única
    const rtv = hdc.getRange(referencia).getRichTextValue();
    // getRichTextValue() devuelve null cuando la celda no contiene texto, por esa razón se usa runs?.
    if (todos) {
      return rtv?.getRuns().filter(run => run.getLinkUrl()).map(run => run.getLinkUrl()).join(separador);
    } else {
      return rtv?.getRuns().find(run => run.getLinkUrl())?.getLinkUrl();
    }
  }

}

Primeramente el código determina si el intervalo es múltiple o se trata exclusivamente de una sola celda testeando intervalo.map. Esto es para la único que se utiliza de manera explícita el parámetro que recibe la función, dado que su contenido en ningún caso nos va a permitir extraer los URL de los enlaces, como te explicaba hace un momento.

En el primer supuesto usaremos el método getRichTextValues(), que devolverá una matriz de objetos de tipo RichTextValue. En el segundo nos apañaremos con getRichTextValue(), que, como cabe suponer, solo retornará uno de estos objetos.

A continuación se identifican las secuencias de texto enriquecido (runs) que contienen un enlace, estos se extraen y se devuelven como resultado. La cosa cambia en función de si le hemos pedido a la función que extraiga todos los URL (todos es true) o no.

• Si solo queremos el primero, se usa el método JS Array.prototype.find() para localizar, si es que existe, el primer run (subcadena con formato homogéneo) y extraer su URL.
• Si en cambio los queremos todos, se recurre al método Array.prototype.filter() para enumerarlos, recorrerlos y extraer los URL que pudieran atesorar, concatenándolos (usando la secuencia de caracteres separadora designada) por medio del método JS Array.prototype.join().

🧐 Un detalle que me parece importante: por alguna razón, getRichTextValue() devuelve null cuando la celda interrogada no contiene un elemento de texto (hasta aquí bien) pero, extrañamente, el método getRichTextValues(), siempre devuelve objetos de tipo RichTextValue, aún cuando la celda contenga un valor numérico. Ya sé que un array de objetos con elementos nulos es feo de narices, pero esta falta de homogeneidad hace que el TOC que habita en mí ruja. En fin, avisado quedas.

Para mantener a raya esto de los posibles valores nulos y evitar errores indeseable en tiempo de ejecución se usa en varias ocasiones el operador de encadenamiento opcional de JavaScript (?.). Probablemente no constituya una buena práctica abusar de él, pero personalmente lo encuentro irresistible... y me pasa exactamente lo mismo (lo de abusar, digo) con el de fusión de nulos (o como demonios se traduzca en castellano).

Finalmente, esta es la función OBTENERENLACES en acción:

Aquí tienes la hoja de cálculo que he utilizado para realizar todas las capturas que te he mostrado a lo largo de este artículo.

👉 fx personalizada extraer URL 👈

Si en ella accedes al editor Apps Script (recuerda que nos lo acaban de cambiar de sitio), encontrarás diferentes versiones de la función que te he presentado.

En el archivo Fx personalizada.gs reside la versión final de la función OBTENERENLACES.

El archivo Anteriores.gs contiene tres versiones preliminares, más sencillas, que no admiten un conjunto de celdas como parámetro de entrada, además de presentar otras deficiencias. Tal vez te apetezca echarles un vistazo para ver cómo se ha llegado a la versión final.

También te dejo una copia de todo el código en este repositorio de GitHub. Ya sabes cómo va esto (copiar código, pegar en tus hojas de cálculo, etc.). Evidentemente, esta función debería llegar en algún momento, junto con un buen puñado de cosas que tengo guardadas, a mi complemento para hojas de cálculo HdC+... si encuentro tiempo en alguna vida para lanzar una nueva versión.

Tengo que decir que la motivación para escribir este artículo ha surgido de la sugerente pregunta formulada recientemente por uno de los participantes en Apps Script Ñ, la comunidad que abrí hace unas pocas semanas en Discord para todos los GAS-frikis de habla hispana y a la que estás más que invitado a participar, por supuesto.

¡Pasate a vernos!