I have a Google sheet which I follow stock prices Daily; every day I get the stock prices online and write them to the sheet via apps script. Now I want to check monthly P/L for each stock and create a sheet that shows monthly P/L for each stock.

I am trying to get the prices with dates for each stock in an array and then push the first and last entries for every month in "monthly" array. And then I am trying to write this monthly values in "Aylık P/L" sheet. The code works fine till I added "for" statement to write the monthly datas to the sheet; it throws: "Error in Monthly: TypeError: Cannot read properties of undefined (reading '0')": Here goes the code:

function aylikRapor() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Fiyat Çek');
  var sheet2 = ss.getSheetByName('Günlük Fon Takip');
  var sheet3 = ss.getSheetByName('Haftalık Fon Takip');
  var sheet4 = ss.getSheetByName('Aylık Fon Takip');
  var sheet5 = ss.getSheetByName('Funds Main');
  var sheet6 = ss.getSheetByName('Aylık');
  var sheet7 = ss.getSheetByName('Aylık P/L');

  var pfs = sheet5.getRange(1,1).getValue(); // pfs = PortföydekiFonSayısı
  var i = 1;
  var f = 1;
  var a;
  var b;

  try {
    
    for(i=1; i<=pfs; i  ) {
        var colH = sheet2.getRange(1,3*i,1,1);
        var harf = colH.getA1Notation().match(/([A-Z] )/)[0];
        var column = sheet2.getRange(harf ":" harf);
      
        // get lastFilledRow
        var value = ''
        const max = sheet2.getMaxRows()
        var values = column.getValues()
        values = [].concat.apply([], values)
        for (row = max - 1; row > 0; row--) {
          value = values[row]
          if (value != '') { break }
      }

      lastRowD = row   1;
      Logger.log(lastRowD);

   let veriler = sheet2.getRange(14,3*i-1,lastRowD-12,2).getValues();
    

    let month = veriler[0][0].getMonth();

    
    let monthly = [[veriler[0][0],veriler[0][1]]];
    
    veriler.forEach( (row,index) => {
        
        let test = new Date(row[0]).getMonth();
        if( test !== month ) {
          monthly.push([veriler[index-1][0],veriler[index-1][1]]);
          
          monthly.push([row[0],row[1]]);
          month = test;

        }
      }
    )
    Logger.log(monthly);

      for (f=1, a=0, b=1; f <=monthly.length-1; f  , a =2, b =2){
        sheet7.getRange(3 f,5*i-2).setValue(monthly[a][0]); 
        sheet7.getRange(3 f,5*i-1).setValue(monthly[a][1]);
        sheet7.getRange(3 f,5*i).setValue(monthly[b][0]);
        sheet7.getRange(3 f,5*i 1).setValue(monthly[b][1]);
      }
    
  }
  }
  catch(err) {
    Logger.log("Error in Monthly: " err);
  }
}

I think there is a null value in "monthly" array; but I could not figure out how I can solve it!

Execution Log:

Yürütme günlüğü
09:49:37    Bildirim    Yürütme işlemi başlatıldı
09:49:49    Bilgi   119.0
09:49:50    Bilgi   [[Wed Aug 17 00:00:00 GMT 03:00 2022, 0.080777], [Wed Aug 31 00:00:00 GMT 03:00 2022, 0.082585], [Thu Sep 01 00:00:00 GMT 03:00 2022, 0.0831], [Fri Sep 30 00:00:00 GMT 03:00 2022, 0.08431], [Mon Oct 03 00:00:00 GMT 03:00 2022, 0.085019], [Mon Oct 31 00:00:00 GMT 03:00 2022, 0.096867], [Tue Nov 01 00:00:00 GMT 03:00 2022, 0.098144], [Wed Nov 30 00:00:00 GMT 03:00 2022, 0.115665], [Thu Dec 01 00:00:00 GMT 03:00 2022, 0.117343], [Fri Dec 30 00:00:00 GMT 03:00 2022, 0.126554], [Mon Jan 02 00:00:00 GMT 03:00 2023, 0.127234], [Thu Jan 12 00:00:00 GMT 03:00 2023, 0.111894], [, ]]
09:49:50    Bilgi   Error in Monthly: TypeError: Cannot read properties of undefined (reading '0')
09:49:38    Bildirim    Yürütme işlemi tamamlandı

CodePudding user response：

I have fixed the problem: As you can see from the execution log; for i=1 the first monthly array length is 12; during the for (f=1, a=0, b=1; f <=monthly.length-1; f , a =2, b =2) the max f should be 6; so after 6 there would be no values in the array.

I fixed the problem by the following amendment:

(f=1, a=0, b=1; f <=monthly.length/2; f  , a =2, b =2)

I hope the others could get some help in case problems like this.