CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
sebmpo17
sebmpo17

Reputation: 11

How to return values from Array using Google sheets script

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?

Upvotes: 1

Views: 1192

Answers (1)

Ritesh Nair
Ritesh Nair

Reputation: 3355

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.”

Upvotes: 1

Related Questions