1

I have a formula in a cell (18,2) which is =(B17-B14)*B15+B17. So what you see if a numeric value. But when I tried to use the following script to fetch the value to a new cell, it put #num in the new cell. I tried other cells with formulas, when it is a simple formula, (b+c) for example, the getValue() works. But not this one. Why? Thank you for your help in advance!

function myfunction1 () 
{
    var app=SpreadsheetApp;
    var ss=app.getActiveSpreadsheet();
    var activeSheet=ss.getActiveSheet();
    var range = activeSheet.getRange(18,2);
    var fstave=range.getValue();

    // retrieve value
    activeSheet.getRange(8,8).setValue(fstave);
    Logger.log("Value: " + fstave);
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
Kele Ding
  • 9
  • 4
  • 1
    What are the values / formulas used by B14, B15 and B17? – tehhowch Feb 13 '19 at 23:11
  • they are all formulas though: b14: =AVERAGE(OFFSET(F2,COUNT(F:F)-B13,0,B13)) b15: =2/(B13+1) b17 is a cell address: =ADDRESS(count(F:F)-B13,6) – Kele Ding Feb 14 '19 at 01:26
  • And what is the underlying data? Raw numbers? imports? Google Finance output? – tehhowch Feb 14 '19 at 02:27
  • Thank you tehhowch. The data in F is googlefinance output. could be considered raw numbers. – Kele Ding Feb 14 '19 at 02:46
  • No, by design you cannot use programming to access historical Google Finance data. – tehhowch Feb 14 '19 at 02:48
  • 1
    Possible duplicate of https://stackoverflow.com/questions/50656540/reading-the-values-of-cells-that-summarize-google-finance-data-results-via-apps – tehhowch Feb 14 '19 at 02:51
  • I get data via formula and go as far as I can using formula to get the 1st EMA in cell (18,20). Now I want to move the value to next to the value on F column so I can start another set of formula to get moving average. That is what I thought I may be able to do. But the code won't bring the value over. – Kele Ding Feb 14 '19 at 02:52
  • I am going to study a bit more and then comeback to you for help. Thank you for the link! – Kele Ding Feb 14 '19 at 02:54
  • 1
    Possible duplicate of [Reading the values of cells that summarize Google Finance data results via Apps Script](https://stackoverflow.com/questions/50656540/reading-the-values-of-cells-that-summarize-google-finance-data-results-via-apps) – TheMaster Feb 14 '19 at 08:33

1 Answers1

0

You shold be able to use getDisplayValue() for the effect you want.

J. G.
  • 1,922
  • 1
  • 11
  • 21
  • Thank you for response. I did try it after searching google and this forum. However, it doesn't work. what setValue did was a #VALUE!, instead of #NUM. Any ideas? Thanks again! – Kele Ding Feb 13 '19 at 20:38