CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-finance
Scott Fitzpatrick
Scott Fitzpatrick

Reputation: 41

Can you write Google finance directly to an array?

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")

};

Upvotes: 1

Views: 647

Answers (2)

Wicket
Wicket

Reputation: 38160

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

Upvotes: 2

0Valt
0Valt

Reputation: 10345

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

Upvotes: 2

Related Questions