Import CoinGecko Cryptocurrency Data into Google Sheets

Screenshot_288

With over 4,800 cryptocurrencies and over 330 exchanges tracked, CoinGecko has one of the most comprehensive cryptocurrency databases in the world. We have received several requests for our data to be made available on Google Sheets.

This is a simple 3 step guide to get your portfolio the most updated price, market capitalization, trading volume and all the other information that you will need in your spreadsheet.

Step 1: Add an ImportJSON script to your Google Sheets

In your Google Sheets, go to the tools section and select “<> Script editor”. Once the script editor is open, replace all the existing code with the following code:

/**
* Imports JSON data to your spreadsheet
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(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 “ImportJSON”. Close the script editor and return to your spreadsheet.

Step 2: Find the API endpoint you need

CoinGecko offers a comprehensive set of crypto data in our CoinGecko API. For this exercise, we will be using the /coins/list endpoint to identify the api ID of the specific coins and /coins/markets endpoint to obtain the market data.

To obtain the price of Bitcoin, the /coins/markets endpoint would be:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin

Step 3: Import data using ImportJSON formula and CoinGecko API

The ImportJSON formula:

=ImportJSON (“[API URL]”,”[JsonPath]”

If you are unfamiliar with JsonPath implementation, you can refer to JsonPath’s github which includes comprehensive explanation on JsonPath. Combining the ImportJSON formula and CoinGecko API, you will get the desired data. Below are examples of the formula to obtain Bitcoin data.

Bitcoin price:

=importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.current_price")

Bitcoin market capitalization:

=importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.market_cap")

Bitcoin 24 hours volume:

=importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.total_volume")

Bitcoin All Time High price:

=importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin","0.ath")

Bitcoin circulating supply:

=importJSON("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:

=ImportJSON("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:

=ImportJSON("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:

=ImportJSON("https://api.coingecko.com/api/v3/coins/bitcoin/history?date=30-5-2019","market_data.total_volume.usd")

Using the ImportJSON formula and CoinGecko API, you can easily obtain all the information that you need for your cryptocurrency watchlist or portfolio tracking in Google Sheets. There are other CoinGecko API endpoints available as well which you are free to experiment with.

Here is a sample of our completed Google Sheets for the most popular cryptocurrencies. Feel free to make a copy and modify it for your own use!

Updated 17 August 2020: Some of you prefer seeing your favorite coins in a different manner. We have added a new template to our Google Sheets template. Here is a preview which you can make a copy and edit further.

Additionally, we frequently get questions about the API ID for many tokens. You may refer to this Google Sheets to see the API ID associated with each token listed on CoinGecko, sorted in alphabetically order.

If you would like to import data into Microsoft Excel, Cryptosheets may be a tool that you may use to query CoinGecko’s data.

Share on twitter
Twitter
Share on telegram
Telegram
Share on facebook
Facebook
Share on linkedin
LinkedIn
Share on email
Email

161 thoughts on “Import CoinGecko Cryptocurrency Data into Google Sheets”

    1. I have tried this

      =ImportJSON(“https://api.coingecko.com/api/v3/global”,”total_market_cap.usd”)

      But, the response was:
      Error getting data

    2. 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.

  1. 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.

        1. 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

        2. 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

  2. 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

  3. 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.

  4. 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!

    1. 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?

  5. 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”)?

    1. 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

    2. 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.

  6. 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).

  7. 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!

  8. 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?

  9. 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.

  10. 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?

    1. 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”))

  11. 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?

  12. 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

  13. 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.

    1. 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.

    2. 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.

  14. 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

  15. 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?

    1. 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

    2. 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?

      1. 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

    3. 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

  16. 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!

  17. 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?

    1. 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.

  18. 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.

      1. 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!

  19. 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”)

  20. 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?

  21. 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

  22. 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”)

  23. 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”)

  24. 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.

  25. 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.

  26. 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!

  27. 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!

  28. 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 !

      1. 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

      2. 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?

      3. 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.

    1. 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);
      }

  29. 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”)

    1. 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

    2. 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!

  30. 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

  31. 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?

  32. 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?

  33. 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.

  34. 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”?

  35. 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

    1. 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/

      1. 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.

          1. 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?

  36. 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

  37. 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.

  38. 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?

  39. 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

  40. 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.

  41. 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

  42. Richard Johns

    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

  43. 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.

    1. 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

  44. 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!

  45. 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.

  46. 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.

    1. 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

  47. 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?

  48. 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”;
    }
    }

  49. 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?

  50. 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)

    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

  51. I fixed the ‘Error getting data’ problem by just deleting all the formulas and then doing Ctrl+Z.
    Seems to refresh them.

  52. 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.

  53. 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.

  54. 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

Leave a Comment

Your email address will not be published. Required fields are marked *