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

203
Views
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 answers
Answer question

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 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!