0

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.

pfra
  • 5
  • 1
  • 5
  • Once a formula is pasted, you should copy/paste values only preferably after a `.flush()` call – TheMaster Dec 22 '20 at 22:49
  • Can you share a copy of the spreadsheet with the script to test it? – Kessy Dec 23 '20 at 11:19
  • @ThaMaster, thanks for your comment! I am not sure if I understand you correctly. As I understand, the formula is inserted into the next cell from the next row, where it should then gather the information from the google finance server. I don't see what I should .flush() before inserting the data to the cell. Can you please clarify? – pfra Dec 24 '20 at 13:20
  • @Kessy, Thanks for your comment. I attached a link to the spreadsheet in the initial question. The code is already in the question above. – pfra Dec 24 '20 at 13:23

1 Answers1

0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Kessy
  • 1,894
  • 1
  • 8
  • 15
  • Thanks for your answer, it worked doing it this way! But I am still wondering why it does not work the way I did it. Do you know why? Cause doing it this way looks pretty unhandy to me since you have to do it for every stock. – pfra Jan 04 '21 at 20:23