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
google script filter

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.

google script filtering

Related Articles

Responses

Your email address will not be published. Required fields are marked *

  1. Hi
    I am really looking for a filter that will will provide me data for that specific value e.g. column B has name and column C has sales numbers so the range is expected for all records where column B has name = Jack and column c which has sales should > 50000

    1. Hi Sandeep,

      This allows for the text filter to look on one specific value for one column filter while filtering for an amount on a different column.

      function FilterOnTextAmount() {
      var ss = SpreadsheetApp.getActive()
      var range = ss.getDataRange();
      var filter = range.getFilter() || range.createFilter()
      var text = SpreadsheetApp.newFilterCriteria().whenTextContains(‘Jack’); //this will filter on ‘Jack’
      var amount = SpreadsheetApp.newFilterCriteria()
      .whenNumberGreaterThan(50000) //this will filter on amount greater than 50000
      .build();
      filter.setColumnFilterCriteria(2, text);
      filter.setColumnFilterCriteria(3, amount);
      }