Reading the values of cells that summarize Google

问题:

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.

回答1:

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.