Is there a way to interact with google finance data in google app script?

Question

I'm trying to calculate the rate of change in Google finances' data. For example if price of AMD was 2 dollar and tomorrow it was 1 dollars, then the result I want is -0.5. I called the Google finance data using =GOOGLEFINACE function in the Google spreadsheet. For some reason, my app script logs are telling me I only have 2 cells while I have years worth of stock data in the cell. What's confusing is that I don't get any error warnings from debugger. At the same time,​ the Gsheet doesn't change.

I imported the data into the Google sheet by using this function.

=GOOGLEFINANCE("AMD", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")

google app script

function Practice() {
  var app = SpreadsheetApp;
  var theSheet = app.getActiveSpreadsheet().getSheetByName("Sheet1");
  var Avals = theSheet.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  Logger.log(Alast);
  for(var i=1;i<Alast;i++){
  var j = i+1;
  var someCell = theSheet.getRange(i, 1).getValue();
  someCell = theSheet.getRange(j,1).getValue()/someCell;
  someCell = someCell-1;
  theSheet.getRange(j, 3).setValue(someCell)
  j++;
  }
}

Unsightly

If you want to retrieve the values from GOOGLEFINACE using the script, unfortunately, it cannot be achieved because of this reason. – Reviere

This var Avals = theSheet.getRange("A1:A").getValues(); var Alast = Avals.filter(String).length; could be replaced with theSheet.getRange('A1').getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues(); – Ate

Answer 1

in 2024 C.E. you can do:

=INDEX(IFERROR(LET(a; A2:A; OFFSET(a;1;)/a-1)))

or direct approach:

=INDEX(IFERROR(LET(a; 
 QUERY(GOOGLEFINANCE("AMD"; "price"; DATE(2014;1;1); DATE(2014;12;31); "DAILY"); 
 "select Col2 offset 1"; ); QUERY(a; "offset 2"; )/a-1)))

Liquor