Refresh live Google Finance data prior to reading value

Question

I am trying to copy content from one column to another. Source column has formula based content like =GOOGLEFINANCE(SYMBOL,"PRICE") which gets updated every minute.

I have created trigger which runs for every 5min. But while copying it doesn't copy latest value which is seen in sheet, instead it copies initial value from formula. Lets say initial stock value is 100, later it changes to 105. When the function runs, my script copies 100 to the destination range.

...
var ss = SpreadsheetApp.getActive().getSheetByName("Sheet1");
ss.getRange(startRowRange).copyTo(ss.getRange(col), {contentsOnly:true});

How can I read the current, displayed value of =GOOGLEFINANCE() cells?

Note that Google Finance in Google Sheets is in general internally disallowed from being used in any programmatic fashion. Live data is the only exception. You can try to flush the spreadsheet's values, by setting a cell formula to be `=1+[Google Finance live price cell reference]`, calling `.flush()`, and then reading your prices. You probably need to wait for a longer time. — tehhowch, Oct 10 '18 at 20:14
As explained by tehhowch, problem isn't in your code. In general, spreadsheet functions that call external services (GOOGLEFINANCE) are NOT updated, when you don't manually open your spreadsheet. It's a unnecessary waste of resources. `flush` might help. — TheMaster, Oct 10 '18 at 20:48

score 1 · Accepted Answer · answered Oct 11 '18 at 05:27

As @tehhowch suggested, flush did the trick. Calling this applies all pending Spreadsheet changes at that point of time.

var ss = SpreadsheetApp.getActive().getSheetByName("Sheet1");
SpreadsheetApp.flush();
ss.getRange(startRowRange).copyTo(ss.getRange(col), {contentsOnly:true});

Refresh live Google Finance data prior to reading value

1 Answers1

Linked