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

Question

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

}

Look into using arrays and making the modifications to the sheet all at once, or using a range to delete all the rows at same time. — pgSystemTester, Sep 29 '22 at 16:42
Can you share a copy or sample of your spreadsheet? Please remove sensitive data if any. — Logan, Sep 29 '22 at 23:42

score 2 · Answer 1 · edited Oct 05 '22 at 22:16

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)

Result 2:

(using filter)

References:

As a general rule in StackOverflow, if your concern is solved please accept an answer which works best for you by clicking the check mark beside the answer. This is to let other members from the community with the same concern as yours know that the issue resolved. How to accept an answer. — Twilight, Oct 01 '22 at 04:15

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

1 Answers1

Script:

Result 1:

Result 2:

References: