0

Apps Script seems to be unable to deal with a cell that uses the average formula, when the average is over Google Finance data:

Consider that column A is full of data from Google Finance. Then you type into cell B1 =average(A1:A100). Lets assume the average is 5 and as such cell, B1 shows 5. Great, except that using the following script will fail:

var AveResult = spreadsheet.getRange('B1').getValue();
----> The log show a #DIV/0! error

On the other hand, if you overwrite the formula in B1 and simply type in a 5, then it works perfectly:

var AveResult = spreadsheet.getRange('B1').getValue();
----> The log shows 5

Is there any way to get around this problem, to use scripts with summaries of Google Finance data?

Thank you.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Cameron
  • 11
  • 6
  • :( Numbers.. simple numbers. Anyway, whatever it is in there, the average formula is returning the average no problem. It's disappointing that the script can't handle the cell's output. – Cameron Jun 02 '18 at 17:16
  • Yes, it's a derivative of it. – Cameron Jun 02 '18 at 21:05
  • It seems you're right to some extent. Using formulas based on =GoogleFinance() data works fine. But setting a variable in scripts that refers to any data that ultimately links to the =GoogleFinance() cells doesn't work. This is a huge disappointment. – Cameron Jun 03 '18 at 04:57

1 Answers1

2

As written in the Official documentation,

Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.

The average formula is NOT the problem. Any attempt to take historical finance data through any formula hoops doesn't seem to be possible.

However live data doesn't seem to be restricted the same way.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • With thanks to the above replies - It seems that =GoogleFinance is the problem. When using a sheet, formulas based on cells using =GoogleFinance() data works fine. But when using a script, setting a variable that refers to any cell derived from =GoogleFinance() does not work. This is a huge disappointment. – Cameron Jun 03 '18 at 05:06