I have a Google sheet that fetches the fiat price from Google Finance using the formula
=GOOGLEFINANCE("CURRENCY:BTCUSD") and it hasn't changed in over a week - it's been stuck at 63,126.50.
The web UI using the same source is https://www.google.com/finance/quote/BTC-USD?hl=enIt's odd that they still haven't fixed it, considering how resourceful they are. It's as if they didn't want to.
My first workaround was to fetch the price of an ETF and multiply it by a constant, but that only updates during US market hours.
I also tried the
=IMPORTXML from this Reddit thread, but that doesn't update and it only works in the browser, not in the mobile Sheets app.Eventually I created a script to make an API call, extract the price and write it into the cell. This also works in the mobile app (I've tested it on Android).
I wanted an unauthenticated API and found Bitvavo, which unfortunately only has the BTC-EUR price, so you need to fetch
=GOOGLEFINANCE("CURRENCY:EURUSD") and multiply the BTCEUR price by that.To add the script, click on Extensions / Apps Script and paste the following (replace
B3 with your target cell):function fetchBitcoinPriceBitvavo() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change "Sheet1" to your actual sheet name
var url = "https://api.bitvavo.com/v2/ticker/price?market=BTC-EUR"; // Bitvavo API URL for BTC-EUR price
try {
// Fetch the API data
var response = UrlFetchApp.fetch(url);
var jsonData = JSON.parse(response.getContentText());
// Get the price from the API response
var price = jsonData.price;
sheet.getRange("B3").setValue(price);
} catch (error) {
Logger.log("Error fetching price: " + error);
sheet.getRange("B3").setValue("Error fetching price");
}
}
To make it update periodically, click on the clock icon, click on "Add Trigger" and set it to update at your selected interval, e.g. every 5 minutes.