Import CoinGecko Cryptocurrency Data into Google Sheets

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!

Leave a reply

3 comments