Apps Script UrlFetchApp API, Get JSON data, Build Google Sheets Function, Advanced Tutorial
ฝัง
- เผยแพร่เมื่อ 4 ก.ย. 2017
- 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.chicagocomputerclasses.co...
Google Sheets
www.google.com/sheets/about/
Company Page
www.chicagocomputerclasses.com/
The best teacher on youtube! You should get an award. :)
is that work with POST request as well ?
Thanks for posting this video. You literally saved me 2 hours of frustration. Well done.
Please I need assistance on a google app script.
Absolutely brilliant...your best video...was looking for something like this for a long time
Well-put-together. Worth the subscription!
Excellent Video. Very good and clear explanations about API calls, and JSON object data extraction.
Exactly what I was looking for
Mind blown! awesome stuff! thanks
exactly what i was searching for, thanks
Thanks a lot for a very important learning 🙏
Thank you so much for posting this
.एक टिप्पणी कहती है कि आप सबसे अच्छे शिक्षक हैं .... आप अनमोल हैं ..... उन लोगों के लिए जो ... यूट्यूब से भाषा सीखते हैं
Great function. Thanks.
Excelenteeeee es lo que buscaba
You're amazing! A reference for us!
🙂
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.
Amazing. Thanks a lot.
Awesome video! Thnx !
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
omg this makes my life so much easier!
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
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.
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?
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 🥲
very useful thank you. what if the remote server had a username/password for the query, I am struggling with Apache Solr.
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 ;)
.
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
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.
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
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.
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.
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.
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...
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.
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 TH-cam does when people don't watch these videos.
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 ....
Amazing video ! Is there a script to have automatically a blank space after every 15 rows please
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
impressive, how to get gold rates, and if fails get it from another link?
How can I import data (in CSV file if downloaded) in Google Trend into Sheets?
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?
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.
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.
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.
how do I export data from google spreadsheet to my web app ?
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(
that is an example of get request... how about post request?
is it posible to add bulk data using webapp api? If posible please help!
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:
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.
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.
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?
I got JSONInit is not defined (line 3, file "code") error when i try running myfunction
If the json have many columns, how can we spit ?
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?
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
is that work with POST request as well ?
Yes.
How to change to data of json on Cell?
Please help
why do i get ERROR with this?
www.bitstamp.net/api/ticker/
=IMPORTJSON("www.bitstamp.net/api/ticker/","last")
Can I get HTML Code of of a web page into Google Sheet ?
No logs found. Use Logger API to add logs to your project.
How to put headers in google script.
var json = JSON.parse(content);
isnt working how do I fix it?
Check the lines before that.
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?
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.