Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

286
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda