Have you ever looked at a massive Google Sheets spreadsheet and thought, “I just want to pull out the rows that match my specific criteria right away”?
For example, situations like wanting to gather only records where sales are $100 or more, extracting rows assigned to a specific team member, or isolating last month’s data come up all the time in everyday business workflows.
While you could combine a for loop with an if statement to inspect rows from top to bottom one by one, that approach tends to make your code long, cluttered, and prone to bugs.
That is where our topic for today comes into play: Array.filter()!
By using Array.filter(), you can sift through and extract only the data that matches your criteria in just a few lines of clean code.
On top of that, we will thoroughly break down modern JavaScript “arrow functions (=>)”—a global standard syntax that makes your Google Apps Script (GAS) code dramatically shorter and easier to read!
Once you master filtering two-dimensional arrays retrieved from Google Sheets via getValues(), your workflow automation skills will level up significantly. Let’s walk through the fundamentals step-by-step so you never get stuck!
1. What Is Array.filter() in the First Place?
Array.filter() is a method that checks each element in an array one by one and creates a brand-new array containing only the elements that satisfy a given condition (where the expression evaluates to true).
Visualize It Like a Kitchen Sieve (Colander)
When learning programming concepts, everyday analogies make everything click much faster. Array.filter() works just like a kitchen sieve or colander.
- The Original Array (before sifting): A group containing various types or sizes of data mixed together.
- The Filter Condition (the mesh size of the sieve): The criteria for sifting, such as “numbers greater than or equal to 20” or “text matching a specific name”.
- The New Array (what remains in the sieve): Only the desired items that met your condition and stayed behind.
The key point is that “even after filtering, the original data remains completely untouched.” Because it creates a fresh copy containing only the desired data without modifying or mutating the original array, filter() is both reliable and safe to use.
2. Basic Syntax and How Array.filter() Works
Let’s start by looking at a simple 1D array (a list of single numbers or strings) to inspect the basic syntax and underlying mechanism step-by-step.
Traditional Syntax
var newArray = originalArray.filter(function(element) {
return condition;
});
What Happens Behind the Scenes (Step-by-Step Breakdown)
Suppose we have an array var numbers = [10, 25, 40, 5, 60]; and want to extract only numbers that are 30 or greater.
var numbers = [10, 25, 40, 5, 60];
// Extract only numbers that are 30 or greater
var bigNumbers = numbers.filter(function(num) {
return num >= 30;
});
Logger.log(bigNumbers); // Output: [40, 60]
Behind the scenes, the JavaScript runtime evaluates each element in order, just like this:
- 1st loop (when
numis10):10 >= 30isfalse(Failed!) ➡ Discard - 2nd loop (when
numis25):25 >= 30isfalse(Failed!) ➡ Discard - 3rd loop (when
numis40):40 >= 30istrue(Passed!) ➡ Keep in new array! - 4th loop (when
numis5):5 >= 30isfalse(Failed!) ➡ Discard - 5th loop (when
numis60):60 >= 30istrue(Passed!) ➡ Keep in new array!
Ultimately, only 40 and 60 evaluated to true, so they are saved into the new bigNumbers array.
3. Make Your Code 10x Cleaner! Mastering Arrow Functions (=>)
Now let’s tackle arrow functions, a modern feature essential for writing efficient Google Apps Script and JavaScript!
When searching for GAS code online, you will often see => instead of function(). While seeing so many symbols at first glance might feel overwhelming, once you understand how it works, you will realize it is simply a neat shorthand notation.
3-1. What Is an Arrow Function? (Why an “Arrow”?)
The name “Arrow Function” comes directly from the symbol itself.
Because => (equal sign + greater-than sign) looks like a rightward-pointing arrow, it became known as the arrow syntax.
Functionally, it behaves identically to a function written with the traditional function keyword. It was introduced to allow developers to write cleaner functions with less boilerplate code.
3-2. Transforming Traditional Functions into Arrow Functions in 3 Steps
To understand how long blocks of code turn into compact one-liners, let’s transform a traditional function into an arrow function step-by-step.
[Before (Traditional Syntax)]
function(num) {
return num >= 30;
}
Just follow these three simple steps to transform it into an arrow function!
[Step 1] Remove the function keyword and add the arrow =>
Delete the keyword function and place an arrow => directly after the parameter (num).
(num) => {
return num >= 30;
}
[Step 2] If the body is a single line, omit the curly braces { } and return
When the body consists of just a single conditional evaluation, JavaScript allows you to omit both the curly braces { } and the return keyword together.
(num) => num >= 30
[Step 3] If there is only one parameter, omit the parentheses ( ) as well
When you have exactly one parameter (in this case, num), you can even omit the outer parentheses ( )!
// [After (Completed Arrow Function!)]
num => num >= 30
3-3. Parentheses Rules for Arrow Functions
Whether you need parentheses around your parameters depends entirely on how many arguments your function takes.
| Number of Parameters | Syntax | Example | Parentheses Needed? |
| Exactly 1 | param => expression |
num => num >= 30 |
Can be omitted! (Cleanest) |
| 2 or more | (param1, param2) => expression |
(row, index) => row[2] >= 20 |
Parentheses () are required! |
| 0 (none) | () => expression |
() => Logger.log("Hello") |
Empty parentheses () are required! |
3-4. The Dramatic Difference When Combined with filter()
Now let’s compare the traditional syntax side-by-side with the arrow function syntax inside filter().
[Traditional Syntax]
var bigNumbers = numbers.filter(function(num) {
return num >= 30;
});
[Arrow Function Syntax]
var bigNumbers = numbers.filter(num => num >= 30);
Notice how much clearer it is?
With arrow functions, the expression reads almost like plain English: “Take numbers and filter where num is greater than or equal to 30“!
3-5. ⚠️ A Common Pitfall: The Arrow Function “Curly Brace {} Trap”
There is one major mistake beginners frequently make when adopting arrow functions: forgetting return when using curly braces { }.
// ✕ Danger! Incorrect code
var bigNumbers = numbers.filter(num => {
num >= 30; // Using curly braces without writing return!
});
// ➡ The result becomes an empty array []!
- Without curly braces
{}: The expression automatically returns the evaluation result (trueorfalse). - With curly braces
{}: The implicit return is disabled, meaning you must explicitly writereturn.
If you need multiple lines of code and decide to use curly braces { }, remember to always pair them with an explicit return!
// ◯ Correct syntax (when using curly braces)
var bigNumbers = numbers.filter(num => {
Logger.log("Checking number: " + num);
return num >= 30; // Explicitly return the condition!
});
4. STEP 1: Setting Up the Sample Spreadsheet Data
Now that we appreciate the power of arrow functions, let’s put them to work on real-world GAS tasks: filtering two-dimensional tabular data from Google Sheets.
For this exercise, we will use monthly maximum temperature data from Sapporo as autumn begins to cool the air.
Spreadsheet Setup Instructions
- Open a new Google Spreadsheet and name the sheet tab “Temperature Data”.
- Enter the following sample data into cells A1:C6:
| A | B | C | |
| 1 | Month | Area | Max Temperature (°C) |
| 2 | July | Sapporo | 27.5 |
| 3 | August | Sapporo | 29.1 |
| 4 | September | Sapporo | 22.4 |
| 5 | October | Sapporo | 16.2 |
| 6 | November | Sapporo | 8.5 |
Once your data is entered, click Extensions > Apps Script in the top menu to open the script editor.
5. STEP 2: [Hands-On] Filtering 2D Array Data with GAS
As covered in Lesson 13, data fetched from a spreadsheet using getValues() comes back as a two-dimensional array (an outer array containing nested row arrays):
[
["July", "Sapporo", 27.5], // Row index 0 (row)
["August", "Sapporo", 29.1], // Row index 1 (row)
["September", "Sapporo", 22.4], // Row index 2 (row)
["October", "Sapporo", 16.2], // Row index 3 (row)
["November", "Sapporo", 8.5] // Row index 4 (row)
]
Let’s use our new arrow function knowledge to extract only the rows where the maximum temperature was 20°C or higher!
1. Writing the Code
Clear out any default code in your editor and paste the following script:
function filterTemperatureData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// Retrieve the range A2:C6 (excluding headers) as a 2D array
var data = sheet.getRange("A2:C6").getValues();
// [Written with an arrow function!]
// Extract only rows where Column C (Max Temp: row[2]) is 20 or higher
var warmMonths = data.filter(row => row[2] >= 20);
// Output the filtered results to the execution log
Logger.log(warmMonths);
}
That is all it takes! With just the single line data.filter(row => row[2] >= 20);, your filtering logic is complete.
2. Why row[2] Points to Column C
When running filter() on a two-dimensional array, the parameter row receives an entire row array during each iteration.
For instance, during the first iteration, row holds ["July", "Sapporo", 27.5].
Because programming languages use zero-based indexing, each column corresponds to the following indices:
row[0]➡ 1st element (Column A: Month ="July")row[1]➡ 2nd element (Column B: Area ="Sapporo")row[2]➡ 3rd element (Column C: Max Temperature =27.5) ★ The value we want to compare!
Therefore, writing row => row[2] >= 20 checks whether the 3rd element (temperature) is greater than or equal to 20.
3. Saving and Running Your Script
- Click the Save icon at the top of the editor.
- Click the Run button.
If you see output like this in your execution log, it was a complete success!
Info [[July, Sapporo, 27.5], [August, Sapporo, 29.1], [September, Sapporo, 22.4]]
October (16.2°C) and November (8.5°C) were neatly filtered out, leaving only July, August, and September!
6. STEP 3: Advanced Filtering Techniques for Real-World Tasks
In day-to-day office automation, you will often need more versatile criteria, such as combining multiple conditions (AND / OR) or checking for partial text matches. Here is how to handle them cleanly with arrow functions.
Pattern 1: AND Conditions (Both Must Be True)
To extract rows that satisfy two conditions simultaneously, use the logical AND operator (&&).
Let’s extract rows where the max temperature is 20°C or higher AND the month is “September”:
// row[2] (Temperature) >= 20 AND row[0] (Month) === "September"
var result = data.filter(row => row[2] >= 20 && row[0] === "September");
Logger.log(result);
// Execution output: [["September", "Sapporo", 22.4]]
Pattern 2: OR Conditions (Either Can Be True)
If satisfying either condition is acceptable, use the logical OR operator (||).
Let’s pick out extreme months where the temperature was 28°C or higher OR fell below 10°C:
// row[2] >= 28 OR row[2] < 10
var result = data.filter(row => row[2] >= 28 || row[2] < 10);
Logger.log(result);
// Execution output: August (29.1°C) and November (8.5°C) are extracted
Pattern 3: Partial String Matching (Includes Keyword)
To check whether text contains a specific substring, combine your arrow function with JavaScript’s includes() method.
For instance, to extract rows where Column B (Area) contains the string “Sapporo”:
// Returns true if "Sapporo" is found within Column B (row[1])
var result = data.filter(row => row[1].includes("Sapporo"));
7. Top 5 Traps Beginners Encounter with filter() and How to Avoid Them
Here are five common pitfalls almost everyone runs into when first working with Array.filter(). Keeping these in mind will save you hours of debugging!
Trap 1: Expecting the Original Array to Be Modified (Non-Destructive Method)
filter() is a non-destructive method; it never alters the original array.
// ✕ Common mistake
data.filter(row => row[2] >= 20);
Logger.log(data); // data still contains all 5 original rows!
// ◯ Correct approach: Store the returned array in a new variable
var filteredData = data.filter(row => row[2] >= 20);
Logger.log(filteredData); // filteredData contains only the matching rows!
Always assign the returned result to a new variable, like var filteredData = data.filter(...).
Trap 2: Using Curly Braces {} in Arrow Functions Without return
As mentioned earlier, wrapping your arrow function body in curly braces { } disables implicit returns.
// ✕ Returns an empty array []
var result = data.filter(row => {
row[2] >= 20; // Curly braces present, but return is missing!
});
// ◯ Correct: Remove curly braces for single expressions, or add return
var result = data.filter(row => row[2] >= 20);
Trap 3: Column Index Off-by-One Errors (Zero-Based Indexing)
- On the Google Sheet: Column A = 1st, Column B = 2nd, Column C = 3rd column
- In Apps Script: Column A =
row[0], Column B =row[1], Column C =row[2]
It is very easy to mistakenly write row[3] when targeting Column C. In JavaScript, row[3] refers to a non-existent 4th column, which causes unexpected results or errors. Remember the rule: “Column Number minus 1”!
Trap 4: Accidentally Filtering the Header Row
If you pull data using sheet.getDataRange().getValues() or sheet.getRange("A1:C6").getValues(), the first row containing column headers will also be evaluated by your filter condition.
[Solution 1] Target only the data range from the start
Start your range from row 2, such as getRange("A2:C6").
[Solution 2] Separate headers programmatically with shift()
var allData = sheet.getDataRange().getValues();
var header = allData.shift(); // Remove row 1 (headers) and store separately!
var body = allData; // Only the remaining rows stay in body
// Run filter on the body rows
var filteredBody = body.filter(row => row[2] >= 20);
This header-isolation technique lets you keep your header safe while easily filtering table rows, which is practical in production scripts.
Trap 5: Confusing filter() with find()
filter(): Returns an array of all matching items.find(): Returns only the very first matching single element.
Choose the method based on whether you want a full list of matches or just the single first match.
8. Advanced: Writing Filtered Results to Another Sheet
Once you have filtered your data with filter(), you can write it straight back into a new spreadsheet range using setValues(), which we covered in Lesson 14.
function exportFilteredData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getActiveSheet();
// 1. Retrieve data and filter matching rows using an arrow function
var data = sourceSheet.getRange("A2:C6").getValues();
var warmMonths = data.filter(row => row[2] >= 20);
// Write out data only if matching rows were found
if (warmMonths.length > 0) {
// Retrieve the target sheet (or create it automatically if it does not exist)
var targetSheet = ss.getSheetByName("Filtered Results") || ss.insertSheet("Filtered Results");
// 2. Write headers into row 1
targetSheet.getRange("A1:C1").setValues([["Month", "Area", "Max Temperature (°C)"]]);
// 3. Batch-write the filtered rows starting at row 2
// targetSheet.getRange(startRow, startColumn, numRows, numColumns)
targetSheet.getRange(2, 1, warmMonths.length, warmMonths[0].length).setValues(warmMonths);
Logger.log("Data export completed successfully!");
}
}
Running this script creates a full automation pipeline: it identifies records with temperatures of 20°C or higher and writes them cleanly into a separate sheet!
9. Summary and Next Steps
Today, we explored how to extract targeted data with Array.filter() and how to write concise, readable logic using arrow functions (=>).
Key Takeaways
filter()creates a new array containing only elements that evaluate totrue.- Arrow functions (
=>) are a clean shorthand syntax for writing functions. - Single-line arrow functions omit both
{}andreturn(if you use{}, an explicitreturnis mandatory!). - When handling 2D arrays, reference columns using zero-based indices (
row[columnIndex]). - Filtered arrays can be directly written back to a Google Sheet using
setValues().
Getting comfortable with arrow functions drastically reduces code clutter, making your Google Apps Script projects much easier for you and your teammates to read and maintain.
In the next tutorial (Lesson 16), we will dive into another indispensable array tool: Array.map().
We will learn how to transform and manipulate every element in an array at once—such as calculating sales tax on all prices or formatting text uniformly. Stay tuned!
