-1

I want to create a historic sheet in Google Spreadsheets, where I take the stock data from one sheet and I fill the historic sheet using a specific date in the script.

This is how my sheet looks like: Example

With my script I want to:

1º Fill the first row with my stock names as header and the last column with the sum of all of them (I'd do this manually latter).

2º For a specified date I want to get the quote price from google finance, only if it was bought before the date in the first column of the data, otherwise just put a 0.

 function createHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet1 = ss.getSheetByName("Acciones"); //Get Values
  var sheet2 = ss.getSheetByName("Histórico2"); //Write Historic


  var source = sheet1.getRange("C2:C67");  //Header
  var values = source.getValues();
  var result = new Array(source.getNumRows()+2); 
  result[0] = "";
  for(var x=0; x < source.getNumRows(); x++){
    result[x+1]=values[x].toString()
  }
  result[source.getNumRows()+2]='Total';
  sheet2.appendRow(result);

  Logger.log('Header Saved Successfully');

  var source2 = sheet1.getRange("A2:AB67"); //Get all values form the sheet
  var values2 = source2.getValues();


  var date = new Date(2017,06,20);

  var newLine = new Array (source2.getNumRows()+2);
  newLine[0] = date;

  Logger.log('JS generated date:' + date);

  for (var i in values2){ //For each value
    Logger.log('This is the value number: ' + i);
    if(new Date(values2[i][0]).getTime() <= date.getTime()){

      var ticker = values2[i][3];
      var market = values2[i][4];
      var shares = values2[i][5];
      var name = values2[i][2];
      var price = values2[i][6];
      var costs = values2[i][10] + values2[i][10] + values2[i][12];
      var xchange_buy = values2[i][14];
      var xchange_sell = values2[i][16];
      var sold = values2[i][25];

      Logger.log(' Ticker: ' + ticker + ' Market: ' + market + ' Shares:' +  shares + ' Name: ' + name + ' Price: ' + price + ' Costs: ' + costs + ' Sold: ' + sold);

      var date_formated = Utilities.formatDate(date, "GMT+1:00", "yyyy-MM-dd");
      debugger;
      var stockPrice = '=' + shares +'* INDEX(GoogleFinance("'+ market +  ':' + ticker + '"; "price";"' + date_formated + '"; TODAY());2;2)';
      newLine[i+1] = ""+ stockPrice;
      Logger.log('The vale that will be stored is: ' + stockPrice);
    }
    else {
      newLine[i+1]=0;
      var ticker = values2[i][3];
      var market = values2[i][4];
      var newdate = new Date(values2[i][0])
      Logger.log('The stock value: ' + ticker + ':' + market + ' is not bought till '+ newdate + '.' );
    }
  }

  Logger.log('Lets print the end result:');
  for (var i in newLine){
    Logger.log(newLine[i]);
  }

  sheet2.appendRow(newLine);

  //}
};

Well, I think this is correctly written, and if I check the log, each values is properly obtained: Log

And if I take a look at the array before writing it into the spreadsheet, it also looks OK: Log2

Then I go to the spreadsheet and it looks like this, the yellow cell is the one corresponding with the stock name in yellow, and in the middle there are a lot of NOT_FOUND cells, which aren't shown in the log: enter image description here

I can't understand why does this happen. I'm doing the same in the first step with the header with ".appendRow()" and this problem does not show up.

Edit I've realized that it follows the following pattern: Let's say the header is:

GOOGLE, AMAZON, QUALCOMM, APPLE

Then the second row is like this:

Date, 10xNOT_FOUND, AMAZON_QUERY, 10XNOT_FOUND, QUALCOMM_QUERY, 10XNOT_FOUND, APPLE_QUERY...

So, the first column is omitted (GOOGLE), and then every 10 cells with NOT_FOUND there is one cell with the correct value.

  • 1
    GoogleFinance doesn't work with Google Apps Script anymore, see 6th bullet of the help page: support.google.com/docs/answer/3093281 – Jacob Jan Tuinstra Nov 23 '17 at 22:15
  • @JacobJanTuinstra correct me if I'm wrong, but I though that GoogleFinance won't work if try to make a query from the script, but If I write the query as a string in the cell, and then I enter the spreadsheet to actually send the query It should work right? As you can see in the last photo it's working properly, but just for some values and displaced compared to the header. – nck Nov 24 '17 at 17:09
  • I've updated the question with some information. – nck Nov 24 '17 at 17:29
  • 1
    @pnuts there are 66 rows without the header, but the last 7 are empty right now. So the range with non-empty cells is C2:C59. i just tried to limit the range in the script to non-empty cells but I still get the same problem. – nck Nov 24 '17 at 18:28
1

Well, It took me forever to find out what was the problem. After putting a lot of loggers to see the size of the array, I found out that once I put the array inside the for loop it changed the size. When I looped though it using the same method it just showed the correct values in the log, but using appendrow wrote the whole vector inside the spreadsheet..

So, this is what I updated in the script:

  Logger.log('newLine length at the begining is: ' +  newLine.length);
  newLine[0] = date;

  Logger.log('JS generated date:' + date);

  for (var i = 0; i< values2.length; i++){ //For each value
    Logger.log('This is the stock_value counter: ' + i);
    if(new Date(values2[i][0]).getTime() <= date.getTime()){

      var ticker = values2[i][3];
      var market = values2[i][4];
      var shares = values2[i][5];
      var name = values2[i][2];
      var price = values2[i][6];
      var costs = values2[i][10] + values2[i][10] + values2[i][12];
      var xchange_buy = values2[i][14];
      var xchange_sell = values2[i][16];
      var sold = values2[i][25];

      Logger.log(' Ticker: ' + ticker + ' Market: ' + market + ' Shares:' +  shares + ' Name: ' + name + ' Price: ' + price + ' Costs: ' + costs + ' Sold: ' + sold);

      var date_formated = Utilities.formatDate(date, "GMT+1:00", "yyyy-MM-dd");
      debugger;
      var stockPrice = '=' + shares +'* INDEX(GoogleFinance("'+ market +  ':' + ticker + '"; "price";"' + date_formated + '"; TODAY());2;2)';
      newLine[i+1] = ""+ stockPrice;
      Logger.log('The vale that will be stored is: ' + stockPrice);
    }
    else {
      newLine[i+1]=0;
      var ticker = values2[i][3];
      var market = values2[i][4];
      var newdate = new Date(values2[i][0])
      Logger.log('The stock value: ' + ticker + ':' + market + ' is not bought till '+ newdate + '.' );
    }
  }

  Logger.log('Lets print the end result:');
  Logger.log('newLine length is :' + newLine.length);
  for (var i in newLine){
    Logger.log('This is value number: '+ i);
    Logger.log(newLine[i]);
  }

  //sheet2.appendRow(newLine);
  //Start at row 1, end at the last row of the spreadsheet
  var lastRow = sheet2.getLastRow();
  Logger.log('lastRow + 1 is :' + lastRow + 1);
  Logger.log('newLine length is :' + newLine.length);

  for (var i = 0; i<newLine.length; i++ ){
    Logger.log('getRange is: ' + lastRow + 1 + ', ' + (i+1) + ', ');
    sheet2.setActiveRange(sheet2.getRange(lastRow + 1, i+1,1,1)).setValue('' + newLine[i]);
  }

1º I tried to copy the values one by one instead all at once. It led to even stranger results, because of how the for was looping.

2º I added a lot of logs to check the size of the array.

3º I realized the size of the variable "newLine" changed after the for loop.

4º I changed all the for loops from:

for ( var i  in values2) 

to

for (var i = 0; i < values2.length; i++)

And that was it. The problem came from a wrong use of the for loop. I think in this link you can read the reason of why this happens.

0

Just for those looking for a solution to this problem, I found changing the for loops didn't help in my case.

It seems that Google Doc introduces NOT_FOUND when there are missing elements in the array. Solved it by replacing the missing elements in the array by empty strings "".

Not sure if it's the right way to handle it, but I pulled my hair out long enough to want to share that hack.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.