CODEWITHSUNDEEP
performancegoogle-apps-scriptoptimizationgoogle-sheets
Brian - RGY Studio
Brian - RGY Studio

Reputation: 505

Slow Archiving Google Apps Script

I'm working on a finances tracking sheet and have the entry part of it completed, and working fine. The problem is, it runs very slowly as it goes line by line in most cases. Aside from it being a lot of code, I can't figure out how to speed it up or batch anything together. Additionally, the braces for the beginning and ending of the function in question are not connected (they are both colored red when the cursor is next to them), which I can't understand. Can anyone help clean up my code a bit to speed this up? Here is the function in question:

function onClickSubmit(e) {
  var wkbk = SpreadsheetApp.getActiveSpreadsheet();
  var ss = wkbk.getSheetByName("Entry");

  //Bank Details Move
    var bankDeposit = ss.getRange(3,9).getValue();
    var bankWithdrawal = ss.getRange(3,10).getValue();
    var bankDate = ss.getRange(3,12).getValue();
    var targetSheet = wkbk.getSheetByName("BANK DS");
  if(bankDate != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var bankAmt = (bankDeposit - bankWithdrawal);
    var bankDetail = [[bankDate, bankAmt]];
    targetSheet.getRange(targetRow,1,1,2).setValues(bankDetail);
    targetSheet.getRange(targetRow-1,3).copyTo(targetSheet.getRange(targetRow,3));
    ss.getRange(3,9,1,4).clearContent();
  }
  //Work Chits Move
    var work1Hours = ss.getRange(5,9).getValue();
    var work1Job = ss.getRange(5,10).getValue();
    var work1Rate = ss.getRange(5,11).getValue();
    var work1Date = ss.getRange(5,12).getValue();
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work1Date != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var work1Detail = [[work1Date,work1Job,work1Hours,work1Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work1Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(5,9,1,4).clearContent();
  }
  var work2Hours = ss.getRange(6,9).getValue();
    var work2Job = ss.getRange(6,10).getValue();
    var work2Rate = ss.getRange(6,11).getValue();
    var work2Date = ss.getRange(6,12).getValue();
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work2Date != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var work2Detail = [[work2Date,work2Job,work2Hours,work2Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work2Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(6,9,1,4).clearContent();
  }
    var work3Hours = ss.getRange(7,9).getValue();
    var work3Job = ss.getRange(7,10).getValue();
    var work3Rate = ss.getRange(7,11).getValue();
    var work3Date = ss.getRange(7,12).getValue();
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work3Date != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var work3Detail = [[work3Date,work3Job,work3Hours,work3Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work3Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(7,9,1,4).clearContent();
  }

  //Receipts Move
  var i = 9
  var reDate = ss.getRange(i,12).getValue()
  var targetSheet = wkbk.getSheetByName("RECEIPTS DS");
  if (reDate !=""){
  while (reDate !="") {
    var reAmt = ss.getRange(i,9).getValue();
    var rePlace = ss.getRange(i,10).getValue();
    var reCat = ss.getRange(i,11).getValue();
    var reDate = ss.getRange(i,12).getValue();
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var reDetail = [[reDate,rePlace,reCat,reAmt]];
    targetSheet.getRange(targetRow,1,1,4).setValues(reDetail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(i,9,1,4).clearContent();
    i++
  }
  }

  //Safe Move 3, 9, 13 (dates) 3-8, 9-12, 13-18
    var safeDate = ss.getRange(3,6).getValue();
    var targetSheet = wkbk.getSheetByName("SAFE DS");
  for (var i=3;i<9;i++){  
    var safeIn = ss.getRange(i,2).getValue();
    var safeOut = ss.getRange(i,4).getValue();
    var safeCnt = (safeIn - safeOut);
    var safeType = ss.getRange(i,3).getValue();

  if(safeCnt != 0){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();

    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
//    targetSheet.getRange(targetRow-1,3).copyTo(targetSheet.getRange(targetRow,3));
    ss.getRange(i,2).clearContent();
    ss.getRange(i,4).clearContent();
  }
}
    ss.getRange(3,6).clearContent();
  //Safe Move 9 (rolls) 3-8, 9-12, 13-18
    var safeDate = ss.getRange(9,6).getValue();
    var targetSheet = wkbk.getSheetByName("SAFE DS");
  for (var i=9;i<13;i++){  
    var safeIn = ss.getRange(i,2).getValue();
    var safeOut = ss.getRange(i,4).getValue();
    var safeCnt = (safeIn - safeOut);
    var safeType = ss.getRange(i,3).getValue();

  if(safeCnt != 0){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();

    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    ss.getRange(i,2).clearContent();
    ss.getRange(i,4).clearContent();
  }
}
    ss.getRange(9,6).clearContent();
  //Safe Move (loose) 13-18
    var safeDate = ss.getRange(13,6).getValue();
    var targetSheet = wkbk.getSheetByName("SAFE DS");
  for (var i=13;i<19;i++){  
    var safeIn = ss.getRange(i,2).getValue();
    var safeOut = ss.getRange(i,4).getValue();
    var safeCnt = (safeIn - safeOut);
    var safeType = ss.getRange(i,3).getValue();

  if(safeCnt != 0){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();

    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    ss.getRange(i,2).clearContent();
    ss.getRange(i,4).clearContent();
  }
}
    ss.getRange(13,6).clearContent();
}

Here is a copy of the Sheet for you to see how it works: https://docs.google.com/spreadsheets/d/1Ij7DPqQnVAR9cCpqZRf6QFUIKfgrvLvj-y5gyUPKJMw/edit?usp=sharing

Thanks in advance!

UPDATE I accepted the more thorough of the two answers, but both offered excellent insight. While this answer has the most helpful information, the other answer added a lot as well. For anyone interested, I used a lot of these edits, and also worked at removing the calls to SpreadsheetApp methods by calling them outside of loops/if tests, and then building arrays of the results. The combination of these two answers sped up the code by about 400%, so thank you to both of you!

To further help anyone who is interested in speeding up Google Script code, here is my modified function Note, I did have to keep in one instance of "A1:A" vs. getLastRow() because in that particular target sheet there are a number of columns with information in them that need to be ignored:

function onClickSubmit(e) {
  var wkbk = SpreadsheetApp.getActiveSpreadsheet();
  var ss = wkbk.getSheetByName("Entry");

  //Bank Details Move
    var t=ss.getRange(3,9,1,4).getValues()[0];
    var bankDeposit = t[0];
    var bankWithdrawal = t[1];
    var bankDate = t[3];
    var targetSheet = wkbk.getSheetByName("BANK DS");
  if(bankDate != ""){
    var targetRow = targetSheet.getRange(1,1,targetSheet.getLastRow()).getValues().length + 1;
    var bankAmt = (bankDeposit - bankWithdrawal);
    var bankDetail = [[bankDate, bankAmt]];
    targetSheet.getRange(targetRow,1,1,2).setValues(bankDetail);
    targetSheet.getRange(targetRow-1,3).copyTo(targetSheet.getRange(targetRow,3));
    ss.getRange(3,9,1,4).clearContent();
  }
  //Work Chits Move
    var vals=ss.getRange(5,9,3,4).getValues();
    var work1Hours = vals[0][0];
    var work1Job = vals[0][1];
    var work1Rate = vals[0][2];
    var work1Date = vals[0][3];
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work1Date != ""){
    var targetRow = targetSheet.getRange(1,1,targetSheet.getLastRow()).getValues().length +1;
    var work1Detail = [[work1Date,work1Job,work1Hours,work1Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work1Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(5,9,1,4).clearContent();
    targetRow = targetRow + 1;
  }
    var work2Hours = vals[1][0];
    var work2Job = vals[1][1];
    var work2Rate = vals[1][2];
    var work2Date = vals[1][3];
  if(work2Date != ""){
    var work2Detail = [[work2Date,work2Job,work2Hours,work2Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work2Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(6,9,1,4).clearContent();
    targetRow = targetRow + 1;
  }
    var work3Hours = vals[2][0];
    var work3Job = vals[2][1];
    var work3Rate = vals[2][2];
    var work3Date = vals[2][3];
  if(work3Date != ""){
    var work3Detail = [[work3Date,work3Job,work3Hours,work3Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work3Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(7,9,1,4).clearContent();
    targetRow = targetRow + 1
  }

  //Receipts Move
  var i = 0
  var targetSheet = wkbk.getSheetByName("RECEIPTS DS");
  var reDetail = [];
  var reVals=ss.getRange(9,9,20,4).getValues();
  var reDate = reVals[0,3]
  var targetRow = targetSheet.getRange(1,1,targetSheet.getLastRow()).getValues().length + 1;
  if (reDate !=""){
  while (reVals[i][3] !="") {
    var reAmt = reVals[i][0];
    var rePlace = reVals[i][1];
    var reCat = reVals[i][2];
    var reDate = reVals[i][3];
    var reDetailNew = [[reDate, rePlace, reCat, reAmt]];
    var reDetail = reDetail.concat(reDetailNew);
    i=i+1
  }
    targetSheet.getRange(targetRow,1,i,4).setValues(reDetail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,i,3));
    ss.getRange(9,9,i+1,4).clearContent();


  //Safe Move (bills)

    var targetSheet = wkbk.getSheetByName("SAFE DS");
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var safeVals = ss.getRange(3,2,16,5).getValues();
    var safeDate = safeVals[0][4];
  for (var i=0;i<6;i++){
    var safeIn = safeVals[i][0];
    var safeOut = safeVals[i][2];
    var safeCnt = (safeIn - safeOut);
    var safeType = safeVals[i][1];

  if(safeCnt != 0){    
    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    targetRow = targetRow + 1;
  }
}
  //Safe Move (rolls)
    var safeDate = safeVals[6][4];
  for (var i=6;i<10;i++){  
    var safeIn = safeVals[i][0];
    var safeOut = safeVals[i][2];
    var safeCnt = (safeIn - safeOut);
    var safeType = safeVals[i][1];

  if(safeCnt != 0){
    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    targetRow = targetRow + 1;
  }
}
  //Safe Move (loose)
    var safeDate = safeVals[10][4];
  for (var i=10;i<16;i++){  
    var safeIn = safeVals[i][0];
    var safeOut = safeVals[i][2];
    var safeCnt = (safeIn - safeOut);
    var safeType = safeVals[i][1];

  if(safeCnt != 0){
    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    targetRow = targetRow + 1;
  }
}
    ss.getRange(3,2,16,1).clearContent();
    ss.getRange(3,4,16,1).clearContent();
    ss.getRange(3,6,16,1).clearContent();
}
}

Upvotes: 0

Views: 95

Answers (2)

Wicket
Wicket

Reputation: 38218

Your script is slow because it's making many calls to the SpreadsheetApp methods. To improve it's performance you should change the logic to reduce these calls and use Arrays to manipulate the data.

I.E. instead of using multiple getValue() use only one getValues(), you could even use getDatatRange() to get the "data range" (a range embracing all the cells having data including the blank cell between them) of a sheet, then use getValues() to get all the sheet values.

As a rule of thumb avoid using SpreadsheetApp methods inside loops (for, while, etc)

Upvotes: 1

Cooper
Cooper

Reputation: 64072

Here's some suggestions:

I put my suggestions as comments below your code.

In general avoid the use of getValue() use getValues() instead it returns a 2D array of data. Read the documentation and also look at the returned values in the debugger so that you really understand what you data looks like: [[row0],[row1],[row2]....]

and avoid the use of A1 notations of the form A1:A. Use Sheet.getRange(1,1,Sheet.getLastRow().... instead.

function onClickSubmit(e) {
  var wkbk = SpreadsheetApp.getActiveSpreadsheet();
  var ss = wkbk.getSheetByName("Entry");
  //Bank Details Move
    var bankDeposit = ss.getRange(3,9).getValue();
    var bankWithdrawal = ss.getRange(3,10).getValue();
    var bankDate = ss.getRange(3,12).getValue();
    var targetSheet = wkbk.getSheetByName("BANK DS");
    //var t=ss.getRange(3,9,1,4).getValues()[0];
    //var bankDeposit = t[0];
    //var bankWithdrawal = t[1];
    //var bankDate = t[3];
    //var targetSheet = wkbk.getSheetByName("BANK DS");
  if(bankDate != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();//This often returns nulls between data last row and max rows  
    var targetRow = Avals.filter(String).length + 1;
    //var targetRow = targetSheet.getRange(1,1,targetSheet.getLastRow()).getValues().length;
    var values = ss.getRange("A:A").getValues();//Same problem as described above
    var bankAmt = (bankDeposit - bankWithdrawal);
    var bankDetail = [[bankDate, bankAmt]];
    targetSheet.getRange(targetRow,1,1,2).setValues(bankDetail);
    targetSheet.getRange(targetRow-1,3).copyTo(targetSheet.getRange(targetRow,3));
    ss.getRange(3,9,1,4).clearContent();
  }
  //Work Chits Move
    var work1Hours = ss.getRange(5,9).getValue();
    var work1Job = ss.getRange(5,10).getValue();
    var work1Rate = ss.getRange(5,11).getValue();
    var work1Date = ss.getRange(5,12).getValue();
    //try as above to get data all at once
    //var vals=ss.getRange(5,9,1,4).getValues();
    //then use vals[0]...
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work1Date != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var work1Detail = [[work1Date,work1Job,work1Hours,work1Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work1Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(5,9,1,4).clearContent();
  }
  var work2Hours = ss.getRange(6,9).getValue();
    var work2Job = ss.getRange(6,10).getValue();
    var work2Rate = ss.getRange(6,11).getValue();
    var work2Date = ss.getRange(6,12).getValue();
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work2Date != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var work2Detail = [[work2Date,work2Job,work2Hours,work2Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work2Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(6,9,1,4).clearContent();
  }
    var work3Hours = ss.getRange(7,9).getValue();
    var work3Job = ss.getRange(7,10).getValue();
    var work3Rate = ss.getRange(7,11).getValue();
    var work3Date = ss.getRange(7,12).getValue();
    var targetSheet = wkbk.getSheetByName("WORK DS");
  if(work3Date != ""){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var work3Detail = [[work3Date,work3Job,work3Hours,work3Rate]];
    targetSheet.getRange(targetRow,1,1,4).setValues(work3Detail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(7,9,1,4).clearContent();
  }

  //Receipts Move
  var i = 9
  var reDate = ss.getRange(i,12).getValue()
  var targetSheet = wkbk.getSheetByName("RECEIPTS DS");
  if (reDate !=""){
  while (reDate !="") {
    var reAmt = ss.getRange(i,9).getValue();
    var rePlace = ss.getRange(i,10).getValue();
    var reCat = ss.getRange(i,11).getValue();
    var reDate = ss.getRange(i,12).getValue();
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();
    var reDetail = [[reDate,rePlace,reCat,reAmt]];
    targetSheet.getRange(targetRow,1,1,4).setValues(reDetail);
    targetSheet.getRange(targetRow-1,5,1,3).copyTo(targetSheet.getRange(targetRow,5,1,3));
    ss.getRange(i,9,1,4).clearContent();
    i++
  }
  }

  //Safe Move 3, 9, 13 (dates) 3-8, 9-12, 13-18
    var safeDate = ss.getRange(3,6).getValue();
    var targetSheet = wkbk.getSheetByName("SAFE DS");
  for (var i=3;i<9;i++){  
    var safeIn = ss.getRange(i,2).getValue();
    var safeOut = ss.getRange(i,4).getValue();
    var safeCnt = (safeIn - safeOut);
    var safeType = ss.getRange(i,3).getValue();

  if(safeCnt != 0){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();

    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
//    targetSheet.getRange(targetRow-1,3).copyTo(targetSheet.getRange(targetRow,3));
    ss.getRange(i,2).clearContent();
    ss.getRange(i,4).clearContent();
  }
}
    ss.getRange(3,6).clearContent();
  //Safe Move 9 (rolls) 3-8, 9-12, 13-18
    var safeDate = ss.getRange(9,6).getValue();
    var targetSheet = wkbk.getSheetByName("SAFE DS");
  for (var i=9;i<13;i++){  
    var safeIn = ss.getRange(i,2).getValue();
    var safeOut = ss.getRange(i,4).getValue();
    var safeCnt = (safeIn - safeOut);
    var safeType = ss.getRange(i,3).getValue();

  if(safeCnt != 0){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();

    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    ss.getRange(i,2).clearContent();
    ss.getRange(i,4).clearContent();
  }
}
    ss.getRange(9,6).clearContent();
  //Safe Move (loose) 13-18
    var safeDate = ss.getRange(13,6).getValue();
    var targetSheet = wkbk.getSheetByName("SAFE DS");
  for (var i=13;i<19;i++){  
    var safeIn = ss.getRange(i,2).getValue();
    var safeOut = ss.getRange(i,4).getValue();
    var safeCnt = (safeIn - safeOut);
    var safeType = ss.getRange(i,3).getValue();

  if(safeCnt != 0){
    //Finds first blank cell in column A, to enter new data in:
    var Avals = targetSheet.getRange("A1:A").getValues();
    var targetRow = Avals.filter(String).length + 1;
    var values = ss.getRange("A:A").getValues();

    var safeDetail = [[safeDate, safeType,safeCnt]];
    targetSheet.getRange(targetRow,1,1,3).setValues(safeDetail);
    ss.getRange(i,2).clearContent();
    ss.getRange(i,4).clearContent();
  }
}
    ss.getRange(13,6).clearContent();
}

Upvotes: 1

Related Questions