How to Get Live Crypto Prices in Google Sheets

Google Sheets has a built-in function GOOGLEFINANCE that you can use to access real time or historical stock prices. This feature works great for stocks, but it does not work for most cryptocurrencies. To get around this, I wrote a quick and easy Google Apps Script to fetch this data from a public Coinbase market data API. I'm going to walk through how to set this up so you can access crypto prices in Google Sheets.

Coinbase Market Data API

Coinbase has a public market data API where we can get live data with unauthenticated requests. For example, we can use this API to get the current spot price of Ethereum using the following endpoint:

https://api.coinbase.com/v2/prices/ETH-USD/spot

At the time of writing this, the response is:

{
  "data": {
    "amount": "2254.665",
    "base": "ETH",
    "currency": "USD"
  }
}

We can leverage this same API to create a Google Apps Script so we can access this data within Google Sheets.

Google Apps Script

The following script will call the Coinbase market data API for a given ticker and return the current spot price in USD.

javascript
function GETPRICE(ticker) {
  var url = "https://api.coinbase.com/v2/prices/" + ticker + "-USD/spot";
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var data = JSON.parse(json);
  var price = data["data"]["amount"];
  return price;
}

Here are the steps to add this script to your Google Sheet:

  1. Click on extensions then click Apps Script
  2. Copy and paste the code from above into the Apps Script code editor
  3. Save then click run
  4. Click review permissions
  5. On the window that says "Google hasn't verified this app" click advanced in the bottom left corner
  6. Click "Go to [your project name] (unsafe)" at the bottom
  7. Click allow
  8. Return to your google sheet and use the GETPRICE function in any cell. For example: =GETPRICE("ETH")

Now you should be able to use GETPRICE in your google sheet and retireve price data for any of the 200+ cryptocurrencies listed on Coinbase in real time.

← Back to home