Apps Script UrlFetchApp API, Get JSON data, Build Google Sheets Function, Advanced Tutorial
Вставка
- Опубліковано 16 жов 2024
- Learn how to use UrlFetchApp Class to import external data to Goole Sheets or use it otherwise in Google Apps Scripts. This tutorial will also walk though creating UDF (user defined function) in Google Sheets, JSON parse in JavaScript and other related topics.
You can find the final code here
www.chicagocom...
Google Sheets
www.google.com...
Company Page
www.chicagocomp...
The best teacher on youtube! You should get an award. :)
is that work with POST request as well ?
Superb. One thing not covered is that sometimes the JSON has some of its data wrapped in an array with square brackets. This can result in an "undefined" error appearing when trying to access the data. Each array item needs to be opened before the data can be accessed by using ["data"][0] or ["data"][n] where n is being generated by a for loop to run through the entire array contents.
THANK YOU!!
i just want you to know that this comment saved me probably like a few hours. i finally got there. it is weird to have an array like that within a object.
Absolutely brilliant...your best video...was looking for something like this for a long time
exactly what i was searching for, thanks
.एक टिप्पणी कहती है कि आप सबसे अच्छे शिक्षक हैं .... आप अनमोल हैं ..... उन लोगों के लिए जो ... यूट्यूब से भाषा सीखते हैं
The great course
You rock dude!!
I've been learning a lot with your videos
can you make a video of how to use the Smartsheet API in google app script?
I will love that
Thanks for posting this video. You literally saved me 2 hours of frustration. Well done.
Please I need assistance on a google app script.
This function works great!
How can I set this function to reload or refresh the data every x amount of time? could you suggest a tutorial or link where I can find this?
You have to use google sheets events to refresh your formula. Look into Time-driven triggers developers.google.com/apps-script/guides/triggers/installable
You're amazing! A reference for us!
🙂
Thanks a lot for a very important learning 🙏
Excellent Video. Very good and clear explanations about API calls, and JSON object data extraction.
Well-put-together. Worth the subscription!
Thank you so much for posting this
Exactly what I was looking for
Excellent tutorial and teaching style. One issue I had (uncertain if an error with my code): When my xpath is for example data/stream1a/stream1b where stream1b does not exist, the error handler works 100% and returns "Node not available". However if I go for a deeper level in which the parent level does not exist like: data/stream1a/stream1b/stream1c then the error handler does not catch it - it throws an execution error.
Thank you for all the tutorials
I am trying to fetch api from a software api when I am fetching it it's showing Authorization error
I have user nam id and password but how I authorize it in google sheet please help me I am trying to solve this from last one month but not get any solution please help me.
Great tutorial!, say could you make a tutorial series on like user login with authentication and all thanks!
Unlikely, it would be 10 hours long video just to make the authentication system and very few people would be interested in watching that. It's simply not worth the time spent, not even to mention that it would negatively impact the channel because that's what UA-cam does when people don't watch these videos.
Thank you for sharing your knowledge!
Please consider making a video like this (in depth) explaining how to mail merge emails with data from a Google Form/Sheet and SendGrid's API using Google Apps Script. The code should only send emails for new submissions. We need your help please.
Long story short, it should be a rest API request using URLFetchApp. As far as what the exact request structure should be, I would have to dig their documentation for at least a couple of hours to figure it out. Sorry, it's too niche for me to spend time on.
I have learned a great deal from your videos and have some ideas of things I would like to experiment with.
One project would be connecting to Twitter API to be able to parse URLs for the image of a post and then be able to use that insert the image in a Slide. Do you have some knowledge or resources to share about connecting Apps Script to Twitter API?
TY VERY MUCH U MADE ME SAVE HUNDRED OF HOURS! CAN U HELP ME USE FETCH FOR ZIPPED XTML DIRECT TO GOOGLE SHEETS?
could I ask you to do a video on html data and xpath and apps script ;)
.
Amazing video ! Is there a script to have automatically a blank space after every 15 rows please
Mind blown! awesome stuff! thanks
impressive, how to get gold rates, and if fails get it from another link?
hello thank you for your great tutorials i learn a lots from you :)
I'm a beginner in the scripting and i want to know if you can help me please to adapt this script to my needs. In fact i have a list of keywords in a spreadsheet column and i would like to use a script to search them one by one in google search and among the result obtained in the first page of search I would like to know if a "Specific word "(e.g "Time ")exists in all the page not only in the titles (it is as if I made CTRL + F and see the result) then if the result> 0 return "yes" in another column
What if for example I have a range containing a set of package tracking numbers I got from delivery services and I want to make the adjacent range to show me the last status of the package using a rest api. Kind of getting data from api based on data we have on Google Sheets. Any help would be great thanks...
Excelenteeeee es lo que buscaba
Great video. I would now like to have the data imported from the API URL update every minute. When I set a trigger, it does not do anything. How can I proceed with it updating every minute? Thanks in advance.
Amazing. Thanks a lot.
Thanks for the tutorial.
I have a problem with error
Exception: Attribute provided with no value: url
aIMPORTJSON
@
I have done everything like on the video.
For some reason it screams that url is undefined :/
any help?
Is it an example of webhook?
I wanna know what is the name of the chrome extension that you use to make JSON more readable?
Firefox Browser, no extension.
is it posible to add bulk data using webapp api? If posible please help!
I found what I needed 5min in to the Video !- Thank you PS: I love that you speech-to-text.
what package are you using for that?
how did you do please? I'm looking for a way to insert xpath please
It's his natural voice 🥲
Do you know if it is possible to do it the other way around? It is that from postman it reads the API but when I try to use it from appscript it gives me Exception: Bad request:
I am trying to get only two values (no. of reviews, no. of rating stars) from the Google maps search page, but UrlFetchApp does not seem to capture this information. Any guidance would be much appreciated? Thank you
What search page?
First of all thanks for your excellent videos, they are really helpful!!!
I would like to ask you if is possible to import data that I normally get using "curl -d" command from terminal?
Thanks a lot
What does curl -d do? is it download?
@@ExcelGoogleSheets in this specific case when I call from terminal it gives me this:
{
"names": [
"timestamp",
"device_id",
"topic",
"message"
],
"rows": [
[
"2020-01-20 07:57:09",
"device1",
"events",
"{\"name\":\"created\"}"
],
[
"2020-01-20 08:04:36",
"device1",
"events",
"{\"name\":\"created\"}"
]
]
}
My problem is that I don't know how to use the command:
curl -d "query=select * from data limit 2" *****
in Google Sheets.
Thank you.
Great function. Thanks.
very useful thank you. what if the remote server had a username/password for the query, I am struggling with Apache Solr.
I got JSONInit is not defined (line 3, file "code") error when i try running myfunction
that is an example of get request... how about post request?
Awesome video! Thnx !
How can overcome the time limit at a urlFetchApp.fetch method, Because in my app take this time at above 30s in particular API. At this time not handle exception using try/catch.
Hi!, need help on something: how can i measure the request by second as i have a system built on google app script, we are 20 usersusing it filling a form that download a big array of 150 elements. The system blocked for maybe 1 hour and after works again. I would like to see why but as i'm new on this i don't knnow how to check if we went over the 6 min allowed by google
Thanks for the video, what if the the external api I getting data from requires authentication? Would I pass the secret token to the "UrlFetchApp.fetch" method?
Thomas Prevarin it would be its own function in the script.
You please help how to copy a shared google drive file on the internet to my drive using UrlFetchApp or another method. Thanks a lot. Have a nice weekend.
Can I get HTML Code of of a web page into Google Sheet ?
If the json have many columns, how can we spit ?
How can I import data (in CSV file if downloaded) in Google Trend into Sheets?
Thank you for this video! Very cool stuff. I have an API that requires a key. I have that key but when I use the UrlFetchApp.fetch(url), the API doesn't authorise the user. The API key is baked into the URL. Any idea how to get around this? Thanks!
hey, I don't know if this is still useful, but you can pass the key in the second argument of UrlFetchApp(url,options). Create a var of options with the appropriate descriptor for the key. It's in the docs
Has anyone tried doing this from a CRM ? Like in order to keep a spreadsheet that shows live registration data directly from a CRM that you have a log in to?
is that work with POST request as well ?
Yes.
any reason why: return(json["BTC"]["USD"]) and return(json.BTC.USD) is working
but
var curr = ss.getRange(i,1).getValue(); //BTC
return(json[curr]["USD"]); and return(json.curr.USD); is not working. It throws the error: "Cannot read property 'USD' of undefined"
I need to the pass the object as a variable from the sheet.
This should work return(json[curr]["USD"]);
this should not return(json.curr.USD);
@UCFmxKyg324vY9PTvxUwdYZw That just means you have some error above that line and you need to figure out what it is. Your curr value is not BTC
Thank you for all your videos on google apps. They are so freaking helpful it's ridiculous. I am having a problem where a function is updating a cell and the rest of the function relies on the updated data in the cell but when it runs the cell doesn't update fast enough and the function misses the updated data. I read something about utilities.sleep(milliseconds) and I use about 2000ms which should be plenty of time, but when I put it into my function nothing happens. When I type "Utilities." no other utilities popup after the period. Any ideas?
Unfortunately this can be 1000 different things.
@@ExcelGoogleSheets Yes, I understand. I found a solution pretty easily after posting here lol.
@@calebdoyel164 you may share with us your solution ? that extremely help us also.
@@sultanularefin5674 In the end I used "Utilities.sleep(10000)" the number is in milliseconds so the function pauses for 10 seconds which is enough time to update the sheet.
omg this makes my life so much easier!
Hi,
the function .split() isnt available for me, is it possible I have to download extra add-on, was it depracated? Thank you
No, it's built in javascript function. If it doesn't work that usually means the variable is not a string, it will only show up for string type.
How to change to data of json on Cell?
Hello, I'm from Russia.
I liked your code.
but when I recreated it with me, this entry (= importjson ("api.fixer.io/latest?base=USD", "rates") in the cell produces a syntax error on all variants.
To tozhe when I start to write (= import) that to me does not give out a variant for sampling of the punctured function.
and still logged
Perhaps I have somehow not properly tuned Google Sheets.
tell me possible options?
make sure you save the script editor file, reload the spreadsheet & don't use spaces between =importjson(
hola saludos y gracias... podrias explicar como hacer un snapshop u bufer para evitar el error de tiempo de ejecucion es decir evitar el limite de tiempo de ejecucuion ....
var json = JSON.parse(content);
isnt working how do I fix it?
Check the lines before that.
how do I export data from google spreadsheet to my web app ?
Does Json.parse not work for spreadsheet anymore? It kept throwing errors
it's case sensitive
or you ran out of your daily limit
for fetch
I want to parse html data from URL and find a id or class.
how can i do?
check IMPORTXML function video.
@@ExcelGoogleSheets hello, i want to ask something, can i?
Please help
why do i get ERROR with this?
www.bitstamp.net/api/ticker/
=IMPORTJSON("www.bitstamp.net/api/ticker/","last")
No logs found. Use Logger API to add logs to your project.
Isn't it easier to use an existing api such as www.sheetdb.io ?
2 completely different things.
thanks man u made ma life easier hahahhaha
Too much expensive
Google Apps Script: You do not have permission to call fetch
There was probably a problem while setting permission or whatever the fuck, and of course the bug will now stay there.
This is actually the second bug from google sheets, the first one is that I'm not able to view the script from google chrome, only from firefox, and this bug has been there for months, and good luck trying to get a response from anyone on that shit.
How to put headers in google script.