While doing an investment study, I've been keeping a stock trading journal in Google Sheets, and since I found Google Apps Script quite useful, I'm putting together a quick summary.
What Is the Google Finance Function?
Before getting into Apps Script, let's first take a look at the Google Finance function. This is one of the built-in functions available in Google Sheets, and it can fetch real-time stock quote data.
If you enter =GOOGLEFINANCE("AAPL") into a cell, it fetches the current Apple stock price and displays it in the cell.
As shown in the image below, I use it to check the current stock price and decide whether to buy or sell.
The Google Finance function provides quote data for most stocks, but there are cases where it doesn't. For example, it does not provide spot gold prices.
How to Implement a Custom Function?
Since the Google Finance function doesn't provide spot gold prices, you need to implement a custom function that fetches the data through a different API and inserts it into a cell.
To fetch spot gold price information, I use the API from RapidAPI Stock-API.
1.Writing the Apps Script
Google Apps Script provides a JavaScript platform that lets you automate and extend various Google services (e.g. Google Sheets, Docs, Gmail), enabling interaction with Google services.
To write an Apps Script in Google Sheets, click Extensions > Apps Script to launch it. After writing the code below and clicking the run button, you'll need to go through an authorization step at least once.
If an "unverified app" warning window appears, click "Advanced" and then "Go to ... (unsafe)" to grant access.
function GoldShareInit() {
var options = {
'headers' : {
'X-RapidAPI-Key': 'check this value in the rapid api console',
'X-RapidAPI-Host': 'stock-api7.p.rapidapi.com'
}
};
var res = UrlFetchApp.fetch('https://stock-api7.p.rapidapi.com/v1/latest/M04020000', options);
var content = res.getContentText();
var json = JSON.parse(content);
var result = json['currentPrice'];
return result;
}
2.Using the Custom Function
When you enter the following in Google Sheets, you'll see the spot gold price value displayed.
=VALUE(GoldShareInit())
