Google Script Loop Performance

I am new to google scripts and I am not sure why I am experiencing such poor performances compared to Excel VBA for a simple loop. I attached the code below which is a loop on ~120

Solution 1:

Ok, it is not obvious for a beginner that deleting rows is such an expensive process (compared to VBA). Here is a work around I used to avoid deleting rows inside a loop.

PS: I am new to this, so it is not the most elegant way, but could be helpful for others.

function removeEmpty() {


varss= SpreadsheetApp.getActiveSpreadsheet();
  varsheet= ss.getSheetByName("Sheet1");  
  vardrng= sheet.getDataRange();
  varrng= sheet.getRange(2,1, drng.getLastRow()-1,26);
  varrangeformula= sheet.getRange(2,26, drng.getLastRow()-1);
  rangeformula.setFormula('=SUM(K2:V2)');
  varrngA= rng.getValues();
  varnewRangeVals= [];
  varlen= rngA.length+1;
  for(vari= len; i >=2; i--){
    if(rngA[i-2][25] != 0){

  newRangeVals.push(rngA[i-2]);
};
  };




rng.clearContent();

  varnewRange= sheet.getRange(2,1,newRangeVals.length, newRangeVals[0].length);
  newRange.setValues(newRangeVals);

}

Solution 2:

• You want to reduce the process cost of your script.

Modification points:

• In your script, I think that the reason of your issue is due to the cost of deleteRow rather than the cost of for loop. In this case, in order to reduce the cost, I would like to propose to use Sheets API. When Sheets API is used, the each rows can be deleted by one API call. By this, I think that the cost can be reduced.
• In your for loop, rngA[i-2] is run in the loop of for(var i = len; i>=1; i--). In this case, I think that when i is 1, an error occurs. So if you want to loop from before one element from the last element to the 1st element, please modify to for(var i = len; i>=2; i--) {.
• But in this modification, the for loop is not used.

Modified script:

When your script is modified, please modify as follows. Before you run the script, please enable Sheets API at Advanced Google services.

From:

varlen = rngA.length;
for(var i = len; i>=1; i--) {

  if(rngA[i-2][25] == 0){

    sheet.deleteRow(i);}}

To:

const sheetId = sheet.getSheetId();
const requests = rngA.reduce((ar, r, i) => {
  if (r[25] === 0) {
    ar.push({deleteDimension: {range: {sheetId: sheetId, startIndex: i + 1, endIndex: i + 2, dimension: "ROWS"}}});
  }
  return ar;
}, []).reverse();
Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());

Note:

• Please run the script with enabling V8.

References:

• Advanced Google services
• Method: spreadsheets.batchUpdate
• DeleteDimensionRequest

Solution 3:

function myfunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName("Sheet1");  
  var drng=sheet.getDataRange();
  var rng=sheet.getRange(2,1,sheet.getLastRow()-1,26);
  var vA=rng.getValues();
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if(vA[i][25]==0){
      sheet.deleteRow(i+2-d++);
    }
  }
}