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