将数据从一张纸过滤到另一张纸 [英] Filter data from one sheet to another

问题描述

我是阵列的新手，发现自己已经走到了尽头。

I am new to arrays and have found myself at a dead end.

我想：

1. 从一张工作表中取出所有数据，并将其放入一个数组中。


2. 从该数组中选取某些信息，并将其添加到新数组中。


3. 将经过过滤的新数组保存到其他工作表中。

这是我的功能：

function myFunction() 
{
  var ss = SpreadsheetApp.openById('ID');
  var sheet1 = ss.getSheetByName('sheet1');
  var sheet2 = ss.getSheetByName('sheet2');
  var range = sheet1.getDataRange();
  var values = range.getValues();
  var lastRow = sheet1.getLastRow();
  var lastCol = sheet1.getLastColumn();      
  var rowCounter = 0;
  var inArr = new Array(new Array());
  var outArr = []
  for (var i = 1; i <= lastRow; i++) 
  {
     var suspended = values[i][39]; //this value is ether True or False
     if (suspended == 'False')
     {
        rowCounter = rowCounter +1;
        outArr[rowCounter] = [];
        inArr[i] = values[i];
        for (var j = 0; j <= 42; j++) 
        {        
           outArr[rowCounter].push(inArr[i][j]);
      }
   }
}
sheet2.getRange(1, 1,rowCounter,lastCol).setValues([outArr]);
}

inArr数组正在收集整行，但我只想收集该行中的特定列：

The inArr array is collecting the entire row, but I would like to collect only specific columns in that row:

values[i][0], values[i][26], values[i][28]

此外，当我尝试将数组追加到工作表时，也会收到错误消息：

Also when I try to append the array to the sheet I get an error:

范围宽度不正确，是1493，但应为1

Incorrect range width, was 1493 but should be 1

哪些更改将解决这些问题？

What changes will fix these problems?

推荐答案

inArr数组正在收集整行，我只想收集该行中的特定列

The inArr array is collecting the entire row I would like to collect only specific columns in that row

如果 values 是我们的完整数据数组，而 values [i] 是第i行，我们可以这样获得对应的精简行：

If values is our complete data array, and values[i] is the i-th row, we can get the corresponding reduced row like this:

var newRow = [ values[i][0], values[i][26], values[i][28] ];

创建一维数组， newRow ，其中包含值[i] 中的特定元素。如果我们对许多值执行此操作，那么代码行将变得很长且难以维护。相反，我们可以使用另一个数组来包含我们感兴趣的列的索引，并对其进行循环：

That creates a one-dimensional array, newRow, with specific elements from values[i]. If we were doing this for many values, the line of code would get long and hard to maintain. We can instead use another array to contain the indexes of columns we're interested in, and loop over them:

var outColumns = [0,26,28];   // Column indexes we want
for (var j=0; j < outColumns.length; j++) {
  newRow.push( values[i][outColumns[j]] );
}

范围宽度不正确，为1493，但应为1

Incorrect range width, was 1493 but should be 1

此处，范围是（1,1） （rowCounter，lastCol），显然是1493列宽，并且正在使用包含一个元素的数组调用 setValues（）：

Here, you have a range from (1,1) to (rowCounter,lastCol), apparently 1493 columns wide, and are calling setValues() with an array containing one element:

sheet2.getRange(1, 1,rowCounter,lastCol).setValues([outArr]);

单个元素是因为您已包含 outArr 大括号中的数组，使其成为具有一个元素的一维数组（恰好是二维数组）。

The single element is because you've enclosed the outArr array in braces, making it a one-dimensional array with one element (which happens to be a 2-D array).

编写一个消除不需要单独计算行和列的矩形数组：

There is a simple pattern for writing a rectangular array that eliminates any need to separately calculate rows and columns:

sheet2.getRange(1, 1, outValues.length, outValues[0].length)
      .setValues(outValues);

通过使用JavaScript数组 .length（）在整个数组（行）和第一行（列）上的方法，我们总是得到要设置的数组的正确大小。

By using the JavaScript array .length() method on the whole array (rows) and first row (columns), we always get the correct size of the array being set.

function newFunction() {
  var ss = SpreadsheetApp.openById('ID');
  var sheet1 = ss.getSheetByName('sheet1');
  var sheet2 = ss.getSheetByName('sheet2');
  var range = sheet1.getDataRange();
  var values = range.getValues();  
  var outValues = [];
  var outColumns = [0,26,28];   // Column indexes we want
  var suspendedColumn = 39;

  for (var i=0; i < values.length; i++) {
    var newRow = [];
    var suspended = values[i][suspendedColumn]; //this value is ether True or False
    if ('False' === suspended)
    {
      // Filter values using outColumns
      for (var j=0; j < outColumns.length; j++) {
        newRow.push( values[i][outColumns[j]] );
      }
      outValues.push(newRow);
    }
  }
  // Write selected data to sheet2
  sheet2.getRange(1, 1, outValues.length, outValues[0].length)
        .setValues(outValues);
}

这篇关于将数据从一张纸过滤到另一张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！