With over 13,000 cryptocurrencies tracked across 500 exchanges worldwide, CoinGecko is the world’s largest independent crypto data aggregator on a mission to democratize access to cryptocurrency data. Through CoinGecko’s API, you have access to the world’s most comprehensive crypto database to power all of your applications.
In this guide, we’ll share how you can use CoinGecko’s API on Google Sheets and at the end of this guide, you’ll be able to build the following:
- Cryptocurrency Portfolio (record & calculate your holdings value)
- Build your own list of selected crypto to track prices/volume/market capitalization and more.
- Perform analysis on a set of coins (volume changes, price changes etc.)
This guide also bundles together some tips & tricks such that your worksheet will be able to:
- Use CoinGecko’s API in an optimized way (1 API call for 250 prices, market cap, volume and more!)
- Automatically refresh data so your sheet is always up to date
Your spreadsheet will be a handy & powerful tool you’ll have on hand for your crypto tracking needs. However, this can take a bit of work – if you’re looking for a Portfolio that just works, take a look at CoinGecko’s Portfolio instead for all your tracking needs! Alternatively, if you are looking for a Microsoft Excel guide, we have it prepared here as well!
Let’s get started!
Step 1: Add scripts to your Google Sheets to import data and auto-refresh
Script 1: ImportJSON – This script allows your sheet to import CoinGecko’s API data
- Open a new Google Sheets (try https://sheets.new/)
- On the top left menu section, click on “Extension”, then select “Apps Script” and a new tab will open up.
- In the Apps Script tab, navigate to “< > Editor”
- Add in a new script using the “+” button
- Copy and replace everything with the importJSON script from here (it’s a pretty huge script, can’t fit it in here!)
- Save and name the script ImportJSON.
Note: The ImportJSON script used here is very versatile – you can import data in many ways and you should definitely play around with it once you’re more familiar!
Script 2: autoRefresh – So your sheet can automatically refresh
- Same as above, add a new script using the “+” button.
- Copy and replace everything from below, then save the script as autoRefresh. As the name suggests – this allows you to automatically refreshes your sheet at fixed intervals.
/**
* This function by Vadorequest generates a random number in the "randomNumber" sheet.
*
* It needs to be triggered with a Google Apps Scripts trigger at https://script.google.com/home/:
* - Select project and add trigger
* - Choose which function to run: triggerAutoRefresh
* - Select event source: Time-driven
* - Select type of time based trigger: Minutes timer
* - Select minute interval: 10 minutes (to avoid too many requests)
**/
// Updates cell A1 in "randomNumber" with a random number
function triggerAutoRefresh() {
SpreadsheetApp.getActive().getSheetByName('doNotDelete').getRange(1, 1).setValue(getRandomInt(1, 200));
}
// Basic Math.random() function
function getRandomInt(min, max) {
min = Math.ceil(min);
max = Math.floor(max);
return Math.floor(Math.random() * (max - min + 1)) + min;
}
Step 2 – Let’s get some auto-refresh going on!
For Script #2 (autoRefresh), we’ll need to instruct Google Sheet to run it automatically. Only two steps here!
First, create a new sheet called “doNotDelete”.
Next, create a Trigger (Google Sheet function) so the autoRefresh script can trigger. To do that:
- On the top left menu section, click on “Extension”, then select “Apps Script” and a new tab will open up.
- In the Apps Script tab, navigate to “< > Editor”
- Set up a new Trigger (bottom right corner) with the following settings:
- triggerAutoRefresh
- Time-driven
- Minutes timer
- 5 to 10 minutes (anything less might not be useful as results are cached).
And we’re all set up! Next we’ll start getting the data into your worksheet!
Step 3 – Getting top 500 coin prices (and more) with just two API calls.
In a new worksheet, paste in the following lines:
In cell A1,
=ImportJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false","/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h","noTruncate",doNotDelete!$A$1)
In Cell A252 (copy this, don’t reuse the one on top!),
=ImportJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=false","/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h","noTruncate,noHeaders",doNotDelete!$A$1)
Note: The formula above for cell A252 includes “noHeaders” as a parameter, so it doesn’t show headers like “Current_price, Market_cap” and so on!
You should expect a list of 500 coins to show up with data that we have entered above, as follows:
- /name – Coin Name
- /current price – Coin Price
- /market_cap – All Market Cap details
- /price_change – 24h price change
- /total_volume – 24h trading volume
- /high_24h and /low_24h – 24h high and low prices
In the next step, we’ll use VLOOKUP to pull out the data that you need.
Step 4 – VLOOKUP to get the prices!
Now you have a table of top 500 coins on CoinGecko that automatically refreshes, the next thing to do is make it work in a table you’d like to! If you are familiar with VLOOKUP, feel free to skip this section.
Here is a brief explainer on VLOOKUP:
- Search_key – this is where you say “I want Bitcoin price”
- Range – Which table should the formula check for the data?
- Index – In that table, which column should the data come from once there is a match?
- [is_sorted] – just leave this as 0
For example – to get Bitcoin price, your will want the formula to do something like below:
=VLOOKUP(Bitcoin, our big data table, 2 because Price is 2nd column in Data table, 0)
Continue building your table up by matching it to the appropriate table column number to finish it up.
Outcome of Portfolio
Below is a prepared example of the Portfolio using the methodology above. Make a copy and simply edit the “Name” field (or use the dropdown!) marked in orange and it should automatically change the coin for you. Add more rows and copy all the formulas and you should be set!
This approach is extremely scalable – you can build a portfolio that uses all of the data that comes from CoinGecko’s API, potentially only 1 call per 250 coins, each with 26 different data points (or more, depending on what you use).
Note: at times you may run into #ERROR when calling for data, it is normal for free API plan due to rate limits – you can try waiting for a little bit and then refreshing the cell data to trigger the API again. There is a short explainer for this right below so read on!
Closing Thoughts
At the end of this guide, you have a worksheet that uses only 2 API calls per 10 minutes and is capable of querying top-500 coins for price, market cap, trading volume and more. However, if you are using this on Google Sheets, there are limitations to it as Google Sheet relies on shared hosting and you may experience rate limit (shows as #ERROR) even when you use very little calls. While this method does not eliminate #ERRORs, it may alleviate it slightly – read this post for more details.
As you scale up, you will rack up API calls rather quickly and you may find yourself struggling to expand your worksheet beyond a certain point and that is when higher tier plans truly help. For the power users who need something more powerful, we recommend checking out CoinGecko’s API plans here. If you want to learn more, fill in the form below and we will be in touch shortly!
Once again, this is geared towards more advanced work/analysis uses. If you want something quick & easy that’s synced across web/mobile app – check CoinGecko’s Portfolio which works right out of the box complete with charts, transaction tracking, Profit & Loss and more!
Appendix
Resources & links for your convenience
- CoinGecko API Documentation
- CoinGecko API Plans – for all your scaling needs!
- CoinGecko API Token List on G.sheets (ids to copy)
- Sample Portfolio Design using this methodology
- CoinGecko.com – come visit us 🙂
Previously: the older, less scalable method for single calls – importJSONsingle formula
In your Google Sheets, go to the tools section and select “<> Script editor”. Add a new script and paste the following codes in:
/**
* Imports JSON data to your spreadsheet
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSONsingle(url,xpath){
try{
// /rates/EUR
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var json = JSON.parse(content);
var patharray = xpath.split(".");
//Logger.log(patharray);
for(var i=0;i<patharray.length;i++){
json = json[patharray[i]];
}
//Logger.log(typeof(json));
if(typeof(json) === "undefined"){
return "Node Not Available";
} else if(typeof(json) === "object"){
var tempArr = [];
for(var obj in json){
tempArr.push([obj,json[obj]]);
}
return tempArr;
} else if(typeof(json) !== "object") {
return json;
}
}
catch(err){
return "Error getting data";
}
}
Save the code and rename it as “ImportJSONsingle”. Close the script editor and return to your spreadsheet. The ImportJSONsingle formula:
=ImportJSONsingle (“[API URL]”,”[JsonPath]”
If you are unfamiliar with JsonPath implementation, you can refer to JsonPath’s github which includes comprehensive explanation on JsonPath. Below are examples of the formulas to obtain Bitcoin data.
Bitcoin price:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.current_price")
Bitcoin market capitalization:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.market_cap")
Bitcoin 24 hours volume:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.total_volume")
Bitcoin All Time High price:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.ath")
Bitcoin circulating supply:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.circulating_supply")
Bitcoin historical price on 30th May 2019, 00:00 UTC:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/bitcoin/history?date=30-5-2019","market_data.current_price.usd")
Bitcoin historical market capitalization on 30th May 2019, 00:00 UTC:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/bitcoin/history?date=30-5-2019","market_data.market_cap.usd")
Bitcoin historical 24 hours volume on 30th May 2019, 00:00 UTC:
=ImportJSONsingle("https://api.coingecko.com/api/v3/coins/bitcoin/history?date=30-5-2019","market_data.total_volume.usd")
Notice how the importJSONsingle script requires several calls for the same data (price, market cap, volume), while the newer version (used above) can rely on 1 single call for many more datapoints.
327 thoughts on “Import CoinGecko Cryptocurrency Data into Google Sheets [2022 Edition]”
How can i get total market cap, in usd, in Google sheets?
I was trying with the global api :
https://api.coingecko.com/api/v3/global
And import json script, but no success.
I get and error.
Thanks in advance
I have tried this
=ImportJSON(“https://api.coingecko.com/api/v3/global”,”total_market_cap.usd”)
But, the response was:
Error getting data
Solved…
=ImportJSON(“https://api.coingecko.com/api/v3/global”,”data.total_market_cap.usd”)
Depending of your location you must use “;” insted of “,”.
I’m using the sheets in portuguese and this solved my error.
When I open my spreadsheet, prices are loading, but quite often I get an #ERROR! Message, with description Exceeded maximum execution time. How can I fix this?
There is a small typo.
The formula calls ImportJSON but the script is called IMPORTJSON i.e. a caps mismatch. Change to make consistent and it should work.
Hello,
I was wonder if I could do this in a more efficient way. For example If I’m tracking 100 coins then when I load my spreadsheet I’m doing 100 API calls. Is there a way to do one API call that will go into a tab and I can reference that info for the rest of the spreadsheet info. I’m only pulling the price of the coins.
Hi,
Did you find a solution to this? I´m trying to do something similar
Hep,
Same need here 🙂
If I find the time to investigate, I’ll post my answer here.
Regards;
Yes, you need to pull from the coins/markets api. so for btc and eth you would enter:
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin%2C%20ethereum%2C%20&page=1&sparkline=false
You can enter as many currencies as you want. Then when you import its only 1 call. The easiest way is to use the tools here to look up the correct names of the coins in the database and enter them: https://www.coingecko.com/en/api
Use the get coins and search for the correct name to enter in the markets call. some names are old or different from what you would expect
Did someone find a solution. I am also having the same issue. It keeps making several hundreds of calls and many of them fail to load data in the end. I want to be able to make one call download all the data and play with it. Will just keep downloading fresh data at defined intervals
Check this out, this fetches multiple results in a single api call:
https://docs.google.com/spreadsheets/d/1nN60SuqoybP7we_kITjFr6bhHEGbW5mHuVs_nkO8BJU/edit?usp=sharing
Hello. How can I get price of the coin for a specific date? Your solution gives current price
This is what I did:
https://docs.google.com/spreadsheets/d/1nN60SuqoybP7we_kITjFr6bhHEGbW5mHuVs_nkO8BJU/edit?usp=sharing
This allows you to check prices of hundreds of coins in a single api call, the function takes ranges as input to be able to do this.
If you’re using the Cryptosheets add-on in Excel or Googlesheets, try this simple formula that uses only 1x API call:
Excel:
=CS.QUERYA(“CoinGecko”,”Coins Markets”,”vs_currency”,”usd”)
Googlesheets:
=CSQUERYA(“CoinGecko”,”Coins Markets”,”vs_currency”,”usd”)
See example video clips here: https://docs.cryptosheets.com/providers/coingecko/coins-markets/
cryptosheets is garbage, banned me for excessive usage I was only getting the price of a few coins. absolute rubbish
You could try databar.ai as an alternative (also no-code, gets you a csv of datasets from CoinGecko).
hey, i wanted to import prices and then specific percentage will that work?
Thanks for sharing this article! Article is nice. very informative.
If you want to hire top rated Crypto Developers for Develope ICO development, STO development, IEO development, real estate tokenization, art tokenization, stable coin development, crowd funding platform, local Bitcoin clone, crypto payment development. We are a team of professional blockchain developers.
Thanks
Putin’s Personal Trader Exposes How To Turn $10 To $100K
Renegade Russian Analyst Reveals How To Turn $10 To $100K
Have you seen this viral video?
The Cryptocurrency Institute have released a video show casing the methods a Putin’s Cryptocurrency Traders uses to generate 10,000% ROI!
[Click here to watch this before it’s taken down]
This is shocking.
I didn’t believe this myself until I saw it.
See for yourself.
It will blow you away.
useful information
I can recommend bigsocialbang.com/en if want to buy real ig followers with btc!
What happened to the CoinGecko Google Sheets Add-On? I was using it happily for many months, and today it just vanished without trace from my cryptotracking G-Sheet, and there’s no sign of it in the G-Suite Marketplace. Made it super-easy to include price data, without having to include an ImportJSON script!
Same here. I was using this function: =COINGECKO(“BTC/USD”)
It’s been working for many months and I now get a message saying that this is an unknown function.
CoinGecko, can you tell us what’s going on?
Very good article! Thanks man
Gecko team, are you going to fix the add-on?
Regarding the Jason script, how often the price will refresh – =importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin”,”0.current_price”)?
im also trying to find a way to make it refresh from itself but its not working, if u find the answer please let met know
I got this reply from the support team.
“The API price is updated on average is 2 minutes. As for google sheet, you’ll need to update it manually or create a script to update it automatically.”
And currently there’s no reference script for us to write the automatic price update.
Coingecko add-on has disappeared from google sheets..
#metoo :(((
omg, what we gonna do, no more add-on
I don’t think the CoinGecko Google Sheets Add-On was made by the CoinGecko team — it appears to be a completely separate project:
https://github.com/coingecko/coingecko-gsheet-addon
A real shame, as it made it super-easy to use the CoinGecko API!
My guess is that it stopped working due to the CoinGecko API being changed, since the sample Google Sheet linked to in this article also doesn’t work any more, either (prices are from 2019).
Ignore my comment above — seems like the Coingecko Add-On *was* an official Coingecko preoduct after all.
Which makes it’s disappearance from the Google Marketplace even more mysterious!
Anyone know how to edit the script in the sample GSheet refresh the values? They’re stuck at 25 September 2019!
How do you get the script to refresh? The data doesn’t change unless I open the script editor and click ‘save’… I have a button with assigned script to run the function IMPORTJSON and although it appears to run the script, the data doesn’t update. Can anyone help?
same problem here!
Same problem here. I was using the Coingecko ADD-ON in my Google Sheet, but it’s not working anymore. Can’t find the add-on anywhere.
Now my whole Google Sheet is not able to display prices. The function =COINGECKO(“BTC/USD”) was really useful, but now it’s missing.
If anyone knows an alternative, please let us know.
Great, I was able to import the current price of the coin I’m interested in.
But now I noticed that the price doesn’t update. I’ve tried waiting, refreshing the web page, and completely closing out of the page and reloading. The only way I can get it to update is to redo the formula in the cell(ie copy/paste) This is kind of redundant and the whole reason I wanted the data pulled from the site in the first place. So that I didn’t have to do any more copy and pasting.
Am I missing something?
Did somebody find out how to import market dominance in % into the sheet?
That would be very nice
If you’re using the Cryptosheets add-on in Google or Excel try these:
Just BTC dominance:
(Excel) =CS.QUERYA(“CoinGecko”,”Global”,”_path”,”data.market_cap_percentage”,”_fields”,”btc”)
(Googlesheets) =CSQUERYA(“CoinGecko”,”Global”,”_path”,”data.market_cap_percentage”,”_fields”,”btc”)
>> List of Top 10 Market Cap w/ Dominance (%) for Each
(Excel) =TRANSPOSE(CS.QUERYA(“CoinGecko”,”Global”,”_path”,”data.market_cap_percentage”))
(Googlesheets) =TRANSPOSE(CSQUERYA(“CoinGecko”,”Global”,”_path”,”data.market_cap_percentage”))
why do some coins you get “error getting data”, and others don’t? I know some don’t have a data on the website, like M Cap, but still happens to some that do?
See my comment from the 10 of January 2021 to solve this 😉
I’m running the script to run the function IMPORTJSON: what name do I use to retrieve the price for Bitcoin SV? I’ve tried:
Bitcoin SV
Bitcoin_SV
BitcoinSV
BSV
BTCSV
BSVUSD
BSV/USD
Thanks
Go to the page of your coin on the Coingecko site, for example:
For SV it’s: https://www.coingecko.com/en/coins/bitcoin-sv
The ID is the last section: “bitcoin-sv”
bitcoin-sv
Hey Mate,
If you go here: https://www.coingecko.com/api/documentations/v3#/coins/get_coins_list
and go ctrl + f
to search for the project you’re after, it will come up with everything you need there.
So for Bitcoin SV it is: bsvhalf
Hope this helps.
Cheers,
Chad
That’s a great move.
Nice Information….
Thanks for sharing nice information
If anyone is having trouble refreshing their rates in Google Sheets you can add an additional parameter that references any cell and that cell can be used to trigger a query of the API.
Ex. =importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=ethereum”,”0.current_price”,A1)
Now all you need to do is update the A1 cell (any value should work) and it will trigger any API calls that include this cell as an additional parameter.
Thank you SKRYPTS! This solves the importJSON update issue for me.
thanks, this help a lot.
Dude are you Felix Hoe from DBS 98
Excellent Solve! I took your solution one step farther and made “A1” a checkbox so all you need to do is check or uncheck that box and it updates all the calls.
Dude you awesome
Just a noob questions as I am learning programming right now.
How can you add a third parameter to the function, if in the script the function only accepts 2 parameters? Thank you, would love to understand this.
What does the code for this look like and I don’t know anything about scripts.
Hi there, is there a way to request the price at a specific time of day?
What is happening with this GoCrypto (GOC)? I invested in it through Hotbit. The same day hotbit is not showing it, the value is going up. What should I do? Now is showing:
1
Bitcoin.com Bitcoin.com GOC/ETH $0.02093902 1.97% $38 $82 $30
0.38% about 1 hour ago
2 TokensNet TokensNet GOC/ETH $0.01942893 – – – $7,698
96.54% Recently –
3 Bitcoin.com Bitcoin.com GOC/BCH $0.02075875 1.5% $609 $94
$230
2.88% Recently
Regards
Conie
What is the correct id for SNX token?
https://www.coingecko.com/en/coins/synthetix-network-token
This is not working:
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=synthetix-network-token”,”0.current_price”)
Thanks!
The value you want for “ids” is actually “havven”, the old name before rebranding to Synthetix.
Is there a way to reference the ID from a specific cell? For example the formula: =importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin”,”0.current_price”)
The above references the actual ID in the formula itself (“bitcoin”) where as I want the formula to reference a specific cell where the ID is i.e A1 for example. And then drag that down for 100 coins so I can get the price for 100 coins/IDs by having the formula reference A1,A2,A3,A4 etc. rather than having to manually type in the ID “bitcoin”, “ethereum”, “polkadot” into the formula each time?
If you use the Cryptosheets add-on for Googlesheets, these formulas should work and give you exactly what you’re looking for:
=CSQUERYA(“CoinGecko”,”Simple Price”,”ids”,”bitcoin”,”vs_currencies”,”usd”)
=CSQUERYA(“CoinGecko”,”Simple Price”,”ids”,A1,”vs_currencies”,A2)
*where A1 = bitcoin and A2 = usd
Nick, did you find a solution to your questions.
I’ve been trying for the last couple of hours w/o any solution in sight.
My approach: I’ve created the whole input for importjson in a cell. However when I do importjson(D2). Assume D2 has necessary string to fetch data for bitcoin, it doesn’t work.
An online search tells me indirect function could be of help, but still, I’m not able to make it work.
did anyone make it work?
This is what I did (below). It worked but then when I reopened the sheet nothing would update. So I tried using their actual ids… but they won’t refresh without errors either.
But to reference a certain cell:
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=”&A1,”0.current_price”)
&A1 being the cell it referenced
You can use Concatenate function. For example I have this =CONCATENATE(“https://api.coingecko.com/api/v3/coins/”,E$1,”/history?date=”, to_text($A3)) And at the end I get something like this: https://api.coingecko.com/api/v3/coins/bitcoin/history?date=01-01-2019
This is what I did (below). It worked but then when I reopened the sheet nothing would update. So I tried using their actual ids… but they won’t refresh without errors either.
But to reference a certain cell:
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=”&A1,”0.current_price”)
&A1 being the cell it referenced
Hi CoinGecko Spreadsheet Users!
I have some great news for all of you and some simple, free solutions to try right now that can resolve basically every recent question/comment on this blog post!
We have been working with the CoinGecko team to fully integrate the entire Coingecko API into the Cryptosheets Excel & Googlesheets add-ons (see below for instructions to get your exclusive CoinGecko user discount!)
# HOW IT WORKS
– All CoinGecko API endpoints are fully integrated, parameterized and searchable inside Excel & Googlesheets using the Cryptosheets add-on
– Send data directly to your sheets through the side panel query builder and API console
– Pull data into any cells in your sheets using simple, familiar formulas then refresh them live or at any interval you want
– Try pre configured sample queries/formulas and explore dedicated support resources, tutorials, videos & quick start templates for every single endpoint
# EXAMPLES
TIP: just copy + paste the formulas into your spreadsheet… it’s that easy
>> Googlesheets
——————————————-
=CSQUERYA(“CoinGecko”,”Coins Markets”,”vs_currency”,”usd”)
=CSQUERYA(“CoinGecko”,”Coins Markets”,”vs_currency”,”usd”,”_fields”,”symbol,total_supply,current_price,market_cap”)
=CSQUERYA(“CoinGecko”,”Simple Price”,”ids”,”bitcoin”,”vs_currencies”,”usd”)
=CSQUERYA(“CoinGecko”,”Simple Price”,”ids”,A1,”vs_currencies”,A2) –> where A1 = bitcoin and A2 = usd
=CSQUERYA(“CoinGecko”,”Coins Market Chart”,”vs_currency”,”usd”,”days”,”14″,”id”,”bitcoin”)
=CSQUERYA(“Coingecko”,”derivatives”)
=CSSYMBOLS(“exchange”,”coingecko”,”_limit”,”8000″,”_orderBy”,”symbol”)
>> Microsoft Excel
——————————————-
=CS.QUERYA(“CoinGecko”,”Coins Markets”,”vs_currency”,”usd”)
=CS.QUERYA(“CoinGecko”,”Coins Markets”,”vs_currency”,”usd”,”_fields”,”symbol,total_supply,current_price,market_cap”)
=CS.QUERYA(“CoinGecko”,”Simple Price”,”ids”,”bitcoin”,”vs_currencies”,”usd”)
=CS.QUERYA(“CoinGecko”,”Simple Price”,”ids”,A1,”vs_currencies”,A2) –> where A1 = bitcoin and A2 = usd
=CS.QUERYA(“CoinGecko”,”Coins Market Chart”,”vs_currency”,”usd”,”days”,”14″,”id”,”bitcoin”)
=CS.QUERYA(“Coingecko”,”derivatives”)
=CS.SYMBOLS(“exchange”,”coingecko”,”_limit”,”8000″,”_orderBy”,”symbol”)
# RESOURCES
Cryptosheets WebApp: https://app.cryptosheets.com/#/browse/coingecko
Cryptosheets Docs Portal:
– https://docs.cryptosheets.com/providers/coingecko
– https://docs.cryptosheets.com/providers/coingecko/finance-products/
Templates:
– https://docs.cryptosheets.com/templates/coingecko-query-builder
– https://docs.cryptosheets.com/templates/crypto-exchange-rankings-dashboard/
– https://app.cryptosheets.com/#/templates/386
– https://app.cryptosheets.com/#/templates/428
Help Center: https://intercom.help/cryptosheets/en/collections/2418653-providers#coingecko
YouTube: (Cryptosheets/CoinGecko playlist) :https://www.youtube.com/playlist?list=PLBg8GR5ao-axSqTrwnAodL2YVVqzFjYlO
# EXCLUSIVE DISCOUNTS FOR COINGECKO USERS
1. Check the settings > candy > rewards section in your CoinGecko user profile/settings
2. MAKE SURE to visit the CoinGecko/Cryptosheets referral page:
– https://cryptosheets.com/partners/coingecko
– Enter your email to automatically get a 25% discount if/whenever you upgrade to a paid Cryptosheets subscription!
3. Install the Microsoft & Google approved add-ons directly from the add-on stores:
– Googlesheets: https://gsuite.google.com/u/2/marketplace/app/cryptosheets/898843532532
– Excel: https://appsource.microsoft.com/en-us/product/office/WA104381695
4. Explore the Cryptosheets platform and use/access CoinGecko data for free –> starting RIGHT NOW!
Hi is there a way to download a time series into google sheet?
Tried this:
=ImportJSON(“https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=14″,”prices”)
and all i got is a series of running numbers. any experts?
I am also trying to do that for the top25 crypto assets. I am not sure what the xpath should be. I always get an “Error” message or “The path doesn’t exist”. I also tried “prices”, but I get no result. If someone has a solution for this, I would kindly ask him to leave a comment.
Hi GIN – It definitely helps, especially as you get more details.
This is so painful. I just want to code the API myself without depending on any third party add on’s. Why can’t I download all the symbol data in one shot and then use json to parse and read values. IT IS SO INEFFICIENT TO USE URL FOR EACH VALUE MAKING THOUSANDS TO INDIVIDUAL CALLS.
Read the doco…
Try this example. This grabs data for top 250 market cap.
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C%2024h%2C%207d%2C%2030d%2C%201y%2C%202y
You could specify the exact coin ids in a comma separated list. You could write an Excel formula to concatenate them from a column list. I plan to do this.
Thank you for that, UNLIKELY.
Just as a quick note, you need to replace the “%2C%20” values in the link above with commas for it to work ok in a google sheet and get the percentage changes. I also found this link really helpful for setting up a script that updates every few minutes:
https://medium.com/unly-org/how-to-consume-any-json-api-using-google-sheets-and-keep-it-up-to-date-automagically-fb6e94521abd
Hi unlikely, what would be the xpath to use with this url? Im trying to get a list of the top 500 cryptocurrencies by marketcap and i have the url already but what xpath do i need to finish the importJSON formula?
Thanks so much!
Play trading for free & win real Bitcoins at https://battletraders.io/?ref=rAjTJ9g0_
Thanks, man.
Not sure why with importjson I get the message – error getting data.
Is it happening with others? Any workaround?
=IMPORTJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C%2024h%2C%207d%2C%2030d%2C%201y%2C%202y”)
Nice Article
Useful Information
Guys i am trying to get smart contract address using coin id, does anyone has a sample code i can use?
https://ecency.com/poker/@harapanqqpoker/agen-judi-online-terpercaya-agen-agen-sbobet-sbobet-casino
https://www.fablabs.io/users/rekanpokerseo
https://www.plimbi.com/author/16844/sekodilemoseo
http://www.laptopscreenexpert.com/
Thanks for article. I try to get historical data for many coins and in some cells get “Error getting data”. How can I get historical data without errors?
See my comment from the 10th of January 2021, it might solve your issue !
This method is easier: https://mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/
Hi,
(and thank you for this great job!)
I would like to know how the changes concerning the token list are going: https://api.coingecko.com/api/v3/coins/list
the procedure does not look automated in case of swap/modif/etc…
I take the example of the Aave-end today : on CoinGecko, there is the [new] Aave “symb:AAVE” https://www.coingecko.com/en/coins/aave and the [old] Aave “symb:LEND” https://www.coingecko.com/en/coins/aave-old
when we use the old ‘ethlend’ ID it redirects us to the [old] and I didn’t see any trace of the new ID in the list for the API.
Beyond this example (even if I’m very interested in the new Aave ID 😉 ), I’d like to know how the list of this API is updated, because we all know that in the world of cryptos this kind of case is more than frequent…
Thank you for enlightening me,
Alexis
https://are2007.com/
Why when i try this it doesn’t work i get a “error getting data” ?
=importJSON(“https://api.coingecko.com/api/v3/exchanges/uniswap_v1/tickers?coin_ids=dai”;”tickers[0].converted_last.usd”)
You should do ”tickers.0.converted_last.usd”
how is =importjson(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=havven”,”0.current_price”)
the Synthetix price? I can’t find all the price URLs meaning for OMG for example or verge, I have BTC and ETH working just fine. the other ones get an error. I initially thought that SNX would be something like =importjson(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=synthetix-network”,”0.current_price”)
Is there anyone who knows how to get orderbook depth value?
Hi,
Anyone knows the way to automaticaly convert current price in USDT (tether) in a Google Sheet ?
Regards,
Hi…How can we extract data from JSON for a certain time range, like from 1 Dec 2020 to 20 Dec 2020. Kindly help. Thnx.
Any idea how to get this ETH/USD price…
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=ethereum”,”0.current_price”)
…to be replaced with the ETH/BTC price? Thx.
Finding “Error getting data” for stats after coming back to the spreadsheet. Worked fine earlier today.
Anyone find a way to debug? Is this a server issue?
I’ve used both:
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=” & lower(A2),”0.current_price”)
and
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=degenerator”,”0.current_price”)
Both return the error, as does a random selection of other coins.
Thanks for anyone who can help!
See my last comment 😉
Any code for 7days marketcap changing?
Help me, pls
Hi!
I have a question. If I want to be able to type in a Coin Symbol (for example BTC) into a cell, and then have another cell return the price for that entry – I understand that I would use:
=IMPORTJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin&order=market_cap_desc&per_page=100&page=1&sparkline=false&price_change_percentage=1h%2C%2024h%2C%207d%2C%2030d”,”0.current_price”)
BUT I want to be able to type in a NEW COIN and have that cell auto update with the price of the NEW COIN I typed in.
How would I do this?
I need to somehow reference the cell where I’m typing in a ticker with the formula for price. I can’t find out how to do that in the formula.
Thanks!
you can concat 3 cells
1. https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=
2. bitcoin
3.&order=market_cap_desc&per_page=100&page=1&sparkline=false&price_change_percentage=1h%2C%2024h%2C%207d%2C%2030d”,”0.current_price
and than change the second cell with the coin you want, and that will change your URL
If you are struggling with multiple imports, and some of your data are randomly giving you “error getting data”, it is because there are to many of them in the same time.
You can correct that by adding delay (500ms) before retry when getting an error.
In the IMPORTJSON file, just replace this :
catch(err){
return “Error getting data”;
}
By this :
catch(err){
setTimeout(IMPORTJSON, 500);
}
Oh yeah ! Works like a charm now !
Or try this :
catch(err){
Utilities.sleep(2000);
IMPORTJSON();
}
what a legend this works thank you so much i was going mad wondering why I was getting errors. This one worked for me instead of the 1st suggestion
Yes it is not the perfect solution though because I keep getting errors with that one 😀
ack, with utilities.sleep(2000) I am getting:
Exceeded maximum execution time.
Neither of these solved it, instead of “error getting data” I now get just “error” lolol 😡 What else can I try? This is really frustrating and is messing up my table. Would dividing the table into two sheets help?
thanks!
Hello
When i put that:
catch(err){
setTimeout(IMPORTJSON, 500);
}
Erreur
ReferenceError: setTimeout is not defined
When i put that, it’s telling me:
ERROR into the Cell
Any other tips to help ?
Thanks.
ReferenceError: “setTimeout” is not defined. <-this is what I got when I tried your code for setTimeout.
Another user got this same error. How do I fix this?
catch(err){
setTimeout(IMPORTJSON, 500);
}
Maybe it’s the German setup of my PC, but the examples didn’t work for me. Only when I exchanged the comma for a semicolon did the script work!
Original(ERROR):
=ImportJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin”, “0.current_price”)
New(SUCCESS):
=ImportJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin”; “0.current_price”)
Thx!!! same issue here.
omg……precisely my issue too. Thanks!! Your comment should be way up at the top.
Hello when i try and confirm my formula, if i open again the comma is always coming back instead of the semicolon
Any tips mate ?
thx
Thank you very much, this was indeed the problem! I’m also using german google sheets here, changing out the comma to semicolon works perfect!
i couldnt find the API ID for defi-perp, could you add in please?
https://www.coingecko.com/en/derivatives/ftx/DEFI-PERP
How to get the current price of top 100 crypto across defi or all exchanges ?
Thanks
Hello, is there a cmd to import the logo of the current currency in the sheet ?
Seems like a useful promising app!!
Does anyone have a tutorial or step by step broken down example of how to build an excel sheet from scratch using this API method? Sorry, do not have experience with inputting code or where
The example uses time 00:00, what is the syntax for a different time?
Once you concatenate, how do you put the concatenated text into the importjason formula?
Nevermind got it!
why am i getting “error getting data” only in certain cells when i’m simply copy and pasting a script from above and its working in the other cells?
happens from time to time. try refreshing the page, or just waiting
Hi guys, following problem.
I use this call.
https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum,aave,duckdaodime,dlp-duck-token,uniswap,yaxis,wrapped-crescofin,chainlink,swissborg,geyser,bitpanda-ecosystem-token,omisego,bancor&vs_currencies=usd,eur
Whenever i refresh the data the cells get mixed up. So i cannot make a reference/lookup from this sheet to another because now its BNT price in first place then its link and so on.
Someone has a solution?
=INDEX(sheetNameHere!cc:cc,MATCH(columnOfCoinname,sheetNameHere!xx:xx,0))
cc = column in a sheet with current market prices
xx = column in a sheet with coingecko token names
=INDEX(setCurrentPriceCAD!E:E,MATCH(B2,setCurrentPriceCAD!A:A,0))
Works, but wonky. For example pulling about 25 spot prices with no issue, but always have issues with ZIL and MIOTA… sometimes they work, but usually it’s just “Error Loading Data”, which even refreshing doesn’t help.
I guess we can’t complain for something free.
Is there a way to retrieve the tickers for a coin? I mean, to do the JSON call, one uses for instance “bitcoin”. Is there a way to retrieve its tickers, being “BTC”?
Change the list of fields parsed by ImportJSON to include /symbol:
=ImportJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false”,”/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h,/symbol”,”noTruncate”,doNotDelete!$A$1)
Does anyone know how to get the highest and lowest value of 7 days.
something like this:
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=eur&ids=bitcoin&order=market_cap_desc&per_page=100&page=1&sparkline=false&price_change_percentage=24h”;”0.high_24h”)
but for 7d..
Thanks in advance
How do i do this in LibreOffice on Linux ubuntu using Calc (which is almost like Excel) ??
Hi, here an approach for LibreOffice with SERVICEWEB and REGEX.
=SERVICEWEB(“api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd”)
The result will be (for exemple in A1): {“bitcoin”:{“usd”:49365}}
If the SERVICEWEB function is in A1 and you will extract the value in another cell, write this in : =SI(ESTNUM(CHERCHE(CAR(46);REGEX(A1;”[:digit:]{1,}+\.+[:digit:]{1,}”)));SUBSTITUE(REGEX(A1;”[:digit:]{1,}+\.+[:digit:]{1,}”);CAR(46);CAR(44));SI(ESTNUM(CHERCHE(CAR(44);REGEX(A1;”[:digit:]{1,}+\,+[:digit:]{1,}”)));REGEX(A1;”[:digit:]{1,}+\,+[:digit:]{1,}”);REGEX(A1;”[:digit:]{1,}”)))
This take the digit if you have a point it transform it in , .
After that take this cleared value in a cell where you can us it for your evaluation.
Here a some litterature: https://ask.libreoffice.org/fr/question/296326/fonction-regex-transformer-un-point-en-virgule/
Regis Frei,
Thank you very much for the assistance.
Is this french? (the second part of your message)
Do you have this in english for me because it is not working.
Regarding the first part, when i enter =SERVICEWEB(“api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd”) it gives me a error 501.
I’ve been getting the same
The function is WEBSERVICE, not SERVICEWEB. At least in my Calc. The second part of REGIS FREI’s message I don’t understand and did not work for me. Can someone clarify, please?
How do I get the total market cap API?
How do I get the ping? I don’t know what data i need to input in the JsonPath to get the ping of the API
How do you query the ATH date of a crypto using JSON?
I found it easier too use this script:
https://gist.github.com/paulgambill/cacd19da95a1421d3164
Then simply paste the URL directly for example:
=ImportJSON(CONCATENATE(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=”,A5,”&sparkline=false”))
Where A5 # page number. And change usd to whatever other currency you need. This is done since their is a hard limit of 250 coins. I ended up having several sections 250 at a time. Still messed up sometimes. Would be great if their was a way around this limit of anyone knows.
Is there a way to import xml the circulating supply into google sheets? I’ve been trying to find a way how to but nothing comes to mind. plus how would I be able to load multiple currencies instead of tediously going through each coin and inputting it separately?
This is awesome! Thank you.
But, I work with a team and would love to have a way to simply share my CG Watchlist with them. Can’t seem to find a share feature on the site. Am I missing it.
CoinMarketCap.com recently added this feature to their site, whereby I can share any of my watchlists and my team can duplicate it in their CMC account.
Will CG be doing this soon? Please let me know.
– Fred
The IMPORTJSON described in this post could be improved by allowing to query a JSONPath for multiple elements.
For instance, “$[*].current_price”, to retrieve all current prices at once, instead of “0.current_price”, which retrieves only the current price for the first element.
As a positive side-effect, this allows for far less API calls to CoinGecko API.
If you are interested, you can copy/paste the content of the IMPORTJSON.gs script in my example spreadsheet ->
https://docs.google.com/spreadsheets/d/1I4ZhUipArhsip76n7LOTe30zTuty4qB9dWWxStEeYVU/edit?usp=sharing
PS: In this spreadsheet, I am also showing how to send trade orders to Binance directly from Google Sheets.
I was having issues with this, getting a generic “Error”. Turns out it was permissions based. I ran the debug code in Apps Script (https://script.google.com/) where it asked for my permission as the owner of ‘Untitled Script’. once I granted permission, the error went away.
hope that helps someone. You’ll have to decide for yourself if giving this script permissions is okay with you
I would like you to build me a bot that looks at the price once every hour. Then add up a 100 of those numbers for a total. Anyone point me in the right direction before i reinvent the wheel
This works for me (for now, as I only just made it and I haven’t tested to see if it refreshes data well):
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=”&A1,”0.current_price”)
in Column A I list the id names (located here: https://api.coingecko.com/api/v3/coins/list)
note: for BitTorrent, the id is “bittorrent-2” but I had to use a semi-colon after the “&A#; in order for it to pull the data, otherwise I just kept getting error.
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=”&A1;”0.current_price”)
I’m only interested in knowing my total investment vs my total return, so current price is the only data I need atm.
update: once I reopened the app, none of the prices would update, I just got an “error” message. I had the same issue pulling from coinmarketcap as well.
So I checked the sample coingecko spreadsheet they provided above … and bitcoin is at $10,000… i saved a copy, reopened and refreshed…. nothing changed… still says $8,000 on first sheet and $10,000 on second. So clearly something doesn’t work correctly with the script
Since a week ago, some of the coins don’t show the real money they cost. For example Pancake Swap is right now £17 but my google sheet has been showing 12.8 for more than one week. I don’t really know why because if I go to the endpoint (https://api.coingecko.com/api/v3/coins/markets?vs_currency=gbp&ids=pancakeswap-token) the current_price shows 17.
All I want is simple current price data for a few coins. I took the example for bitcoin (=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin”,”0.current_price”)) and simply switched the coin id from “bitcoin” to “ethereum”, also tried “litecoin”. But nothing works! Error after error. Ive looked up the proper IDs and have no clue why this is so frustrating. It works fine for bitcoin!
I am using ‘=IMPORTXML(“https://www.coingecko.com/en/coins/dogecoin”, “//div[@class=’text-3xl’]”)’ function in a google sheet cell to import current price .. might be helpful for folks only tracking their prices. This refreshes automatically and if not just an f5 would do the trick.
not found this model =/
Pretty useless, sometimes it shows the data, next time the same formula gives Error getting data. Moving the formula to another column sometimes does the trick, moving it back sometimes keeps it, sometimes gives the error again.
Confirm, it’s really working awful. But, i my opinion, it’s not script’s benefit, but only coingecko’s API – it’s can’t to handle frequently querries
Hi i am having issues with historical retrievals.
I am trying this formula “=ImportJSON(“https://api.coingecko.com/api/v3/coins/bitcoin/history?date=01-01-2019″,”0.market_data.current_price.usd”)”
and it returns “error getting data”
This formula is copy and pasted from the API instructions.
Any ideas on how to fix?
Here is the code that caches USD values and returns from cache:
var cache = CacheService.getUserCache();
function IMPORTJSON(coin, element){
if (coin == null)
coin = “bitcoin”;
if (element == null)
element = “current_price”;
try{
// /rates/EUR
var jsonText = cache.get(“coins”);
if (jsonText === null) {
//…prepare your fetch
var res = UrlFetchApp.fetch(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd”);
jsonText = res.getContentText();
cache.put(“coins”, jsonText);
}
Logger.log(“all ok”);
var data = JSON.parse(jsonText);
var item = data.find(x => x.id == coin);
Logger.log(item);
Logger.log(item[element])
return item[element];
}
catch(err){
return “Error getting data”;
}
}
Hi i have just set this api up to google sheets, however i am having trouble trying to work out how to refresh the data? the numbers are staying stagnant?
Add a check box on A1, clicking on it will force a refresh to fetch the info. It might help. I still find myself having to click the checkbox around 10 times before the all the results comes back without the “Error getting data” and “#VALUE!” errors.
try for fetching BTC current price. note the , $A$1 towards the end.
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=SGD&ids=bitcoin”,”0.current_price”, $A$1)
Hi Fernando
I am trying to follow the instructions but I dont know why, it doenst works for me.
I even copied the exemple file and it keeps the old data but doesnt update data, and doenst even recognice the function “importJSON”, I did autorice the scrypt but, it doesnt work
It seems work for many peoble.
Can you help me ?
Thanks
Possible solution for ‘Error getting data’:
http://third.sedecilliard.com/2021/05/21/crypto-currency-rate-checker-for-coingecko/
I can recommend cardrare.com if you want a custom luxury metal credit card with Bitcoin logo on it!
I fixed the ‘Error getting data’ problem by just deleting all the formulas and then doing Ctrl+Z.
Seems to refresh them.
Hi CoinGecko,
Thanks for making this data available!
I want to look into historical correlations between FIAT and BTC for a few coins.
I see mostly tips/data for looking at the data as they are now, but how would I pull data over a range, and are there perhaps datasets I could access/download with say, XRPBTC, over a 2 year period, giving hourly data?
Thanks in advance.
Thank you for making this article, your article is very good
Is there a way to use this with LibreOffice? I do not want to put all my crypto data online, nor give it to Google.
Hi every one.
I am trying to follow the instructions but I dont know why, it doenst works for me.
I even copied the exemple file and it keeps the old data but doesnt update data, and doenst even recognice the function “importJSON”, I did autorice the scrypt but, it doesnt work
It seems work for many peoble.
Can someone help me ?
Thanks
lucky post
For those of you that are looking for a spreadsheet which tracks both crypto and stocks and also comes with awesome tools such as newsfeed and stock screener, I can recommend: https://www.coin-atlas.com/
A real thank-you to BitcoinMinersUK.com!! Our business just used to prepare us for sorting a mining rig, their team turned up in under a few days of calling them. They were highly knowledgable about crypto. Really could not reccomend enough checking them out.
Error
This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()
=importJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=litecoin”,”0.current_price”,B$2)
B$2 =RAND()
Great news!
Hey Guys.
I know some of you spent a lot of time to create these API sheets, but does someone mind saving me the trouble and sharing their sheets with me? Please
noob here. how do i get current prices? if i copy the sheet, get 2019 prices???
TIA
I can’t get any data today, it’s always an error. Anyone else the same?
Im too
Same
Temp workaround example
=INDEX(SPLIT(INDEX(IMPORTDATA(“http://api.coingecko.com/api/v3/coins/markets?vs_currency=EUR&ids=cardano”),5),”:”),2)
This does nothing for me. Today I also noticed the importJSON stopped working.
same here
I’m having the same issue although the URL’s return data when independently posted into a browser.
importJSON doesn’t work as of the moment(July 29,12am SGT)
Can someone help us?
Try this:
=SUBSTITUTE(INDEX(importdata(“https://api.coingecko.com/api/v3/simple/price?ids=Bitcoin&vs_currencies=usd”;”:”;”en_US”);;3);”}”;””)
“#ERROR!” in cell.
“Error.
Formula parse error.” contextual pop-out message.
Apparently has to do with Google deprecating API v3 and move to API v4, security scopes etc., which is due to happen on Aug 2nd.
However documentation is really lacking.
Does the price get auto-updated in the spreadsheet? If so, how often it gets updated?
Aug 10,2021
still not working. any workaround guys?
Our organization has been providing online class help services to understudies for more than 5 years. The best minds in business-related fields are working with us. They respond to each question by the students precisely. With over five years of experience in this field, our online experts are highly knowledgeable about the topic. Before they begin to handle the mission, they place some effort into understanding its purpose. Our article authors begin working on your article the moment you place an order at domyexamnow.com. The team explores the entire point to ensure it is covered in all its layers. Our brand is the most trusted one when it comes to online course help.
Sadly this doesn’t work. I get “Error getting data” randomly. I have tried all the suggested solutions and workarounds during the whole afternoon and hit my time stop-loss now 🙁
It would be really great to take prices from coingecko, since coinmarketcap and other trackers don’t have almost all the coins like coingecko does.
I am really a newbie in this, will I be ok if I just want to grab OHLC for each weekly price bar? It sounds like the major problem is calling on the database too frequently. I’m just using this to track my BTC and ADA for my side project in crypto casinos.
On initial load, it fails constantly for just getting one symbol. This fixes it. Hope it helps someone.
https://docs.google.com/document/d/18FWCRXzAiDyjFr0EzUMyBJUyYhINyx2nZQzfBUg6dpE/edit?usp=sharing
It’s working, Thanks for your help.
I’m not very good at English, but I’ll do my best.
This method probably won’t work correctly with the free plan.
The reason is that Google Apps Script is a server-side script.
Why can the CoinGecko API aggregate the number of requests per user even though it doesn’t receive an API Key?
If it doesn’t receive an API key, the number of requests would probably be aggregated by the client’s IP address.
However, in Google Apps Script, that IP address is likely to be shared by many users.
FYI, here’s how I worked around the problem with Google Sheets getting blocked by CoinGecko due to overuse of their api:
1. used code above to populate a sheet “Prices” (or, frequently, get an error)
2. had another scheduled job that copied the Prices to a backup table (SavedPrices), IF there wasn’t an error
function makeDataBackup() {
var ss = SpreadsheetApp.getActive()
var source = ss.getSheetByName(‘Prices’).getRange(‘A1:Q501’)
if (source.getValue() != ‘#ERROR!’) {
Logger.log(“copying data to ” + source.getWidth() + “w and ” + source.getHeight() + “h”);
var destinationSheet = ss.getSheetByName(‘SavedPrices’)
source.copyValuesToRange(destinationSheet, 1, source.getWidth(), 1, source.getHeight())
}
}
3: used iferror() function to fail-over from Prices to SavedPrices. For example:
=iferror(VLOOKUP(“BTC”,PricesData,2,0),VLOOKUP(BTC”,SavedPricesData,2,0))
(PricesData is actually a Named Data Range that points at the table)
Thank you so much. This saved me hours of headaches. Legend.
If this helps anyone else, I used your code but for step 3 I changed it to this:
=IFERROR(IF(ISBLANK(ARRAYFORMULA(PriceData)),ARRAYFORMULA(BackupData),),ARRAYFORMULA(BackupData))
Which allowed me fill errors/blank cells in an entire sheet with this single line.
For some reason, this line applies the IFERROR to individual cells even though the Named Data Ranges are for the entire sheets, meaning it ONLY uses the backup sheet’s data on individual error/blank cells and not swapping the entire sheet.
You can stay at home and earn money online through bitcoin.
You can earn massive funds within 24hours of trading.
Don’t waste your precious time doing nothing.
You don’t know how to trade?
You want to learn more?
Download whatsapp and add up +1(828)-829-1436
Email:(investmentcoinseries360@gmail.com)
This is Couponsva, a professional blogger I enjoy writing on multiple callings with a firm grip on specialty include digital marketing, affiliate marketing, entertainment, and e-commerce blogs.
Great Blog!! Keep up the good work , hope to seemore informative blogs from your side , author. For best online class help services in all of the US , visit our websitte great online class help.
I want to say thanks to you. I have bookmark your site for future updates.
Hello, how would I get TVL data point for DEXs?
Hi Michael,
I had also the parse error with the formula:=ImportJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false”,”/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,”noTruncate”,doNotDelete!$A$1)
I’ve changed this to your one to track a individual currency but for me it’s not working when I use this one:
=IMPORTDATA(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&ids=ethereum”)`
Is there something that I have to change in the settings or script or something that you know?
Many thanks for the support in advance!
The workaround I found for the “Error
Formula parse error.” on `=IMPORTJSON` was to instead use `=IMPORTDATA` for each individual currency I’m attempting to track. Autoupdates hourly and can be updated manually. Example for ETH: `=IMPORTDATA(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&ids=ethereum”)`
I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives indepth information. thanks for this nice article…
Blockchain Financial Cybersecurity
Using the example as shown above, I’m trying to get the last 30 days of Prices using:
=ImportJSON(“https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=30&interval=daily”, “/prices,/market_caps,/history”, “noTruncate”, CryptoData!$A$1)
The URL is AOK, as I checked it in a browser tab, and my sheet name is CryptoData rather than “doNotDelete”.
So I think there might be a problem with the “query” part, as I get Error: “Exception… code 429… error code 1015”. However, the original example on this webpage works AOK in my sheet.
I’m trying to get the data as Date | Price | Volume. Has anyone a suggestion please?
After my hourly Trigger fired, I now have data – the problem must have been the known Google Sheets – Coingecko issue.
However, I obviously still have a problem with the “query” part of the ImportJSON junction. All the data is in just 2 cells. The first cell has 30 pairs of timestamp & price, all comma delimited, and the second cell has 30 pairs of timestamp & market cap. There is no volume data.
Can anyone advise what the “query” should look like?
I need this too.
Does anyone know if it is possible to create a refreshing list in google sheets for, for example, bitcoin price, which stores its price every day, obviously combined with autoRefresh script?
Hey I managed to copy 500 coins, but when i want to run autorefresh in scripts it creates error as following:
TypeError: Cannot read property ‘getRange’ of null
triggerAutoRefresh @ autoRefresh.gs:14
Any help?
Cheers.
Wow. I came across a very fascinating site on Uniswap Clone Software. In addition, I agree with a few of the points made. In any case, thank you for the blog. I’ll tell my colleagues and friends about it. Cheers.
Hi,
each first time I open my spreadsheet I receive: “Exception… code 429… error code 1015”
When the trigger will fire the data appear without any problem, this issue is really annoying. Do you have any suggestion?
Is that working for any of you? If yes can you please advice how you did it? For me seems that something is wrong but i cannot understand what as it gives me an ERROR before the creation of the table.
Is that working for any of you? If yes can you please advice how you did it?
Is that working for any of you? If yes can you please advice how you did it? For me seems that something is wrong but i cannot understand what as it gives me an ERROR before the creation of the table.
https://www.sirketdedikodulari.com/
Wow. I came across a very fascinating site on Uniswap Clone Software. In addition, I agree with a few of the points made. In any case, thank you for the blog. I’ll tell my colleagues and friends about it. Cheers.
https://kripto365.net/
Hi i have just set this api up to google sheets, however i am having trouble trying to work out how to refresh the data? the numbers are staying stagnant?
https://www.kucukcekmeceden.com/
If you are struggling with multiple imports, and some of your data are randomly giving you “error getting data”, it is because there are to many of them in the same time.
https://kisapas.com/
I appreciate the efforts u did to explore how to import CoinGecko Cryptocurrency data into google sheets. Google sheets is an easy way to manage your data as well. What do you say?
I have a error with that:
=ImportJSONsingle(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=polygon,”0.current_price”)”)
do you have a idea to solve this error?
Created google sheet, script, trigger, etc. as described here. After closing and reopening the sheet I see a small message that temporarily appears at the top of the sheet saying “saved to drive” which after a few seconds disappears. I am not sure what “saved to drive” means nor to I see coingecko data anywhere in the sheet or on my google drive. The trigger also does not seem to fire. Any help would be appreciated.
This was working for me up until today, and now I’m getting “#ERROR!” for all my queries all day.
Any ideas what the issue is?
Hi! What if I want to fetch a custom list of coin prices for 1. current prices and 2. prices in a specific date, is it possible? Any available tutorial or guidance? Thanks!
Great Post!
Need to take your class online? Take Your Class Online offers best Online Class Takers! Just say take my online class for me. We offer do my online class help!
Are you willing to earn high grades in your online classes? Hire our Do My Course For Me – Do My Classes Now services and get professional assistance with promising results. Our academic experts guarantee that they will get you top-notch grades.
Are you looking for buy cheap land or home in south Florida? We have best property consultant who help your home on best location of south Florida. We are in this industry since 10years.
The atoms were explained to be have no perceptible qualities. They existed in a void and which thus allowed the atoms to move freely within the void. theology assignment help In comparison to the Par median view of being and not being, the atoms represented a state of being while the void represented a state of not being.
Hi,
each first time I open my spreadsheet I receive: “Exception… code 429… error code 1015”
When the trigger will fire the data appear without any problem, this issue is really annoying. Do you have any suggestion?
Dul Bayan Arkadaş bulmak artık çok kolay. Sitemizde ücretsiz bayan ilanları paylaşılmaktadır sende hemen arkadaş bulmaya başlayabilirsin.
We have a team of professional online course takers who can submit your assignments timely without you being anxious about the confidentiality of your credentials. So log in now. https://domyclassesforme.com/
Thanks for sharing
Great Post! Keep it up the great work.
Hi, how can I get the data for more coins? for example, from 502 to 700. thanks in advance
very useful post for all..
Thank you very much for such sharing. Very useful and nice sharing. No problem, it works fine.
https://www.bizdekalmasin.com/kategori/coin-gelecegi/
Thank you very much for sharing your knowledge and information; it has greatly aided me in my job and personal life.
I appreciate your article about cryptocurrency data into google sheets, i would like to share you the best cryptocurrency exchange software developed by Hivelance Technologies for further details visit here https://www.hivelance.com/crypto-exchange-software-development
Hi!
I did everything according to the instructions described in the article
Script ImportJSON. When starting debug show error:
Exception: Attribute provided with no value: url
ImportJSONAdvanced @ ImportJSON.gs:220
ImportJSON @ ImportJSON.gs:71
How to solve this problem?
Same problem…
Thank you so much
https://www.bizdekalmasin.com/coin-gelecegi/shiba-coin-gelecegi/
Need take my online class services? Reach out to Online Class Help Now. We have the best online class takers to help your Do My Class Online for me needs.
I appreciate your efforts to explore how to import CoinGecko’s cryptocurrency data to google sheets.
Thanks, kriptoparayorumlari
Hey good hack there. Meanwhile, anything related cryptocurrency API issues, setup any form of code running, you can rely us on at https://hivelance.com/
Searching for Pay Someone To Do Your Online Class For Me or do my online class? Get the best online class help from Online Classes Hub. We take your classes and courses.
Thank you very much for sharing your knowledge and information; it has greatly aided me in my job and personal life.
https://www.bizdekalmasin.com/coin-gelecegi/multi-coin-gelecegi-multichain-yorum/
Is there a way to get to return CAD prices instead of USD using either?
I did everything according to the instructions described in the article! its works. Thanks for cryptocurrency article.
Thank you very much for sharing
Great information. Thanks a lot for sharing
I didn’t know about this feature of google. thanks
very good share
great share
How can I integrate this?
thank you all
Hello,
Is it possible to get the historic data of bitcoin dominance?
Thanks
I have continuously accepted that mold was not as it were to create ladies and men more lovely but moreover to console them shearling coats and jackets , donate them certainty.
Thank you so much for sharing!
https://kriptopara.com/
That’s really delightful to know. You can check out the latest Grace Rose Farm coupons now.
great postt!
TEKKEN 6 APK Download Free Game App For Android & iOS(Latest Version) https://apkchew.com/tekken-6/
very good topic
very good
Such an amazing info
email 10 menit
I am very grateful to seen this blog . it’s has lot of tips and knowledge I want to share a company that making amazing products on united state so, you should go here and see amazing products
https://luxurymetalcards.com/c/Brass-Metal-Cards
PG SLOT, slots on the web PG-SLOT.GAME No. 1, the easiest way to play PGSLOT slots, PG games break often, promotions, free credits, PG SLOT GAME, free bonuses every day. available 24 hours
Thank you for sharing this blog !!!
https://armoredandstrong.ae/book-ac-servicing-dubai
Crypto is making huge noise in UAE. Government is also supporting it. It will change lives of many. Crypto and NFT is the way to move forward.
I was scammed by a binary option website iq options to be exact. I lost about $253,000 to them. It was a really hard time for me because that was all i had and they tricked me into investing the money with a guarantee that i will make profit from the investment.. I was referred to laxbtc Recovery, they are recovery specialists for all types of online scams, they helped me in recovering all my lost funds I couldn’t believe at first that this was possible as i have lost all hope in contacting anyone on the internet, but i guess not everyone is bad after all. You can reach them on laxbtcrecovery@gmail.com.
My boss at work noticed how frustrated I was so I had to open up to him about my encounter with scammers. He linked me up with with Phoenix Cyber Corp. and to my greatest surprise my lost funds was recovered directly to my wallet address in less than 24hours. I don’t know how they did it but it was amazing.
Phoenix Cyber Corp. is a financial regulator and fund recovery body. They warned that these types of scams involving bitcoins and other cryptocurrencies or fake merchants are becoming common lately and targeted victims come from different parts of the world.
I am sharing this to save someone out there from a lifetime disaster.
You can contact these group of Ethical Hackers at laxbtcrecovery@gmail.com
Whatsapp: +1 (862) 640-8409
Thanks
Absolutely…very helpful and clear to understand. Brilliant, concise, thank you so much.
thank you for sharing this.
This article is written so well and much useful and informative. Thanks for giving such wonderful informative information. I hope you will publish again such type of post.
Regards
Hello – Good morning – I am very happy and I enjoyed this article, it was really useful for me
I saved it to visit this page again tehc
Megagame online slots No. 1, entrance to play slots, the easiest to break, PG games, often broken, promotions, bonuses and free credits, the highest online slots, must be MEGASLOTGAME.COM , everything goes through an automatic system
. Custom and visual reports can be created and access permissions can also be set based on hierarchy, departments or individuals. Best Reporting Software
There are occupation permits, residence permits, a permanent residency program and even a Premium visa that allows foreign nationals to come to Mauritius to live for a year with very few conditions. We also cover the different ways for foreign nationals to get Mauritian citizenship and decipher the many immigration regulations. We try to answer a few key questions and topics on the subject. Citizenship Of Mauritius
Our meeting rooms are available to hire with a number of different packages, incorporating quality catering produced by our team in the Alpine Kitchen. Capacities range from 5 through to over 200 people and, for larger events, the entire Snozone venue can be exclusively yours for up to 800 delegates! Meeting Rooms Milton Keynes
Ajman University has 9 colleges including the brand new College of Medicine. Currently, AU offers 35 programs – 22 undergraduate and 13 graduate – all of which are accredited by the UAE Ministry of Education. best universities in uae
This essay is extremely well-written and quite useful and instructive. Thank you for providing such valuable information. I’m hoping you’ll write more posts like this in the future.
Regards
Jason S.
tutorial is out of date. can you post the new method. googlesheet not even have tools -> editor anymore 🙁
Founders Mark Carroll and Andreas L. Borgmann were leaving the gym one evening with just one thing on their mind – food. Looking around and racking their brains they were at a loss for option that didn’t involve huge portions. best healthy restaurants in dubai
With its year-round sunshine, superior safety records, world-class facilities from restaurants to shopping malls, pristine beaches and dreamy desert-scapes, Dubai continues to attract tourists from across the globe. best tourist spot in dubai
Migraines are severe throbbing pains that could impose a pulsing sensation in the head. Migraines may result in various symptoms such as nausea, vomiting or even extreme sensitivity to sound or light. Individuals may suffer from Migraine problems for a few hours and sometimes may take days to recover. Severe Migraine can prevent the patient from performing the daily activities. migraine treatment dubai
Very nice post and blog, I found it very explanatory and informative, thank you very much for sharing your knowledge and wisdom with us.
ดาวน์โหลด red tiger
is there a list of what headers we can pull or is this article include the full list? I’d like to pull fees and users from the api? I’m able to pull the prices/supplies/volume/etc per this article but don’t see the documentation on fees/users.
I am really enjoying reading your well written articles. I think you spend numerous effort and time updating your blog
Thank you for sharing this information with us
Good! Thanks for your information!
Learn to log in and exit out of Hulu on your device. If you’re having difficulty with entering your href=”http://www.wwwhulucomactivate.com/”>activate hulu code on a computer, you have the option to log in using your email address and password.
I haven’t read such a good article in a long time.
Very Useful Article. Quite Interesting and Really Awesome
Sound Good. Useful Info for Crypto Data
interesting article about crypto, thanks for share I get many point from the article
Score! Hero APK is a game that probably you are familiar with since it is a game that is broadcast on television. We all used to watch FIFA and watch it now as well when it begins.
If you’re not the biggest huge fan of the popular games Wordle and Heardle perhaps consider the film spin-off Framed. It is described as an game for cinephiles and casual movie fans alike’, it’s a homage to the world-wide popular game of guessing. Each day, there is a every day a brand…
Our expert and highly qualified team of massage therapists is run by Sally Kambuya, who has over 12 years of experience and has helped thousands of clients with traditional and modern techniques and treatments including Thai deep tissue, lymphatic drainage massage and reflexology hand, head and feet treatments. Dubai Massage Home Service
Watch All Mose Chhal Kiye Jaaye Episodes HD High Quality Videos…!
mosechhalkiyejaye.com
Watch All Punyashlok Ahilyabai Episodes HD High Quality Videos…!
punyashlokahlyabai.com
Watch All Swaran Ghar Episodes HD High Quality Videos…!
swaranghaar.com
Watch All Dosti Anokhi Episodes HD High Quality Videos…!
dostianokhii.com
Watch All Yeh Jhuki Jhuki Si Nazar Episodes HD High Quality Videos…!
yehjhukijhukisinaazar.com
Watch Free Anime Online with English Sub and Dub
gogoanimesplay.com
Gogoanime – Watch Popular Anime Series List With English Subtitles
gogoanimelist.net
Gogoanime – Watch Anime Online in HD High Quality for FREE
gogoanimeapp.org
Watch all Korean Drama And Movies At Dramacool and kissasian With English Sub titles
9anime is the best free anime streaming website where you can watch English Subbed and Dubbed anime online. WATCH NOW! No Ads GUARANTEED!
When you need a reliable, temporary email address, you can count on eTempMail website. We provide a safe and secure way to create a temporary email address that you can use for all of your online needs. Whether you’re signing up for a new online service or simply need a disposable email address for one-time use, etempmail.com has you covered.
Nice Information!
Something is not quite right here.
Thank you for letting me post on your blog. I am very happy to share my insight working with DR AMBER because his powerful spell made me financially buoyant that no bill scares me anymore after winning 87 MILLION DOLLARS from the lottery game I played with your sure winning numbers.
Mira Coreano, Chino, Tailandés, Japonés, Estrenos Doramas con subtítulos en español gratis en línea, doramas español online, ver los últimos capítulos de
That’s really delightful to know. You can check out the latest womens suede jackets
Nice Information Good Work Keep it Up! We are sell the best Yellowstone Jacket in USA with Free delivery.
With enormous ducts, groups of distribution grids, or other forms of air-supplying units, this series of midea split duct air conditioners was created to maintain the temperature parameters in vast halls like those in supermarkets, warehouses, airports, and train stations. Indoor units are typically put on the ceiling, in technical spaces like “ventilation rooms,” with fake ceilings, or both.
https://www.ogeneralacs.com/midea-ducted-split-ac/
One of the most important things to do when fixing a bathtub is to check the temperature. If it’s too hot, you can have problems with bacteria and fungus growing in the pipes.
Thanks for Sharing Useful Information. Quite Interesting.
Useful post.Thankyou For Sharing
Nice
Superb
İnat Box Açılmıyor 2022 son zamanlarda inat box kullananlar tarafından oldukça aratılan bir sorun haline geldi. Bu uygulama ücretsiz bir şekilde abonelik isteyen kanalların izlenmesi için kullanılmakta. Uygulamayı kullanırken inat box açılmıyor ve inat box vpn hatası gibi sorunlar karşımıza çıkabiliyor. Yazımızda bu hataların çözümü detaylı bir şekilde açıklanmıştır. Yazımızın sorunu çözdüğünü düşünüyorsanız yorum bırakmayı unutmayınız.
Thanks for sharing
There are many reasons to love shopping for makeup online in Dubai. The city is home to some of the world’s best cosmetic brands, and the selection of products available is truly impressive. makeup online dubai
Thank you blogger for sharing,I love your blog and learned a lot from it.
Nice!!
Thanks for sharing this article! The article is nice. very informative. This app allows you to add some fun features to your dating profile, such as selecting different avatars and adding voice messages. jerkmat mod apk is free app
Thank you Blogger for sharing your blog, I am a huge fan of your blog and have learned lots from it. MrDeepfakes Mod Apk is the best download app based on overall usability. Deepfake technology has great potential in entertainment, gaming, satire, and culture if used responsibly.
This Pacific Rim Breach Wars Mod Apk is a combination of many games so it is perfect for anyone who likes even one of the gaming genres incorporated.
Here i Will Provided All mod games and apps please Clik Here Arashi Adventure MOD APK
Thanks sharing for the amazing information Here I Will Provided All mod games and apps please Clik Here Plato MOD APK
Thanks sharing for the amazing information Here I Will Provided All mod games and apps please Clik Here to Create your iFriend today and get devotion from a dream virtual AI companion. Make progress with the most kindhearted AI chatbot available!