Improve function delete rows by contain value

Question

I have working code, that delete row from sheet if column corresponds to one of the conditions. It based on arrays and because of it works much more faster than standard google sheet deleteRow function. I call it like this:

deleteRowsBV('SIZ',4,'0','')

where

deleteRowsBV(listName,ColNum,FirstSearch,SecondSearch)

What I want is a call function with more or less and equal signs, like this:

deleteRowsBV('SIZ',4,<='0',=='')

But in case of my main function, it doesn't work, when I specify a variable instead of a sign and a value. Here is main function:

function deleteRowsBV(listName,ColNum,FirstSearch,SecondSearch) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(listName);
  var DataLengBefore = sheet.getLastRow();
  var DataColLeng = sheet.getLastColumn();
  var data = sheet.getRange(1,1,DataLengBefore,DataColLeng).getValues();
  for (var i = data.length-1; i >= 0; i--) {
    if (data[i][ColNum] <= FirstSearch||data[i][ColNum] == SecondSearch) {
      data.splice(i, 1);
    }
  }
  sheet.getRange(10, 1, DataLengBefore,DataColLeng).clearContent();
  sheet.getRange(10, 1,data.length,5).setValues(data);
}

score 0 · Answer 1 · edited May 23 '17 at 11:54

Based from this related post, spreadsheet rows and columns are numbered starting at 1, for all methods in the SpreadsheetApp, while javascript arrays start numbering from 0. You need to adjust between those numeric bases when working with both. When deleting rows, the size of the spreadsheet dataRange will get smaller; you did take that into account, but because you're looping up to the maximum size, the code is complicated by this requirement. You can simplify things by looping down from the maximum.

You may refer with this thread. However, this only looks at the value from a single cell edit now and not the values in the whole sheet.

function onEdit(e) {
  //Logger.log(JSON.stringify(e)); 
  //{"source":{},"range":{"rowStart":1,"rowEnd":1,"columnEnd":1,"columnStart":1},"value":"1","user":{"email":"","nickname":""},"authMode":{}}
  try {
    var ss = e.source; // Just pull the spreadsheet object from the one already being passed to onEdit
    var s = ss.getActiveSheet();

    // Conditions are by sheet and a single cell in a certain column
    if (s.getName() == 'Sheet1' &&  // change to your own 
        e.range.columnStart == 3 && e.range.columnEnd == 3 &&  // only look at edits happening in col C which is 3
        e.range.rowStart == e.range.rowEnd ) {  // only look at single row edits which will equal a single cell
      checkCellValue(e); 
    }
  } catch (error) { Logger.log(error); }
};

function checkCellValue(e) {
  if ( !e.value || e.value == 0) {  // Delete if value is zero or empty
    e.source.getActiveSheet().deleteRow(e.range.rowStart);
  }
}

Improve function delete rows by contain value

1 Answers1