How to return values from Array using Google sheets script

Question

I am recording the difference between the current currency value and yesterdays close on an hour by hour basis, using scripts in Google Sheets to automatically add new rows each hour with the new values.

For the most part this works fine, however the Google Finance formula for Historic data displays the data in a 2x2 array.

=GOOGLEFINANCE("CURRENCY:USDGBP", "close", TODAY()-1, TODAY())

Using scripts I need to reference the Close value returned in Column 2, Row 2 of the array for further calculations.

var CloseVal = sheet.getRange("$I$13").getDisplayValue();
console.log(posNeg);
sheet.getRange(numRows + 1,3).setValue(CloseVal);

However when I try to reference the results of the array using scripts as above, it returns a null value. It suggests that while the value is displayed in this cell, it doesn't actually exist there.

I've tried using getDisplayValue and also referencing the formula cell to no available.

Does anyone know how I can get the value to return?

Ritesh Nair · Accepted Answer · 2017-11-15 21:10:48Z

1

It is not possible to access the values returned by GOOGLEFINANCE function via App script. This feature was disabled by Google [link] around Sep 2016.

We want to make you aware of a small change to the GOOGLEFINANCE function, which makes it easy to pull current or historical securities information directly into your spreadsheets in Google Sheets. Starting today, it will not be possible to download historical data or access it outside of Sheets using Apps Script or an API.

If you have a spreadsheet with historical data generated from the GOOGLEFINANCE function and you try to download it or access it via Apps Script or an API, the corresponding cells will show “#N/A.”

answered Nov 15, 2017 at 21:10

Ritesh Nair

3,3651 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return values from Array using Google sheets script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related