Import Live Cryptocurrency Prices into Google Sheets with Auto Refresh

This tutorial provides a stable, free solution to import live cryptocurrency data into Google Sheets using a custom API function. Unlike web scraping methods that break with website updates, this approach leverages the LiveCoinWatch API for reliable, real-time crypto pricing.

Why This Method Works Best

✅ No more broken scripts – Uses official API endpoints instead of HTML scraping
✅ Fully customizable – Fetch only the data you need (price, market cap, volume, etc.)
✅ Automated refreshes – Schedule updates using Google Apps Script

Step 1: Create the getCoinInfo() Custom Function

Prerequisites

1. API Key: Register at LiveCoinWatch (free tier available).
2. Google Sheets: Open a new sheet and go to Extensions > Apps Script.

Script Implementation

function getCoinInfo(coinSymbol, dataType) {
  const apiKey = "YOUR_API_KEY"; // Replace with your key
  const url = `https://api.livecoinwatch.com/coins/single`;
  
  const payload = {
    "currency": "USD",
    "code": coinSymbol.toUpperCase(),
    "meta": true
  };

  const options = {
    "method": "POST",
    "headers": { "x-api-key": apiKey },
    "payload": JSON.stringify(payload)
  };

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

  switch (dataType.toLowerCase()) {
    case "price":
      return json.rate;
    case "volume":
      return json.volume;
    case "cap":
      return json.cap;
    default:
      return "Invalid data type";
  }
}

Parameters Explained

• coinSymbol: Coin ticker (e.g., BTC, ETH).
• dataType: Choose price, volume, or cap (market capitalization).

👉 Get your API key here

Step 2: Schedule Auto-Refresh

1. Create a Time-Driven Trigger:

   • In Apps Script, go to Triggers > Add Trigger.
   • Set to run getCoinInfo hourly/daily.

2. Force Formula Updates:
   Add this script to refresh cells:

   function refreshData() {
     const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
     sheet.getRange("A2:B10").activate();
     SpreadsheetApp.flush();
   }

FAQs

Q: Is the LiveCoinWatch API free?

A: Yes, it offers a free tier with limited requests (enough for personal use).

Q: Can I track multiple cryptocurrencies?

A: Absolutely! Use an array formula or multiple getCoinInfo() calls.

Q: Why not use CoinGecko or CoinMarketCap APIs?

A: LiveCoinWatch provides simpler integration for Google Sheets, but you can adapt this method for other APIs.

Q: How do I troubleshoot API errors?

A: Check the API key validity and coin symbols. Use Logger.log(response) for debugging.

Pro Tips

• Optimize API Calls: Cache results if refreshing frequently.
• Expand Functionality: Add error handling or more data points (e.g., 24h price change).

👉 Explore advanced crypto tools

By following this guide, you’ll have a self-updating crypto tracker without relying on fragile scraping methods. Happy analyzing!