clear specific cells' content of a list/sheet seeing values from another list/sheet in google s-CodePudding

I am sorry if this is really basic, but I have searched a lot and can't find the answer. I am trying to write a script that will clear not delete the specific cells' content (these are column-based i.e., A, B, C, etc) seeing values from another list. Like Phone 1, and phone 3 have a list of numbers, this list should look into another list and if it finds any matching numbers in the list, the script should clear the content in the cell but do not delete the cell.

These are the images that may help in better understanding. I have managed to find a way that also clears the rows/cells but that also pushes non empty cells above so they do not stay on the same position. I have also tried other methods with "if(keep){ resultArray.push(values1[n])};" like shift,setValue, pop, etc, but then length error popsup (TypeError: Cannot read property 'length' of undefined). A little help will be alot for me. Thanks

function deletenumber21() { 
 var s1 = SpreadsheetApp.openById("148wFAJnlJzZVStawtvyLIJJiZYe2HJ- 
 81WPJ5V2afWI").getSheetByName('Sheet1');
 var s2 = SpreadsheetApp.openById("148wFAJnlJzZVStawtvyLIJJiZYe2HJ- 
 81WPJ5V2afWI").getSheetByName('DNC'); 
 var values1 = s1.getDataRange().getValues();
 var values2 = s2.getDataRange().getValues();


 var resultArray = [];

 for(var n in values1){
  var keep = true

  for(var p in values2){
   if( values1[n][20] == values2[p][0] ){
    keep=false ; break ;
    
    // s1.deleteRow(n 1);
   }
 }
 if(keep){ resultArray.push(values1[n])};
}

var phone1 = s1.getRange("U2:U");
var phone2 = s1.getRange("W2:W");

phone1.setValue("");
phone2.setValue("");

 s1.getRange(1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

CodePudding user response：

I'd propose to use textFinder():

function deletenumber21() { 

  // get spreadsheet
  var ss = SpreadsheetApp.openById('148wFAJnlJzZVStawtvyLIJJiZYe2HJ-81WPJ5V2afWI');

  // get the searched values
  var s1 = ss.getSheetByName('DNC');
  var values = s1.getDataRange().getValues().flat().filter(String);

  // get destination sheet and the range that will be changed
  var s2 = ss.getSheetByName('Sheet1');
  var range = s2.getDataRange();
 
  // change the values to '' with 'textFinder()'
  for (var val of values) {
    range.createTextFinder(val).matchEntireCell(true).replaceAllWith('');
  }

}

Class TextFinder