Gists

This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.

When I had tested this before, I had got the value of #N/A. About retrieving the values from GOOGLEFINANCE function on Google Spreadsheet, I had known “Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets”. By this situation, #N/A had been returned when the value had been retrieved using a script.

But, now (May 18, 2022), it seems that he values can be retrieved by the Spreadsheet service (SpreadsheetApp) and Sheets API.

## Sample formula

As a sample formula, I used the following formula. This is from here.

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


## Sample situation

When the above formula is used, the following result is obtained on Google Spreadsheet.

## Sample script

In order to retrieve the values from GOOGLEFINANCE on Google Spreadsheet, I used the following sample scripts.

### Sample 1

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


### Sample 2

This script uses Sheets API.

const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
console.log(values);


## Result

When the above scripts are run, the same result of the following values is retrieved.

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

• I’m not sure whether this is a temporal situation, because I cannot find the official document. If you have any information about this, please tell me. I’m glad for it.