Reputation: 63
I am using a script to delete rows that contains a specific value. It works fine but I would like to be able to add multiple values to look for. In this example I would like every row that contains "housse" or "accessoires" in column 7 to be deleted. Thank you very much for any help !
var SS = SpreadsheetApp.getActive();
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();
var VALUES = RANGE.getValues();
var DELETE_VAL = "housse" || "accessoires";
var COL_TO_SEARCH = 6;
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
Views: 1274
Reputation: 27312
Change
var DELETE_VAL = "housse" || "accessoires";
to
var DELETE_VAL = ["housse", "accessoires"];
and
if(rangeVals[i][COL_TO_SEARCH] !=DELETE_VAL){
to
if(DELETE_VAL.indexOf(rangeVals[i][COL_TO_SEARCH]) === -1){
and see if that works?
EDIT: My bad! I didn't realize you were looking for partial matches. Try replacing the content of your scriptfile 'CLEANUP.gs with
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 sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var range = sheet.getDataRange();
var pattern = new RegExp("Connectique|Adaptateur");
var columnToSearch = 6;
var newRangeVals = range.getValues().filter(r => r[0] && !pattern.exec(r[columnToSearch]))
range.clearContent();
var numRows = newRangeVals.length;
var newRange = sheet.getRange(1,1, numRows, newRangeVals[0].length).setValues(newRangeVals);
var maxRows = sheet.getMaxRows();
sheet.deleteRows(numRows + 1, maxRows - numRows);
}
and see if that helps?
Upvotes: 1