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

google-apps-script google-sheets google-finance
Syed Mohammed Mehdi picture Syed Mohammed Mehdi · Jun 18, 2017 · Viewed 16.3k times · Source

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?

Answer

Ray Shan picture Ray Shan · Oct 20, 2018

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:

enter image description here

Related questions

How to make google spreadsheet refresh itself every 1 minute?

My google spreadsheet is using GOOGLEFINANCE('symbol','price) function to retrieve stock prices of my portfolio. Unfortunately, I have to refresh manually now. How can I make the spreadsheet refresh itself every 1 minute? Thank you for your help.

Read More
Google Sheets - googlefinance not working

I have one google sheet using googlefinance function on multiple stocks. It was working fine until this morning. Now, googlefinace is not giving output for all stocks and giving output for only some of the stocks. Please share, if is …

Read More
How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.

Read More