eloise1988 / coingecko Goto Github PK

Really appreciate your work

Since implementing your V2 changes, the GeckoPrice API call no longer executes before the timeout limit set by Google. After reviewing your Sample Google Sheet, it appears this is happening on your side as well, but let me know if you need any additional data.

Hi Eloise,

Thanks for the share, its a very usefull script. However, I am really new in programming and cannot understand everything.

There is an issue, some of the tickers are the same for different crypto on coingecko such as MDS corresponding to medishare and mdsquare, or NODE for pocket node and whole network.
I was thinking to add a condition to allow the user to enter either the symbole (MDS) or the id (medishare) but I am not sure how to do it, and if it will be applicable to the script.

Cheers,
Flow

I use this script for several months now in 3 spreadsheets, but today all prices "loading..." the hole time in 1 sheet... but in the other two it works like always.
Didnt change anything, no reloads, restarts and no creating the script from scratch helped... someone please has any ideas??

Hello!

Very much appreciate your work. This is a useful tool. I've installed the updated code into the script editor in Sheets. I can't get a clean run. I receive the following error. I'm not a coder so this might be obvious to someone else.

Please advise.

RangeError: Maximum call stack size exceeded
(anonymous)@ Code.gs:1

Is there any way to get the ATH date?

Hello,

I'm confused about how to copy/paste the content of CoinGecko.gs.
I've tried to paste it in the "Script Editor" of my spreadsheet, but it does not compile and outputs some errors.

Thanks for your insights

This is perhaps due to Uniswap V3 release and Coingecko attempted support for V3 pools.

=GECKOPRICE(B15,"USD") where B15 = "UNI" will show a value equal to 0

This worked fine yesterday?

Hi and thanks for this great project!

For some COINS the return value of this functions is empty:

• geckoprice
• gecko24hpricechange
• geckoath

I have tried with these coins: PLANETS, SECO, TULIP, SLIM, SX

Hi Eloise!
Firstly, your script has been fantastic it's been super helpful so thanks heaps for your great work! I did have one question though - how can you correct coins that have duplicate tickers. For example - ROSE, the API seems to be pulling the wrong ROSE even though it's on CoinGecko, do you happen to have a workaround for situations like this?

Thanks!
SJ

Receiving the following error for ticker: IOT

{"error":"Could not find coin with the given id"} (line 0).

Hi,

about the switch from "https://api.coingecko.com/api/v3/search?locale=fr&img_path_only=1"
to "https://api.coingecko.com/api/v3/coins/list

there is still a reference to it for GECKOCHANGE (l.1372) and GECKOLOGO (l.1524)...

Is it important, should it be changed? or can it be left as it is?

(if I change) Is it enough to change that :
".coingecko.com/api/v3/search?locale=fr&img_path_only=1"
--> ".coingecko.com/api/v3/coins/"
or are there other modifications/changes to be done on the code of these parts?

thank you

Eloise - Thank you so much for your much useful work and sharing!

These calls are succeeding sometimes (once or twice in the day) but most of the time they are failing and giving #ERRROR as the result with reason - "Exceeded Maximum Execution time line(0)"

I made a copy of your sample CoinGecko spread sheet and reproduced the same issue in that file also..

Obviously this seem like a network issue and calls are timing out. However, I confirm my network is fine and I am able to access internet just fine while these failures are happening.

Thanks

This isn't really an issue, but if you're interested, I made a repo with some changes to your functions and implemented clasp so you can deploy locally: https://github.com/mabraithwaite/crypto-scripts

When trying to get formula's to work in my own sheet, i get the following:

#ERROR!
Error
SyntaxError: Identifier 'ExpirationInSeconds' has already been declared (line 1)

I'm specifically using =GECKOHISTBYDAY("BTC","USD","PRICE","14-01-2022") but it doesn't seem to matter which one I use.

Hi there :)
I have copied the script of CoinGeckoV2.gs and tried to use it on my own Google Sheets, but keep on getting ERROR tried to use the GECKOHIST function.
(I tried other functions like GECKOPRICE and GECKOVOLUME and they worked fine.)
May I know what the issue would be?

I tried
=GECKOHIST("BTC", "usd", "Volume", "02-02-2022", "09-11-2021")
which returns Error.

and I also tried
=GECKOHIST("ethereum","usd","price",datevalue("12-31-2020"),datevalue("08-31-2020"))
which I saw you posted on another Issues post a while ago.
It returns #ERROR! with the following statement:

Exception: Request failed for https://api.coingecko.com returned code 429. Truncated server response: error code: 1015 (use muteHttpExceptions option to examine full response) (line 1013).

The refresh interval in the documentation doesn't say how often the data is refreshed meaning what is the time interval. If I put in 10 is that 10 minutes, 10 days or 10 weeks?

=GECKOHIST("ethereum","USD","price", H5:H7,false)

H5:H7 is equal to 01-01-2021, 01-02-2021, 01-03-2021

I keep getting an error message when I try to run this formula. I'm trying to set it up where it will post 30 day historical but was using those three dates for example

Hi,

with coingecko2.1, in comparison to V1, I have a problem with the situation of empty cells...

indeed with V2, as seen and discussed on telegram, we sometimes have an empty cell response when we reached the limit of daily requests for the free version of the coingecko api.

this is a problem for me for some data for which there is a "null" answer from the API, meaning "no-data"; that we cannot distinguish from this daily limit...

(so for some data I still use the old v1 version, because it always returns '#error' when the limit is reached; so it's the only way I found to distinguish these 2 states...)

So, if one day you have to touch up this script, it would be great to add a piece of code to distinguish these 2 things/states...

Anyway, a big MERCI for all your work on this script and the API... 😍
Alexis

Just wanted to let you know that the cache is crashing the code when custom functions are called with a long list of tickers.

When you call
GECKOPRICE(ticker_array,defaultVersusCoin)
with a big ticker_array, that will result in big cache key because of
var id_cache=coinList+versusCoinList+'price'
and then
var cached = cache.get(id_cache);
will fail because the cache key is limited to 250 characters as explained here:
https://developers.google.com/apps-script/reference/cache/cache#put(String,String)

Possibles fixes :

• Remove the cache
• Hash the cache key

Hi!

I trying to use GECKOCHART, it works fine, but when I refresh it to update, I gave an error message #NUM. (The result is not a number.)

If I change something, like days, it works again until I refresh it.

Hi thanks for sharing this great script

I having issue for this token's price, is delivering me zero or wrong price.
Jade Protocol JADE
Hector DAO HEC
Town Star TOWN
Atlantis ATLAS
Bombcrypto BCOIN
GalaxyGoggle DAO GG

Can you help me solve this?

Hi Elosie,

My apology for putting this up here again. I don't know where else I can speak to you without rising a 'issue' which technically isn't one.

I have a few suggest for this script, those are good to have but not necessary imo.
Since I am building a sheets to do a checklist of fundamental analysis, some stuff I looking forward to grab data from coingecko automatically is.

1. Number of people watching the project
2. Grabbing the tags in coingecko
3. The network that project is on (such as BSC/ ETH/ MATIC)

I can drop my sheets link here if you are interested in seeing what i am building.

Hi,

I see that this function mentions taking different parameters but every combination is not clear to me.
Most importantly there is mention of an optional 3rd parameter 'refresh cell'. This isn't shown as an option when I enter the formula in a google-sheet, and I'm not sure how it works because in examples it seems to point to empty cells.

I have my sheet set to recalculate values on change, so is it possible to make my sheet automatically re-run GECKO calls every X minutes, or perhaps to add/mock a button-press? If you can give a concrete example this would be appreciated.

I've found a bit of a confusing mixture of crypto/gecko files online and I'm unclear, do I just need coingecko.gs or do I also need CryptoTools.gs, I was initially reading this page: https://medium.com/coinmonks/how-to-value-your-stocks-crypto-portfolio-in-google-sheets-22bb7b42c39d

Good afternoon Eloise,

The following Gsheet function does not seem to fetch the price =GECKOPRICE(B4,"USD") where B4 = "eth2x-fli". It used to work fine prior to the May-2021 release that I have just updated to.

There is perhaps something wrong with the coin caching logic that you have added.

NB: the asset is listed in http://api.charmantadvisory.com/COINGECKOID/json

Not quite sure what is wrong.

I'm using GECKOPRICE and I have set a cell to let me trigger an update on-change.

However, I notice that sometimes cells update value even when I don't modify this cell - it appears GOOGLEFINANCE does similar. Do you know where and how this is controlled? The problem changing a cell is it leads to a very long edit history on my sheet.

CoinGeckoV2.gs commit 2a4cc71d027b1a6f00aee52848c734cccbc5bc37 does not save in a new file in Google App script, fails with syntax error (my file is named gecko.gs):

Missing ; before statement. (line 91, file "gecko")

If I delete the async keyword from all functions the error line moves around (to line 104) but the error remains. Neither line 91 or 104 appear to by missing semicolons and are otherwise complete.

Tried in both the old and new App Scripts setup.

I earned some "Celo" through Coinbase, which assigns it the currency code "CGLD". The GECKOPRICE formula I setup that looks at activity I exported from Coinbase worked for all my other assets, but failed for CGLD. I searched CoinGecko and they list it's currency code as "CELO". Is there a way you could add a way of dealing with this type of mismatch?

Is it possible to include a GECKOTICKERNAME, allowing the ability to reference a cell with the ticker or by typing the ticker, and then produce the coins name?

I import data from Yahoo and currently convert the ticker from "my holdings" as follows:

XLM-USD becomes XLM. It would be great if I could reference the ticker cell, or the ticker code to produce the coin name. I.E, "Stellar".

Loving the work.

Hi,

I am having trouble to get the script to return '0' if no market cap is found.
The reason of doing this is because I am doing some research analysis calculation using the market cap data to determine whether if it's a High cap Project.

How can i do that? I tried doing return pairList.map((pair) => pair[0] && (dict[pair[0]] && (dict[pair[0]] || "") || (defaultValueForMissingData !== null ? defaultValueForMissingData : "0")) || "0");

But no luck on it. Not very familiar with programming, so perhaps someone can help me out ?

Initially it was working, but after I added one more coin whole sheets stopped working and I am getting the above error.

Can you please help me resolving it.

TypeError: Cannot read property 'toUpperCase' of undefined
GECKOPRICE @ CoinGecko.gs.gs:51

Wrong price is delivered when using =GECKOPRICE("THOR";"USD")

The price for https://www.coingecko.com/en/coins/thorswap is delivered instead of https://www.coingecko.com/en/coins/thor

When I do this API request I get the right price: https://api.coingecko.com/api/v3/simple/price?ids=thor&vs_currencies=usd

{
	"thor": {
		"usd": 117.67
	}
}

First, let me say that I love what you have done with this script. I use this functionality every day.

Every coin seems to work except XEC. The price always comes in as Null. I have checked on the CoinGecko API site and it reports the price back correctly, but something in the error handling of the formula doesn't spit out the right value. Can you look into that one?