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)
