GoogleSheets Token Price AppScript to streamline TurboTax CSV

README.md

Why this script?

If you have sales from an unsupported token, you need to know the price of a token during the time of trade/sale/transaction.

This uses the coindesk API so you'll need to get an API key here.

AppScript

Import

In Google Sheets, click:

1. Extensions
2. Apps Script
3. Copy past coindesk_crypto_app_script.js as a new file
4. Use it like so in a cell:

   = getCryptoPriceCoindesk("ETH:USD", "2024-01-01")

5. I've also created getScriptVersion to verify that the code gets updated. Use it like so:

   =getScriptVersion()
   

TurboTax Resources

TurboTax is getting much better with support for Crypto sales & transactions:

1. How do I upload a CSV file of my crypto transactions?
2. How do I download a CSV file from Coinbase?
3. How do I create a CSV file for an unsupported crypto source?

coindesk_crypto_app_script.js

function getScriptVersion() {
  return "1.6.1"; // Update this when you change your code
}

function getCryptoPriceCoindesk(pair, date) {
  // CoinDesk MIN API key
  const API_KEY = "UPDATE ME";
  
  if (!pair || typeof pair !== "string" || !pair.includes(":")) {
    return "Invalid input format. Use 'crypto:currency'";
  }

  var [crypto, currency] = pair.split(":").map(s => s.trim().toUpperCase());
  
  var baseUrl = "https://min-api.cryptocompare.com/data/v2/histoday";

  // Validate and format the date into a timestamp
  var toTimestamp = "";
  if (date) {
    try {
      var d = new Date(date);
      if (isNaN(d)) return "Invalid date format";
      toTimestamp = Math.floor(d.getTime() / 1000); // Convert to UNIX timestamp
    } catch (e) {
      return "Invalid date format";
    }
  }

  var url = date
    ? `${baseUrl}?fsym=${crypto}&tsym=${currency}&limit=1&toTs=${toTimestamp}`
    : `https://min-api.cryptocompare.com/data/price?fsym=${crypto}&tsyms=${currency}`;

  try {
    var options = {
      muteHttpExceptions: true,
      headers: {
        "Authorization": `Apikey ${API_KEY}`,
        "Accept": "application/json"
      }
    };
    
    var response = UrlFetchApp.fetch(url, options);
    var responseCode = response.getResponseCode();
    
    // Debugging logs
    Logger.log("URL: " + url);
    Logger.log("Response code: " + responseCode);
    
    if (responseCode !== 200) {
      return "API Error: " + responseCode + " - " + response.getContentText().substring(0, 100);
    }
    
    var json = JSON.parse(response.getContentText());

    if (date) {
      var price = json?.Data?.Data?.[0]?.close;
      if (price) {
        return price;
      } else {
        Logger.log("Historical data error: " + JSON.stringify(json).substring(0, 200));
        return "No historical data available";
      }
    } else {
      var price = json[currency];
      if (price) {
        return price;
      } else {
        Logger.log("Current price error: " + JSON.stringify(json).substring(0, 200));
        return "Invalid pair or missing data";
      }
    }
  } catch (e) {
    Logger.log("Exception: " + e.toString());
    return "Error fetching price: " + e.toString();
  }
}

function getCryptoPrice(pair, date) {
  // Your CoinGecko API key
  const API_KEY = "CG-LFVL72Ypj9Aadyy3azC9FFXJ";
  
  if (!pair || typeof pair !== "string" || !pair.includes(":")) {
    return "Invalid input format. Use 'crypto:currency'";
  }

  var [crypto, currency] = pair.split(":").map(s => s.trim().toLowerCase());
  
  // No mapping needed - pocket-network is already correct
  var cryptoId = crypto;
  
  var baseUrl = "https://api.coingecko.com/api/v3/coins/";

  // Validate date input and format correctly for CoinGecko (DD-MM-YYYY)
  var formattedDate = "";
  if (date) {
    try {
      var d = new Date(date);
      if (isNaN(d)) return "Invalid date format";
      var day = ("0" + d.getDate()).slice(-2);
      var month = ("0" + (d.getMonth() + 1)).slice(-2); // Months are 0-based
      var year = d.getFullYear();
      formattedDate = `${day}-${month}-${year}`;
    } catch (e) {
      return "Invalid date format";
    }
  }

  var url = date
    ? `${baseUrl}${cryptoId}/history?date=${formattedDate}&localization=false`
    : `https://api.coingecko.com/api/v3/simple/price?ids=${cryptoId}&vs_currencies=${currency}`;

  try {
    // Add headers including the API key with the correct header name
    var options = {
      muteHttpExceptions: true,
      headers: {
        "x-cg-demo-api-key": API_KEY,
        "Accept": "application/json"
      }
    };
    
    var response = UrlFetchApp.fetch(url, options);
    var responseCode = response.getResponseCode();
    
    // Debug information
    Logger.log("URL: " + url);
    Logger.log("Response code: " + responseCode);
    
    if (responseCode !== 200) {
      return "API Error: " + responseCode + " - " + response.getContentText().substring(0, 100);
    }
    
    var json = JSON.parse(response.getContentText());
    
    if (date) {
      var price = json?.market_data?.current_price?.[currency];
      if (price) {
        return price;
      } else {
        Logger.log("Historical data error: " + JSON.stringify(json).substring(0, 200));
        return "No historical data available";
      }
    } else {
      var price = json[cryptoId]?.[currency];
      if (price) {
        return price;
      } else {
        Logger.log("Current price error: " + JSON.stringify(json).substring(0, 200));
        return "Invalid pair or missing data";
      }
    }
  } catch (e) {
    Logger.log("Exception: " + e.toString());
    return "Error fetching price: " + e.toString();
  }
}