Creating a Filtered Sheet in Google Sheets with Google Apps Script

Google Apps Script is a powerful tool that lets you automate tasks and enhance the functionality of your Google Sheets. In this post, we’ll walk you through how to create a script that filters data from a sheet named main and copies rows with non-empty values in columns A, B, and C into a new sheet named Filtered.

This script is particularly useful when working with large datasets and you need to extract only relevant rows based on specific conditions.


The Code

Here’s the full script:

function createFilteredSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheetByName('main');

if (!mainSheet) {
throw new Error("Sheet 'main' not found");
}

// Get all data from the 'main' sheet
var data = mainSheet.getDataRange().getValues();

// Filter rows where columns A, B, and C are non-empty
var filteredData = data.filter(row => row[0] && row[1] && row[2]);

// Create a new sheet or clear an existing one named 'Filtered'
var newSheetName = 'Filtered';
var filteredSheet = ss.getSheetByName(newSheetName);
if (!filteredSheet) {
filteredSheet = ss.insertSheet(newSheetName);
} else {
filteredSheet.clear(); // Clear content if the sheet already exists
}

// Write the filtered data to the new sheet
if (filteredData.length > 0) {
filteredSheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
} else {
filteredSheet.getRange(1, 1).setValue("No matching rows found.");
}
}

How It Works

1. Access the Spreadsheet

The script starts by accessing the active spreadsheet and looking for a sheet named main. If the sheet does not exist, the script throws an error.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheetByName('main');
if (!mainSheet) {
throw new Error("Sheet 'main' not found");
}

2. Retrieve Data

All data from the main sheet is fetched using the getDataRange() method. This method ensures the script captures all rows and columns with content.

var data = mainSheet.getDataRange().getValues();

3. Filter Rows

Using the filter() method, the script checks each row to ensure that columns A, B, and C are non-empty.

var filteredData = data.filter(row => row[0] && row[1] && row[2]);

4. Create or Clear the Filtered Sheet

The script looks for a sheet named Filtered. If it exists, the content is cleared. If not, the script creates a new sheet.

var newSheetName = 'Filtered';
var filteredSheet = ss.getSheetByName(newSheetName);
if (!filteredSheet) {
filteredSheet = ss.insertSheet(newSheetName);
} else {
filteredSheet.clear();
}

5. Write Data

If filtered rows exist, they are written to the Filtered sheet starting from the first cell. If no rows match the criteria, a message is added to the sheet.

if (filteredData.length > 0) {
filteredSheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
} else {
filteredSheet.getRange(1, 1).setValue("No matching rows found.");
}

Example Usage

Input Data (main Sheet):

ABCD
Item1Value1Info1Extra1
Item2Info2Extra2
Item3Value3Info3Extra3
Item4Value4Extra4

Output Data (Filtered Sheet):

ABC
Item1Value1Info1
Item3Value3Info3

Rows 2 and 4 are omitted because they have empty values in columns A, B, or C.


How to Implement the Script

  1. Open your Google Sheets file.
  2. Go to Extensions > Apps Script.
  3. Paste the script into the editor.
  4. Save the project.
  5. Run the createFilteredSheet function.
  6. Grant permissions if prompted.

Benefits of This Script

  • Time-Saving: Automates data extraction, saving manual effort.
  • Customizable: Easily adjust to filter based on other conditions or include additional columns.
  • Reusable: Works across different spreadsheets with minimal adjustments.

Conclusion

This script is a practical example of how Google Apps Script can enhance your productivity. By automating the process of filtering and creating a new sheet, you can focus on analyzing clean data instead of manually sifting through rows.