Google Apps Script Filter On Multiple Sheets

Published: 05 June 2022
on channel: The Excel Cave

2,846

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.

For a more detailed explanation on joining non-adjacent ranges into one check out my website at the link below:

https://googlesheetsfocus.weebly.com/...

Learn more about Google Apps Script with this Beginner's Guide Book at Amazon.
https://amzn.to/3MMvDZs
DISCLAIMER: As an Amazon Affiliate I get a commission for every purchase via this link below

#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();

}

On this page of the site you can watch the video online Google Apps Script Filter On Multiple Sheets with a duration of hours minute second in good quality, which was uploaded by the user The Excel Cave 05 June 2022, share the link with friends and acquaintances, this video has already been watched 2,846 times on youtube and it was liked by 26 viewers. Enjoy your viewing!

2,790