Filtering data using include 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 explore how to filter data in Google Sheets using Apps Script. We will write a function that filters a specific column in a worksheet based on a given criteria and returns the filtered data.
Code
|
1 2 3 4 5 6 7 8 |
function filterName() { const SS = SpreadsheetApp.getActiveSpreadsheet() const T_SHEET6 = SS.getSheetByName('시트6') const data = T_SHEET6.getDataRange().getValues() let dataFilter = data.filter(row => row[1].includes('보미')) Logger.log(dataFilter) } |
Code Explanation
The code declares a function named filterName. It retrieves the active spreadsheet and gets a specific sheet using getSheetByName method.
The getDataRange method is used to get the range of data in the sheet, and the getValues method retrieves the values in the range.
The filter method is applied to the data array, filtering rows where the second column (index 1) includes the string ‘보미’. The filtered data is then logged using the Logger.log method.
Additional explanations about include can be received from AI as follows.
data.filter(row => row[1].includes('Bomi')) is the element whose second column value (name) contains ‘Bomi’ for each element of array data JavaScript code that filters only . This code means using the array’s filter() method to return only the elements that meet the condition as a new array.
The arrow function used here row => row[1].includes('Bomi') takes each element, gets the value of the second column of that element, and determines whether the value includes ‘Bomi’.
Description of each part:
– data: An array to filter.
– filter(): A method of an array, which applies a given function to each element to meet the condition. Returns a new array of .
– row: Each element in the array.
– row[1]: The value of the second column of each element. Array indexes in JavaScript start at 0, so the second column corresponds to index 1. Inspect. So, row[1].includes('Bomi') checks whether the values in the second column contain ‘Bomi’.
As a result, data.filter(row => row[1].includes('Bomi')) is a code that filters only elements whose value in the second column contains ‘Bomi’ and returns it as a new array. .
Example
Let’s say we have a Google Sheet with a column containing names of employees. We want to filter the data to only include rows where the name includes the string ‘John’.
|
1 2 3 4 5 6 7 8 |
function filterName() { const SS = SpreadsheetApp.getActiveSpreadsheet() const T_SHEET = SS.getSheetByName('Sheet1') const data = T_SHEET.getDataRange().getValues() let filteredData = data.filter(row => row[1].includes('John')) Logger.log(filteredData) } |
AI Prompt
Write a function that filters a specific column in a Google Sheet based on a given criteria and logs the filtered data. The criteria should be passed as a parameter to the function.
Example usage: filterColumn('Sheet1', 2, 'John')
Expected output: An array of rows where the second column in ‘Sheet1’ includes the string ‘John’.
Get data from Google sheet and write code to check if a certain column contains the specified character.
