Upgrade to use Coinmarketcap v2 API instead of v1 (EOL is Nov '18 for v1)

[LesterCovax]

source

As brought to my attention by @CharlesChow in #46 , the v2 API (Documentation Here) appears to have some major changes, such as curbing the practice of downloading all coin information in one call, as opposed to their preferred method of using pagination and dictionaries/arrays. This will require a bit of work.

[tmlee]

May I suggest considering our API endpoints https://www.coingecko.com/api/docs/v3 from https://www.coingecko.com/en

There are already quite a number of services deploying our API.

For this use case, may I suggest the /coins/markets endpoint with no pagination
eg. https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd

Do let me know if this helps solve some of the problem you guys are facing.

[LesterCovax]

Thanks @tmlee , looks good at first glance and I'll have to take a deeper look at it. I'm not opposed to switching API sources and I think users have even requested in the past to provide access to different API points.

Pretty cool after having received so many Coingecko moon report emails to have you drop by 😀

[tmlee]

@LesterCovax Awesome, hope you are liking the moon reports :)
Alright, do look at them, open to any concerns and suggestions you may. See if we can help to make it as easy as possible for your integration.

[LesterCovax]

@tmlee Hoping to make the switch to Coingecko over the weekend or early next week. The procrastination cannon has now reached full power!

[johndoe312]

I tried using Coingecko but unfortunately, they do not have 1H and 7D percentage changes for coins.
Instead, I tried to use the one provided by Coinstats provided here https://api.coinstats.app/public/v1/coins?skip=0

Unfortunately, I get the 'TypeError: Cannot read property' for whatever datatype I try to use for myCoins and myCoinsCount.

[LesterCovax]

@johndoe312
According to my notes, the Coins endpoint can provide 1H and 7D values. Here's a table from my notes the last time I looked into it along with a dump of URLs / endpoints I made note of:

Google App Script Quotas

https://developers.google.com/apps-script/guides/services/quotas#flexible_quotas_early_access

API Docs

https://www.coingecko.com/api_doc/3.html
https://www.coingecko.com/api/docs/v3#/coins/get_coins_markets
https://coinmarketcap.com/api/

API Endpoints

https://api.coingecko.com/api/v3/coins?order=market_cap_desc&per_page=50&page=1&localization=false&sparkline=true#
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd

Sample API Calls

curl -X GET "https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin%2Cethereum%2Cnano&order=market_cap_desc&sparkline=true" -H "accept: application/json"

curl -X GET "https://api.coingecko.com/api/v3/coins/bitcoin?localization=false&sparkline=true" -H "accept: application/json"

Data Reduction

https://stackoverflow.com/questions/47165247/pagination-when-data-source-is-supporting-multi-page-requesting
https://developers.google.com/apps-script/reference/cache/

Sparklines

https://www.benlcollins.com/spreadsheets/sparklines-in-google-sheets/
https://support.google.com/docs/answer/3093289?hl=en&ref_topic=3105411

[johndoe312]

Thanks for the information. Unfortunately, my skills on the matter are quite limited and it's getting late but I'll give it another shot at a later stage.

[johndoe312]

Looks like I am almost there! The only problem is that when using https://api.coingecko.com/api/v3/coins
I can get the data from within the first set of {} (id etc) but not from the second set (market_data).

Unfortunately, this still would not solve everything since the results are capped and not all coins are shown.

[LesterCovax]

id and market_data are at the same hierarchy, so it should be as simple as using [1] instead of [0]. JSON is nice that way. You can then traverse deeper with something like data[0][1]['usd'] to get the current usd price in market_data. That call takes the first array in the data variable, the second array within that array (market_data), and then calls for the value of the 'usd' key.

There are examples of this at the bottom of the Crypto Sheets script (LINK). I'd recommend installing a JSON viewer browser extension, or at least put it into an IDE or text editor that can pretty-print it for you.

[brian911]

I taught myself javascript and re-wrote a bunch of code for my own purpose. It uses the v2 API, so that's working now. I also moved the variable inputs to a separate sheet called parameters so you don't need to go into the script at all. In the parameters sheet, you can punch in your currency, CoinMarketCap API Key, ethplorer.io API key (freekey works for now), and ETH and NEO wallet addresses. I haven't modified the code for any other cryptocurrencies since I only hold significant amount of ETH and NEO tokens. I also upgraded the code so it pulls in all your token balances on both blockchains into the Wallets sheet.

The rates it pulls into the Rates sheet is based on the coin symbols column in a new Portfolios sheet.

I have no idea how to use Github. What's the best way to share my code??

[zdavis28]

@brian911 will you send it to me?