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:

Add a new sheet

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

New sheet name

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:

Gold price in google sheet

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 

grab gold spot price in google sheet

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

Gold price google apps script

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:

Importing gold price in google sheet

After that, click on the run button:

import gold spot price into google sheet

⚠️ 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:

get gold price in google sheet

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

get gold price in google sheet

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.

Categories: Finance

17 Comments

Mario · 26 April 2019 at 21 h 28 min

Thank you, great info. Just one thing. On your image where it says “Choose which function to run” it should be “updateGoldPriceInSheet” correct? rather than “getOneOZGoldPriceUsd” Your clarification would be greatly appreciated.

    Adel · 21 February 2020 at 14 h 29 min

    Hello Mario,

    Almost one year after, i noticed your comment. Completely forgot I created this tutorial… But you are right I took the screenshot too quickly !

    Thank you !

Yves · 23 June 2019 at 11 h 43 min

I get this error message:

TypeError: Cannot call method “getRange” of null. (line 34, file “Code”)
Dismiss

it is the line
var range = valuesSheet.getRange(“B1:B1”);

It seems getRange needs more than one cell

Josh · 18 September 2019 at 21 h 21 min

how do you do this for Silver? I am trying to change the coding but cannot figure it out. Can you help? The gold one is SO incredible and easy to understand!!!

    Adel · 21 February 2020 at 14 h 31 min

    As soon as i find time I will publish an updated method to gather gold and silver. I’ll notify you when its published.

Brian · 28 September 2019 at 2 h 49 min

Hello and thank you for this guide! Can you please add an addition for the gold price in grams from the same website? I have a need for grams. Thank you kindly!

    Adel · 21 February 2020 at 15 h 09 min

    At the moment, you are getting the gold price in the B1 cell for 1oz. You can use the B2 cell to convert the result you get in the B1 cell by entering this formula :

    =(B1/28,3495)

    This number is the ratio between OZ and Grams. 1 Oz = 28,3495 so we just divide the 1 Oz gold price by 28,3495 to get the gold price for 1 gram 🙂

    To make it even more accurate (as 28,3495 is an approximation of the exact ratio) you can use the formula :

    =(B1/CONVERT(1;”ozm”;”g”)

    Hope it helps even if it’s late 🙂

Jay · 22 November 2019 at 3 h 59 min

Really helpful tutorial. My first very exposure to scripts. Well explained. Thank you

    Adel · 21 February 2020 at 15 h 09 min

    You’re welcome thank you for reading

Jay · 22 November 2019 at 4 h 02 min

This is great…but I’m surprise that there is nothing else on your website than this.

If you had more content, I would be a regular visitor

    Adel · 21 February 2020 at 15 h 10 min

    Yes I started this blog and forgot I did… Some new posts will be published soon 🙂

Ken · 25 December 2019 at 7 h 51 min

Hi Adel, thanks for sharing. Great post! I would love to further automate my Google sheets. Appreciate if you can share more on how to record historical prices for others like crypto and also quotes from Bloomberg/YahooFinance/other sources. Not just the hourly refreshed prices using importhtml/importxml.

And sometimes these function returns error and is important to spread the requests across time to not hit the limit of client server.

I don’t know how the table/span, etc works for importhtml, importxml. Lesser known websites I can’t find much help around on how to pick the source info out. Sometimes ended up pick entire table of data!

These are which i currently copy from other sites and is clunky. Some not well-known websites if you can share more on how to actually know which table/cell for importxml (importhtml doesn’t seem to work for Bloomberg) would be much appreciated.

I would love to have scripting to run to collect closing prices/other specific stats into my minimalist data centric sheet. Currently using different columns for different monitored.

If you can share more pointers, much appreciated. Some I’m manually downloading PDF, convert to XLS, clean data and copy paste into sheets. Very laborious. Ouch!

    Adel · 21 February 2020 at 15 h 19 min

    Thank you a lot! I will post new things about finance and how to automate stock analysis 🙂 Some years ago i automated all datas from companies from exotics/very old websites by using a tool that was named Kimono Labs. Unfortunately it does not exist anymore as far as I know. It was a wysiwyg letting you turn any website data to a programatically requestable API. I then did the same job as I did for gold price to get around 20 metrics for each companies.
    The big limit with google sheet is that it gets very slow when you start managing too much rows.

    I will notify you when i publish some new content 🙂

Phanindra · 30 January 2020 at 10 h 47 min

Instead of ounce can i fetch data in grams’s or kg’s ?, To acheieve this what we need to do ?

    Adel · 21 February 2020 at 15 h 24 min

    At the moment, you are getting the gold price in the B1 cell for 1oz. You can use the B2 cell to convert the result you get in the B1 cell by entering this formula :

    =(B1/28,3495)

    This number is the ratio between OZ and Grams. 1 Oz = 28,3495 so we just divide the 1 Oz gold price by 28,3495 to get the gold price for 1 gram 🙂

    To make it even more accurate (as 28,3495 is an approximation of the exact ratio) you can use the formula :

    =(B1/CONVERT(1;”ozm”;”g”)

    For kg :

    =(B1/CONVERT(1;”ozm”;”kg”)

    Hope it helps 🙂

Amy · 19 February 2020 at 20 h 44 min

I followed your tutorial to the letter, and everything works great except the gold price isn’t updating. The trigger is running without errors, so I don’t understand why the price isn’t updating on the spreadsheet.

    Adel · 21 February 2020 at 15 h 26 min

    Hi Amy,

    I will need more informations to help you. You can start by copy pasting all your code and i’ll review what you did 🙂

Leave a Reply

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