How to Get Live Crypto Prices in Google Sheets

January 5, 2024

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:

output

{
  "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:

Click on extensions then click Apps Script
Copy and paste the code from above into the Apps Script code editor
Save then click run
Click review permissions
On the window that says "Google hasn't verified this app" click advanced in the bottom left corner
Click "Go to [your project name] (unsafe)" at the bottom
Click allow
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.