How to import Gold price into Google Sheet

Today, you will learn how to automatically import gold price into a Google Sheet cell.

This post will be the first of a series that will help you to masterize simple yet powerful Google Sheet & Google Script features. During this tutorial, you will be using some code. If you follow the instructions step by step, you will need zero development knowledge. I’m also available to answer your questions in the comments 👍

These tutorials will be willingly explaining even the simplest things.
My goal is to let beginners understand everything, without being too technical. I hope you will like it.

Step 1: Prepare your Spreadsheet to import gold price

First of all, to create your first spreadsheet, you will have to visit Google Spreadsheet home page: https://docs.google.com/spreadsheets/?usp=mkt_sheets

Then, you will start by creating a new sheet in your spreadsheet. In fact, this sheet will be used to store the gold price (and other values during nexts tutorials).

So just click on add a sheet on the bottom left of the screen:

Then, name it VALUES. To do so, double-click on it and type down the new name. At this point, you should have:

Obviously, this sheet will be used to store all assets values you will learn to grab from the web. In the A row, you will put the asset name. In the B row, your project will regularly update the asset price.

For now, you will type GOLD into the A1 cell:

Now it’s done, your are up and ready to dive into the beautiful world of ⭐️Google Apps Script ⭐️

You never eared of this gem ? No problem, let’s discover it together.

Step 2: Import gold price into Google Sheet using a function in Apps Script

Again, in case you have questions while following this tutorial, do not hesitate to post them in the comments at the end of this post 🤙

To import gold price into google sheet, you will use Google Apps Script.

“Apps Script is a scripting language for light-weight application development in the G Suite platform.” Wikipedia

To make it simple, Apps Script is the best way to automate things in your Spreadsheet. Most noteworthy, Apps Script automations are able to run while your Spreadsheet is closed.

First of all, open App Script by clicking on Tools -> Script editor 

Then, a new tab will be opened in your browser. It should look like this:

At the moment, Google Apps Script has initialised your project with a default function named myFunction().

Please delete any code in the script editor and paste the code below:

// This function will be in charge to return the current gold price
function getOneOzGoldPriceUsd() {
  // The website we will grab gold price from. goldprice.org is my favorite website when it comes to precious metals values.
  var url = "https://data-asg.goldprice.org/dbXRates/USD";
  // UrlFetchApp is a powerfull tool that let your script request a web ressource to grab its content.
  // Our option variable will be in charge to explain to UrlFetchApp how to do to grab what we need.
  // If you want to learn more about http requests, you will find a great tutorial here: https://learn.onemonth.com/understanding-http-basics/
  var options =
      {
        "method"  : "GET",
        "followRedirects" : true,
        "muteHttpExceptions":true
      };
  var result = UrlFetchApp.fetch(url, options);
  
  // Now we got the content we need, we store it into the data variable
  var data = JSON.parse(result.getContentText());
  
  // Finally, we extract the gold price from the data. The price is for 1oz of gold.
  var goldPrice = data['items'][0]['xauPrice'];
  
  
  return goldPrice;
}

// This function will set the gold price in the B1 cell
function updateGoldPriceInSheet(){
  // We have to manipulate the spreadsheet content. We put it in a variable named ss.
  var ss = SpreadsheetApp.getActive();
  
  // We select the sheet we want to work on: the "VALUES" sheet you created before 
  var valuesSheet = ss.getSheetByName("VALUES");
  // We select the range we want to work on. In this tutorial we will just need to edit one cell: the B1 cell.
  var range = valuesSheet.getRange("B1:B1");
  // We select the first cell of the first row of the previously selected range (the only one actually)
  var goldValueCell = range.getCell(1,1)
  // Finally, we set the value !
  goldValueCell.setValue(this.getOneOzGoldPriceUsd());  
}

Basically, this code is composed of two functions:

• getOneOzGoldPriceUsd(): This function is in charge of returning the USD gold spot price for 1oz of pure gold. The value is gathered from https://goldprice.org/. GoldPrice.org is my favourite place to check Gold & Silver price.
• updateGoldPriceInSheet(): This is the main function that will be executed. It will run getOneOzGoldPriceUsd() and set the returned gold price in the B1 cell of the VALUES sheet.

If you want to learn more on how it works, I added some explanation (comments) on each line of code.

🔵PRO TIPS🔵: If you locate the line of code :

var goldPrice = data['items'][0]['xauPrice'];

And replace xauPrice by xagPrice, your script will start gathering Silver price. Anyway, I will soon publish a tutorial on how to automatically update multiple assets.

Step 3: Let’s run our project !

At this point, you should be able to manually run our script to import gold price into Google Sheet.

To do so, click on Select function in our toolbar and select updateGoldPriceInSheet() in the dropdown menu:

After that, click on the run button:

⚠️ At your first run, Google Sheet may ask you to give some access right to Apps Script  ⚠️

You must accept to allow the script to request the gold price and put it in your sheet.

WARNING: If you get this modal:

No worries, just click on Advanced and then click on ‘Go to <your spreadsheet name>(unsafe)’.

Actually, it is safe: Google added this warning in case someone sends you a link to execute a script you didn’t created to steal data from your spreadsheet.

And Voilà! The gold price should appear in the B1 cell of your VALUES sheet 👍

Step 4: Automatically import gold price into Google Sheet

You didn’t go through all those steps just to add the gold price in your sheet. It would have been easier to copy past it from somewhere else…

Actually, the advantage of those functions is that you are now able to make them refresh the price automatically, even when your spreadsheet is closed.

To do so, start by opening the Google Apps Script tab and click on Edit -> Current Project’s Triggers

Triggers are tools that let you choose which functions should be executed and which event should trigger the execution.

Lets setup your trigger to import gold price into Google Sheet every 6 hours:

• Click on the Add Trigger  button on the bottom right
• In the Choose which function to run section, select your function updateGoldPriceInSheet()
• For the Select event source section, select Time Driven
• In Select type of time based trigger section, select Hour Timer
• To finish, in Select hour interval section, select Every 6 hours
• Let anything else on default values

You should get something like this:

WARNING: There’s an error on the following screenshot. At “Choose which function to run” please select “updateGoldPriceInSheet()” instead of “getOneOzGoldPriceUsd()” as I explained just before. Thank you Mario for your comment 😉

Now, just click on save and you are good to go. As a result, gold price will be automatically refresh in your spreadsheet.

💥 Good Job !

Conclusion

I hope you easily & successfully went through this tutorial. In further posts, I will explain you how to:

• Import other assets price (Crypto-assets, Stocks…)
• Keep track of assets prices evolution
• Anything you will ask in the comments 🤷‍♂️

As i told you before, if you have any question or need further help, ask me anything in the comments.

Also, if this tutorial has been useful to you, feel free to share it using social buttons below.