Search API Connector Documentation

Print

Import CoinGecko Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull cryptocurrency market data from the CoinGecko API directly into Google Sheets, using the API Connector add-on for Sheets. 

The cool thing about CoinGecko is that they provide a huge amount of well-organized crypto data, including some unique API endpoints like most-searched coins. This tutorial will show how to get an API key before fetching data.

Mixed Analytics users can currently enjoy 1 month complimentary access to the CoinGecko Analyst API by subscribing with the offer code MIXEDANALYTICS.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your CoinGecko API Key

  1. To get started, you'll need a CoinGecko account, so, if you haven't already, create an account and log in to coingecko.com.
    coingecko-login
  2. Once you're logged in, navigate to https://www.coingecko.com/en/api/pricing.
  3. To subscribe to a paid API plan, click one of the Upgrade buttons in the pricing table. Alternatively, get a free API key by clicking Create Demo Account underneath the pricing table.
    coingecko-upgrade
  4. If you select a paid plan, you'll see the Billing Info form on the left (use the MIXEDANALYTICS offer code for a free month of the Analyst plan). If you are creating a free demo account, you'll see the form on the right instead.
    coingecko-paymentOrDemo
  5. Either way, once your account is set up, navigate to the Developer dashboard and click +Add New Key
    coingecko-addkey
  6. You'll be prompted to label your key and click Create
    coingecko-createkey
  7. Your API key will now be listed on the page. Copy this key and keep it safe as we'll use it shortly!
    coingecko-copykey

Part 2: Pull CoinGecko API Data into Sheets

The easiest way to get started with the CoinGecko API is through API Connector’s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. If you're using a free API key through a Demo Account, select CoinGecko from the drop-down list of applications. If you are using a paid CoinGecko API plan, select CoinGecko Pro. The CoinGecko Pro application contains some unique endpoints that aren't accessible to free users.
    coingecko-menu
  3. Under Authorization, enter your API key.
    coingecko-authorization
  4. Choose an endpoint. For example, select /coins/markets, which is the endpoint for fetching the latest market data.
    coingecko-endpoints
  5. In the parameters section, select which "vs_currency" you'd like to use.
  6. Optionally select other parameters, e.g. select specific coin IDs or set per_page to 250 to get more than 100 records.
  7. Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
    coingecko-response
  8. Optionally open the field editor to filter out any unnecessary fields from your report.

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the API URLs shown in the API documentation. Here's an example request setup:

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following. If you're using CoinGecko's paid API, change the base URL to https://pro-api.coingecko.com/api/v3/, and the header key to x-cg-pro-api-key.
    • Application: Custom
    • Method: GET
    • Request URL: https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd
    • Headers:
      • x-cg-demo-api-key: your_key
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Name your request and click Run. A moment later you’ll see a list of coins populate your sheet.

Part 4: Handle Pagination

  1. For several endpoints, CoinGecko limits the number of records returned in each response. By default, only 100 records will be returned unless you set the per_page parameter to 250:
    coingecko-img5
  2. If you need more than 250 records, you can loop through multiple pages automatically with page parameter pagination handling (paid feature), like this:
    • Pagination type: page parameter
    • Page parameter: page
    • Run until: choose when to stop running the request
      pagination-page-parameter

Part 5: CoinGecko Limits

Update: As of October 2023, CoinGecko now supports API keys. You can now get your own personal API key to avoid running into shared rate limits!

CoinGecko does allow access to their API without a key. However, if you run requests through Google Sheets without an API key, you'll probably come across error messages like these:

  • API server responded with an error (429), error code: 1015
  • API server responded with an error (403): error code: 1020

This is because CoinGecko applies rate limits, as shown in their terms & conditions.

When you run your requests through API Connector / Google Sheets, you’re more likely to hit these rate limits because all requests running through Google Sheets share the same pool of IP addresses from Google’s servers. Therefore, get a key first to avoid running into issues.

Part 6: API Documentation

Official API documentation: https://www.coingecko.com/en/api/documentation

Appendix: CoinGecko Template

In this template, everything is configured for you to simply type in whatever coins you’re interested in and get a dashboard like below:
coingecko-img4

You can jump right to a copy of the template here. Happy data grabbing!

98 thoughts on “Import CoinGecko Data to Google Sheets”

  1. Hey so i did this but it seems like it doesn't keep up with the price fluctuations and it just stays at the original price when i set up the API, do you know the reason why this is happening?

    Reply

    • Hi there, the sheet doesn't automatically pull in new data from the API. To get fresh data, you need to click Run or set up scheduling. The IMPORTAPI custom function also has some auto-refresh functionality. Let me know if anything isn't clear and I'll be happy to explain further.

      Reply

      • Once I refresh the data, the sequence in which the data is downloaded is not always the same. For example, if I use the code https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=ethereum,bitcoin,simple-token,ripple,litecoin,stratis,waves,litecoin-cash,holotoken,celo-gold

        If I add another 10-20 tokens to the end of that code, the list appears in a different sequence each time. This makes it difficult to use this data in other sheets (since the reference cell no longer points to the correct token)

      • Hey Nik, when you make a spreadsheet you usually don't want to reference cells directly, since it's pretty normal for source data to move around. You can use a function like VLOOKUP so the data stays in place regardless of its exact location in the list. With VLOOKUP you set the name of the coin as your lookup value, then set your sheets to read from that, rather than having them look for a fixed cell location.

    • Hey there, this is due to the way they've structured their response data. If you check it in an API tool that returns the raw JSON (e.g. Swagger), you'll notice that the response from CoinGecko uses named objects where normally you would see arrays. This prevents API Connector from recognizing where to split the data into columns vs. rows when converting the JSON response into Sheets format.
      In any case, to answer your question, you can use JMESPath, which is a very useful query language that allows you to filter and restructure the response data. The query rates.* removes the object names so that the response can be parsed correctly. I added a screenshot of this to the article.
      One other option is to create a second sheet containing a Sheets function like =transpose(Exchange_rates!1:2) to convert the response from one long row to one long column. I don't think it's a great solution but it's still a bit better than the default response.

      Reply

  5. Hi Ana

    I came up with 3 solutions for this problem.

    Solution 1 & 2 require you to enter the formula in the first Row of 4 cells. Then drag the formula down in this case for 61 Rows since there are 61 rates returned by the API (a horizontal row of 4x61=244 cells)

    Solution 3 returns the whole table,
    after you enter the formula in the top left cell where you want the table

    Solution 1:

    =INDEX($7:$7,(ROW()-11)*4+COLUMN())

    Solution 2:

    =OFFSET(A$7,0,(ROW()-11)*4)

    Solution 3:

    Note this assumes the API data is returned on Row A7:7 - just change that to suit your Row.

    =arrayformula( query( query( iferror( if( {1,1,0}, floor( mod(row(A:A)-{1,1},{9^9, 4}), {4,1} ), transpose( split( regexreplace( query( transpose( query( transpose(A7:7 & char(9)), "", 9^9 ) ), "", 9^9 ), "\s+$", "" ), char(9) & " ", ) ) ) ), "select max(Col3) where Col3 '' group by Col1 pivot Col2", 0 ), "offset 1", 0 ) )

    Solution 3 is care of the experts at Docs Editors Help.

    Reply

  6. In the message above showing Solution 3 I was using the following
    URL Path = https://api.coingecko.com/api/v3/exchange_rates

    To use the Solution 3 above you need two Google Sheets
    1 - Results Sheet (This is where the table will display)
    2 - Data Sheet (This is where the API writes its ouput)

    Also where you see the function - transpose(A7:7 - this shows my data was being read from row7 - BUT -

    You should point to the sheet holding your Data with - transpose('Data'!A2:2 & char(9)

    Results Sheet
    =============
    Col A Col B Col C Col D
    Row(1) rates » btc » name rates » btc » unit rates » btc » value rates » btc » type
    Row(2)

    Data Sheet
    ==========
    This is where the API Connctor will put the data.
    1 - URL Path = https://api.coingecko.com/api/v3/exchange_rates
    2 - Output Option = Overwrite
    This means the data will always be written on to Row(2) starting with Cell A2

    Reply

  7. Is it possible to use the template but expand it to go beyond just 30 inputs? (I have around 100 coins I want to track). Can anyone help with what changes I would need to make?

    Thanks

    Reply

    • Should be possible, just do the following:
      1) in the Inputs tab, add in as many rows as you want
      2) in the cells Inputs!D5 and Summary!B8, change the formulas to include the new rows you added

      Reply

  8. Hi there!

    I'm using this code:
    https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum,litecoin,aave,morpheus-network,sparkpoint,zcash,0x&vs_currencies=usd

    and for some reason it doesn't pull zcash or 0x - I found some other ids didn't pull as well. Any tips? Thanks!

    Reply

      • 429 errors mean you're hitting their rate limits. The rate limit for the CoinGecko API is 10 calls each second per IP address. Unfortunately it's easy to run into these limits, since all Sheets calls are routed through Google's IP address pool, meaning other people are using the same resources. It should work if you wait a minute and try again.
        Update: you can now avoid this by getting a CoinGecko API key .

      • Good question, I checked and I think it's because this request sheet is set up to look for the ID rather than the symbol. In this case, the ID is "republic-protocol" while the symbol is "ren". In many cases the ID and symbol are the same, so that's why this problem isn't always evident. I'll try to make that more clear.

  11. This may be the most useful article I've ever found online! I was looking at the CoinGecko page on importing to Google Sheets and was getting nowhere. This is super-clear and the example spreadsheet has taken all of the work out of it. Thanks.

    Reply

  12. Hi,

    Thank you for the template, very helpful!
    I'm looking for some workaround to fit my need. I don't understand why the "Summary" lines order is not based on the "Data" one. Each time I refresh, the order change in "Data" in relation to the market cap rank.

    How can I set up that for "Summary"?

    Thank you for your insights.

    Reply

    • The order of the Summary items comes from the Inputs tab. You can see the formula in the Summary!B8 cell.
      As you note, the order for the Data tab is set by CoinGecko, and by default is based on descending order of market cap size.
      I'm not totally sure what you're looking to do, but if you want your data sorted by market cap, then you can just use the Data sheet, or make your own summary table that isn't based on the Inputs tab.

      Reply

      • Thanks. I managed to do it with the Vlookup trick

        Any idea how to keep tracking my portfolio performance monthly/annually?

      • If you have a list of all your coins and their values, you could copy/paste that at the start of each month into a new column, to keep a record. My other (free) add-on Archive Data (link) automates that, you can check if it helps.

  13. Hey,
    somehow it does not work for me. I always get an "ERROR" in the cell.
    I really don`t know why? Maybe you have an idea?

    Cheers
    Lars

    Reply

    • Can you please say a little more about your request setup? Are you running your request from the sidebar or via the IMPORTAPI custom function? Where are you seeing "ERROR", is there any more info returned when you hover over the cell?

      Reply

  14. Hi Ana, thanks for all your work with this great plugin, I was previously using json coingecko method and whilst it worked sometimes, as my portfolio grew, the scripts became less reliable.

    REFRESH BUTTON
    One thing I'm still trying to solve, is the ability to run your API via a REFRESH button on my portfolio sheet and referencing my API coingecko data from another sheet, this was a setup I made with the JSON script and worked ok.

    I have seen you mention IMPORTAPI but I'm unsure how to trigger this from the script function on the button, I suspect this might need some custom script adding as code.gs where the json was previously parked? Any help would be appreciated.

    Here's a link to the sample sheet with the setup:
    https://docs.google.com/spreadsheets/d/1bJ5x7bqBVN4ngPcYWQTNqVqttatt5hA6XUtKp9ux-dM/edit?usp=sharing

    Would you also know how to use the sparklne data for each coin in the sheet using coingecko's import, i have the sparkline data imported but google sheets removed support for [SPARKLINE=]

    Reply

    • Hey Fagin, thanks for the message. If you want to use IMPORTAPI, you would just set up your request in the API Connector sidebar. Then, if your request is named, say, Get Coins, you’d enter =IMPORTAPI("Get Coins") into any cell to run that request directly in your sheet. You can also run this request on demand by checking a box, this article has some examples of that (check the section called "Fast Cell-Based Refresh").
      I’m not really sure what you mean about adding a custom script. I checked your sheet – it looks great – but I wasn’t sure what part you wanted me to look at (and without Edit access I can’t see your API Connector requests). So feel free to message back if you’d like to clarify anything further.

      Reply

  15. This Article is awesome!!! i'm now trying a way to adapt your template so it pulls historical prices at a certain day. Do you know if that's possible?

    Reply

    • Thank you for the comment! You can use the 'history' endpoint to get historical prices. You would need to run a different request for each coin you're looking to pull, though, like this. Please check and see if it works for you.
      https://api.coingecko.com/api/v3/coins/bitcoin/history?date=30-12-2018

      Reply

  16. Hey!
    Great application. I got a question though. Is it possible that some APIs (like in my case for the StormX coin) are not working?

    https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=stormx

    Is the command i used but the connector says "no records found".

    Any Ideas?

    Reply

    • Hey Jonas, the problem here is that 'stormx' is the coin name, not the ID. Please replace 'stormx' with 'storm' and it should work. You can see a full list of coin names, IDs, and symbols by making a request to https://api.coingecko.com/api/v3/coins/list

      Reply

  17. Is there a volume to get data on 24h volume change? I see market cap change; would be nice to get volume changes as well?

    Reply

  18. Thanks for useful topic. I have one question. I want to know how to get latest import token or coin on coingecko ? Can you have any idea for this?

    Reply

  20. I get the following error when making a request. Rarely the request works but most of the times I get this error. Any help is appreciated

    809: unexpected token at '
    !--[if lt IE 7]> <![endif]-->
    !--[if IE 7]> <![endif]-->
    !--[if IE 8]> <![endif]-->
    !--[if gt IE 8]>>--> <!--

    Access denied | api.coingecko.com used Cloudflare to restrict access

    Reply

    • Hmm I haven't come across this specific error before but CoinGecko writes about using Cloudflare here. On that page they write "However, occasionally there can be false positive situation where legitimate users may accidentally get flagged by Cloudflare. This can due to our security settings being too tight or incorrectly setup. It might also be because the user is making requests from an IP address pool that is deemed to be suspicious."

      So it looks like they've erroneously flagged your request. You might need to contact their support or use a different crypto API that gives you a private key.

      Reply

    • Sorry, I don't really understand your question, are you saying your requests work through a desktop browser but not through mobile, or something else?

      Reply

      • You mean you can't open the add-on? The mobile version of Google Sheets doesn't support add-ons for all phones, unfortunately we can't do anything about that.

  23. Hey Ana, thanks for the tutorial. Quick question - I would like to include the 24H price change AND the 7 days price change of let's say BTC. How should I change the following path?

    https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd&include_24hr_change=true

    Reply

    • Hi there, their /simple/price endpoint doesn't support 7 days price change parameter. You can see all the parameters they support here: https://www.coingecko.com/en/api/documentation

      Based on that, only the /coins/markets endpoint enables 7 days price change, so you'd make your URL like this:
      https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin&order=market_cap_desc&per_page=100&page=1&sparkline=false&price_change_percentage=24h,7d

      Reply

  24. Hey love this tool. It's pretty much perfect except it doesn't seem to refresh data very much. After successfully refreshing once, I only get this error:

    Data: Request failed: Service invoked too many times for one day: urlfetch.

    Has Coingecko throttled the API or something recently?

    Thanks so much for this!

    Reply

    • Hey Rick, that's an error message from Google Sheets itself, not from API Connector or CoinGecko. Google Sheets has a daily limit of 20,000 URL fetches (which includes API calls as well as functions like GOOGLEFINANCE). So it sounds like you're running a massive number of queries and hitting Google's limits. Assuming this isn't on purpose, you probably need to reconfigure your setup so it doesn't refresh so often. If you're using the IMPORTAPI function, please check this article, the section on Excess Recalculation has some suggestions for avoiding the urlfetch error.

      Reply

      • Thanks. It was just using the API connector template form this article. I don't get what I could be doing wrong. Even today it still gives the error. Hmmm... I'll keep trying I guess.

      • That's strange, the template can't possibly run anywhere near 20K requests a day so I don't think it's related to that.
        Could you be running any other scripts or add-ons, or "urlfetch" functions like IMPORTRANGE or GOOGLEFINANCE?
        If you're not sure, please check https://script.google.com/home/executions to see which scripts are running under your Google account.

  26. hi Ana,
    thanks for the tutorial! however, I am wondering if it's possible to pull the market cap of the coins at a specified time on a date? ex. I'd like to know the market cap of all coins on my list at 2pm on 2. Sep 2018.

    Reply

    • Hey, you can see all the data points provided by CoinGecko here: https://www.coingecko.com/en/api/documentation
      The /coins/{id}/history endpoint gives you historical data (name, price, market cap) for a given date, so you could run a request like https://api.coingecko.com/api/v3/coins/bitcoin/history?date=02-09-2018
      They don't let you specify an hour though, so you might need to check out different crypto APIs for that.

      Reply

  27. Hi Ana

    How can I get coin details using (coin_id )number
    For example this coin :

    {"id":"antimatter","coin_id":14112,"name":"AntiMatter","symbol":"MATTER"

    So if I use (id) its works fine
    https://api.coingecko.com/api/v3/coins/antimatter

    but I want to get same details using ( coin_id":14112 )

    Thanks

    Reply

  28. Hello Ana,

    I used the template provided. Now I'm wondering if there is an easy way to change the currency of all the coins that are already loaded in there?

    Thanks!

    Reply

    • Hey Konstantin, sure, just open up the API Connector sidebar, and edit the request called Data. It contains a URL like https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=+++Inputs!D8+++. To change that to, say, euros you can edit that to look like this instead: https://api.coingecko.com/api/v3/coins/markets?vs_currency=eur&ids=+++Inputs!D8+++

      Reply

      • Hey Ana,

        thank you for your quick reply. It worked for a minute but then I'm getting this error message:
        "We received an error from the API server (429) show response
        error code: 1015"

        Do you know how I could resolve this?

        Thanks!

      • Yep, unfortunately rate limits are pretty common with CoinGecko. Just wait and try again later, you can see some more info about it in the "limits" section above.

  29. - How to remove the header from my requests to avoid situations like this: https://i.imgur.com/3zGGPph.png?

    - For the situation above I'm doing 2 different requests because if I do it in the same request the tokens are ordered based on market cap (e.g. BTC --> ETH --> BNB), how can I maintain a custom order (e.g. BNB --> BTC --> ETH)?

    Reply

    • If you use "append" mode the headers won't print out. But as for maintaining an order, I would just create a second sheet where you list the coins in the order you want, and then do a VLOOKUP to pull in the values from your data sheet. Then you can maintain your order regardless of how the API sends back the data.

      Reply

  30. Hi Ana, thanks for the tutorial. I'm wondering if there is a way to pull random currency prices from coingecko with an API regardless of their rank? Thanks in advance

    Reply

    • I'm not really sure what you mean by random, do you mean get the price of a random coin? I don't think CoinGecko has any endpoint for that, you can either get all the coins or you can get coins that you specify. I suppose you could pull all the coins and then pick a random number yourself (maybe with a Sheets function) and then grab the associated coin, would that work?

      Reply

  31. Hola, hay alguna manera de actualizar la información del fichero añadiendo un boton"update" asociado a una macro?

    y segunda cuestión, se puede generar automaticamente ficheros "summary" historicos, correspondientes al número de veces que hayas actualizado el fichero original?

    gracias!!

    Reply

  32. Hello, is there any way to update the file information by adding an "update" button associated to a macro?

    and second question, is it possible to automatically generate historical "summary" files, corresponding to the number of times you have updated the original file?

    Thank you!

    Reply

    • Hey Manuel, you can look into our IMPORTAPI function, which lets you update data by clicking a checkbox (check the section called "Fast cell-based refresh").
      As for summary files, I'm not totally sure what you're looking for, but my other (totally free) add-on Archive Data lets you automatically store historical data in your sheet, maybe that's what you're looking for?

      Reply

    • Sure, but I'd use a different API than CoinGecko for this since they don't provide this data.
      I like Coinpaprika, you can get a year's worth of open/high/low/close data at a time like this: https://api.coinpaprika.com/v1/coins/btc-bitcoin/ohlcv/historical?start=2018-02-15&limit=366

      Reply

    • Do you mean total market cap of all crypto on that date? I don't think CoinGecko provides that data, you can check for yourself here. If you're just looking for the value of specific coins, you can use their /coins/{id}/history?date=31-12-2021 endpoint.

      Reply

    • Sorry, I'm not sure what you mean by Bitcoin dominance. You can see all of CoinGecko's data points here, does that contain what you're looking for?

      Reply

    • Do you mean list all coins? This is the URL for coins: https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd But they use pagination so you need to make a few requests to get all the data.

      Reply

  38. Hi there. How I could get the ATH (e.g. BITCOIN) from a date range ?
    It is possible to get the ATH from between 01.01.2023 and 31.01.2023 ?

    Reply

    • I don't see ATH as one of CoinGecko's available data points in their docs, but you can use their /market_chart/range endpoint to get daily prices between a date range like this: https://api.coingecko.com/api/v3/coins/bitcoin/market_chart/range?vs_currency=usd&from=1680507951&to=1683099951

      Reply

Leave a Comment

Jump To