1

Can googlefinance write results directly to an array? I am currently writing it to a sheet first and then pulling the sheet range into the array. It would save a lot of processing if I could write it directly to an array so I am investigating. If you have knowledge and expertise on this could you let me know?

I get an error when I try. Is it just incorrect syntax or is what I am trying to do not possible? I would like to avoid writing to the sheet to save on time and in sheet processing. But not sure if the function is allowed to write into a variable instead of a sheet

function TEST() {

 var APPLEPRICEARRAY = GOOGLEFINANCE("AAPL","price","1/1/2009","12/31/2020","WEEKLY")

};
Rubén
  • 34,714
  • 9
  • 70
  • 166
Scott Fitzpatrick
  • 41
  • 4
  • 1
    GOOGLEFINANCE is undefined – Cooper Jul 04 '20 at 22:04
  • 1
    You could put a bunch of GFs in a linear array on a spreadsheet and get them all at one time with Sheet.getRange().getValues(); – Cooper Jul 04 '20 at 22:05
  • 1
    In addition, historical finance data cannot be accessed through apps script. *I am currently writing it to a sheet first and then pulling the sheet range into the array* Unless you're copy/pasting, I doubt this is true. – TheMaster Jul 05 '20 at 07:33

2 Answers2

2

Is it just incorrect syntax?

You seem to confuse functions (formulas) that are exposed in the Google Sheets application with services that are available in Google Apps Script environment. What you wrote would require GOOGLEFINANCE to be a service "attached" to global scope, so yes, this is incorrect.

But the error you get is not a syntax error, your reference is invalid (no GOOGLEFINANCE variable is ever declared in the global scope, therefore none can be referenced), hence you get ReferenceError.

Can googlefinance write results directly to an array?

No, for reasons explained above, it cannot. I apologize for this, but you are comparing apples with oranges: an array is a data structure (an indexed collection, to be precise) in JavaScript (on which Google Apps Script language is based), while formulas are not even built-in objects: they are not part of the language.

Is what I am trying to do not possible?

Unfortunately, Google Finance API has been shut down for a long time now, so no, not possible.

Not screaming with ALL-CAPS is considered a common courtesy as well

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
  • *Not screaming with ALL-CAPS is considered a common courtesy as well* OP is following formula syntax convention, where all formulas are traditionally in uppercase. – TheMaster Jul 05 '20 at 07:32
  • 1
    @TheMaster - sure, I understand the reason why, it was an ironic statement, but I cannot bring myself to use emotionally charged style in answers :) Just noted that it is not a good idea to transfer that into scripting – Oleg Valter is with Ukraine Jul 05 '20 at 12:34
2

In the code example is shown that you are trying to use a Google Sheets function as a Google Apps Script / JavaScript function. That is not possible.

Unfortunately there isn't Google Apps Script advanced service for Google Finance and there isn't a API.

From https://groups.google.com/g/google-finance-apis

Dear Finance API users,

As you may have heard from the Google Developers Blog, Google is doing an API spring cleaning. One of the APIs affected is the Google Finance API (both the Portfolio API and the Finance Gadgets and Tools API), which will be shut down on October 20, 2012.

References

Rubén
  • 34,714
  • 9
  • 70
  • 166