CODEWITHSUNDEEP
stringgoogle-apps-scriptgoogle-sheets
Antoine
Antoine

Reputation: 63

Google sheets script : using multiple values with Indexof

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

Answers (1)

JPV
JPV

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

Related Questions