Google Sheets Script Optimization - Avoiding Max Execution Time

Question

I'm running a script to remove rows that contain a text string in a Google Sheet. My target spreadsheet has 50k rows and I'm running into max execution time limits (1 hr per day and 6 min per run). I believe my script could be bloated and I'm not sure how to optimize it.

Note: I am not the author of this code.

 function deleteRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('All Marketplaces Removed');
var r = s.getRange('B:B');
var v = r.getValues();
for(var i=v.length-1;i>=1;i--)
if(v[i][0].includes("amazon")) {
s.deleteRow(i+1);
}
};

It's probably the delete call for each row. Are a lot of rows deleted? If that's the problem you could do it all on Javascript then replace the entire sheet at the end. To be sure remove the delete call and time the function without that call — Jazz, Jul 14 '21 at 18:11
Take a look at what's said about `getRange` in the comments [here](https://stackoverflow.com/questions/56517186/searching-through-a-range-with-textfinder-is-slower-than-searching-the-entire-sh) — James, Jul 14 '21 at 18:31
You can check the tips from this existing post at https://stackoverflow.com/a/29800759/15384825 — SputnikDrunk2, Jul 14 '21 at 21:15

score 0 · Answer 1 · answered Jul 14 '21 at 23:48

Sheet operations are significantly slower than javascript, especially in large loops. See a similar problem here.

Try this:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('All Marketplaces Removed');
  var val = s.getDataRange().getValues();
  var lr = s.getLastRow();
  var lc = s.getLastColumn();
  var res = [];
  for (var i = 0; i < lr; i++){
    var v = val[i][1];
    if (v != 'amazon'){
      res.push(val[i]);
    }
  }
  s.getDataRange().clearContent();
  s.getRange(1, 1, res.length, lc).setValues(res);
};

Google Sheets Script Optimization - Avoiding Max Execution Time

1 Answers1