在Google脚本编辑器（Google表格）中按列K过滤数据 [英] Filter data by column K in Google Script Editor (Google Sheets)

问题描述

在这个例子中，我有一组来自Google Sheet（4Cat）的数据，这些数据正在运行我正在运行我的Google Script的另一张表（ImportFeeder）。

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.

在下面的脚本的末尾，如何插入过滤器脚本来按行K对数据进行排序？ （仅显示iPad产品）

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)

< img src =https://i.stack.imgur.com/GqckL.jpgalt =在此处输入图像说明>

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样本数据 https://docs.google.com/spreadsheets/d/1kL96dRm3Z8XBtMXnSzUARxR1b34-njlkZQ1sU0c3g1s/edit?usp=sharing * /

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

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

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

必填 - 成功完成工作表导入，按Google Scripts中的行K过滤数据。在脚本的顶部添加一个clear（）表函数，因为它将每天运行，并且每日导入之前需要清除工作表。

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.

推荐答案

您可以在排序中找到相关文档。范围在这里。要使用getRange对一个范围进行排序，然后按照这样排序函数

You can find the documentation on sorting in a range here. To sort select a range using getRange and then sort function like so

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

编辑1：
要仅根据要复制到新工作表的列过滤值，您需要修剪从中获取的数据一张你不需要的所有值的表格。

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.

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. 

编辑2 ：这是最终代码的样子，

Edit 2: This how the final code should look like,

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);

}

这篇关于在Google脚本编辑器（Google表格）中按列K过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！