Javascript 在 Google 脚本编辑器（Google 表格）中按 K 列过滤数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43522602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filter data by column K in Google Script Editor (Google Sheets)
提问by me9867
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.
在此示例中，我将一组来自 Google 工作表 (4Cat) 的数据输入到另一个工作表 (ImportFeeder) 中，我在其中运行我的 Google 脚本。
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)
在下面脚本的末尾，如何插入过滤器脚本以按第 K 行对数据进行排序？（仅展示 iPad 产品）
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)
（无法从 undefined 读取属性长度）
4Cat Sample Data https://docs.google.com/spreadsheets/d/1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s/edit?usp=sharing*/
4Cat 示例数据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
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.
必需- 成功导入工作表，在 Google Scripts 中按第 K 行过滤数据。在脚本顶部添加一个 clear() 工作表函数，因为这将每天运行，并且在每天导入之前需要清除工作表。
采纳答案by Hyman Brown
You can find the documentation on sorting in a range here. To sort select a range using getRange and then sort function like so
您可以在此处找到有关按范围排序的文档。使用 getRange 对选择一个范围进行排序，然后像这样对函数进行排序
var range = ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length)
// Use the below line instead, if you want to sort whole sheet, not just the newly entered data!
// var range = ts.getDataRange()
range.sort(11) // sort based on column number 11
Edit 1:To filter only values based on the column to copy into the new sheet you will do need to trim the data you get from a sheet of all values you don't need.
编辑 1：要仅根据要复制到新工作表中的列过滤值，您需要修剪从您不需要的所有值的工作表中获得的数据。
var rawData = range.getValues() // get value from spreadsheet1
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])
}
}
// Now you can paste array data to your new spreadsheet like before.
Edit 2: This how the final code should look like,
编辑 2：这最终代码应该是什么样子，
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 = [] // 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])
}
}
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);
}
回答by k4k4sh1
Why not using a custom filter function?
Added a .toLowerCase()to match "ipad" case insensitive.
为什么不使用自定义过滤器功能？添加了一个.toLowerCase()不区分大小写的匹配“ipad”。
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 :
您是否考虑过使用电子表格公式？您可以尝试组合=IMPORTRANGE(spreadsheet_key, string_range)和QUERY(data, query, [header])导入您已经过滤的范围：
=QUERY(IMPORTRANGE("1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s";"4cat!A:V");"SELECT * WHERE Col11 = 'iPad'")
You won't even need to clear your sheet this way.
您甚至不需要以这种方式清理工作表。
回答by Antoine Colson
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:
只要您在 myFunction 函数之后添加此函数，它就会起作用：
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 codeused by Google Apps Script's ArrayLib library filterByText method. I literally just copied it and made the throw friendlier.
以上是Google Apps Script 的 ArrayLib 库filterByText 方法使用的代码。我实际上只是复制了它并使投掷更友好。
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;
}
}
