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!

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

25 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

  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.

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

  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?

  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?

Leave a Comment

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