Google finance 200 day moving average is getting as #NA in Apps Script

Question

I'm calculating Google finance 200 day moving average formula in google sheet using formula

=average(query(sort(GoogleFinance("GOOG","price", TODAY()-320, TODAY()),1,0),"select Col2 limit 200"))

Then in google app script I'm getting the above cell value in variable as below

var val = sheet.getRange("T101").getValue();

but in google script I'm getting that variable value as #NA. Can anyone please advise what is causing the issue?

Based from this blog, be noted that GoogleFinance() only executes when the spreadsheet is open. So when your trigger runs unattended, the values will not be set. The sample code in this thread may help to keep refreshing the sheet (using .getValues()) until all of the #N/A's disappear. It works but does add a small lag. — abielita
I used the logic mentioned in thread as below data = sheet.getRange("B106").getValue(); // necessary to refresh custom functions var nanFound = true; while(nanFound) { if(data.indexOf('#N/A') >= 0) { nanFound = true; data = sheet.getRange("B106").getValue(); } // end if else nanFound = false; } // end while but it is running for infinite time and #NA is not getting changed to value — Syed Mohammed Mehdi
This is expected behavior for historical Google Finance use when accessed via script. — tehhowch
Possible duplicate of Reading the values of cells that summarize Google Finance data results via Apps Script — tehhowch

Ray Shan Ray Shan · Accepted Answer · 2018-10-20T18:56:57

To expand on @Ric ky's answer, the trick here is to get to a range so the average math can be performed on it.

For this working answer:

=AVERAGE(INDEX(GoogleFinance("GOOG","all",WORKDAY(TODAY(),-200),TODAY()),,3))

Here's why it works:

=AVERAGE( INDEX( // used to get 1 value or a range of values from a reference GoogleFinance("GOOG","all",WORKDAY(TODAY(),-200),TODAY()), // returns an expanded array with column headers, used by INDEX as the reference , // bypass INDEX's row argument to return an entire column to be AVERAGE'd 3 // we want the High column with an index of 3; index is 1-based ) )

A visual:

Google finance 200 day moving average is getting as #NA in Apps Script

2 Answers