Having trouble transferring data from a data sheet to my report sheet using multiple criteria for matching. I wrote some code that worked, but it's returning all data instead of filtering by criteria.
I want the function to search for column criteria and skip it if it's empty. For example, if Col B has 'name' as a criteria and Col F has 'case', I only want to see rows that match both criteria. If I add another criteria in Col C like 'agency' and filter again, I want to see results based on all 3 criteria.
This is the current code:
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();
// Remaining Criteria variables omitted for brevity
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 });
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 image of the report sheet below
Still learning Apps Script, trying to get the hang of it.