I am trying to figure out how to pull only specific rows and columns using Google apps script. The =QUERY is not an option to use. Need to keep all of the logic in the script - my actual data set is quite large. To illustrate what I'm trying to solve. I have a little table of test data. TestData From which I only want columns 2,3,5 (zero based index) and only the rows with "fur".

function testFour(sheetID, fromTabName, toTabName) {
  var sourceTab = SpreadsheetApp.openById(sheetID).getSheetByName(fromTabName);
  var values = sourceTab.getDataRange().getValues();
  var columns = [2,3,5]; //only want these columns
  var output = new Array();
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[0].length; j++) {
      if (values[i][4] == "fur") { // only want these rows
        if (j in columns) {
          output.push(values[i][j]);
       }
      }
    }
  }
  var targetTab = SpreadsheetApp.openById(sheetID).getSheetByName(toTabName);
  targetTab.getRange(1, 1, output.length, columns.length).setValues(output);
}

This code bit works but it is not very maintainable if there are many columns:

for(n=0; n < values.length; ++n) {
  var columns = [2,3,5];
  if (values[n][4] === 'fur') { 
    output.push( [values[n][2], values[n][3], values[n][5]]); 
  }

When I run the testFour function I get an error about "Cannot covert Array to Object[][] on this line of the code:

targetTab.getRange(1, 1, output.length, columns.length).setValues(output);

Appreciate any help. I was really surprised to not have found any examples of this. Every solution was using the =QUERY on the sheet.

I am trying to figure out how to pull only specific rows and columns using Google apps script. The =QUERY is not an option to use. Need to keep all of the logic in the script - my actual data set is quite large. To illustrate what I'm trying to solve. I have a little table of test data. TestData From which I only want columns 2,3,5 (zero based index) and only the rows with "fur".

function testFour(sheetID, fromTabName, toTabName) {
  var sourceTab = SpreadsheetApp.openById(sheetID).getSheetByName(fromTabName);
  var values = sourceTab.getDataRange().getValues();
  var columns = [2,3,5]; //only want these columns
  var output = new Array();
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[0].length; j++) {
      if (values[i][4] == "fur") { // only want these rows
        if (j in columns) {
          output.push(values[i][j]);
       }
      }
    }
  }
  var targetTab = SpreadsheetApp.openById(sheetID).getSheetByName(toTabName);
  targetTab.getRange(1, 1, output.length, columns.length).setValues(output);
}

This code bit works but it is not very maintainable if there are many columns:

for(n=0; n < values.length; ++n) {
  var columns = [2,3,5];
  if (values[n][4] === 'fur') { 
    output.push( [values[n][2], values[n][3], values[n][5]]); 
  }

When I run the testFour function I get an error about "Cannot covert Array to Object[][] on this line of the code:

targetTab.getRange(1, 1, output.length, columns.length).setValues(output);

Appreciate any help. I was really surprised to not have found any examples of this. Every solution was using the =QUERY on the sheet.

• javascript
• google-apps-script

• Which code is giving you the error? – Cooper Commented Mar 5, 2019 at 17:24
• The code in the "testFour" function that I put in the main part of the post. It errors on the line indicated above with the error stated. – bop-a-nator Commented Mar 5, 2019 at 17:42
• You can look at my code below. I used an intermediate are to collect the terms and then pushed that whole array into the output array. The array needs to look like [ [column items],[column items],[column items]...] where each internal array is a row. – Cooper Commented Mar 5, 2019 at 18:00
• Do not answer your question in the question. That's not where an answer belongs. – tehhowch Commented Mar 20, 2019 at 23:17

3 Answers 3

Based on Cooper's suggestion above this was how I was able to get this to work:

function testFourNew(sheetID, fromTabName, toTabName) {
  var sourceTab = SpreadsheetApp.openById(sheetID).getSheetByName(fromTabName);
  var targetTab = SpreadsheetApp.openById(sheetID).getSheetByName(toTabName);
  var srcrg = sourceTab.getDataRange();
  var srcvA = srcrg.getValues();
  var desvA=[];
  // var columns = [2,3,5]; //only want these columns
  var columns = String("2,3,5").split(','); //only want these columns
  var tstVal = "fur"; 
  var tstCol = 4; 
  for (var i=0;i<srcvA.length;i++) {
    var tA=[];
    if (srcvA[i][tstCol] ==tstVal) {
      for (var j=0;j<columns.length;j++) {
        //Logger.log(srcvA[i][columns[j]]);
        tA.push(srcvA[i][columns[j]]);
      }
      desvA.push(tA);
    }
  }
  targetTab.getRange(1, 1, desvA.length, desvA[0].length).setValues(desvA);
}

Thank you Cooper your direction and suggestions!

EDIT:

Don't need [] around output if using this line to push to output -

output.push( [values[n][2], values[n][3], values[n][5]]);

To set values by row, data has to be in this format -

[ row,
  row ]

or,

[ [1,2,3],
  [4,5,6] ]

Cannot covert Array to Object[][] error is shown when range and value format do not match.

Try this line -

targetTab.getRange(1, 1, output.length, columns.length).setValues([output]);

Report Builder

I'm guessing that you want to use this function to build different reports from the same dataset. So I might try something like this:

The Function:

function testFour(t4Obj) {
  var ss=SpreadsheetApp.openById(t4Obj.ssId);
  var srcsh=ss.getSheetByName(t4Obj.srcShName);
  var dessh=ss.getSheetByName(t4Obj.desShName);
  var colA=String(t4Obj.zbcols).split('~~~');
  var tstCol=Number(t4Obj.zbtstCol);
  var tstVal=t4Obj.tstVal;
  var srcrg=srchsh.getDataRange();
  var srcvA=srcrg.getValues();
  var desvA=[];
  for (var i=0;i<srcvA.length;i++) {
    var tA=[];
    if (srcVa[i][tstCol]==tstVal) {
      for (var j=0;j<colA.length;j++) {
        tA.push(colA[j]);
      }
      desvA.push(tA);
    }
  }
  dessh.getRange(1, 1, desvA.length, desvA[0].length).setValues(desvA);
}

The t4Obj Data Table:

Of course you would have to build yourself a function that loads tthe t4Obj from the above table. From looking at your code, I'd guess that will be no problem for you.

And please note I have not tested any of this code so it is extremely unlikely to work the first time out of the box. You can sharpen your debugging skills on it.

Let's try making a array for a square range where the number incremented in by one in the same column position down each row. The array would look like this:

var A=[[1,6,11,16,21],[2,7,12,17,22],[3,8,13,18,23],[4,9,14,19,24][5,10,15,20,25]];

Make an Array by hand

function makeArray() {
  var A=[[1,6,11,16,21],[2,7,12,17,22],[3,8,13,18,23],[4,9,14,19,24],[5,10,15,20,25]];
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(5,5,5,5);//could be this way
  //var rg=sh.getRange(5,5,A.length,A[0].length);//or in most programs this way
  rg.setValues(A);
}