Copy/Push data from array into a sheet range one r

问题:

I've a multi-dimensional array sheetValues[][] created using .getValues() on the origination sheet. I'd like to copy the values from the sheetValues array into the destination sheet. How could I push the contents of each row of the sheetValues array into the destination sheet?

What function allows me to push each row of the array, one at a time (after checking for an IF condition) into the corresponding range of the destination sheet?

Here is the code snippet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Source Sheet"); //Source sheet that has all data
var lastRow = sheet.getLastRow(); //last row number of Source sheet
var sheetValues = sheet.getRange(1, 1, lastRow, 18).getDisplayValues; //Copy Display values to an array; getDisplayValues() is used instead of getValues() to ignore formulas in the source cells such as "IMPORTRANGE"
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetbyName('Target Sheet'); //destination sheet
var currentRow = targetSheet.getLastRow(); //current last row of the destination sheet (start copying after this row)
var target = targetSheet.getRange(currentRow+1, 1); //target range where the copying of data should begin in destination sheet

loop through each row of the array (sheetValues) to check if the status (column I) is 'On Target'; if yes, copy that entire row to the target range defined above
for (var i = 0; i <= lastRow; i++) {
  if (sheetValues[i][9] == 'On Target') {
    How do I copy/push the entire row at once (column 1 - 18) directly into the target range (currentRow, columns 1-18)? What function do I use?
  }
  currentRow++;
}

回答1:

Never 'push each row of the array, one at a time". This is clunky code and extremely slow to boot. Setting values it very similar to getting values. You declare a range variable with appropriates coordinates that fit the dimensions of your values[][] array, and pass the entire values array to 'setValues()' method of the Range object.

One of method signatures for getRange() is the following:

sheet.getRange(row, column, numberOfRows, numberOfColumns);

More info https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns

The first 2 arguments are for the upper leftmost cell in a range while others define the number of rows and columns your range should span.

Presuming you'd like to start inserting values from the 1st cell in the target sheet, here's the single line of code that you need.

sheet.getRange(1, 1, sheetValues.length, sheetValues[0].length);

The number of rows is the length of your array, the number of columns is the length of the first element(row) of the array.

UPDATE

I missed the comment buried in your code. You can filter values before writing them to the target sheet.

sheetValues = sheetValues.map(function(row){

if (row[9] == 'Target') { return row;  }

});