Filtering Using Google Script

When handling data inside reports, filtering is an essential feature of google sheets. Filtering is used to look at data from a certain view or criteria. What if we wanted to filter data using google script rather than doing it manually in google sheets? It is very possible using google script.

Filter in Google Sheets Using Google Script

Here is a dataset that has 10 rows. We want to apply the filters on the headers. In google script, we have to set the range of data that we want the script to filter on. The range starts at A1 and ends at F10 (A1:F10).

Code to apply filter:

function FilterSheet() {
	var spreadsheet = SpreadsheetApp.getActive()
	spreadsheet.getRange('A1:F10').createFilter();
}

Once we set the filter, we need to filter on a specific column. If we want to filter on a specific ‘Vendor ID’, we will need to set the criteria for that column. We want it to filter on ID 605, 804, and 472 because these vendors are not required to be on the report. A variable is created to store these ids in the list.

function FilterSheet() {
	var spreadsheet = SpreadsheetApp.getActive()
	spreadsheet.getRange('A1:F10').createFilter();
	var criteria = SpreadsheetApp.newFilterCriteria()
	.setHiddenValues(['605','804','472']
	.build
	spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);
}

For the .setColumnFilterCriteria, we are telling it which column index to filter on starting from 1. This means it will filter on the first column. Then, we give it the id’s criteria to excluded that will be applied to the filter.