6

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++;
  }
}
jjunbbug
  • 117
  • 1
  • 6
  • 6
    If you want to retrieve the values from GOOGLEFINACE using the script, unfortunately, it cannot be achieved because of [this reason](https://gsuiteupdates.googleblog.com/2016/09/historical-googlefinance-data-no-longer.html). – Tanaike Jul 10 '19 at 02:49
  • 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();` – Cooper Jul 10 '19 at 04:33
  • It seems now it is possible to interact with googlefinance data in apps script – PriyankaJ Jan 13 '23 at 14:10

0 Answers0