Google Sheets and Adding CoinMarketcap API

[elowin]

With all of the activity lately on Coinmarketcap.com, I've started a Google Sheets doc to keep track of some metrics.

For those interested, I've created a script to allow the retrieval of certain information based on CMC's api.

function onEdit(e) {
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}

function search(symbol, myArray) {
for (var i=0; i < myArray.length; i++) {
if (myArray[i].symbol == symbol) {
return myArray[i];
}
}
return myArray[0];
}

function cmc(symbol, key, rand) {
var url = "https://api.coinmarketcap.com/v1/ticker/?convert=USD&limit=70";

var response = UrlFetchApp.fetch(url);
var text = response.getContentText();

var obj_array = JSON.parse(text);
var obj = search(symbol, obj_array);
var value = obj[key];

return parseFloat(value);
}

Add the code above to the script editor under tools and the following function is now available:

=cmc("BTC","price_usd",A1)

The "A1" is needed as the third parameter because Google Sheets is weird with its caching and won't update the API get without that.

Here's a sample of all the Symbols and Descriptions you can get from this function:

    "id": "bitcoin", 
    "name": "Bitcoin", 
    "symbol": "BTC", 
    "rank": "1", 
    "price_usd": "573.137", 
    "price_btc": "1.0", 
    "24h_volume_usd": "72855700.0", 
    "market_cap_usd": "9080883500.0", 
    "available_supply": "15844176.0", 
    "total_supply": "15844176.0", 
    "percent_change_1h": "0.04", 
    "percent_change_24h": "-0.3", 
    "percent_change_7d": "-0.57", 
    "last_updated": "1472762067"

So to get the current total supply of BTC, the function would be:

cmc("BTC","total_supply",A1)

Hope this is useful!

Comments

[cryptoctopus]

really cool! Thanks!

[elowin]

Glad you like it :)

[filcap]

Made a few tweaks, that helped me below. Each lookup is a direct query by id, so it only returns one row. Also if a id isnt found, returns 0, instead of the BITCOIN price.

function search(cmcId, myArray) {
for (var i=0; i < myArray.length; i++) {
if (myArray[i].id == cmcId) {
return myArray[i];
}
}
return 0;
}

function cmc(cmcId, key, rand) {
var url = "https://api.coinmarketcap.com/v1/ticker/" + cmcId + "?convert=USD&limit=0";

var response = UrlFetchApp.fetch(url);
var text = response.getContentText();

var obj_array = JSON.parse(text);
var obj = search(cmcId, obj_array);
var value = obj[key];

return parseFloat(value);
}

[steemitboard]

Congratulations @elowin! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

Award for the number of upvotes received

Click on any badge to view your own Board of Honnor on SteemitBoard.
For more information about SteemitBoard, click here

If you no longer want to receive notifications, reply to this comment with the word STOP

If you want to support the SteemitBoard project, your upvote for this notification is welcome!

[revostrike]

Awesome!

[bobulator]

@elowin its not working for me i just see something strange

[ultimanz]

Thanks Great guide

[sylvaindem]

Thanks for this great post! Quick question, is there any way to automatically reload within the sheet ?

[elowin]

Glad you found it helpful. Yes, if you leave the cell A1 free, and you use the formula =cmc(symbol, key, $A$1) , it should auto reload every time you do something new.

[sylvaindem]

I see yes, I was looking for a way to automatically reload every XX minutes :) probably i need to add some code in the script ;)

[thenike]

This is a great tool. One thing to note that i ran into; If you are having issues with low market cap coins, the part of the script here:
var url = "https://api.coinmarketcap.com/v1/ticker/?convert=USD&limit=70";
limits the coin lookup to the top 70 market cap. You can change that to 100,400, or 800 to your liking to get all coins. Thanks

[bdonovan]

Nice find!

[lawaladekunle]

Thanks this was really helpful.

[bdonovan]

This is great @elowin ! This works for most of my coins except 2; the ones that don't work are: Loopring (LRC) and Kickico (KICK); in these cases, it simply brings up the bitcoin price. Kickico is new (added 3 days ago to CoinMarketCap). Is there some delay when they add coins to the API (but have already added the to the site)? For Loopring, it has been on CMC for ~29 days, not a month yet.

[bdonovan]

I think @thenike 's comment above solved my problem!!

[toan85]

The problem is: If your Sheet has 100 or more cryptocurrencies, then soon the server will block your IP address and your Sheet, because of too many requests, queries, and too many requests queries everytime it reload. My Sheet was blocked.

I think that the solution is: Put the url "https://api.coinmarketcap.com/v1/ticker/?convert=USD&limit=70" or "https://api.coinmarketcap.com/v1/ticker/?limit=100" or "https://api.coinmarketcap.com/v1/ticker/?limit=1000" or whatever you want to local computer cache. After that then read from the local computer cache, and you can have as many queries as you need and the server will not block you.

And Please also put an option to modify how long the function will update and reload, for example "5 minutes, 15 minutes, 1 hour, everyday, and elses". This is a good Script, Thank You!

[diddycarterico]

hey can you elaborate further on this local computer cache stuff?

[toan85]

My solution is save the page https://api.coinmarketcap.com/v1/ticker/?limit=100 or https://api.coinmarketcap.com/v1/ticker/?limit=1000 or whatever number you need to local computer file, plain text file, after that then read from the local computer file, and you can have as many queries as you need (as I estimated a normal use that updates prices several times a day and has some hundreds cryptocurrencies will have up to ~1 million requests queries a day, you think what the sever will do to you.).