Search code examples
google-apps-scriptgoogle-sheetsgoogle-finance

Why is the result of my GOOGLEFINANCE formula always the same?

I wrote a script with Apps Script for my Google Sheet, in order to have a record of stock prices at a given time. The script is executed every hour by a time-driven trigger.

function logStockPrices() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var writeSheet = ss.getSheetByName('Stock History');
  var timestamp = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy hh:mm:ss");
  writeSheet.appendRow([timestamp, '=googlefinance("NASDAQ:AAPL")', '=googlefinance("NYSE:SPCE")', '=googlefinance("FRA:D7G")', '=googlefinance("FRA:11L1")', '=googlefinance("FRA:LHA")', '=googlefinance("FRA:AIR")', '=googlefinance("FRA:10E")']);
}

But the price which is logged is always the same, even tho the price is changing. The timestamp however is correct.

This is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/11empPIZzVOc1tc7toTU6YVbeygVfaR_izW60QNYTjwI/edit?usp=sharing

I am thankful for any hint and comment.

Solution

  • From your sheet I can see that on each column the price is correct but for the last time that the price changed. So if yesterday one was 2€ and today it is 4€ you have all the column with 4€.

    I would recommend you to get the current value on another cell and then copy that value, NOT the formula, and append to the last row the copied value.

    To get a value from a cell you can do it bu getting the cell with getRange(a1Notation) and then getting the result with getValue().

    Doing this for each finance you want to track you then will create a log with all the changes.