How can I improve speed when looping through hundreds of rows?

Question

I'm trying to loop through a worksheet and calculate the "First In, First Out" cost basis of position. My worksheet in only ~120 rows right now, but it will undoubtedly grow with time. The function already performs pretty slowly, so I'm looking for solutions on how to speed it up. I'm also a noob coder and this is my first time using javascript, so any other advice would be appreciated!

Thanks!

function FifoCostBasis(symbol, quantity) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Transactions Import");
  var lastRow = 150;
  var lastCol = 12;
  var costBasisSold = Number(0);
  var quantitySold = Number(0);


    for (var row = lastRow; row >= 2; row--){
      var action = sheet.getRange(row,2).getValue();
      var sym = sheet.getRange(row,3).getValue();
      var qSold = Number(sheet.getRange(row,5).getValue());
      var cbSold = Number(sheet.getRange(row,10).getValue());
      var price = Number(sheet.getRange(row, 6).getValue()); 

      if(["Buy", "Reinvest Shares", "Short Term Cap Gain Reinvest", 
"Qual Div Reinvest", "Long Term Cap Gain 
Reinvest"].indexOf(action)>=0
         && sym == symbol){
        quantitySold += qSold;
        costBasisSold += cbSold;

        if(quantitySold > quantity){ 
          var difference = quantitySold - quantity;
          var cbSoldAdj = difference*price;
          quantitySold = quantitySold - difference;
          costBasisSold = costBasisSold - cbSoldAdj;  
        };
      };
    };

      return costBasisSold;

};

Possible duplicate of [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, Dec 23 '18 at 20:42

score 1 · Answer 1 · edited Dec 24 '18 at 21:23

1

You should minimize the calls to getValue(). Rather than getting one value at a time, you should pull down an array of values.

var data = sheet.getRange(2, 1, lastRow, lastCol).getValues();

This will return a Javascript array object. One thing to remember is that a Javascript array starts at an index value of 0.

 var row2 = data[0];  //Since we the range started at row 2, row 2 will be at position 0.
 var row2col1 = row[0][0];

If you work with the values in the array, the process will be much faster.

edited Dec 24 '18 at 21:23

Rubén

34,714
9
70
166

answered Dec 23 '18 at 22:02

terrywb

3,740
3
25
50

Ok, I will try this. Thank you! – TarHeel45 Dec 24 '18 at 00:53
I just made one small edit. Note the call to getValues() after selecting the range. – terrywb Dec 24 '18 at 02:48

Cooper · Answer 2 · 2018-12-24T22:08:58.127

Something like this might work for you:

function FifoCostBasis(symbol, quantity) {
  if(symbol && quantity){
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sh=ss.getSheetByName("Transactions Import");
    var lastRow=150;//there is a sh.getLastRow() function that might work for you
    var lastCol=12;//there is a sh.getLastColumn() function that might work for you
    var rg=sh.getRange(2,1,149,12);//row 2, column 1, num rows = 150-2+1, 12 columns
    var vA=rg.getValues();//all data for the entire sheet is acquired here
    var costBasisSold=0;
    var quantitySold=0;
    for(var i=vA.length-1;i>=0;i++){//i=0 is row 2
      var action=vA[i][1];
      var sym=vA[i][2];
      var qSold=Number(vA[i][4]);
      var dbSold=Number(vA[i][9]);
      var price=Number(vA[i][5]);
      if((["Buy","Reinvest Shares","Short Term Cap Gain Reinvest","Qual Div Reinvest","Long Term Cap Gain Reinvest"].indexOf(action)>=0) && sym==symbol){
        quantitySold += qSold;
        costBasisSold += cbSold;
      }
      if(quantitySold > quantity) { 
        var difference = quantitySold - quantity;
        var cbSoldAdj = difference*price;
        quantitySold = quantitySold - difference;
        costBasisSold = costBasisSold - cbSoldAdj;  
      }
    }
    return costBasisSold;
  }else{
    throw('Error: Invalid Inputs in function FifoCostBasis');
  }
}

How can I improve speed when looping through hundreds of rows?

2 Answers2