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

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
– Tanaike, Commented Apr 8, 2020 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
– Filippo, Commented Apr 8, 2020 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
– Tanaike, Commented Apr 8, 2020 at 23:30

Filippo · Accepted Answer · 2020-04-08 23:05:47Z

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

}

halfer · Accepted Answer · 2020-10-24 21:39:56Z

1

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:

edited Oct 24, 2020 at 21:39

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Apr 8, 2020 at 22:13

Tanaike

203k12 gold badges126 silver badges222 bronze badges

1 Comment

Filippo Over a year ago

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.

Cooper · Accepted Answer · 2020-04-08 23:09:20Z

0

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

answered Apr 8, 2020 at 23:09

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Collectives™ on Stack Overflow

google script loop performance

3 Answers 3

Comments

Modification points:

Modified script:

Note:

References:

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Modification points:

Modified script:

Note:

References:

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related