Create Filters in Google Sheet using Google Apps Script

In this blog, I am going to show you how to create filters in Google Sheets using Google Apps Script. We are going to be using the following example:

We have a Google Sheet that contains some data regarding covid cases in India.
We want to filter the data using the following criteria:
(1) Cases in India/Mumbai
(2) Places where the cases reported are over 1000.
Once the data is filtered, it will be transferred to a new sheet. This will help the further in-depth study of the filtered data.

Let’s get started.

What is the Filter Function?

The FILTER function in Google Sheets helps you extract data that meets specified criteria. Multiple criteria can be added across columns. The FILTER function generates a new set of data while keeping the original intact.

Why use Google Apps Script to Filter Data?

Google Sheets provides a filtering function. However, using Google Apps Script to filter data will automate the process, make it faster to apply on various data sets.

More helpful tips on Google Sheets here

Step 1: Sample Google Sheet

The Google Sheet contains data on the Covid cases in different cities in India.

The Google Sheet contains the following details:

Region (North, South, East, West)
Date of entering the data
Number of Covid cases recorded on that day
Name of the country followed by the name of the city

Copy the template sheet

Step 2: Write the Automation Script

While you are in the Sheet, launch the Apps Script Editor.

To do that:

(1) Click on the tools button next to the Add-ons button.

(2) Next click on the Script Editor option. This brings up the Script Editor as shown below:

We have reached the Script Editor. Let’s Code.

function create_filter(){
    const  ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet1 = ss.getSheetByName("Filter_Sheet");
    const  range = sheet1.getRange("A:D");
    const  filter = range.createFilter();
    const Filter_Criteria1 = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(1000);
    const  Filter_Criteria2 = SpreadsheetApp.newFilterCriteria().whenTextContains(["India/Mumbai "]);
    const coll1 = 3;
    const coll2 = 4;
    
    const  add_filter1 =  filter.setColumnFilterCriteria(coll1,Filter_Criteria1);
    const  add_filter2 = filter.setColumnFilterCriteria(coll2,Filter_Criteria2);

  Logger.log("Filter has been added.");

  const  range = sheet1.getDataRange();

  const  new_sheet = ss.insertSheet();
  new_sheet.setName("India/Mumbai data");

  range.copyTo(new_sheet.getRange(1,1));

  filter.remove();
}

function create_filter(){
    
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const sheet1 = ss.getSheetByName("Filter_Sheet");
   const range = sheet1.getRange("A:D");

Declaring the function:
(1) Get the active Spreadsheet
(2) Get the sheet to be filtered
(3) Get the Range to be filtered

It’s time to add the filter to the sheet followed by adding the filter criteria and setting them into the sheet.

const  filter = range.createFilter();

To add the filter to the sheet, we use the createFilter function followed by the range.

Let’s set the Filter Criteria.

What are Filter Criteria?

Filter criteria are the conditions based on which the filter is going to sort the data and give you the desired data. There are tons of filtering criteria you can use, but for this blog, we are going to be using the following two criteria:

(1) whenNumberGreaterThan():

Sets the filter criteria to show cells where the cell number is greater than the number specified, which in this case is 1000. Using these criteria will only show the rows of data that have cases over 1000.

(2) whenTextContains():

Sets the filter criteria to show cells where the cell text is equal to the specified text, which in this case is ‘India/Mumbai’. So it will show us the data regarding the cases in India/Mumbai.

const  filterCriteria1 = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(1000); 

const filterCriteria2 = SpreadsheetApp.newFilterCriteria().whenTextContains(["India/Mumbai "]);

Here we have declared two filter criteria to filter the data. We have to first assign the filter criteria to the sheet using the SpreadsheetApp.FilterCriteria(), followed by the conditions on which you want to filter the data. There are a lot of filter criteria available. To know more you can check out the link below.

Class FilterCriteriaBuilder | Apps Script | Google Developers

We have created the filter, added the filter criteria, it is time to assign the filters to the specific columns that you want.

const col1 = 3;
const coll2 = 4

const add_filter1 =filter.setColumnFilterCriteria(col1,filterCriteria1);
const  add_filter2 = filter.setColumnFilterCriteria(coll2,filterCriteria1);

To set the filters to a specific column we are going to use the setColumnFilterCriteria. Using this function you can specify the column number followed by the filter criteria variable.

The first filter criteria have to be assigned to the Covid cases column, which is the number 3 column. So we are going to assign the number 3 followed by the first filter criteria variable. The same is to be done for the Country/City column.

We are done with creating, assigning and setting the filter. Now we will look at how to transfer the filtered data to another sheet.

const  range = sheet1.getDataRange();

const  new_sheet = ss.insertSheet();
new_sheet.setName("India/Mumbai data");

range.copyTo(new_sheet.getRange(1,1));

filter.remove();

We start by getting the data range of the first sheet using the getDataRange() function. Next, we are going to insert a new sheet into our main spreadsheet using the insertSheet() function, followed by giving the sheet a name. Since we are going to be filtering our data for India/Mumbai, I have named the sheet as India/Mumbai data.

Using the copyTo function we are going to copy the data range to the new sheet that we have created. In the end, we are going to remove the filter so that you always have your original data with you. To remove the filter you just have to type filter.remove(), and your filter will be removed.

Step3: Check the Output

Our code is complete. Select the correct function (create_filter) as shown below and run the program.

On successful execution, the script will filter the data and a new sheet will be created with the filtered data.

This is the sheet with the original data.

Chronology of Events:

Filter was added
Data was transferred to the new sheet
The filter was removed.

This is the second sheet that contains the filtered data according to the filter criteria.

Need help with Google Sheet Formulas? Learn these 9 useful formulas for efficiency.

Summary

We saw how you can create filters in Google Sheets using Google Apps Script. To sum up :

Accessed the spreadsheet and the first sheet containing the original data.
Added the filter to the sheet, followed by adding the Filter Criteria.
Assigned each of the filters to the respective columns.
Filtered data appears in a new sheet on the successful execution of the script.

If you prefer watching the video, you can click on this link.

I hope you have understood how to Create Filters in Google Sheets using Google Apps Script.