Google Sheets Apps Script - batch loop with if statement-CodePudding

I wrote simple code that loops through a report and deletes unused rows based on the result of the first column. Simply if it says "DEL" it deletes the row.

Since it is not a batch loop it takes forever to run. I know I am supposed to make it a batch loop to make it faster, but after watching many tutorials I am still lost, largely due to not knowing how to integrate the IF statement with an array.

Here's the code:

function CompVM() {

var app = SpreadsheetApp;
var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
var sheet = SpreadsheetApp.getActive().getSheetByName('XML');
sheet.getRange(1,1);
for (var rooow = 3; rooow <= 1554; rooow  ){
var checkCell = sheet.getRange(rooow,1).getValue();
  if(checkCell == "DEL"){
  sheet.deleteRow(rooow);
  rooow = rooow - 1;
}
}
app.getUi().alert('Import File prepared, check for errors now');
  
};

Can anyone help how to make a fast loop out of this code? Maaany thanks

CodePudding user response：

Solution #1 - Try

function CompVM() {
  var app = SpreadsheetApp;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('XML');
  var data = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var rowToBeDeleted = [];
  for (let rooow in data) {
    if (data[rooow].join('') == 'DEL') {
      rowToBeDeleted.push( rooow   1)
    }
  };
  rowToBeDeleted.reverse().forEach(x => sheet.deleteRow(x));
  app.getUi().alert('Import File prepared, check for errors now');
};

you need to get values all at once to reduce the time

there is also others solutions:

sort by column 1, check where DEL begins and ends and delete all rows at once
or use batchupdate if necessary (see next section)

Solution #2 - enable google sheets api services

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('XML');
  var values = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var sheetId = sheet.getSheetId();
  var requests = values.reduce((rowToBeDeleted, [value], i) => {
    if (value == 'DEL') rowToBeDeleted.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i   1, dimension: "ROWS" } } });
    return rowToBeDeleted;
  }, []).reverse();
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}