This tutorial is about copy-paste filtered cells values.
You need to create a spreadsheet named CustomSheet with two sheets named: SheetInput and Target.
In the SheetInput add this data for each column A,B, C, D, E, F:
A B C D E F
A $1 30% AA Criteria
B $1 30% AA AA
C $1 30% CC
The filter cell name Criteria is on row 1 and column F and AA is on filter Criteria value from row 1 and column F.
Use main menu Tools – Script editor to create a script and rename it: CustomSheet.
In this script use this source code, you will see all comments tells you how this works:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | function myFilter() { // get active getActiveSpreadsheet named Sheet1 var ss = SpreadsheetApp.getActiveSpreadsheet(); // get sheet named named Sheet1 var sheet = ss.getSheetByName("SheetInput"); // get values from any row and first four columns let columns = 4; // show data from the first columns var range = sheet.getRange(1,1, sheet.getLastRow(),columns).getValues(); Logger.log(range) // get value of Criteria cel value var Criteria = sheet.getRange(2,6).getValue(); // show the criteria Logger.log(Criteria) // create a filter by the Criteria cell var FData = range.filter(function(e){return e[3]=== Criteria}); // show filtered data Logger.log(FData) // size of rows var row = FData.length; Logger.log(FData.length) // size of columns var col = FData[0].length; Logger.log(FData[0].length) // use the second sheet var newSheet = ss.getSheetByName("Target"); // format column B like $0,000.00 newSheet.getRange("B:B").setNumberFormat("$0,000.00"); // format column C like 0.000% newSheet.getRange("C:C").setNumberFormat("0.000%"); // color area with data in Target sheet newSheet.getRange(1,1,row,col).setBackgroundColor("lightblue"); // set values filtered from the SheetInput var targetSheetNextRow = newSheet.getRange(1,1,row,col).setValues(FData); } |
The result for filter value AA is this:
1 2 | A $0,001.00 30.000% AA B $0,001.00 30.000% AA |