CODEWITHSUNDEEP
google-sheets
Antoine
Antoine

Reputation: 63

Google sheet script : Delete row depending on column content

I am pretty new to Google script and learning right now. I have read many topics on this but did not manage to make my code work so I am posting here :)

I am trying to delete rows that contains a specific value among other text ("housse" in this example) in the 3rd column. Here is what I have done so far, does anyone know what I am missing ?

// Delete rows depending on Column content
function deleteRows() {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
  var row = values[i];
  if (row[2].indexOf("housse") > 0) {
      sheet.deleteRows((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

Thank you very much for your help !

Upvotes: 0

Views: 1639

Answers (2)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

To simplify things, I started from the bottom which let me delete a row at a time without worrying about what was already gone. Also, indexOf would need to be > -1.Also you need deleteRow not deleteRows. I know what you were thinking, but I think your logic would still fail if housse occurred in consecutive rows, say 17 and 18. You would delete row 17, row 18 would become row 17, and you would never look at it. A while rather than for could make it right, but starting from the bottom is simpler.

After those fixes, it seems to work nicely.

 function deleteRows() {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  for (var i = numRows-1; i >= 0; i--) {
  var row = values[i];
  if (String(row[2]).indexOf("housse")>-1) {
      sheet.deleteRow(i+1)
    }
  }
};

or with includes:

   function deleteRows() {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  for (var i = numRows-1; i >= 0; i--) {
  var row = values[i];
  if (String(row[2]).includes("housse")) {
      sheet.deleteRow(i+1)
    }
  }
};

REVISION: and here is an adaptation for performance on the large scale based on what you posted you were trying (based on the helpful article at https://yagisanatode.com/2019/06/12/google-apps-script-delete-rows-based-on-a-columns-cell-value-in-google-sheet/). It worked for me. Notice that the searched-for item in our case is a string, not a number, and we need need to be working on a string if we say indexOf. toString() keeps it from being an error if some other type of data is in that cell in that column.

    var SS = SpreadsheetApp.getActive();
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();
var VALUES = RANGE.getValues();
var DELETE_VAL = "housse"; // a string
var COL_TO_SEARCH = 6; //Zero is first

function myFunction() {
  var rangeVals = RANGE.getValues();
  var newRangeVals = [];
  for(var i = 0; i < rangeVals.length; i++){
    if (rangeVals[i][COL_TO_SEARCH].toString().indexOf(DELETE_VAL) == -1){// text to avoid not found      
      newRangeVals.push(rangeVals[i]);
    };
  };
  
  RANGE.clearContent();
  
  var newRange = SHEET.getRange(1,1,newRangeVals.length, newRangeVals[0].length);
  newRange.setValues(newRangeVals); 
}

Upvotes: 1

Antoine
Antoine

Reputation: 63

Here is what I tried :

var SS = SpreadsheetApp.getActive();
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();
var VALUES = RANGE.getValues();
var DELETE_VAL = VALUES.indexOf("Accessoires");
var COL_TO_SEARCH = 6; //Zero is first

function main() {
  
  var startTime = new Date().getTime();
  var deleteSelectedRows = removeThenSetNewVals();
  var runTime = (new Date().getTime() - startTime)/1000;
  Logger.log("Runtime is: "+runTime + " seconds");
};

function removeThenSetNewVals(){
  
  var rangeVals = RANGE.getValues();
  var newRangeVals = [];
  for(var i = 0; i < rangeVals.length; i++){
    if(rangeVals[i][COL_TO_SEARCH] !=DELETE_VAL){
      
      newRangeVals.push(rangeVals[i]);
    };
  };
  
  RANGE.clearContent();
  
  var newRange = SHEET.getRange(1,1,newRangeVals.length, newRangeVals[0].length);
  newRange.setValues(newRangeVals);
};

Upvotes: 0

Related Questions