Get Live Real-time Price Of Gold in Grams In Google Sheets For Free

You can pull the live gold price in Google Sheets for free with a short Apps Script function — no add-on, no paid data feed. The script fetches the spot price per troy ounce from a public endpoint and divides it by 31.1035 to give you the price per gram.

TL;DR: Extensions → Apps Script, paste the function below, then call =getGoldPriceInGram() in any cell.

The idea was to fetch price of some stock in real-time and add it in some Google Sheet, after I achieved that I decided to documenting the steps in a blog style so that it can be a reference to other people who want to achieve similar things and bypass a lot of difficulties.

Here in this blog I will use Gold as an example product and goldprice.org as a external source .

Note: using goldprice.org only for demo purpose I am not responsible at all about any abuse usage.

---

Step Number 1

From Extensions menu select Apps Script, Extensions -> Apps Script

Step Number 2

Write this function:

function getGoldPriceInGram() {
  var url = "https://data-asg.goldprice.org/dbXRates/USD";
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var xauPrice = parseFloat(data.items[0].xauPrice);
  var priceInGram = xauPrice / 31.1035;
  return priceInGram;
}

And save project with name e.g. GoldPriceScript

Note that you maybe need to pass some Auth step to get access to code editor.

You can change USD to any other currency you want.

So in this function we fetch from API the xauPrice which is ounce gold price then we divide it by ~ 31.1035 it will gives us the price in Gram.

Why divide by 31.1035?

Precious metals are quoted per troy ounce, not the ordinary avoirdupois ounce used for everyday weights. One troy ounce is exactly 31.1034768 grams — roughly 10% heavier than the 28.35 g ounce most people have in mind.

Using 28.35 by mistake is the single most common error here, and it silently inflates your per-gram figure by about 9.7%. The XAU in xauPrice is the ISO 4217 currency code for one troy ounce of gold, which is a useful reminder of what unit you are actually dividing.

The response of url is json like this:

{
  "ts": 1698612276036,
  "tsj": 1698612275488,
  "date": "Oct 29th 2023, 04:44:35 pm NY",
  "items": [
    {
      "curr": "USD",
      "xauPrice": 2006.33,
      "xagPrice": 23.1145,
      "chgXau": 21.855,
      "chgXag": 0.2725,
      "pcXau": 1.1013,
      "pcXag": 1.193,
      "xauClose": 1984.475,
      "xagClose": 22.842
    }
  ]
}

Note xagPrice in the same payload — that is silver. Swapping one field name gives you a silver tracker with no other changes.

Step Number 3

In Google Sheet we will call this function e.g. =getGoldPriceInGram()

And here we get the price of gold in real-time

Making the price actually refresh

This is the part that surprises people. Google Sheets caches custom function results, and it only recalculates a function when its arguments change. =getGoldPriceInGram() takes no arguments, so the value can sit frozen for hours even though the underlying price is moving.

The reliable fix is to stop treating it as a cell formula and let a time-driven trigger write the value instead:

function updateGoldPrice() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  sheet.getRange("A1").setValue(getGoldPriceInGram());
}

In the Apps Script editor, open Triggers (the clock icon), add a trigger for updateGoldPrice, and set it to run on a time interval. Cell A1 now updates on a schedule regardless of caching.

Keep the interval sensible. UrlFetchApp is subject to daily quota limits, and a public endpoint you do not own is a courtesy, not a contract — polling every minute risks both your quota and your access.

Frequently asked questions

Why does my cell show a stale price?

Custom function caching, as described above. A function with no arguments has no reason to recalculate from the spreadsheet's point of view. Use a time-driven trigger.

Can I get the gold price in another currency?

Yes — change USD in the URL to another currency code. The response shape stays the same, so no other edits are needed.

Why not just use GOOGLEFINANCE?

GOOGLEFINANCE covers listed tickers well, but spot commodity prices are not reliably available through it. Fetching a JSON endpoint directly is the more dependable route for spot gold.

Is this suitable for trading decisions?

No. A free public endpoint offers no accuracy or uptime guarantee. It is fine for tracking and personal dashboards; anything financially consequential warrants a licensed data provider.

Thank you for reading :)

For more small automation write-ups, see the tiny hardware automation project that saved me 60+ hours a week.