Create Filters in Google Sheet using Google Apps Script

  • 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 further in-depth study of the filtered data.

What is the Filter Function?

Why use Google Apps Script to Filter Data?

Step 1 : Sample Google Sheet

  • 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

Step2: Write the Automation Script

function create_filter(){

(1) var ss = SpreadsheetApp.getActiveSpreadsheet();
(2) var sheet1 = ss.getSheetByName("Filter_Sheet");
(3) var range = sheet1.getRange("A:D");
var filter = range.createFilter();

What is Filter Criteria?

(1) whenNumberGreaterThan():

(2) whenTextContains():

(1) var Filter_Criteria1 = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(1000); 

(2) var Filter_Criteria2 = SpreadsheetApp.newFilterCriteria().whenTextContains(["India/Mumbai "]);
var add_filter1 =filter.setColumnFilterCriteria(3,Filter_Criteria1);
var add_filter2 = filter.setColumnFilterCriteria(4,Filter_Criteria2);
var range = sheet1.getDataRange();

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

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

filter.remove();
}

Step3: Check the output

Summary

  • Accessed the spreadsheet and got the first sheet containing the original data.
  • Next, we added the filter to the sheet, followed by adding the Filter Criteria.
  • Then we
    assigned each of the filters to the respective columns.
  • Then we created a new sheet and assigned the filtered data to be moved there.
  • In the end, after the filtered sheet had been created, we deleted the filter to keep our original data safe.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store