Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

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

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

 Share!