In this example, I have a set of data from a Google Sheet (4Cat) feeding into another sheet (ImportFeeder) where I am running my Google Script.
At the end of the script below, how can I insert a filter script to sort the data by Row K? (only showing iPad products)
function myFunction() {
var sss = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); //replace with source ID
var ss = sss.getSheetByName('4cat'); //replace with source Sheet tab name
var range = ss.getRange('A:I'); //assign the range you want to copy
var data = range.getValues();
var tss = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); //replace with destination ID
var ts = tss.getSheetByName('Sheet1'); //replace 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() // get value 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 says 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 sheet to sheet import, filter data by Row K within Google Scripts. Add a clear() sheet function to the top of the script, as this will be running daily and the sheet needing clearing before daily import.
In Google Sheets, open the spreadsheet where you want to create a filter view. Click a cell that has data. Create new filter view. Click a drop-down list in a column header and select the data you want to filter.
Google Sheets Filter Function Syntax the Google Sheet filter formula goes as follows: FILTER(range, condition1, [condition2, …]): range: This is the range of cells that you want to filter. condition1: This is the columns/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES.
Why not using a custom filter function?
Added a .toLowerCase()
to match "ipad" case insensitive.
function myFunction() {
var sss = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); //replace with source ID
var ss = sss.getSheetByName('4cat'); //replace with source Sheet tab name
var range = ss.getRange('A:V'); //assign the range you want to copy
var rawData = range.getValues() // get value from spreadsheet 1
var data = rawData.filter(isColKiPad); // Filtered Data will be stored in this array
var tss = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); //replace with destination ID
var ts = tss.getSheetByName('Sheet1'); //replace with destination Sheet tab name
ts.getRange(2,1,ts.getLastRow() - 1,ts.getLastColumn()).clear(); // Assuming header is in the first row, clears sheet but header
ts.getRange(2, 1, data.length, data[0].length).setValues(data);
};
// Change both to ts.getRange(1,1,[...] if there's no header row
function isColKiPad(arr) {
return arr[10].toLowerCase() == "ipad";
};
Did you consider using a Spreadsheet formula? You could try a combination of =IMPORTRANGE(spreadsheet_key, string_range)
and QUERY(data, query, [header])
to import your range already filtered :
=QUERY(IMPORTRANGE("1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s";"4cat!A:V");"SELECT * WHERE Col11 = 'iPad'")
You won't even need to clear your sheet this way.
you can add this to your original code:
filterByText(rawData, 10, iPad);
It will work as long as you add this function after your myFunction function:
function filterByText(data, columnIndex, values) {
var value = values;
if (data.length > 0) {
if (typeof columnIndex != "number" || columnIndex > data[0].length) {
throw "Please, can you choose a valid column index?";
}
var r = [];
if (typeof value == "string") {
var reg = new RegExp(escape(value).toUpperCase());
for (var i = 0; i < data.length; i++) {
if (columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(reg) != -1 || columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(reg) != -1) {
r.push(data[i]);
}
}
return r;
} else {
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < value.length; j++) {
var reg = new RegExp(escape(value[j]).toUpperCase());
if (columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(reg) != -1 || columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(reg) != -1) {
r.push(data[i]);
j = value.length;
}
}
}
return r;
}
} else {
return data;
}
}
Above is the code used by Google Apps Script's ArrayLib library filterByText method. I literally just copied it and made the throw friendlier.
Your full code could look something like this:
function myFunction() {
var sss = SpreadsheetApp.openById('1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s'); //replace with source ID
var ss = sss.getSheetByName('4cat'); //replace with source Sheet tab name
var range = ss.getRange('A:I'); //assign the range you want to copy
var data = range.getValues();
var tss = SpreadsheetApp.openById('1u7547KaniKHLUJn2v-ezN4l8ZcxE4viCFcoxsA904MI'); //replace with destination ID
var ts = tss.getSheetByName('Sheet1'); //replace 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() // get value from spreadsheet 2
var data = filterByText(rawData, 10, iPad); // rawData is now sorted.
range.clear();
var powerRange = ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length);
powerRange.setValues(data);
}
function filterByText(data, columnIndex, values) {
var value = values;
if (data.length > 0) {
if (typeof columnIndex != "number" || columnIndex > data[0].length) {
throw "Please, can you choose a valid column index?";
}
var r = [];
if (typeof value == "string") {
var reg = new RegExp(escape(value).toUpperCase());
for (var i = 0; i < data.length; i++) {
if (columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(reg) != -1 || columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(reg) != -1) {
r.push(data[i]);
}
}
return r;
} else {
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < value.length; j++) {
var reg = new RegExp(escape(value[j]).toUpperCase());
if (columnIndex < 0 && escape(data[i].toString()).toUpperCase().search(reg) != -1 || columnIndex >= 0 && escape(data[i][columnIndex].toString()).toUpperCase().search(reg) != -1) {
r.push(data[i]);
j = value.length;
}
}
}
return r;
}
} else {
return data;
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With