fbpx

El estándar JavaScript Object Notation (JSON) es aceptado como uno de los principales formatos para integrar datos entre servicios web. Se usa para muchas cosas, pero principalmente para enviar y recibir información codificada en una Application Programming Interface (API), que funciona precisamente como interfaz entre los datos que requerimos y los datos que un sistema puede tener guardados en una base de datos y está diseñado para brindarlos fuera del mismo. Importar JSON de una API hoy en día se considera algo fundamental para trabajar con servicios web.

IMPORTXML no siempre es suficiente

Ya hemos visto cómo integrar datos de páginas web estáticas a nuestras hojas de cálculo de Google con la fórmula IMPORTXML(), que procesa una página web HTML como si fuera un documento XML, pues al final del día son estándares compatibles. Entonces hoy vamos a dar un paso más hacia obtener datos de servicios externos, abriendo nuestros horizontes al estándar JSON. Esto es más sofisticado que hacer web scraping básico, porque dependemos de que exista un servicio ya preparado con las terminaciones de URL o endpoints listos para consumir, es decir, dependemos de que se haya diseñado esta interfaz previamente. También significa que a veces están resguardadas por protocolos de seguridad y requieren de autenticación adicional. Pero lo más importante es que al ser JSON un formato estándar, las posibilidades de manipular esos datos se vuelven muy interesantes.

Pues bien, hoy vamos a ver cómo importar datos en formato JSON a una hoja de cálculo de Google (Google Sheets). No hay una fórmula nativa que podamos usar, es decir, Google no provee una forma oficial para hacer esto, pero copiando un poco de código de Apps Script, nos será posible crear una función personalizada que nos lo resuelva. Esto funciona porque las hojas de cálculo de Google esencialmente nacen en la nube, y el código de Apps Script puede ejecutar JavaScript de manera nativa, entonces ya tenemos la parte de la infraestructura resuelta.

Obtener la fórmula IMPORTJSONAPI para importar JSON

Lo primero que haremos será abrir una hoja de cálculo nueva, y abrimos el menú HerramientasEditor de secuencias de comandos:

Editor de secuencias de comandos (Script editor)

Una vez en el editor, vamos al menú Archivo – Nuevo – Archivo de secuencia de comandos

Creamos un nuevo archivo

Y nombramos nuestro archivo como IMPORTJSONAPI

Nombrar el archivo

El código por defecto que vemos aquí, lo vamos a borrar, y lo vamos a reemplazar copiando el código del siguiente archivo y pegándolo en el editor:

Copiar código de IMPORTJSONAPI
Pegar código de IMPORTJSONAPI

Damos click en el botón de Guardar y nombramos nuestro proyecto como queramos.

Ahora sí, ya podemos empezar a usar la fórmula IMPORTJSONAPI para importar datos en formato JSON.

Usar una API pública en Google Sheets

Identifiquemos primero una API pública fácil de accesar para probar nuestra nueva funcionalidad. Mi recomendación es que inicialmente puebes con una API que no requiera de autenticación o de generar una llave. Te doy algunos ejemplos de APIs que puedes simplemente usar para practicar para importar JSON:

Puedes ver más ejemplos de APIs públicas en el repositorio de Github de APIs públicas, pero para este ejemplo voy a usar la API de Pokémon. Supongamos que queremos obtener la información de algunos Pokémon y tenerlas en nuestra hoja de cálculo. Estudiemos un poco la documentación de esta API para saber cómo tenemos que pedir los datos.

Aquí podemos ver que el endpoint para obtener datos sobre un pokémon lleva esta estructura:

https://pokeapi.co/api/v2/pokemon/{id or name}/

Estudiando la API de Pokémon

Entonces, si en el id usamos el número 25 y lo utillizamos en el formato anterior de URL, nos queda esta dirección:

https://pokeapi.co/api/v2/pokemon/25/

Y vemos que tenemos disponibles una gran cantidad de datos sobre Pikachu, como su nombre, algunas de sus habilidades principales, algunas fotos, los juegos en donde puedes capturarlo, etcétera:

Información puntual sobre Pikachu en JSON

Vamos a traer algunos de estos datos a nuestra hoja de cálculo. Utilizamos la fórmula IMPORTJSONAPI de la siguiente forma para importar JSON:

=IMPORTJSONAPI( url , consulta_en_formato_JSONPath, columnas)

  • La URL es el endpoint que nos dará la información
  • La consulta se deberá especificar en formato JSONPath
  • Las columnas son necesarias para que la fórmula pueda transformar el árbol de objetos de JSON a un formato tabular como el que usamos en las hojas de cálculo

Entonces nuestra fórmula para Pikachu quedará así:

=IMPORTJSONAPI("https://pokeapi.co/api/v2/pokemon/25/","$","name, height, weight")

  • La URL es https://pokeapi.co/api/v2/pokemon/25/
  • La consulta es «$» porque se refiere a la raíz, y la información que queremos está a ese nivel.
  • Las columnas deseadas serán: name, height y weight, porque así se llaman los nodos que los contienen y que ahora queremos en forma tabular
Consultando la API con IMPORTJSONAPI

Combinar IMPORTJSONAPI con funcionalidades de Google Sheets

Y bueno, ya tenemos un Pokémon, pero… ¿y si queremos atraparlos a todos? ¿O por lo menos a los primeros 20? Aquí es donde al mismo tiempo aprovechamos las capacidades de Google Sheets como hoja de cálculo, y la API en formato JSON. Solo tendríamos que separar el identificador y concatenar la fórmula de esta manera para arrastrarla hacia abajo:

=IMPORTJSONAPI("https://pokeapi.co/api/v2/pokemon/"&A2&"/","$","name, height, weight")

Utilizando la fórmula IMPORTJSONAPI muchas veces en la misma hoja

Jugando un poco más con estos datos, hasta podemos incluir la foto utilizando la fórmula IMAGE en la columna F:

=IMPORTJSONAPI("https://pokeapi.co/api/v2/pokemon/"&A2&"/","$","name, height, weight, sprites.front_default")

=IMAGE(E2,4,40,40)

Combinar IMPORTJSONAPI con IMAGE
Uso de IMAGE con una URL

¡Y listo! Ahora tenemos un Pokédex listo para usar en forma de Hoja de cálculo de Google.

Usar IMPORTJSONAPI con APIs que requieren autenticarse (API Key)

No todas las APIs permiten acceso directo a su información, algunas requieren que previamente hayas generado una llave o API key. Cada API y cada servicio tiene su manera de proveer este acceso, por ejemplo, la página de Marvel.com tiene una API pública para consultar información sobre comics y superhéroes, pero antes requiere que hagas una cuenta para asociarte una llave personal.

Ejemplo de API que requiere una llave (API Key): API de Marvel.com

Una vez que la tienes, debes asegurarte de enviar los encabezados (headers) correctos para autorizarte con tu llave de API. Para cada caso será diferente, porque depende de cómo esté especificado en la documentación de la API. Eso lo debes especificar con el parámetro headers o payload, junto con el método que quieras usar (GET o POST):

=IMPORTJSONAPI( endpoint_de_tu_url, consulta_JSONPath, columnas, "headers={ 'Authorization' : 'Basic blablabla' }")

=IMPORTJSONAPI( endpoint_de_tu_url, consulta_JSONPath, columnas, "method=post", "payload={ 'user' : 'miuser', 'pass' : 'etcetcetc' }")

Investigar APIs ocultas para hacer scraping

Vamos a hacer una última cosa interesante con este nuevo conocimiento. Como ya aprendimos en la guía básica sobre web scraping con Google Sheets, si revisando cualquier página y encontramos que carga los datos de forma dinámica, esto nos detiene de usar la fórmula IMPORTXML directamente. Pero hay muchas páginas que son dinámicas y usan APIs públicas para obtener su información, solamente no lo dicen abiertamente o su documentación no está disponible:

Un buen ejemplo son las páginas de algunos bancos y la información de sus cambios de divisas. Si vamos por ejemplo a

Divisas Citibanamex

Vemos que IMPORTXML no nos trae los datos directamente por ser una página dinámica. Pero podemos dar click derecho en cualquier parte de la página o presionar CTRL+ SHIFT + I, abrir la consola de desarrollador y abrir la pestaña Network (o Red) para investigar un poco más sobre esos datos:

Abrimos la consola de desarrollador en Google Chrome

Una vez aquí, activamos el filtro que se llama XHR y recargamos la página, para que la herramienta pueda hacer el registro de las peticiones realizadas de forma dinámica después de haber cargado el sitio estático:

Inspeciconamos las peticiones XHR

Ahora, podemos inspeccionar cada una de estas peticiones y nos encontraremos con que una de ellas tiene los datos que necesitamos si vemos la pestaña interna de Response:

Inspeccionamos la pestaña de encabezados de la petición
Inspeccionamos la respuesta del servicio web sobre divisas

¡Parece que ahí están nuestras divisas! Solo es cuestión de usar IMPORTJSONAPI como acabamos de aprender para traer esa información a nuestra hoja de cálculo copiando la dirección de esa petición:

=IMPORTJSONAPI("https://finanzasenlinea.infosel.com/banamex/WSFeedJSON/service.asmx/DivisasLast?callback=","$.*","cveInstrumento,ValorActualCompra,ValorActualVenta,Cierre")

Combinamos la fórmula IMPORTJSONAPI para importar estos datos

No siempre es posible usar servicios web ajenos para importar JSON

Ahora, aquí hay que notar una última cosa… esta información parece algo desactualizada. Este es un endpoint antiguo que está en desuso. Si seguimos inspeccionando las peticiones, veremos que la información real está en la petición marcada como summary:

Hay servicios web o APIs que no podremos usar porque están restringidas

Y aquí es donde nos detiene la seguridad implementada en el sitio, porque ese endpoint está protegido de forma que solo acepta peticiones de clientes preaprobados.

De todos modos, este fue un excelente ejemplo de cómo investigar una API que tal vez no esté documentada en ningún lado, pero que sí se pueda utilizar. Hay muchos sitios así, te toca a ti inspeccionarlos e investigar lo que es posible y lo que no 😉

Recuerda estar pendiente de actualizaciones al código de IMPORTJSONAPI en su repositorio de Github, y revisar la documentación de la fórmula para comprender mejor el uso de JSONPath para refinar y probar tus consultas.

Guía en video

Si tienes dudas puedes ver esta misma guía explicada en video:

¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.

¿Ya tienes G Suite en tu empresay hay cosas que crees que se pueden resolver pero no sabes cómo? ¡Contáctanos para que podamos ayudarte!