Importar JSON de una API a una hoja de cálculo de Google Sheets
Vložit
- čas přidán 13. 10. 2020
- Te enseño a procesar datos JSON que vengan desde una API hacia tus hojas de cálculo de Google Sheets con IMPORTJSONAPI. Usamos la API pública de Pokémon (la PokéAPI) para divertirnos un poco y también usaremos este truco para inspeccionar sitios dinámicos que antes no podíamos traer a nuestra hoja solo usando IMPORTXML.
Puedes leer el post original con acceso al ejemplo para practicar aquí: tesel.mx/importar-json-de-una...
Código del archivo IMPORTJSONAPI.gs: raw.githubusercontent.com/qee...
Github de APIs públicas: github.com/public-apis/public...
PokéAPI: pokeapi.co/docs/v2
Validar JSONPath online: jsonpath.com/
Github de IMPORTJSONAPI: github.com/qeet/IMPORTJSONAPI
Suscríbete al canal para estar al tanto de más guías e información de
tecnología.
¿Buscas un proveedor confiable de tecnología, desarrollo y análisis de datos? ¡Contáctanos en tesel.tech ! También somos Google Partners certificados y podemos ayudarte a tener Google Workspace en tu negocio.
Síguenos en nuestras redes:
Twitter: / teseltech
Facebook: / teseltech
Voice over: Alekz
Twitter de Alekz: / aalkz
Producción: Alekz para Tesel
Música de fondo: Jason Shaw, utilizada mediante la licencia Creative Commons
Imagen usada al principio: Pankaj Patel - Věda a technologie
Actualización: Google cambió el menú que muestro en 01:34 para crear código a Extensiones - Apps Script , ya no está en Herramientas - Editor de secuencias de comandos. Por si lo buscaban y no les aparecía. 😀
-Alekz
Gracias!, que bueno que dejaste tu comentario, estaba perdido jajaj
entonces ya no se puede hacer nada? :(
Hola
La sintaxis de la fórmula también es diferente... ahora usa ";" en vez de ","
Super excelente. Muchas gracias me sirvió un montón. Ojalá dieras clases personalizadas. Yo con gusto me inscribo.
¡Muchas gracias! Justo lo que estaba buscando.
Con mucho gusto Carlos, qué bueno que te haya servido.
-Alekz
Excelente y muy bien explicado. Felicitaciones
Gracias por tu comentario Cristian, con mucho gusto. 😀
-Alekz
Genial, muchas gracias, me sirvió mucho y veo un potencial para un proyecto en appSheet
Con mucho gusto, qué bueno que te haya servido, gracias por tu comentario Sergio. Y es verdad, existe el potencial para resolver muchos problemas con esta tecnología. 😀
-Alekz
Alex, ¡eres un genio! ¡Muchas gracias!
Con mucho gusto David, qué bueno que te haya servido, gracias por comentar 😀
-Alekz
hola buena explicación y fácil de entender
Espectacular gracias!!
sos un groso!!!! me ayudo mucho tu tutorial muy util... para mi trabajo mil gracias
Hola! Estoy intentando tomar datos de una API que hice yo, el problema es que me da un error 500 y no encuentro porqué . En esa API que usa GET tengo un token Bearer en headers pero no encuentro como pasar los datos que normalmente irían en el Body en formato json que son start_date y end_date para la búsqueda. Como no existe un parámetro body los intenté poner en headers y en la url.
hola el inconveniente que veo es que no toma el valor y lo actualiza como podría ver ese valor en la tabla pero que se mueva o cada cuanto lo hace o si toca recargar el excel para que surta efecto mil gracias
Hola! Yo estoy intentando sacar datos de google classsroom ¿Con esto puedo hacerlo? Sé inglés así que estoy estudiando como escribir el JSONPath con lo que dice el github pero sigo sin llegar a algo. Por favor ayuda
Hola! .. Excelente video, sabes, estuve jugando con la función, principalmente porque me interesa lograr usar google sheet con woocommerce conectados por REST API, las consultas resultan perfectas en algunos casos usando las columnas, pero según estoy viendo la documentación de la rest api de woocommerce las categorías están en un array y cada categoría tiene su propio array algo así:
"categories": [
{
"id": 489,
"name": "Accesorios",
"slug": "accesorios-instrumentos"
},
{
"id": 471,
"name": "Instrumentos",
"slug": "instrumentos"
},
{
"id": 492,
"name": "Otros",
"slug": "otros-accesorios-instrumentos"
}
],
esto está al mismo novel que los otros elementos JSON, logré aislar las categorías $.categories[3].name esto me entrega solo el nombre de las tre categorías a las que este preoucto está, pero no se despliega la información en la hoja de calculo. mi pregunta va a lo siguiente al agregar categories en el argumento de columnas me entrega un array ({ }), pero al filtrar en un jsonpath solo para las categorías no me entrega la info y se queda en blanco ...
Genial sería si pudies conectar esta hoja que da fetch a los productos y luego un update desde aquí mismo, se que se puede, por eso estoy investigando. saludos. y muchas gracias por leerme y la respuesta
Hola Gonzalo, te recomiendo usar una herramienta como jsonpath.com/ para ayudarte a probar las expresiones de JSONPath antes de usarlas en la función, es mucho más sencillo llegar a tu resutlado. Otra opción sería que dividieras tu consulta en más de una petición. A veces por la naturaleza de convertir JSON a una forma tabular, no es posible hacerlo todo en una sola expresión de JSONPath. Espero que te ayude 😀
-Alekz
Hola, primero buenisimo el video, te queria preguntar, el parametro de columnas (3er param), ¿puedo utilizar tambien la notacion de jsonpath? por ejemplo, quiero traer las columnas de un array dentro del path raiz ($...), que cumplan con una condicion dentro de una expresion, los que tengan determinado "id" por ejemplo
Hola Carlos. Con mucho gusto, gracias por tu comentario. Es una buena pregunta la que haces. Lo tendrías que hacer desde el segundo argumento de JSONPath, sería algo como $.id=23 dependiendo de qué tan adentro esté el nodo. Lo más fácil es que hagas pruebas en este sitio: jsonpath.com/ para validar tu JSONPath , porque probar adentro de la hoja de cálculo puede ser algo tedioso. Espero haberte ayudado, ¡un saludo!
-Alekz
la importacion se ejecuta cada sierto tiempo?, por ej: digamos que estoy importando todos los pokemones hoy, y mañana se agrega un nuevo pokemon. (me lo va a agregar al sheet) o si se elimina un pokemon ¿va a quitarlo de la importacion?
Saludos y muchas gracias por el codigo, esta potentisimo.
Muy buen video mi hermano he intentado sacar los precios del Exchange Latoken pero no he podido tendra que ver con el Jsonpath
Agradecido, una pregunta, cómo podemos hacer que se actualice la consulta de esos datos que extraemos de JSON externos.¿? gracias.
Hola Alan, qué bueno que te haya servido. Sobre lo que preguntas, lo primero que puedes probar es ir a 'Archivo - Configuración de la hoja de cálculo' y en la pestaña de 'Cálculo' elegir la opción de 'Nuevo cálculo - Si hay cambios y cada minuto'. Es algo truculento porque Google Sheets tiene sus propias formas de decidir cuándo recalcula, y no siempre es totalmente transparente al respecto. Espero haberte ayudado, ¡un saludo! :)
-Alekz
Saludos, muy completo el video, solo queria haceere una pequeña pregunta en caso de tener la key en que parte la debo de agregar de la funsion para que me de los permisos?
Hola! Puedes agregarla así:
=IMPORTJSONAPI("test.com/api", "$..Title", "@", "headers={ 'Authorization' : 'Basic QWxhZGRpbjpPcGVuU2VzYW1l' }")
En general si tienes la llave de API el header utiliza el paramétro Basic.
Aquí puedes leer más sobre la documentación de IMPORTJSONAPI github.com/qeet/IMPORTJSONAPI
Espero te ayude!
- Anya
Hola amigo, excelente video me ayudó mucho a entender más sobre las apis.
Puedes hacer un vídeo explicando cómo utilizar la api de anilist o mal porfavor?
Hola Darkel, con mucho gusto, gracias por tu comentario, qué bueno que te haya ayudado. La API de anilist parece que trabaja con GraphQL, lo tendré en cuenta para el futuro porque es muy buena tecnología. ¡Un saludo! 😀
-Alekz
Gracias por responder Tesel, si es posible obtener los datos de MAL en español o de cualquier otra pág sería genial 😄
Amigo como descargo el saldo de una wallet, usando el explorador de la cadena
Excelente solución Alex! consulta, como seria la formula a utilizar si la obtención de datos requiere un token de autenticación?
Slds.
Hola Ricardo, gracias por tu comentario. Ssi 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.
La documentación completa con más ejemplos sobre incluir headers y payload la encuentras acá: github.com/qeet/IMPORTJSONAPI
Espero que te sirva, ¡un saludo!
-Alekz
Hola amigo, muchísimas gracias por el video... es posible compartir hojas de cálculo con imágenes desde Google sheets a traves de JSON?
Hola Leandro, ¡qué bueno que te sirvió el video! No sé si entendí bien tu pregunta, ¿te refieres a que la hoja tenga imágenes en las celdas o a traer imágenes hacia tu hoja de cálculo con JSON? -Alekz
Gracias , veo que es por fila que trae la información , y cuando se requiere traer toda la información , como seria ? se podría modificar el método para no cosumirlo fila por fila?
Hola. Sí es posible, depende del diseño de la API que estés consumiendo para que puedas darle forma a tu consulta de JSONPath correctamente, si más de un registro cumple con tu consulta, te los dará en varias filas sin problema. En el ejemplo que usé solamente regresa un registro por consulta, por eso arrastro la fórmula para tener la información por fila. Puedes ver más documentación y ejemplos en esta liga: github.com/qeet/IMPORTJSONAPI . Espero que te ayude, ¡un saludo!
-Alekz
Hola, buenas, tengo un problema que cuando escribo en la celda IMPORTJSONAPI no lo reconoce. Por favor ayuda
Cuando la api pide logs de usuario y contraseña, tienes algún método?
Hola !! copio y pego el archivo pero no me deja guardarlo. Sale error en la fila 29. Si me pudieras ayudar te agradezco
Como se puede hacer con una pagina que requiere autenticación?
Excelente información. Muchas gracias por compartir. Una consulta. Si quiero traer la información alojada en la carpeta de un servidor, solo debo jugar con las rutas de las carpetas?
O no es tan sencillo.... 🤔
El ingreso no es por navegador web sino por explorador....
Hola Alexander. Te refieres a que accesas la carpeta mediante el explorador de windows? Desafortunadamente estas carpetas suelen tener sus propias formas de autenticación y comunicación con tu computadora. Por lo cual no suele ser viable importar estos datos.
Una excepción podría ser si tu servidor es un FTP público, en cuyo caso podría funcionar, pero honestamente no hemos hecho pruebas al respecto
- Anya
@@Tesel Vale. Entiendo. Por FTP se conectan al servidor para facturar, no sé si así se puede conectar uno para sacar los datos planos. Voy a hacer pruebas. Muchas gracias por tu apoyo...
hola, muy buen video, tengo una pregunta, la función cuando se ejecuta tiene un máximo de ejecución de 30 segundos, es posible aumentar el tiempo de ejecución de la función, a por google script es imposible, sale este error 'Exceeded maximum execution time (línea 0).'
Hola, Alejandro! El tiempo de ejecución máximo con Apps Script es de 6 minutos, lo hemos probado extensivamente. Entonces no estoy muy segura pero creo que puede tener que ver con el servicio que estas tratando de consumir con la librería de JSON; sobre todo porque si te das cuenta tu error combina inglés y español: "execution time *línea*" en vez de "line"
Desafortunadamente si este timeout ocurre en el servicio externo no hay mucho que podamos hacer. Si tienes otra duda no dudes en escribirnos!
- Anya
Como se podría hacer en sitios que requieren autenticación? Como creamos la "llave" o que nos permita extraer de una web con una cuenta con usuario y contraseña?
Hola, Franco! Depende, sitios que acepten autenticación en el "header" de la petición si es posible. Sitios que te pidan hacer login a mano, no se puede con este método.
Aquí te dejo la documetnación de IMPORTJSONAPI github.com/qeet/IMPORTJSONAPI pero básicamente si soporta auth mediante headers puedes agregar como opciones algo así: "headers={ 'Authorization' : 'Basic QWxhZGRpbjpPcGVuU2VzYW1l' }"
Este es su ejemplo
=IMPORTJSONAPI("test.com/api", "$..Title", "@", "headers={ 'Authorization' : 'Basic QWxhZGRpbjpPcGVuU2VzYW1l' }")
Espero te ayude!
- Anya
Excelente! muy buena info! Gracias!
Una consulta... si por ejemplo queremos aplicar los datos de cambios del USD en GoogleSheet y usamos la formula IMPORTJSONAPI recien creada... ¿Cada cuanto se actualizan los datos en la planilla?, hay que presionar F5 para recargar la pagina y asi se refrescaran los datos recuperados desde la API? o se hace automaticamente cada determinado tiempo?
GRACIAS!!! 😀😀
Permite la funcion IMPORTJSONAPI descargar los precios de los pares ?
Excelente video! Ahora una consulta... tendré que agregar la función IMPORTJSONAPI toda vez que va ocuparla en una hoja de calculo nueva? Es posible que la función quede disponible siempre? Saludos!
Si. Tendrás que agregarla todas las veces. Si quieres que este disponible siempre tendrías que convertirla en un "Add-on" para ti; pero debido al tiempo que esto puede tomar te recomiendo que la agregues cuando sea necesario
@@Tesel aún sirve esa para 2023?
Hola! Excelente explicación. Solo tengo una consulta. En caso de que la API no sea publica. ¿Cual seria la manera de consumirla? Saludos.
Pudiste resolverlo? tengo la misma duda
hola pido de su ayuda trato de replicar la formula pero los datos de mi url son tipo fetch se puede aplicar
Hola Diego! me puedes platicar un poco más para aclarar lo que necesitas?
- Anya
Buen videoooo!
Y el proceso inverso? pasar de google sheets a json? asi uso esa información desde mi servidor php
Hola, Mati! Justo publicamos un video al respecto. Puedes verlo aquí:
czcams.com/video/61_W4cKwMi8/video.html
- Anya
Agradecido, con paciencia pausando y avanzando pude integrar la api. Ahora los datos se actualizan automáticamente o como lo hago manualmente?
¡Hola! Debería actualizarlos automáticamente, pero si no lo hace puedes forzarlo agregando cualquier número como argumento al final de la fórmula como lo explica la documentación: github.com/qeet/IMPORTJSONAPI?tab=readme-ov-file#refreshing-data . Espero que te ayude 😃
-Alekz
@@Tesel Gracias!!
Hola! ¿Es posible autenticar con un Bearer Token? - En caso positivo, nos puedes dar un ejemplo, por favor?
Buen video,
A mi no me aparece la opcion "Editor de secuencia de comandos" en Google Sheets, como hacer que aparezca?
Gracias
Hola Julio! Google cambió el menú que muestro en 01:34 para crear código a Extensiones - Apps Script , ya no está en Herramientas - Editor de secuencias de comandos. Por si lo buscaban y no les aparecía. 😀
- Anya
Ola, tudo bem? parabéns pelo vídeo! eu tentei fazer e na hora de salvar aparece ERRO DE SINTAXE. Como faço?
Olá Christiane, obrigado pelo seu comentário. Tente usar ponto e vírgula em vez de vírgulas dentro das fórmulas (talvez tenhamos diferentes configurações regionais). Também pode ajudar a validar se a sintaxe de JSONPATH está correta usando este site: jsonpath.com/
-Alekz
Hola, hice los pasos del video pero no reconoce la fórmula, me sale #NAME Función desconocida ¿a qué podría deberse?
Hola graciassss, no entiendo de donde obtenes el ID nro 25, en el minuto 4:15 al 4:29
Hola. El 25 solo es un número que usé sabiendo desde antes que correspondía a Pikachu, pero podrías usar cualquier número mientras esté asociado a un Pokémon. Espero que te ayude 😀
-Alekz
no me sale la opción en Herramientas - Editor de secuencias de comandos, entiendo que Google cambió el menú, pero entonces en que parté se encuentra?
¡Hola Leidy! Ahora ese menú se encuentra en Extensiones - Apps Script. Espero que te ayude 😀
-Alekz
@@Tesel ya entré, pero qué función debo poner? Me aparece así:
function myFunction() {
}
hola, me gustó mucho el video. Consulta, cúal es el visor de api que usas en tu video? (minuto 4:32)
¡Hola! Con mucho gusto, muchas gracias por el comentario, Patricio. No es ningún visor especial, es la manera en que el navegador Mozilla Firefox muestra JSON como ayuda visual. Casi siempre uso Google Chrome para demostrar en los videos, pero para este en particular, la vista de Firefox fue mucho más útil. Espero que te haya sido de ayuda y que puedas suscribirte al canal. ¡Un saludo!
-Alekz
@@Tesel Gracias. Yo uso chrome, pero las extensiones que existen no son tan buenas como la de firefox. Ok, me suscribo para apoyarte. Buen trabajo
Excelente video! Como se puede hacer una petición JSON con argumentos. Gracias!
Hola Alberto, gracias por tu comentario. Puedes incluir más argumentos en la petición simplemente agregándolos al final. La documentación completa con más ejemplos sobre incluir headers y payload la encuentras acá: github.com/qeet/IMPORTJSONAPI#:~:text=for%20post%20requests.-,Parameter%20Examples,-A%20basic%20post . Espero que te ayude 😀
-Alekz
Amigo se podría importar un pantallazo de una página como imagen? Y que se programe y se repita ese pantallazo en un tiempo determinado?
hola y como se obtienen datos cuando necesito ingresar con una api-key? la api-key ya la tengo
Hola Jaime. Para eso tendrías que usar el apartado de headers. Depende de cómo los requiera la API particular que estés consumiendo, pero más o menos sería así:
=IMPORTJSONAPI( endpoint_de_tu_url, consulta_JSONPath, columnas, "headers={ 'Authorization' : '' }")
La documentación completa con más ejemplos sobre incluir headers y payload la encuentras acá: github.com/qeet/IMPORTJSONAPI
Espero que te sirva 😀
-Alekz
Amigo lograste hacerlo, solo que estoy algo perdido con la información, te es posible orientarme un poco.
saludos amigo, me podrías decir por favor cuanto cuesta una hora de asesoría tuya vía google meet?
Hola! Puedes escribir a contacto@tesel.tech para agendar una asesoría!
- Anya
Buenas estoy intentando obtener datos con la misma url que utilizas pero me da error
Hola César, son de ejemplo, puedes usar cualquier dirección pública de API disponible. La API de Pokémon debería funcionar correctamente como en el video: pokeapi.co/ Espero que te ayude 😀
-Alekz
Hola máquina sabes como hacerlo con plataforma Etoro? Gracias
Hola Andrés, el procedimiento debe ser muy parecido si usas la API de eToro: api-portal.etoro.com/docs/services Solo debes asegurarte de seguir su documentación y procedimientos de seguridad por si requieren el uso de un token de autenticación.
-Alekz
Me ayudo bastantee :3, solo tengo una duda, como puedo acceder a una API con codigo de autorizacion?
Hola! Puedes consultar la documentación de IMPORTJSONAPI en la URL que te dejo. Pero el ejemplo de "Autorización básica" es decir, autorización con un API Key es así:
=IMPORTJSONAPI("test.com/api", "$..Title", "@", "headers={ 'Authorization' : 'Basic QWxhZGRpbjpPcGVuU2VzYW1l' }")
Nota que todo lo que dice headers esta dentro de comillas dobles. En este ejemplo la cadena QWxhZGRpbjpPcGVuU2VzYW1l sería el API Key. Por lo cual es necesario escribir 'Basic AquiTuApiKey' La palabra Basic y el espacio son muy importantes.
Espero te ayude!
- Anya
github.com/qeet/IMPORTJSONAPI
Si tengo un Request body, alguien sabe como puedo ponerlo en los parámetros?
Hola Raúl, en la documentación oficial puedes encontrar ejemplos de esto, te pongo algunos esperando que te ayuden 😀
=IMPORTJSONAPI("test.com/api", "$..Title", "@", "method=post", "payload={ 'user' : 'andy', 'pass' : 'pandy' }")
=IMPORTJSONAPI("test.com/api", "$..Title", "@", "headers={ 'Authorization' : 'Basic QWxhZGRpbjpPcGVuU2VzYW1l' }")
-Alekz
@@Tesel Hola! Perdona que pregunte por aquí, pero al utilizar estas formas, me indica un error que no permite un cuarto parámetro. Hay que modificar algo en el código para que lo acepte? Gracias !
sale esto cuando guardo el proyecto: No se ha podido crear el proyecto de Cloud Platform. Código de error RESOURCE_EXHAUSTED.
Hola, probablemente es porque te excediste en el uso de alguna cuota en tu cuenta. Eso puede pasar si usaste la fórmula en demasiadas celdas al mismo tiempo. Aquí hay más información: cloud.google.com/deployment-manager/quotas . Espero que te ayude 😀
-Alekz
no funciona he peado tal cual el scrip y el colocado la URL identica y me da Error d formato
Hola Omar. Asegúrate de haber seguido los pasos de pegar el script correctamente en un archivo nuevo en el editor y dar click en guardar. El error que comentas se puede deber a que no está encontrando la fórmula en el código de Apps Script. Espero que te ayude 😀
-Alekz
Por cierto, también fíjate que estés usando comas o puntos y comas ; correctamente para separar los argumentos, puede ser que tu configiración regional de español sea diferente y debas usar puntos y comas ; .
-Alekz
Hola como estas? Tengo una duda. Estoy dfesarrollando un google sheets donde quiero poder colocar el ID de un axie y que desde google sheets pueda enviarme las partes que componen al axie. E sposible realizar eso? No se bien como se puede hacer ese scraping. Me encantaria si pudieras ayudarme
Intente de todas las maneras posibles con tus videos y no hay caso. No puedo hacer el scraping, me tira error y no me aparece el link
Una pregunta de novato: en el minuto czcams.com/video/DWedQ3S2eow/video.html tienes instalado algún tipo de complemento (en Chrome ???) para ver la consulta a la API formateada ?????????