Search API Connector Documentation

Print

Import Binance Data to Google Sheets

In this guide, we’ll walk through how to pull cryptocurrency market data from the Binance API directly into Google Sheets, using the API Connector add-on for Sheets. For our first example we’ll access Binance’s open, unauthenticated endpoints, so we don’t need to get an API key.

The Binance API is, in my opinion, a bit confusing because it returns data in currency pairs (e.g. BTCAUD, BTCBBTC, BTCBIDR, BTCBKRW, BTCBRL, BTCBUSD, etc.). Other crypto APIs would just return “BTC”, either in USD or in the conversion currency of your choice. But as long as you’re aware of that, it isn’t too hard to get used to. Also, the nice thing about Binance is that it provides some detailed data that other APIs don’t, like open orders and candlestick chart data.

If you want to skip the details, you can jump right to the end and grab a copy of the Binance crypto tracking template.

BEFORE YOU BEGIN

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

PART 1: CREATE YOUR API REQUEST URL

We’ll first follow the Binance API documentation to access 24 hour price change statistics for all currency pair symbols.

  • API root: https://api.binance.com
  • Endpoint: /api/v3/ticker/24hr

Putting it together, we get the full API Request URL:

https://api.binance.com/api/v3/ticker/24hr

PART 2: PULL BINANCE API DATA INTO SHEETS

Now let’s paste that URL into API Connector.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the Request URL we just created
    binance-img1
  3. Leave the Headers section empty. Binance’s market data endpoints are public, so we don’t need any headers here. We don’t need extra Authentication either so leave that set to None.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see 24 hour rolling window price change statistics for all Binance’s supported currency pairs:
    binance-img2

PART 3: MORE EXAMPLE BINANCE API URLS

Experiment with endpoints and query strings as described in the documentation to see other types of crypto data from the API. If you just want to jump in and get a feel for it, play around with the URLs you enter into the API URL path field. Try the following (one at a time).

  • latest price of BTC in USD
    https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT
    binance-img3
  • recent trade orders for specified currency pair
    https://api.binance.com/api/v3/trades?symbol=BTCUSDT
    binance-img4
  • order book data (list of open orders for specified currency pair). Select the “compact” report style for this one to avoid all the data lining up on a single row.
    https://api.binance.com/api/v3/depth?symbol=BTCUSDT
  • Kline/candlestick chart data. Again select the “compact” report style.
    https://api.binance.com/api/v3/klines?symbol=BTCUSDT&interval=30m
  • historical trades data requires an API key. For this you’ll need an account at Binance. You can retrieve your API key by navigating to the API Management section of your account (located under Settings). Enter your key into the Headers section of API Connector, where Key = X-MBX-APIKEY, and Value = your API key
    https://api.binance.com/api/v3/historicalTrades?symbol=BTCUSDT
    binance-img5
To convert Binance’s timestamp to a human readable date, use the following formula in Sheets:

=({Sheet!Cell}/1000)/86400+date(1970,1,1)

APPENDIX: BINANCE 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:
binance-img7

You can jump right to a copy of the template here (click File > Make a copy to make your own copy). Happy data grabbing!

P.S. For dozens of other step-by-step guides for connecting crypto (and other) APIs to Google Sheets, click here: Knowledge Base

19 thoughts on “Import Binance Data to Google Sheets”

  1. The API works pretty well but also has certain limitations.
    If You have too many cells importing data through API, You will quickly hit the error like this one:

    Exception: Request failed for https://api.binance.com returned code 418.
    Truncated server response: {“code”:-1003,”msg”:”Way too much request weight used; IP banned until 1611528105765.
    Please use the websocket for live updates to avoid bans.”}
    (use muteHttpExceptions option to examine full response)
    (line 127).

    Do you know any way to use websockets from google sheet?

    Reply
    • I don’t, but maybe you can try an API like CoinMarketCap instead, that way you’ll get your own API key. Part of the issue with Binance is that everyone using Google’s IPs is sharing the same public quota.

      Reply
    • What’s your full URL? Please make sure your URL is formed correctly, e.g. multiple query strings should be separated with ampersands, not question marks. I tested https://api.binance.com/api/v3/historicalTrades?symbol=BTCUSDT&limit=1000 and it worked for me, try that and let me know if it fixes your error.

      Reply
  2. thanks man!

    i`m keep getting error code 403 using the google apps script.
    i want to get all the bids & asks from any coin.

    is there any solotion to error 403 ? or there will allways be a problem with binance api using google apps script?

    Reply
    • The Binance faq says I’m receiving an HTTP 403 error when using the API. Why?
      You have violated the WAF (Web Application Firewall) rules, most likely by violating a rate limit or sending a malicious request (as outlined in the limits section of this document). Typically, these bans last a period of 5 minutes.
      So in these cases I think you just need to wait and try again.
      If you continue to have issues, you could also try pulling data from other crypto APIs, e.g. CoinMarketCap or CoinGecko.

      Reply
  3. Good day
    How can I pull in the transactions only for the coins I have, in order to automatically calculate the cost I paid for the coins vs current market price?

    Reply

Leave a Comment