Search API Connector Documentation
Import Kraken Data to Google Sheets
In this guide, we’ll walk through how to pull public market and cryptocurrency data from the Kraken API directly into Google Sheets, using the API Connector add-on for Sheets. Kraken is an trading exchange for Bitcoin and other cryptocurrency.
The output for Kraken isn’t very well structured for a tabular format like Sheets, and their naming conventions are (in my opinion) unnecessarily cryptic, so you will need to restructure the data after you pull it into Sheets. But with that said, it can still be a nice alternative to more popular crypto APIs like CoinGecko, since it is more reliable and provides a lot of detailed data points.
- Before You Begin
- Part 1: Create your API Request URL
- Part 2: Pull Kraken API Data into Sheets
- Part 3: More Example API URLs
- Part 4: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Create Your Kraken API Request URL
We’ll follow the Kraken API documentation to access the latest Bitcoin-USD crypto quotes.
- API root: https://api.kraken.com/
- Endpoint: /0/public/Ticker
- Query Strings: ?pair=xbtusd
Putting it together, we get the full API Request URL:
Note that these market data endpoints are publicly available (a Kraken account is not required).
Part 2: Pull Kraken API Data into Sheets
We can now enter our URL into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create screen, enter the Request URL we just created
- We don’t need any OAuth authentication, so leave that set to None.
- This is a free, public API so we don’t need to enter any headers either. Just leave this section blank.
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see the latest ticker data for XBT (Bitcoin) and USD in your Google Sheet:
- The output here is pretty confusing, so we need to refer to the documentation to know what it means:
Basically, the output contains the currency pair you selected, and then letters representing the fields you see above (a = ask array, b = bid array, c = last trade closed aray, v = volume array, and so on).
- Since these field names use individual letters instead of names, we need to rename those fields to make it meaningful. To rename your fields, open the visual field editor by clicking Edit Fields, then click the blue pencil in each header and enter a new name for each column (for example: ask_price, ask_whole_lot_volume, ask_lot_volume,bid_price, bid_whole_lot_volume, ask_lot_volume, last_trade_price, last_trade_lot_volume, volume_today, volume_last_24h, volume_weighted_avg_price_today, volume_weighted_avg_price_24h, numer_trades_today, number_trades_last_24h, low_today, low_last_24h, high_today, high_last_24h, today_opening_price)
Part 3: More Example API URLs
Check the documentation for a full list of endpoints, but if you just want to jump in you can try the following URLs, one at a time:
- Depth (Order Book)
- Trades (Time and Sales)
Part 4: API Documentation
Official API documentation: https://www.kraken.com/features/api#public-market-data