Filtering Data by Number in Apps Script
This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”. Added a strikethrough when editing what AI has written, and added color when edited by author
Table of Contents
Summary
In this blog post, we will learn how to filter data based on age in Apps Script. We will use the filter() function to extract rows from a spreadsheet that meet a specific age criterion.
Code
|
1 2 3 4 5 6 7 8 |
function filterAge() { const SS = SpreadsheetApp.getActiveSpreadsheet() const T_SHEET6 = SS.getSheetByName('시트6') const data = T_SHEET6.getDataRange().getValues() let dataFilter = data.filter(row => row[2] >= 8) Logger.log(dataFilter) } |
Code Explanation
The code defines a function named filterAge. It starts by getting the active spreadsheet and retrieving a specific sheet named ‘시트6’ using getSheetByName().
The getDataRange() method is used to get the range of data in the sheet, and getValues() is used to retrieve all the values from that range.
Next, the filter() function is applied to the data array to create a new array, dataFilter, that contains only the rows where the age in the third column (index 2) is greater than or equal to 8.
Finally, the filtered data is logged using Logger.log().
Example
Let’s say we have a spreadsheet with student data, including their names, ages, and grades. We want to filter out all the students who are 8 years old or older.
Using the filterAge() function, we can easily achieve this:
This will log an array of rows that meet the age criterion to the Apps Script Logger.
Suppose you have a Google Sheet with a column named ‘Sheet6’. We want to filter the data so that it only includes rows where the age is 8 or older.
| Column A | Column B | Column C |
|---|---|---|
| Type | Name | Age |
| cat | Pong | 5 |
| Puppy | Tami | 10 |
| cat | ping | 10 |
When the filterAge() function is run, the filtered data is logged to the Apps Script logger.
| Puppy | Tami | 10 |
| cat | ping | 10 |
AI Prompt
Write a function that filters data based on age. Retrieve the active spreadsheet and get a specific sheet by name. Get the data range and values from the sheet. Apply the filter() function to create a new array that contains only the rows where the age meets a specific criterion. Log the filtered data.
When an animal type is recorded in the first column, a name in the second column, and an age in the third column in a Google sheet, use filter to get the data of which animals are older than 8 years old.
