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.
Contents
- Before You Begin
- Part 1: Fetch Data from Kraken
- Part 2: Create a Custom API URL
- Part 3: 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: Fetch Data from Kraken
The easiest way to get started with the Kraken API is through API Connector’s built-in integration.
- Select Kraken from the drop-down list of applications
- Select an endpoint. These endpoints are all open so you don’t need an API key. For this example we’ll choose the
/public/Ticker
endpoint, which provides ticker information for an asset pair. - Under Request parameters, choose an asset pair for the
pair
parameter - Choose a destination sheet, name your request, and hit Run.
- 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)
- After saving your field names, you won’t need to update them again, they’ll stay in place for all future data refreshes.
Part 2: Create a Custom Request
Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration. When you create a custom request, you add your complete URL into the Request URL field.
Part 3: More Example API URLs
Here are some more examples of custom API URLs. Check the documentation for the full set of available requests.
- Depth (Order Book)
https://api.kraken.com/0/public/Depth?pair=xbteur&count=4
- Trades (Time and Sales)
https://api.kraken.com/0/public/Trades?pair=ltcusd
- Assets
https://api.kraken.com/0/public/Assets
Part 4: API Documentation
Official API documentation: https://www.kraken.com/features/api#public-market-data
Okay so far
Can you explain the 6 data outputs that come through with this API and which is the actual current price of the coin? Trying to pull the current price into another sheet and am an amateur at this.
Hey there, I’m not sure which 6 data outputs you mean, can you please let me know which endpoint you’re using? I just added more information regarding the /ticker endpoint, so maybe that will also help answer your question. In the ticker endpoint, the current price is returned as the first value in the “a” array.
I see your additions. This seems to answer my confusion. Your help is amazing. Thank you so much for keeping data accurate.
Cool, glad that helped 🙂
How is this refreshed?
Hey Jay, there are a few ways to refresh data. You can click the Run or Refresh All Now buttons, or you can set up scheduling (paid feature), or you can hook requests up to the IMPORTAPI custom function to refresh through a checkbox.