Due to an increase in demand, you may experience delays with Live Support. If you're having trouble signing in, please view this guide.
Search
Google Script - REST API public endpoints
Our REST API public endpoints are accessible via a simple HTTP request (just as a web page is requested via a web browser), so an API client to import market data into a Google Sheet can be implemented with just a few lines of Google Script code.

Installation

  1. 1
    Create a new Google Sheet or open an existing sheet
  2. 2
    Open the script editor via the Extensions -> Apps Script menu
  3. 3
    Delete the default code that is shown (Select All then Delete/Backspace, for example)
  4. 4
    Copy/Paste the Google Script API code (shown below) into the script editor
  5. 5
    Optional - Add any additional custom functions that you require (to call different endpoints or return different JSON fields, for example)
  6. 6
    Save the Google Script Code via the
    General_SaveIcon_10052020.png
    (Save project) icon

Usage

The KAPI_Public() function is responsible for creating the appropriate URL and making the HTTP request to the API. The KAPI_Public() function can be called directly by entering the following (or similar depending upon the endpoint and the parameters) within a Google Sheet cell:
  • =KAPI_Public("Ticker", "pair=xbtusd,etheur,ltcusd,xdgxbt")
  • =KAPI_Public("Spread", "pair=xbtusd")
  • =KAPI_Public("Depth", "pair=ethusd&count=5")
The KAPI_Public() function returns the original JSON response from the API, such as the following for the above Depth endpoint example:
  • {"error":[],"result":{"XETHZUSD":{"asks":[["231.74000","4.386",1583402326],["231.75000","27.337",1583402277],["231.76000","5.887",1583402311],["231.79000","36.280",1583402334],["231.80000","50.000",1583402299]],"bids":[["231.70000","18.534",1583402335],["231.67000","22.109",1583402335],["231.61000","7.930",1583402335],["231.60000","33.841",1583402335],["231.54000","115.017",1583402334]]}}}
In order to place the individual fields/values into separate cells within the Google Sheet, additional custom functions can be called to parse the JSON response, such as the example KAPI_Ticker() and KAPI_Depth() functions:
  • =KAPI_Ticker("XBTUSD,ETHEUR,LTCUSD,XDGXBT,XDGUSD")
  • =KAPI_Depth("XDGUSD", "5")
which would display the results similar to the following, where the data would be accessible to any of the standard Google Sheets functions (SUM, AVERAGE, COUNT, etc.):
API_GoogleSheetExample_10052020.png

Google Script Code

function KAPI_Public(endpoint, parameters) {
  http_response = UrlFetchApp.fetch('https://api.kraken.com/0/public/' + endpoint + '?' + parameters)
  api_data = http_response.getContentText()
  return api_data
}

function KAPI_Ticker(currency_pairs) {
  api_data = JSON.parse(KAPI_Public("Ticker", "pair=" + currency_pairs))
  api_results = new Array
  for ( name in api_data['result'] ) {
    api_results.push([ name, api_data['result'][name]['a'][0], api_data['result'][name]['a'][2], api_data['result'][name]['b'][0], api_data['result'][name]['b'][2], api_data['result'][name]['c'][0], api_data['result'][name]['c'][1] ])
  }
  return api_results
}

function KAPI_Depth(currency_pair, depth) {
  api_data = JSON.parse(KAPI_Public("Depth", "pair=" + currency_pair + "&count=" + depth))
  api_results = new Array
  for ( count = 0; count < parseInt(depth); count++ ) {
    api_results.push([ api_data['result'][currency_pair]['bids'][count][0], api_data['result'][currency_pair]['bids'][count][1], api_data['result'][currency_pair]['asks'][count][0], api_data['result'][currency_pair]['asks'][count][1] ])
  }
  return api_results
}
The decimal and thousands separators shown in this article may differ from the formats displayed on our trading platforms. Review our article on how we use points and commas for more information.