CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsformulagoogle-sheets-api
AKG
AKG

Reputation: 11

Google Script for formula value Return #N/A

Column F have a Formula value when I read in script is show #N/A.

Below is a very simple script code, but I'm unable to understand why it is showing #N/A, I didn't implement any trigger.

var spreadsheet = 
         SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
         var values = spreadsheet.getDataRange().getValues();
         Logger.log(values[1][0]);
         Logger.log(values[1][5]);

https://docs.google.com/spreadsheets/d/18EsMMrC1IbzkZaIb5aIx3OfwvN1VzdmHm5B-YVuCMs4/edit?usp=sharing

Upvotes: 1

Views: 2176

Answers (1)

Tanaike
Tanaike

Reputation: 201513

How about a following modification?

From :

var values = spreadsheet.getDataRange().getValues();

To :

var values = spreadsheet.getDataRange().getDisplayValues();

The detail information of getDisplayValues() is here.

If I misunderstand your question, I'm sorry.

Edit

When I tried to retrieve the data using Sheet API v4, following error message occurred.

"#N/A (Historical GOOGLEFINANCE data is not available outside of the Google Sheets UI.)"

So I investigated about this error. And I found a following document.

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.” You will still be able to view that historical data from within the Sheets application, and you will still be able to download current data and access current data via Apps Script or an API. Please keep this in mind when using the GOOGLEFINANCE function going forward.

Document : https://gsuiteupdates.googleblog.com/2016/09/historical-googlefinance-data-no-longer.html

Updated at July 30, 2022:

At May 18, 2022, I noticed that the values from GOOGLEFINANCE on Spreadsheet can be retrieved using a Google Apps Script.

Testing:

Put the following sample formula into the Spreadsheet.

=GOOGLEFINANCE("NASDAQ:GOOG","price",DATE(2022,5,1),DATE(2022,5,10),"DAILY")

The image of this sample situation is as follows.

enter image description here

When the following sample scripts with Spreadsheet service (SpreadsheetApp) are used,

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const values = sheet.getDataRange().getDisplayValues(); // or getValues()
console.log(values);

And, when the following sample scripts with Sheets API are used,

const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
const values = Sheets.Spreadsheets.Values.get(spreadsheetId, "Sheet1").values;
console.log(values);

the following result is obtained.

[
  ["Date", "Close"],
  ["2022/05/02 16:00:00", "2343.14"],
  ["2022/05/03 16:00:00", "2362.59"],
  ["2022/05/04 16:00:00", "2451.5"],
  ["2022/05/05 16:00:00", "2334.93"],
  ["2022/05/06 16:00:00", "2313.2"],
  ["2022/05/09 16:00:00", "2261.68"]
]

Note:

  • Unfortunately, I cannot find the change in this specification in the official document. And, I'm not sure whether this is the current specification. So, this situation might be changed in the future update. Please be careful about this.

References:

Upvotes: 5

Related Questions