Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

292
Visualizações
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 Respostas
Responde à pergunta

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda