How to import crypto prices in Google Sheets

May 15, 2021

Here's how to get the current price of any cryptocurrency directly in Google Sheets.
After following the steps below, you'll be able to use a function GETCRYPTOPRICE which takes 2 parameters: 1. the ticker / symbol of a cryptocurrency and 2. the fiat currency.

Example: This will return the current price of BTC in euro

It doesn't take long to set up, I promise!

STEP 1: Add the script to Google Sheets

In Google Sheets, go to "Extensions" and then "Apps Script". Once the script editor is open, replace all the existing code with the following code:


const CMC_PRO_API_KEY = <YOUR_API_KEY>;

function GETCRYPTOPRICE(ticker, currency) {
  try {
    // fetch data from url
    const res = UrlFetchApp.fetch(`https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${ticker}&convert=${currency}&CMC_PRO_API_KEY=${CMC_PRO_API_KEY}`);
    
    const content = res.getContentText();
    
    // parse content
    const json = JSON.parse(content);

    // return the price
    return json.data[ticker.toString()].quote[currency.toString()].price;
  }
  catch (err) {
    return JSON.stringify(err);
  }
}

Save the code and rename it as “GETCRYPTOPRICE”.

STEP 2: Get your CoinMarketCap key

Go to https://coinmarketcap.com/api/ and click on "Get your API key now". Create an account, which takes less than 2 minutes.
Then on your dashboard, click on "COPY KEY".

STEP 3: Add your API key

Back to the script editor, replace <YOUR_API_KEY> with your actual API key that you just copied. The line should look like this:

const CMC_PRO_API_KEY = "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxx";

Hit "Save project" or Ctrl/Cmd + S.

STEP 4: Use GETCRYPTOPRICE function in your Google Sheet

You can now use the following formula in a cell:

=GETCRYPTOPRICE(ticker, fiatCurrency)

So for example to get the price of ETH in USD: =GETCRYPTOPRICE("ETH","USD")

STEP 5: Sit back and enjoy monitoring your portfolio

Raphael Roullet

I am a software engineer passionate about Crypto & Web 3 currently based in London. In the past, I worked for a few tech startups both in product management and marketing.