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 as a external source .

Note: using 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 = "";
  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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.