tanaikech/submit.md

## submit.md

      
    Raw
  

              submit.md
            
          
    Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script

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

This script uses Spreadsheet service (SpreadsheetApp).
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();
const values = Sheets.Spreadsheets.Values.get(spreadsheetId, "Sheet1").values;
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.

Reference


GOOGLEFINANCE