Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

293
Views
clear specific cells' content of a list/sheet seeing values from another list/sheet in google sheets

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. This the sample sheet

Final Result

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);
}
about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

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
about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!