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

216
Visualizações
App script Filtering data by multiple criteria

I'm struggling to get the data from data sheet to my report sheet by multiple criteria if it matches. I wrote some codes that worked but it returns all data but not by criteria.

I would like the function to search for column criteria but if its empty just skip it. Example: Col B criteria 'name', COL F criteria is 'case' I want to see only matching criteria rows but if I add another Criteria in Col C 'agency' and filter again I want to see by these 3 criteria.

This is the code I have:

function myReport(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var datasht = ss.getSheetByName("CURENT POOL DATA SHEET");
var reportsht = ss.getSheetByName("REPORTS")
var rng = datasht.getRange(2, 1, datasht.getLastRow(),14).getValues();
var Criteria1 = reportsht.getRange("B4").getValue();
var Criteria2 = reportsht.getRange("C4").getValue();
var Criteria3 = reportsht.getRange("D4").getValue();
var Criteria4 = reportsht.getRange("E4").getValue();
var Criteria5 = reportsht.getRange("F4").getValue();
var Criteria6 = reportsht.getRange("G4").getValue();
var Criteria7 = reportsht.getRange("H4").getValue();
var Criteria8 = reportsht.getRange("I4").getValue();
var Criteria9 = reportsht.getRange("J4").getValue();
var Criteria10 = reportsht.getRange("K4").getValue();
var Criteria11 = reportsht.getRange("L4").getValue();
var Criteria12 = reportsht.getRange("M4").getValue();
var Criteria13 = reportsht.getRange("N4").getValue();


var fData = rng.filter(function(e){return e[1]==Criteria1 || e[2]==Criteria2 
|| e[3]==Criteria3 || e[4]==Criteria4 || e[5]==Criteria5 || e[6]==Criteria6 || e[7]==Criteria7 
|| e[8]==Criteria8 || e[9]==Criteria9 || e[10]==Criteria10 || e[11]==Criteria11 
|| e[12]==Criteria12 || e[13]==Criteria13 });
//var fData = rng.filter(function(e){return e[1]==criteria1});
reportsht.getRange(5,1,fData.length,14).setValues(fData);
}


function clearFilter() 
{
  var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); 
  var shUserForm    = myGoogleSheet.getSheetByName("Reports"); 
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Reset Confirmation", 'Do you want to reset this form?',ui.ButtonSet.YES_NO);

 if (response == ui.Button.YES) 
  {
     
  shUserForm.getRange("A5:N3724").clear(); 
  
 shUserForm.getRange("A5:N3724").setBackground('#FFFFFF');
 
 
  return true ;
  
  }
}

Adding report sheet image here

I'm new in Apps Script , just trying understand it.

about 4 years ago · Santiago Trujillo
1 Respostas
Responde à pergunta

0

I believe you are doing this the wrong way.

It is possible to make this type of query using the formula =QUERY()

https://developers.google.com/chart/interactive/docs/querylanguage

but if you want to do this via script, I suggest some improvements in your code.

do not do:

var Criteria1 = reportsht.getRange("B4").getValue();
var Criteria2 = reportsht.getRange("C4").getValue();
var Criteria3 = reportsht.getRange("D4").getValue();
var Criteria4 = reportsht.getRange("E4").getValue();
var Criteria5 = reportsht.getRange("F4").getValue();
var Criteria6 = reportsht.getRange("G4").getValue();
var Criteria7 = reportsht.getRange("H4").getValue();
var Criteria8 = reportsht.getRange("I4").getValue();
var Criteria9 = reportsht.getRange("J4").getValue();
var Criteria10 = reportsht.getRange("K4").getValue();
var Criteria11 = reportsht.getRange("L4").getValue();
var Criteria12 = reportsht.getRange("M4").getValue();
var Criteria13 = reportsht.getRange("N4").getValue();

doing this, you are performing several queries, and it slows down your script

do :

var criterias = reportsht.getRange("B4:N4").getValues();
// criterias[0][1] == Criteria1 ....

In the excerpt below, you are validating if there is any data in the "criteria", but not validating if the data is equal to the criterion. besides, the condition will be true if there is only 1 value informed... My suggestion would be to use the filter() function to reduce the array to the conditions you want. criterias.filter()

var fData = rng.filter(function(e){return e[1]==Criteria1 || e[2]==Criteria2 
|| e[3]==Criteria3 || e[4]==Criteria4 || e[5]==Criteria5 || e[6]==Criteria6 || e[7]==Criteria7 
|| e[8]==Criteria8 || e[9]==Criteria9 || e[10]==Criteria10 || e[11]==Criteria11 
|| e[12]==Criteria12 || e[13]==Criteria13 });
//var fData = rng.filter(function(e){return e[1]==criteria1});
reportsht.getRange(5,1,fData.length,14).setValues(fData);
}

See more at https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter

I hope I've helped a bit

about 4 years ago · Santiago Trujillo 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