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ú Herramientas – Editor de secuencias de comandos:
Una vez en el editor, vamos al menú Archivo – Nuevo – Archivo de secuencia de comandos
Y nombramos nuestro archivo como IMPORTJSONAPI
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:
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}/
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:
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
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")
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)
¡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.
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
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:
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:
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:
¡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")
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:
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!
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
Hola, justo tengo un URL que no me permite ver la información porque no tengo el token, pero es un api al cual tengo acceso, necesito extraer la info a google sheets, pero no la extrae porque dice que no tengo el token, hay manera de extraer estos?
Hola Sebastián, si tienes el token, puedes incluirlo como argumento adicional en esta forma:
=IMPORTJSONAPI( endpoint_de_tu_url, consulta_JSONPath, columnas, "headers={ 'Authorization' : '' }")Lo de headers dependerá de cómo requiera que le des el token esa API en particular, pero ahí mismo puedes incluir cualquier argumento adicional que quieras mandar a la API ej. payload, method, etc.
Si tu API permite usar el token mediante GET, es todavía más sencillo, pues solo tendrías que incluirla en la URL, por ejemplo:
=IMPORTJSONAPI("https://api.test.com/store?api_token=ds45%3F6hjkd%3Ddjs, ...)Puedes revisar más información sobre todo esto en la documentación de la función en la sección de Parameters en esta liga: https://github.com/qeet/IMPORTJSONAPI
Espero haberte ayudado, ¡saludos!
Excelente soy fiel al potencial de las hojas de calculo y sumado al potencial de javascript el limite es infinito. Buen Material
ufff estaba buscando esta liga https://finanzasenlinea.infosel.com/banamex/WSFeedJSON/service.asmx/DivisasLast?callback=“,”$.*”,”cveInstrumento,ValorActualCompra,ValorActualVenta,Cierre”) muchas gracias
En un archivo oculto esta esto crees que si pongo los case salga lo mismo ??? bueno lo probare pero si sabes todos estos dato donde pueda sacarlos te lo agradeceria
switch(messageData.get[item]){
case “dolar”:
dolar();
break;
case “divisas”:
divisasTop();
break;
case “metales”:
metales();
break;
case “mercados”:
mercados();
break;
case “indicadores”:
indicadores();
break;
case “convertidor”:
convertidorDivisas();
break;
case “historico”:
historicoUSD();
break;
case “bonoLast”:
bonoLast();
break;
case “divisasLast”:
divisasLast();
break;
case “metalesLast”:
metalesLast();
break;
case “indiceLast”:
indiceLast();
break;
case “emisorasLast”:
emisorasLast();
break;
case “cetesLast”:
cetesLast();
break;
case “petrolioLast”:
petrolioLast();
break;
case “petrolioMetales”:
petrolioMetales();
break;
case “indicadoresInflacion”:
indicadoresInflacion();
break;
case “tok”:
getToken();
break;
}