Google Apps Script Filter On Multiple Sheets

This video looks at how to perform the Filter Method in Google Apps Script that combines ranges from multiple sheets into 1 single filter output. This lesson also covers how to perform partial text matches in the filter as well as searching on any field.

#GoogleAppsScriptFilterOnMultipleSheets
#GoogleAppsScriptCombineRanges
#GoogleAppsScriptJoinRanges

The Code used in this video is below:

function FilterMultShts (){
Clear();

var ss = SpreadsheetApp.getActiveSpreadsheet();
var shtR1 = ss.getSheetByName("Records1");
var shtR2 = ss.getSheetByName("Records2");
var shtR3 = ss.getSheetByName("Records3");
var rngR1 = shtR1.getDataRange().getValues().slice(1);
var rngR2 = shtR2.getDataRange().getValues().slice(1);
var rngR3 = shtR3.getDataRange().getValues().slice(1);
var Crng = rngR1.concat(rngR2,rngR3);
var shtResults = ss.getSheetByName("Results");
var sCol = shtResults.getRange("A3").getValue();
var Criteria = shtResults.getRange("A6").getValue();
var headers = shtR1.getRange("A1:E1").getValues();
var Col = headers[0].indexOf(sCol);
if(Number.isInteger(Criteria)){var Frng = Crng.filter(function(e){return e[Col] == Criteria})}
else { var Frng = Crng.filter(function(e){return e[Col].match(Criteria)})}

shtResults.getRange(1,3,headers.length,headers[0].length).setValues(headers);
shtResults.getRange(2, 3, Frng.length, Frng[0].length).setValues(Frng);

}



function Clear(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shtResults = ss.getSheetByName("Results");
shtResults.getRange("C1").getDataRegion().clearContent();

}