Crypto Portfolio Tracking in Google Sheets

For investors managing a diverse portfolio of digital assets, especially those held in cold storage, accurate and real-time price tracking is essential for accounting, tax reporting, and strategic decision-making. Google Sheets is a powerful tool for this, but its built-in `=GOOGLEFINANCE` function has a significant drawback: it does not support most cryptocurrencies launched after 2018.

This guide explains why this limitation exists and provides a simple, powerful solution using Google Apps Script and the Coinbase API to pull the price for any digital asset you need, directly into your spreadsheet.

The Problem: The Limitations of `=GOOGLEFINANCE`

The reluctance of Google to list newer crypto assets isn't a technical issue; it's a strategic decision rooted in legal and reputational risk. The regulatory status of thousands of altcoins remains unclear. By only listing older, more established assets like Bitcoin and Ethereum, Google avoids the legal complexities and reputational risk associated with promoting assets that could later be classified as unregistered securities.

While this is a sensible policy for Google, it leaves serious investors needing a more comprehensive solution.

The Solution: A Custom Apps Script

The best way to get reliable, real-time data for any asset is to pull it directly from a major, crypto-native exchange. By using a simple Google Apps Script, you can create your own custom spreadsheet function that fetches prices from the Coinbase public API.

Step 1: Open the Apps Script Editor

In your Google Sheet, go to Extensions > Apps Script. This will open a new tab with the script editor. Delete any boilerplate code in the `Code.gs` file.

Step 2: Paste the Code

Copy the entire code block below and paste it into the script editor. This script defines a new custom function called `COINBASE_PRICE` and includes a cache to make it fast and efficient.

// A cache to store prices temporarily (10 minutes) to avoid redundant API calls.
const CACHE = CacheService.getScriptCache();
const CACHE_EXPIRATION_SECONDS = 600; // 10 minutes

/**
 * Fetches a crypto price from the Coinbase API, using a cache for efficiency.
 * This is an internal function that the custom function will call.
 * @param {string} cryptoSymbol The full currency pair to fetch (e.g., "BTC-USD").
 * @returns {number|string} The price or an error message.
 */
function _getCoinbasePrice(cryptoSymbol) {
  // First, check if a valid price is already in the cache.
  const cachedPrice = CACHE.get(cryptoSymbol);
  if (cachedPrice) {
    return parseFloat(cachedPrice);
  }

  // If not in cache, fetch from the Coinbase API.
  const baseUrl = 'https://api.coinbase.com';
  const endpoint = `/v2/prices/${cryptoSymbol}/spot`;

  try {
    const response = UrlFetchApp.fetch(baseUrl + endpoint);
    const data = JSON.parse(response.getContentText());
    const price = parseFloat(data.data.amount);
    
    // Store the newly fetched price in the cache for next time.
    CACHE.put(cryptoSymbol, price.toString(), CACHE_EXPIRATION_SECONDS);
    
    return price;
  } catch (error) {
    // Return a specific error if the pair is not found.
    if (error.toString().includes('404')) {
        return `Pair not found`;
    }
    return 'Error';
  }
}

/**
 * Custom function to get a crypto price directly in a spreadsheet cell.
 * This is the only function you'll call from your sheet.
 * @param {string} cryptoSymbol The full currency pair to fetch (e.g., "BTC-USD").
 * @customfunction
 */
function COINBASE_PRICE(cryptoSymbol) {
  if (!cryptoSymbol || typeof cryptoSymbol !== 'string' || cryptoSymbol.trim() === '') {
    return 'Invalid symbol';
  }
  // Call the internal function, ensuring the symbol is clean and uppercase.
  return _getCoinbasePrice(cryptoSymbol.trim().toUpperCase());
}

Step 3: Save and Use the Function

Click the "Save project" icon in the Apps Script editor. You can now return to your spreadsheet. In any cell, you can type the formula using the full currency pair for any asset listed on Coinbase.

For example: `=COINBASE_PRICE("ETH-USD")`

Beyond Price Tracking

This tool is just the beginning. At Nanar Consulting, we build robust, tailored solutions for the most complex portfolio challenges, from automated tax reporting to DeFi tracking.

We maintain a focused client roster to ensure every partner receives our full attention. If you're ready to move beyond the basics, contact us to start the conversation.