google script loop performance

Question

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 ~1200 rows and it is deleting about 2-3 rows per second!

Is the script I wrote very inefficient? I am not familiar with Javascript yet, but it does not look so inefficient to me.

Is it normal? Is it the server very slow now, but usually much better?

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");  
  var drng = sheet.getDataRange();
  var rng = sheet.getRange(2,1, drng.getLastRow()-1,26);

  var rangeformula = sheet.getRange(2,26, drng.getLastRow()-1);
  rangeformula.setFormula('=SUM(K2:V2)'); var rngA = rng.getValues();


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

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

    sheet.deleteRow(i);}}

Read best practices. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. — TheMaster, Apr 08 '20 at 22:44
Thank you for replying. I couldn't notice that you don't want to use Sheets API. This is due to my poor English skill. I deeply apologize for this. From your replying, I could understand my answer was not useful for your situation. So I have to delete it. Because I don't want to confuse other users. About your trying of `I am going to try to delete all rows once the loop is completed. Or maybe I can sort by col26 and then delete all the 0s at once.`, I think that it's useful. So when you made it, please post it as an answer. I think that it will be useful for other users. — Tanaike, Apr 08 '20 at 22:46
your answer was great, it pointed to the right direction! It may have also being useful for people more familiar with APIs. I found a workaround for beginners like me and I will post it shortly. Thank you again! — Filippo, Apr 08 '20 at 23:02
Thank you for replying. I saw your answer. I think that your answer will be useful for other users. And also, I confirmed the undelete request. So I undeleted my answer, because I thought that it might be useful for other users from your replying. Thank you, too. — Tanaike, Apr 08 '20 at 23:30

score 2 · Accepted Answer · answered Apr 08 '20 at 23:05

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() {


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

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




rng.clearContent();

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

}

score 1 · Answer 2 · edited Oct 24 '20 at 21:39

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:

var len = 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:

Thank you for your answer! I did some research based on your answer and yes, the delete row is what is causing the slow down. Unfortunately I am very unfamiliar with APIs and not sure I can run the solution you proposed. However I am going to try to delete all rows once the loop is completed. Or maybe I can sort by col26 and then delete all the 0s at once. — Filippo, Apr 08 '20 at 22:35

score 0 · Answer 3 · answered Apr 08 '20 at 23:09

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++);
    }
  }
}

google script loop performance

3 Answers3

Modification points:

Modified script:

Note:

References:

Linked