mrwellmann · November 30, 2025 08:05
diff --git a/Kraken API Wrapper.gs b/Kraken API Wrapper.gs
 /** 
 * Functions to get Kraken price for a UTC time.
 * Kraken Market data Api https://docs.kraken.com/rest/#tag/Market-Data
 */

 /**
 * The onOpen() function, is automatically invoked whenever the spreadsheet is opened.
 * 
 * Ipmortant: onOpen() is only called onece if you have another script in connected 
 * to the spreadsheet which uses onOpen() it will be called only ownce.
 * 
 * For more information on using the Spreadsheet API, see https://developers.google.com/apps-script/service_spreadsheet
 */
 function onOpen() {
  AddMenuGetKrakenHistorcPrice();
 }

 /**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the updateSelectedHistoricPriceData() function.
 */
 function AddMenuGetKrakenHistorcPrice() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuItems = [
    {name: 'Update Selected Kraken Historic Price Data', functionName: 'updateSelectedHistoricPriceData'}
  ];
  sheet.addMenu('Kraken Data Menu', menuItems);
 }

 /**
 * Iterates over all selected cells in the selected range and runs GetKrakenHistoricPriceData
 * 
 * krakenTradingPairCell contains the traiding pair. It has to be in the same row at column -1 from the selected cell.
 * dateTimeUTCCell contains the date when the asset was bought. It has to be in same row at -9 from the selected cell.
 */
 function updateSelectedHistoricPriceData() {
  // Get the active sheet in the current Google Sheets document
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  var dataSheet = sheet.getDataRange();
  
  // Get the currently selected range in the sheet
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); 
  var selectedData = range.getValues();

  // Calculate starting indices of the dataSheet and selected range
  var dataSheetStartRow = dataSheet.getRowIndex(); 
  var dataSheetStartCol = dataSheet.getColumnIndex();  
  var rangeStartRow = range.getRowIndex(); 
  var rangeStartCol = range.getColumnIndex(); 

  // Loop through each row and column in the selected range
  for (var i = 0; i < selectedData.length; i++) {
    for (var j = 0; j < selectedData[i].length; j++) {
      // Get the current cell being processed
      var cell = range.getCell(i + 1, j + 1);     
      console.log(`Processing cell: ${cell.getA1Notation()}`);
      
      // Calculate the row and column offset based on the starting cell of the dataSheet and the selected range
      var rowOffset = rangeStartRow - dataSheetStartRow + i + 1;
      var colOffset = rangeStartCol - dataSheetStartCol + j + 1;

      // Get the cells containing the Kraken trading pair and the date from the dataSheet using the calculated offsets
      var krakenTradingPairCell = dataSheet.getCell(rowOffset, colOffset - 1); 
      var dateTimeUTCCell = dataSheet.getCell(rowOffset, colOffset - 9);

      if (krakenTradingPairCell.getValue() != '' && dateTimeUTCCell.getValue() != '') {
        // Get the values of the Kraken trading pair and the date
        var krakenTradingPair = krakenTradingPairCell.getValue();
        var dateTimeUTC = new Date(dateTimeUTCCell.getValue()); 

        var price = GetKrakenHistoricPriceData(dateTimeUTC, krakenTradingPair);

        // Update the cell in the selected range with the fetched price
        cell.setValue(price);
      }
    }
  }
 }

 /**
 * Fetches trade data from Kraken API for the specified trading pair at the given UTC date and time.
 * If no trades are found, it searches for the next earlier time frame with trading data.
 * 
 * @param {Date} dateTimeUTC - The date and time in UTC for which the trade data is to be fetched.
 * @param {String} krakenTradingPair - The trading pair for which the data is to be fetched, e.g., "ETHUSD" for Ethereum to US Dollar.
 * @return {Number} - Returns the price of the first trade fetched from the Kraken API.
 * @throws {Error} - Throws an error if the trade price cannot be parsed or if no trades are found even after multiple attempts.
 **/
 function GetKrakenHistoricPriceData(dateTimeUTC, krakenTradingPair) {
  // Convert the Date object to Unix timestamp
  var unixTimestamp = GetUnixTimeStamp(dateTimeUTC);
  var url, response, json, trades;
  
  // Retry logic with decreasing timestamp
    url = 'https://api.kraken.com/0/public/Trades?pair=' + krakenTradingPair + '&since=' + unixTimestamp.toString() + '&count=1';
    response = UrlFetchApp.fetch(url);
    json = JSON.parse(response.getContentText());
    
    let tradeKey = Object.keys(json.result)[0];
    trades = json.result[tradeKey];
    
    if (trades && trades.length > 0) {
      var firstTrade = trades[0];
      var priceString = firstTrade[0];
      if (priceString) {
        var price = Number(priceString);
        return price;
      } else {
        throw new Error('No match found for the regular expression');
      }
    } else {
      throw new Error('No trades found');
    }
  }

 /**
 * Fetches the last trade closed price from Kraken API for the specified trading pair.
 * 
 * @param {String} krakenTradingPair - The trading pair for which the data is to be fetched, e.g., "XBTUSD" for Bitcoin to US Dollar.
 * @return {Number} - Returns the last trade closed price fetched from the Kraken API.
 * @throws {Error} - Throws an error if no data is found or if the price cannot be parsed.
 **/
 function GetLastTradeClosedPrice(krakenTradingPair) {
  var url = 'https://api.kraken.com/0/public/Ticker?pair=' + krakenTradingPair;
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  
  if(json.error && json.error.length > 0) {
    throw new Error('Error fetching data from Kraken API: ' + json.error.join(', '));
  }
  
  var tickerData = Object.values(json.result)[0];
  if(tickerData) {
    var closedPriceString = tickerData.c[0]; // "c[0]" is the field for the last trade closed price in the Kraken API response
    if(closedPriceString) {
      var closedPrice = Number(closedPriceString);
      return closedPrice;
    }
    else {
      throw new Error('Unable to parse last trade closed price');
    }
  }
  else {
    throw new Error('No data found for the trading pair: ' + krakenTradingPair);
  }
 }

 /**
 * Converts a UTC Date object to Unix timestamp.
 * 
 * @param {Date} dateTimeUTC - The UTC Date object to be converted into Unix timestamp.
 * @return {Number} - Returns the Unix timestamp (seconds since '1970-01-01T00:00:00Z').
 **/
 function GetUnixTimeStamp(dateTimeUTC){
  // Specify the timezone that matches your Google Sheets file
  var timeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();

  // Convert the dateTimeUTC to the timezone of the Google Sheets
  var adjustedDateTime = Utilities.formatDate(dateTimeUTC, timeZone, 'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');
  var adjustedDate = new Date(adjustedDateTime);

  // Convert the Date object to Unix timestamp
  return (adjustedDate.getTime() - new Date('1970-01-01T00:00:00Z').getTime()) / 1000;
 }
	/**
	* Functions to get Kraken price for a UTC time.
	* Kraken Market data Api https://docs.kraken.com/rest/#tag/Market-Data
	*/

	/**
	* The onOpen() function, is automatically invoked whenever the spreadsheet is opened.
	*
	* Ipmortant: onOpen() is only called onece if you have another script in connected
	* to the spreadsheet which uses onOpen() it will be called only ownce.
	*
	* For more information on using the Spreadsheet API, see https://developers.google.com/apps-script/service_spreadsheet
	*/
	function onOpen() {
	AddMenuGetKrakenHistorcPrice();
	}

	/**
	* Adds a custom menu to the active spreadsheet, containing a single menu item
	* for invoking the updateSelectedHistoricPriceData() function.
	*/
	function AddMenuGetKrakenHistorcPrice() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet();
	var menuItems = [
	{name: 'Update Selected Kraken Historic Price Data', functionName: 'updateSelectedHistoricPriceData'}
	];
	sheet.addMenu('Kraken Data Menu', menuItems);
	}

	/**
	* Iterates over all selected cells in the selected range and runs GetKrakenHistoricPriceData
	*
	* krakenTradingPairCell contains the traiding pair. It has to be in the same row at column -1 from the selected cell.
	* dateTimeUTCCell contains the date when the asset was bought. It has to be in same row at -9 from the selected cell.
	*/
	function updateSelectedHistoricPriceData() {
	// Get the active sheet in the current Google Sheets document
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	var dataSheet = sheet.getDataRange();

	// Get the currently selected range in the sheet
	var range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
	var selectedData = range.getValues();

	// Calculate starting indices of the dataSheet and selected range
	var dataSheetStartRow = dataSheet.getRowIndex();
	var dataSheetStartCol = dataSheet.getColumnIndex();
	var rangeStartRow = range.getRowIndex();
	var rangeStartCol = range.getColumnIndex();

	// Loop through each row and column in the selected range
	for (var i = 0; i < selectedData.length; i++) {
	for (var j = 0; j < selectedData[i].length; j++) {
	// Get the current cell being processed
	var cell = range.getCell(i + 1, j + 1);
	console.log(`Processing cell: ${cell.getA1Notation()}`);

	// Calculate the row and column offset based on the starting cell of the dataSheet and the selected range
	var rowOffset = rangeStartRow - dataSheetStartRow + i + 1;
	var colOffset = rangeStartCol - dataSheetStartCol + j + 1;

	// Get the cells containing the Kraken trading pair and the date from the dataSheet using the calculated offsets
	var krakenTradingPairCell = dataSheet.getCell(rowOffset, colOffset - 1);
	var dateTimeUTCCell = dataSheet.getCell(rowOffset, colOffset - 9);

	if (krakenTradingPairCell.getValue() != '' && dateTimeUTCCell.getValue() != '') {
	// Get the values of the Kraken trading pair and the date
	var krakenTradingPair = krakenTradingPairCell.getValue();
	var dateTimeUTC = new Date(dateTimeUTCCell.getValue());

	var price = GetKrakenHistoricPriceData(dateTimeUTC, krakenTradingPair);

	// Update the cell in the selected range with the fetched price
	cell.setValue(price);
	}
	}
	}
	}

	/**
	* Fetches trade data from Kraken API for the specified trading pair at the given UTC date and time.
	* If no trades are found, it searches for the next earlier time frame with trading data.
	*
	* @param {Date} dateTimeUTC - The date and time in UTC for which the trade data is to be fetched.
	* @param {String} krakenTradingPair - The trading pair for which the data is to be fetched, e.g., "ETHUSD" for Ethereum to US Dollar.
	* @return {Number} - Returns the price of the first trade fetched from the Kraken API.
	* @throws {Error} - Throws an error if the trade price cannot be parsed or if no trades are found even after multiple attempts.
	**/
	function GetKrakenHistoricPriceData(dateTimeUTC, krakenTradingPair) {
	// Convert the Date object to Unix timestamp
	var unixTimestamp = GetUnixTimeStamp(dateTimeUTC);
	var url, response, json, trades;

	// Retry logic with decreasing timestamp
	url = 'https://api.kraken.com/0/public/Trades?pair=' + krakenTradingPair + '&since=' + unixTimestamp.toString() + '&count=1';
	response = UrlFetchApp.fetch(url);
	json = JSON.parse(response.getContentText());

	let tradeKey = Object.keys(json.result)[0];
	trades = json.result[tradeKey];

	if (trades && trades.length > 0) {
	var firstTrade = trades[0];
	var priceString = firstTrade[0];
	if (priceString) {
	var price = Number(priceString);
	return price;
	} else {
	throw new Error('No match found for the regular expression');
	}
	} else {
	throw new Error('No trades found');
	}
	}

	/**
	* Fetches the last trade closed price from Kraken API for the specified trading pair.
	*
	* @param {String} krakenTradingPair - The trading pair for which the data is to be fetched, e.g., "XBTUSD" for Bitcoin to US Dollar.
	* @return {Number} - Returns the last trade closed price fetched from the Kraken API.
	* @throws {Error} - Throws an error if no data is found or if the price cannot be parsed.
	**/
	function GetLastTradeClosedPrice(krakenTradingPair) {
	var url = 'https://api.kraken.com/0/public/Ticker?pair=' + krakenTradingPair;
	var response = UrlFetchApp.fetch(url);
	var json = JSON.parse(response.getContentText());

	if(json.error && json.error.length > 0) {
	throw new Error('Error fetching data from Kraken API: ' + json.error.join(', '));
	}

	var tickerData = Object.values(json.result)[0];
	if(tickerData) {
	var closedPriceString = tickerData.c[0]; // "c[0]" is the field for the last trade closed price in the Kraken API response
	if(closedPriceString) {
	var closedPrice = Number(closedPriceString);
	return closedPrice;
	}
	else {
	throw new Error('Unable to parse last trade closed price');
	}
	}
	else {
	throw new Error('No data found for the trading pair: ' + krakenTradingPair);
	}
	}

	/**
	* Converts a UTC Date object to Unix timestamp.
	*
	* @param {Date} dateTimeUTC - The UTC Date object to be converted into Unix timestamp.
	* @return {Number} - Returns the Unix timestamp (seconds since '1970-01-01T00:00:00Z').
	**/
	function GetUnixTimeStamp(dateTimeUTC){
	// Specify the timezone that matches your Google Sheets file
	var timeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();

	// Convert the dateTimeUTC to the timezone of the Google Sheets
	var adjustedDateTime = Utilities.formatDate(dateTimeUTC, timeZone, 'yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');
	var adjustedDate = new Date(adjustedDateTime);

	// Convert the Date object to Unix timestamp
	return (adjustedDate.getTime() - new Date('1970-01-01T00:00:00Z').getTime()) / 1000;
	}
No results found