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

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.

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
    }
  ]
}

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

Thank you for reading 🙂