Filter information by the K column within Google Script Editor on Google Sheets

Question

Filter information by the K column within Google Script Editor on Google Sheets

For this particular case, I have data coming from a Google Sheet (4Cat) that is being transferred to another sheet (ImportFeeder) where my Google Script is executed.

After executing the script provided below, I am looking to implement a filter script at the end to organize the data by Row K (only displaying iPad products).

https://i.sstatic.net/GqckL.jpg

function myFunction() {
var sss = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); //update with source ID
var ss = sss.getSheetByName('4cat'); //update with source Sheet tab name
var range = ss.getRange('A:I'); //defining the range you want to copy
var data = range.getValues();

var tss = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); //update with destination ID
var ts = tss.getSheetByName('Sheet1'); //update with destination Sheet tab name
ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

var range = ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length)
var rawData = range.getValues()     // retrieve values from spreadsheet 2
var data = []                       // Filtered Data will be stored in this array
for (var i = 0; i< rawData.length ; i++){
if(rawData[i][10] == "iPad")            // Check to see if column K has iPad, if not skip it
{
data.push(rawData[i])
}
}
}

(Cannot read property length from undefined)

4Cat Sample Data https://docs.google.com/spreadsheets/d/1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s/edit?usp=sharing */

feeding into

ImportFeeder https://docs.google.com/spreadsheets/d/1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI/edit?usp=sharing

Required - Successful import of data between sheets, filtering based on Row K within Google Scripts. Include a clear() function at the beginning of the script to ensure the sheet is cleared before each daily import process.

javascript google-apps-script filter google-sheets-api

Answer 1

Answer №1

Have you thought about creating a customized filter function for this task? I integrated a .toLowerCase() method to make the comparison case-insensitive when matching "ipad".

function myCustomFunction() {
var sourceSpreadsheet = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); //update with your source spreadsheet ID
var sourceSheet = sourceSpreadsheet.getSheetByName('4cat');   //update with your source sheet tab name
var dataRange = sourceSheet.getRange('A:V');        //define the range you want to work with
var rawDataValues = dataRange.getValues()        // extract values from the source spreadsheet
var filteredData = rawDataValues.filter(customFilterFunction); // Store the filtered Data in an array
var destinationSpreadsheet = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); // update with your destination spreadsheet ID
var destSheet = destinationSpreadsheet.getSheetByName('Sheet1'); //update with your destination sheet tab name
destSheet.getRange(2,1,destSheet.getLastRow() - 1,destSheet.getLastColumn()).clear(); // Assuming header is in the first row, clears the sheet while keeping the header
destSheet.getRange(2, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
};

// Modify to destSheet.getRange(1,1,[...] if there's no header row
function customFilterFunction(array) {
  return array[10].toLowerCase() == "ipad";
};

Have you explored the option of using a Spreadsheet formula instead? You could experiment with combining

=IMPORTRANGE(spreadsheet_key, string_range)

and QUERY(data, query, [header]) to import a pre-filtered range:

=QUERY(IMPORTRANGE("1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s", "4cat!A:V"),"SELECT * WHERE Col11 = 'iPad'")

This approach eliminates the need to clear your sheet before importing the filtered data.

Answer 2

Have you thought about creating a customized filter function for this task? I integrated a .toLowerCase() method to make the comparison case-insensitive when matching "ipad".

function myCustomFunction() {
var sourceSpreadsheet = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); //update with your source spreadsheet ID
var sourceSheet = sourceSpreadsheet.getSheetByName('4cat');   //update with your source sheet tab name
var dataRange = sourceSheet.getRange('A:V');        //define the range you want to work with
var rawDataValues = dataRange.getValues()        // extract values from the source spreadsheet
var filteredData = rawDataValues.filter(customFilterFunction); // Store the filtered Data in an array
var destinationSpreadsheet = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); // update with your destination spreadsheet ID
var destSheet = destinationSpreadsheet.getSheetByName('Sheet1'); //update with your destination sheet tab name
destSheet.getRange(2,1,destSheet.getLastRow() - 1,destSheet.getLastColumn()).clear(); // Assuming header is in the first row, clears the sheet while keeping the header
destSheet.getRange(2, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
};

// Modify to destSheet.getRange(1,1,[...] if there's no header row
function customFilterFunction(array) {
  return array[10].toLowerCase() == "ipad";
};

Have you explored the option of using a Spreadsheet formula instead? You could experiment with combining

=IMPORTRANGE(spreadsheet_key, string_range)

and QUERY(data, query, [header]) to import a pre-filtered range:

=QUERY(IMPORTRANGE("1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s", "4cat!A:V"),"SELECT * WHERE Col11 = 'iPad'")

This approach eliminates the need to clear your sheet before importing the filtered data.

Answer 3

Answer №2

Here is an additional code snippet that you can incorporate into your existing code:

filterByText(rawData, 10, iPad);

This function should be placed after your myFunction function in order to work effectively:

function filterByText(data, columnIndex, values) {
    var value = values;
    if (data.length > 0) {
        // Check for valid column index
        if (typeof columnIndex != "number" || columnIndex > data[0].length) {
            throw "Please select a valid column index.";
        }
        var result = [];
        if (typeof value == "string") {
            var regExp = new RegExp(escape(value).toUpperCase());
            for (var i = 0; i < data.length; i++) {
                if ((columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(regExp) != -1) || 
                    (columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(regExp) != -1)) {
                    result.push(data[i]);
                }
            }
            return result;
        } else {
            for (var i = 0; i < data.length; i++) {
                for (var j = 0; j < value.length; j++) {
                    var regExp = new RegExp(escape(value[j]).toUpperCase());
                    if ((columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(regExp) != -1) || 
                        (columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(regExp) != -1)) {
                        result.push(data[i]);
                        j = value.length;
                    }
                }
            }
            return result;
        }
    } else {
        return data;
    }
}

The provided code snippet is sourced from Google Apps Script's ArrayLib library, specifically the filterByText method. I have made some adjustments to the error message for readability.

Your complete code implementation could resemble the following structure:

... (Your existing code here)

Answer 4

Here is an additional code snippet that you can incorporate into your existing code:

filterByText(rawData, 10, iPad);

This function should be placed after your myFunction function in order to work effectively:

function filterByText(data, columnIndex, values) {
    var value = values;
    if (data.length > 0) {
        // Check for valid column index
        if (typeof columnIndex != "number" || columnIndex > data[0].length) {
            throw "Please select a valid column index.";
        }
        var result = [];
        if (typeof value == "string") {
            var regExp = new RegExp(escape(value).toUpperCase());
            for (var i = 0; i < data.length; i++) {
                if ((columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(regExp) != -1) || 
                    (columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(regExp) != -1)) {
                    result.push(data[i]);
                }
            }
            return result;
        } else {
            for (var i = 0; i < data.length; i++) {
                for (var j = 0; j < value.length; j++) {
                    var regExp = new RegExp(escape(value[j]).toUpperCase());
                    if ((columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(regExp) != -1) || 
                        (columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(regExp) != -1)) {
                        result.push(data[i]);
                        j = value.length;
                    }
                }
            }
            return result;
        }
    } else {
        return data;
    }
}

The provided code snippet is sourced from Google Apps Script's ArrayLib library, specifically the filterByText method. I have made some adjustments to the error message for readability.

Your complete code implementation could resemble the following structure:

... (Your existing code here)

Answer 5

Answer №3

If you need help with sorting in a range, check out the documentation available here. You can use the getRange method to select a range and then apply the sort function as shown below:

var range = ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length)
// If you want to sort the entire sheet, use the line below instead of the previous one
// var range = ts.getDataRange()     
range.sort(11)         // Sorting based on column number 11

Edit 1: To filter values based on a specific column for copying into a new sheet, you'll need to eliminate unnecessary data from the sheet.

var rawData = range.getValues()     // Retrieve values from spreadsheet1
var data = []                       // Filtered Data will be stored here
for (var i = 0; i< rawData.length ; i++){
 if(rawData[i][10] == "iPad")            // Check if the value in column K is iPad before proceeding
 {
 data.push(rawData[i])
 }
}
// Now you can paste this filtered array data into your new spreadsheet following the same process.

Edit 2: Here's how your final code should appear,

function myFunction() {
var sss = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); // Replace source ID here
var ss = sss.getSheetByName('4cat'); // Specify the source Sheet tab name
var range = ss.getRange('A:V');      // Define the range to copy
var rawData = range.getValues()     // Obtain values from spreadsheet 1
var data = []                       // Filtered Data will be stored here
for (var i = 0; i< rawData.length ; i++){
if(rawData[i][10] == "iPad")            // Check if column K contains iPad, skip if not
{
data.push(rawData[i])
}
}
var tss = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); // Replace destination ID
var ts = tss.getSheetByName('Sheet1'); // Specify destination Sheet tab name
ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

}

Answer 6

If you need help with sorting in a range, check out the documentation available here. You can use the getRange method to select a range and then apply the sort function as shown below:

var range = ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length)
// If you want to sort the entire sheet, use the line below instead of the previous one
// var range = ts.getDataRange()     
range.sort(11)         // Sorting based on column number 11

Edit 1: To filter values based on a specific column for copying into a new sheet, you'll need to eliminate unnecessary data from the sheet.

var rawData = range.getValues()     // Retrieve values from spreadsheet1
var data = []                       // Filtered Data will be stored here
for (var i = 0; i< rawData.length ; i++){
 if(rawData[i][10] == "iPad")            // Check if the value in column K is iPad before proceeding
 {
 data.push(rawData[i])
 }
}
// Now you can paste this filtered array data into your new spreadsheet following the same process.

Edit 2: Here's how your final code should appear,

function myFunction() {
var sss = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); // Replace source ID here
var ss = sss.getSheetByName('4cat'); // Specify the source Sheet tab name
var range = ss.getRange('A:V');      // Define the range to copy
var rawData = range.getValues()     // Obtain values from spreadsheet 1
var data = []                       // Filtered Data will be stored here
for (var i = 0; i< rawData.length ; i++){
if(rawData[i][10] == "iPad")            // Check if column K contains iPad, skip if not
{
data.push(rawData[i])
}
}
var tss = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); // Replace destination ID
var ts = tss.getSheetByName('Sheet1'); // Specify destination Sheet tab name
ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

}

Filter information by the K column within Google Script Editor on Google Sheets

Answer №1

Answer №2

Answer №3

Similar questions

Placing a moveable object in a designated spot for dropping at the desired location

Generating small image previews in JavaScript without distorting proportions

I kindly request your assistance in resolving the issues with the append() and empty

Can anyone point out where the mistake lies in my if statement code?

What are the appropriate situations for utilizing getStaticPaths()?

The functionality of d3.dispatch() is not performing as anticipated

Guide on transferring data from a component to App.vue in Vue 3, even with a router-view in the mix

Unable to modify the value of an object variable generated from a query in JavaScript, ExpressJS, and MongoDB

After transitioning my Image Modal from an ID to a Class, it appears to not be functioning properly

Incorporating a swisstopo map from an external source into an Angular

Phantom.js: Exploring the Power of setTimeout

Encoding a multidimensional associative array into JSON using PHP

Assign an id attribute in KineticJS once the ajax call is successful

A Promise-based value returned by a Typescript decorator with universal methods

Creating dynamic form fields in Flask WTForm based on user's previous selection is a useful feature that can be achieved with some

Comparing non-blocking setTimeout in JavaScript versus sleep in Ruby

Transmit data to PHP servers

$http({method}) is malfunctioning while $http.get is functioning properly

Enhance multiple select functionality

Encountering a fragment error while utilizing create-react-library