0 votes
in Education by (1.7m points)
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 questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
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!
...