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

问题描述

在此示例中，我将一组来自 Google 工作表 (4Cat) 的数据输入到另一个工作表 (ImportFeeder) 中，我在其中运行 Google 脚本.

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)

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])
}
}
}

(无法从 undefined 读取属性长度)

(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. 

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

}

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