Import Live Cryptocurrency Price (And More) In Google Sheets & Schedule Auto Refresh
HTML-код
- Опубликовано: 31 июл 2024
- So my old method to import live cryptocurrency data doesn't work anymore, so I decided to come up with a more stable solution, which is to create a custom function called getCoinInfo() to fetch live crypto data. By using a custom function, we have the flexibility to pick and choose what information we want to display. And by using an actual API instead of web scraping, we no longer need to worry about if the source page's HTML code is updated. This tutorial is ideal for anyone who is looking for a free solution to import live cryptocurrency data.
In this tutorial I will cover two things:
1) Create getCoinInfo function.
2) How to schedule data refresh.
📑 Source Code: wp.me/payCAw-1mQ
💻 Live Coin Watch website: www.livecoinwatch.com/tools/api
💖 Show Support
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
🌳 Patreon: / jiejenn (early access to tutorial source code)
✉️ Business Inquiring: RUclips@LearnDataAnalysis.org
0:00:00 - Introduction to the tutorial on retrieving live crypto data.
0:00:09 - Announcement of a new robust solution for crypto pricing information.
0:00:20 - Quick demo of the proposed solution.
0:00:26 - Introduction of the GetCoinInfo function.
0:00:38 - Requirement to register for LiveCoinWatch for API access.
0:01:15 - Detailed explanation of using the coins/single endpoint.
0:02:08 - Overview of the GetCoinInfo function parameters.
0:03:28 - Google Sheets setup for implementing the function.
0:04:18 - Scripting in Google Sheets to enhance functionality.
0:09:38 - How to refresh formulas using Apps Script triggers.
0:12:25 - Confirmation and testing of the trigger functionality.
#googlesheets #cryptodata #crypto #cryptocurrency #googleappscript
PS: I have updated the source code to fetch values from nested fields like links.website, delta.year, etc.
What else would you like to see. Comment below!
Awesome video man, thank you for uploading this
👍
God bless you my dear.
it was very helpful
Glad to hear!
Nice Job Bro, Thanks
👍
Cheers for this!
I managed to knock up a monitoring sheet in no time at all.
Thanks!
Good to hear!
you are a boss
Thanks for the comment.
tHANK YOU!
👍
Legend - great tutorial!
For those struggling with the coins that have the same identifier, you need to use the ticker in the specific link.
Thanks for the info.
how can i do that?
which specific link, and which ticker
@@ImperialMustache didnt get u
@@caleacosmin7927 where do. u need help with
I can get the datas updated but only when I run the function force_update manually. With the trigger, it doesn't update the datas automatically. And when I close the Apps script tab and re open it again, the function selected automatically go back to the getCoinInfo one. I guess I'm missing something...
Makes sure in the function, you reference the correct cell.
Hi Jie, I came across your channel, very informative videos. I have a question, can you guide me how to extract historical volume of top 100 coins on a daily or hourly basis?
You can use the coins/list method and sort by rank.
Good afternoon,
I have set everything as through the video. Everything works so far however I am not getting any crypto data in my sheet. Time also work fine and the refresh is also good no errors in my code GS.
Now I do not know what goes wrong here. Can you find or indicate a solution.
Thank you very much for your answer
Hard to tell without looking your set up. Perhaps your project is not authenticated? That's my only guess. Otherwise there may be a typo in the function somewhere.
@@diegocoolen7057 user-agent is just telling the service what browser wthr request is coming from, you can change to anything you want. I'm fact, you can ommit user-agent it would still work.
Hi I am using brave browser, will that be an issue with receiving the data or, do i need use firefox javascript function too work
Shouldn't matter. You can use almost all browsers except IE.
Very nice video, Thank you very much!
I have one question, the force update works since it shows no errors and changes the timestamp every minute but the pulled values don't change, any idea on how to fix that?
thanks in advance
Hard to tell without looking at your set up.
How do you differentiate same tickers but two different coins? For example both Jupiter and Jupiter Project using JUP but it defaults to Jupiter Project.
I'm not sure to be honest.
use the ticker in the link
Good morning,
I am wondering how I can get a certain coin that may have the same name as others. i.e. btc will bring in bitcoin but the coin I am wanting is called LANDWOLF and there are multiple coins that go by the same name. Is there any way to get the exact coin imported that I want.
Thank you for your help
could there maybe be a way to connect URL instead of the name
look me look into it. I don't even know multiple coins can have the same name.
@@jiejenn yeah its true. have 2 coins with same name symbol IMX and ML
any idea meanwhile?
Hey, I'm facing a problem that some token prices are not event displayed, and some of them are displayed incorrectly, is there any way to fix that manually? thanks!
I will look into it.
@@jiejenn for example price for (STRK "Starknet token" is different) and for SUI or ALT doesn't even display :(
Hello, first of all thank you very much for this video.
I have a problem, the DATA refresh is only work manually and when I modify the code (when I add a space for example), the automatic refresh is not working, I have done the maneuver several times and meticulously but nothing happens, however, the cell "B2" to indicate the date and time of the refresh it is refreshed every minute, but not the prices. However, the refresh function doesn't indicate any error rate (0%).
Note: When I execute the GetCoinInfo code, I also get the error code 500, as others in the comments. The code with force_update works without error.
Error 500 is server error, so most likely you have a typo somewhere in your code.
@@jiejenn Thanks for your feedback. I only changed the API key in the code.
@@jiejenn Thanks for the great tutorial. I also have the same problem and will not abdate automaticly as it give me the same 500 error. and i copied the same code and change the API only.
@@scroopmd6306 I still have the problem on my side, let me know if you find a solution.
Hi, greatly appreciated for the information. Got stuck with Apps Script giving me an error "Exception: Request failed for... returned code 500..." Any idea what went wrong?
Hard to tell without looking at your script. My guess is probably the header. Not sure.
500 Internal Server Error We had a problem with our server. Try again later. I have the same problem and I got this from the docs. Did you manage to get it working?
Awesome video!
Thank you, I implemented this and it works, except for auto refresh.
I created a trigger to refresh every minute: doesn't work
I created a trigger to refresh every time I open the document: it works.
Any idea what I could be doing wrong for the time-based trigger? I did select the correct function.
Cheers.
Hard to tell the issue without looking at your setup, my guess is probably a cell reference is off somewhere.
@@jiejenn Thank you!
how to keep date and time updated as well? refresh doesnt help
Can you be more specific? Maybe give an example.
Hey, Jie! Thanks for your video!
Please, can you help me to sort a problem I'm facing, please?
When trying to run the formula "=force_update()", I'm receiving the following meesage: "Exception: You do not have permission to call setValue (line 52)."
How can I get around this situation?
In addition to this, my script isn't updating automatically even after configuring everything accourdingly. Can anyone help me with this?
That means you didn't provide the correct permission scope when you authorize the app. Makes sure you create the project within your Google Sheets file.
Why the source code link said page not found?
It should be live. Check again wp.me/payCAw-1mQ
Do you have also wrong name output? here is the solution try to put an "_" underline before the coin identifier, if its still the wrong one put another underline "__" : example "ML" = Market Lab, "_ML" = Mintlayer
Just tested, I stand correct.
Thank you but it works not steadily.
Does this script work on Google Chrome? All my data is blank
Not sure. You will have to try it on your own.
@@jiejenn I'm using Chrome. All my data came back blank.
const headers = {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:123.0) Gecko/20100101 Firefox/123.0',
I don't see Chrome here. Does that matter?
Update: The fields are case sensitive. IE - Name should be "name"
@@devondoyle8942 does it work with you in chrome browser? i have to update the sheet manually .
Yes it does
this is so hard for us normies
How would you use this code for a mac book running MacOS, Big Sur version 11.7.10 and also using a brave browser?
The instructions should be the same regardless the OS or browser.
I added delta.hour and delta.month, however it is not fetching the data
I see. For delta.month, because it has two layers, the function need to be modified to handle those fields. I will look into when I get a chance.