Filter data from one sheet to another

Question

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

I would like to:

1. Take all the data from one sheet and put it into an array.
2. Pick out certain information from that array, and add it to a new array.
3. Save that new, filtered array to a different sheet.

Here's what I have:

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

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:

Incorrect range width, was 1493 but should be 1

What changes will fix these problems?

Answer 1

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

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

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

Incorrect range width, was 1493 but should be 1

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

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

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.

Updated code

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