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):
A | B | C | D |
---|---|---|---|
Item1 | Value1 | Info1 | Extra1 |
Item2 | Info2 | Extra2 | |
Item3 | Value3 | Info3 | Extra3 |
Item4 | Value4 | Extra4 |
Output Data (Filtered
Sheet):
A | B | C |
---|---|---|
Item1 | Value1 | Info1 |
Item3 | Value3 | Info3 |
Rows 2 and 4 are omitted because they have empty values in columns A
, B
, or C
.
How to Implement the Script
- Open your Google Sheets file.
- Go to
Extensions
>Apps Script
. - Paste the script into the editor.
- Save the project.
- Run the
createFilteredSheet
function. - 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.
