How to get live coin prices in Google Sheets?

Introduction

Live coin prices can be incredibly useful for tracking the performance of your cryptocurrency investments directly in a spreadsheet.

Whether you're monitoring Bitcoin, Ethereum, or any other cryptocurrency, having real-time data allows for immediate decisions and analysis.

For instance, you could use this data to calculate your portfolio value in real time or set up alerts for when a coin reaches certain price thresholds.

Step 1: Get an API Key from Coinranking

Before you start, you'll need a Coinranking API key. Sign up and copy your API key. This key is essential for making requests to our API.

Step 2: Setup Google Sheets

Open a new Google Sheets document where you want to display the live coin prices.

Step 3: Use the Apps Script

In your Google Sheet document, go to Extensions > Apps Script.

Delete any code in the script editor and paste the following code:

function getCoinPrice() {
  // This is the UUID for Bitcoin, replace it with the coin you need
  const coinUuid = 'Qwsogvtv82FCd'
  const url = `https://api.coinranking.com/v2/coin/${coinUuid}`;

  // Replace YOUR_API_KEY_HERE with your Coinranking API key
  const apiKey = 'YOUR_API_KEY_HERE';

  const options = {
    method: 'get',
    headers: {
      'x-access-token': apiKey
    }
  };

  const response = UrlFetchApp.fetch(url, options);
  const json = response.getContentText();
  const data = JSON.parse(json);

  return data.data.coin.price;
}
  • Replace "YOUR_API_KEY_HERE" with your actual Coinranking API key.
  • Replace "Qwsogvtv82FCd" with the UUID of the cryptocurrency you want to track if it's not Bitcoin. (You can find it in the URL of any coin page)
  • Click on the disk icon to save the script and name your project.

Step 4: Pull data into your sheet

Go back to your Google Sheet.

In the cell where you want to display the live price, enter =getCoinPrice() and press Enter.

Step 5: Set a trigger

To automatically refresh the price, we need to set up a time-driven trigger in Apps Script.

  1. In the Apps Script editor, find and click on the clock icon or Triggers button on the left sidebar. This opens the "Triggers" page.
  2. Click the + Add Trigger button at the bottom left of the screen. A form will appear. Here, select the function you want to run from the "Choose" which function to run dropdown. In the "Select" event source dropdown, choose Time-driven.

Now, decide when and how often you want your script to run.

You can select specific options like Hour timer for it to run every hour, or Day timer to run at a specific time each day, among others.

Optionally, you can set up notifications for when your script runs or if it fails by clicking on Notifications.

After setting everything up, click "Save". You might need to authorize your script to run under your account. Follow the prompts to allow permissions.

And that's it!

You've successfully set up your Google Sheets to display live cryptocurrency prices using Coinranking API.

This setup can be customized further to track multiple coins, calculate portfolio values, or even set up conditional formatting for visual alerts.

Remember, the Coinranking API offers a wealth of data beyond just live prices, so feel free to explore and expand your spreadsheet's capabilities.

On this page