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

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

}

CodePudding user response：

If you just have data and no formulas this would be faster:

function process() {
  var ss = ss.getActive();
  var sh = ss.getSheetByName("MCC-Processed");
  var rg = sh.getDataRange();
  let o = rg.getValues().map(r => {
    if (r[4]) {
      return r;
    }
  });
  rg.clearContent();
  sh.getRange(1, 1, o.length, o[0].length).setValues(0);
}

CodePudding user response：

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.

result1 (using for loop):

result2 (using filter):

please use these references for more information:

(1) How to get the difference between two arrays in JavaScript?

(2) https://www.youtube.com/watch?v=PT_TDhMhWsE