CODEWITHSUNDEEP
javascriptgoogle-apps-script
bop-a-nator
bop-a-nator

Reputation: 111

Google apps script select specific rows with specific columns

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.

Upvotes: 2

Views: 8302

Answers (3)

bop-a-nator
bop-a-nator

Reputation: 111

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!

Upvotes: 4

Cooper
Cooper

Reputation: 64110

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:

enter image description here

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

Upvotes: 0

ra89fi
ra89fi

Reputation: 1245

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

Upvotes: 0

Related Questions