CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Antoine
Antoine

Reputation: 63

Google sheets script : Delete large number of rows that contains a specific data

I am trying to build a script to delete a large number of rows that contains a specific data (sheet has +80k rows). In this example I am trying to delete any rows that contains the value "Accessoires" in the 7th column. I am pretty knew to this but spent quite some time looking for a solution everywhere on the forum. So far I cam up with this code but this does not work Any idea very much appreciated :)

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; 

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

Thank you very much for any help provided

Upvotes: 0

Views: 164

Answers (2)

Cooper
Cooper

Reputation: 64072

function deleteRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const vs = sh.getDataRange().getValues();
  let vo = vs.map(r => { if (r[6] != 'Accessoires') { return r; } }).filter(e => e);
  sh.clearContents();
  sh.getRange(1, 1, vo.length, vo[0].length).setValues(vo);
}

Upvotes: 1

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

DELETE_VAL = VALUES.indexOf("Accessoires"); is a number. RangeVals[i][COL_TO_SEARCH] were text strings.

Also you wanted partial matches to count. So the fixes were to make a string you are matching

var DELETE_VAL = "housse"; // a string

and then say (using toString to avoid saying indexOf on a number and getting an error)

if (rangeVals[i][COL_TO_SEARCH].toString().indexOf(DELETE_VAL) == -1){// text to avoid not found      
  newRangeVals.push(rangeVals[i]);
};

Upvotes: 1

Related Questions