Using CoinGecko API in Sheets to get cryptocurrency price data through a proxy API.
Use ImportXML to get bitcoin's current price in USD:
=ImportXML("https://your-proxy-api/xml/coingecko/simple/price?
ids=bitcoin&vs_currencies=usd", "result/bitcoin/usd")
- Example API on Render free tier, welcome to use as example.
- Example Sheet with the examples from this readme.
Use /xml/coingecko or /value/coingecko to import data from any CoinGecko API route.
For full documentation of endpoints (live OpenAPI) go to https://your-proxy-api/docs (Example on Render)
Use any route on CoinGecko API live docs to create your target path.
Example: Use
/simple/priceto get current bitcoin price in usd:/simple/price?ids=bitcoin&vs_currencies=usd. See the options below for usage in Sheets.
=ImportXML("https://your-proxy-api/xml/coingecko/simple/price?
ids=bitcoin&vs_currencies=usd", "result/bitcoin/usd")Detailed instruction
Xpath expression can be used more easilty since the full XML is directly visible as output of the proxy API.
- Check the proxy API's output XML by going to the proxy URL (e.g.
https://your-proxy-api/xml/coingecko/simple/price?ids=bitcoin&vs_currencies=usdin the browser) - Use XPath syntax to create an XPath expression to extract your data (example:
result/bitcoin/usd)
=ImportXML("https://your-proxy-api/xml/coingecko/simple/price?
ids=bitcoin&vs_currencies=usd&jsonpath=bitcoin.usd","result")Detailed instructions
JSONPath should be preferred because not every valid JSON can be converted into XML (e.g. if some keys start with numbers).
- Check CoinGecko's output JSON by going to the target URL in the browser (example:
https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd). - Use JSONPath syntax to create a JSONPath expression to get to your value (example:
bitcoin.usd).
=ImportDATA("https://your-proxy-api/value/coingecko/simple/price?
ids=bitcoin&vs_currencies=usd&jsonpath=bitcoin.usd")Detailed instructions
ImportDATA is limited to 50 calls per sheet, so should be used in small sheets only.
The /value/coingecko endpoint can be used to return just the value as plain text which allows using ImportDATA Sheets function instead of ImportXML.
Follow the same steps as for JSONPath with ImportXML above, but use a /value/coingecko proxy route and ImportDATA instead of ImportXML.
Use the generic /xml/any or /value/any to import data from any other API URL that returns a JSON. Intead of CoinGecko routes, use the full target URL.
For example, in /xml/coingecko/ example we used:
https://your-proxy-api/xml/coingecko/simple/price?ids=bitcoin&vs_currencies=usd
The generic equivalent would be:
https://your-proxy-api/xml/any/https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd(addjsonpathas needed)
For the API to be accessible from Sheets it needs to be publicly accessible (because Google is making the requests not from your local machine).
This option is best for actual usage (the free tier should be enough). Also best in terms of privacy
Run API locally and expose publicly via ngrok:
This option is best for development or temporary usage (free as well).
Local python option
- Install in local virtual env after cloning:
make install - Run local server:
make server
Docker with local code option
- After cloning:
make docker-server
Docker without cloning repo option
docker run -it --rm -p 9000:9000 artdgn/crypto-sheets-api(or-p 1234:9000to run on different port)
Tunnelling with ngrok
- After setting up an ngrok account and local client:
- Run
/path/to/ngrok http <port-number>to run ngrok (e.g.~/ngrok/ngrok http 9000if ngrok lives in~/ngrok/and you're using the default port of 9000. If you have the local repo, you can also justmake ngrokto run this command.
ImportJSON seems to also work, and doesn't require any external resources (I only found it after I've already implemented this proxy API because initially I found only non-working solutions of that type).
Some other options that didn't work for me (why I've implemented this)
- CRYPTOFINANCE stopped working. In general trying many of the Google App Scripts solutions (like IMPORTJSON or like the updated CRYPTOFINANCE) didn't work for me because of Auth issues.
- Other Google Sheet add-ons like Apipheny were either paid or required API keys (so registration, or additional Yak-Shaving).
Privacy related thoughts
TL;DR: probably best to host your own.
- I don't think there's a way to know which accounts are making any of the requests.
- Hosting your own proxy API (e.g. on Render) is probably the best option since your requests will be visible only to your proxy (and Render).
- Hosting a local proxy API via tunnelling (the "ngrok" option) will mean that requests to CoinGecko (or any other API you're using through this) will come from your machine.
- Using my example deployment means that I can see the request parameters in the logs (but with no idea about the google accounts).
- I've added a more generalised version of this that supports POST requests, and doesn't have crypto-currency related endpoints, or examples. It aims to be useful for any target API, and not specifically for crypto-currency data.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent