CODEWITHSUNDEEP
google-apps-scriptdelete-rowprocessing-efficiency
Spratty59
Spratty59

Reputation: 1

Google Script - Delete a row based on a blank value in a column

newish to Google Apps Script and typically tend to fall through it when I'm writing a script.

I have written the below script but it is aggressively inefficient. The app is run against over 2k rows of data in 5 columns trying to remove any rows where the cell contains a blank value.

This probably takes the code longer than it takes me manually so trying to find a way to make this more efficient.

Thanks in advance.

function process() {

var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var mProSheet = ss.getSheetByName("MCC-Processed");

//Remove rows where column E is blank
var mProRange = mProSheet.getDataRange();
var mProRangVal = mProRange.getValues();
var deleteVal = '' //Delete value is blank
var deleteColumIdentifier = 4 //column E is 4th column (A=0)

for(var i = mccProRangVal.length-1; i >= 0; i--){
  if(mccProRangVal[i][deleteColumIdentifier] === deleteVal){
    mProSheet.deleteRow(i+1);
  }
}

}

Upvotes: 0

Views: 1484

Answers (1)

Twilight
Twilight

Reputation: 2645

Script:

function process2() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mProSheet = ss.getSheetByName("MCC-Processed");
  var mProRangVal = mProSheet.getDataRange().getValues();
  var deleteVal = ''

  var valToDelete = mProRangVal.filter(function (item) { return item[4] === deleteVal; });
  var newDataVal = mProRangVal.filter(x => !valToDelete.includes(x));

  mProSheet.getDataRange().clear();

  var row = newDataVal.length
  var col = newDataVal[0].length
  mProSheet.getRange(1, 1, row, col).setValues(newDataVal)
}

In the code you have provided, given there's a huge amount of data (2k rows), it is slow because of the deleteRow() function with the for loop which iterates per row and checking on column E if it's blank. Here is an alternate solution. From your mProRangval, which is your whole data, you can use the filter function to determine those with blank rows. Store this in another array. You can then compare the two arrays to get the difference.

I have tested 2k rows with 800 blanks the reduced the time is from 3 mins down to 2 secs using this function.

Result 1:

(Using for loop)

for loop

Result 2:

(using filter)

filter function

References:

Upvotes: 2

Related Questions