after my last question I'm facing a problem with copying rows.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('ws1');
var startRow = 4;
var lastRow = sheet.getLastRow();
var numRows = lastRow - startRow + 1;
var lastCol = sheet.getLastColumn();
var dataSetValues = sheet.getRange(startRow, 1, numRows, lastCol).getValues();
for (var i = 0; i < numRows; i++){
let fVal = dataSetValues[i][5];
let gVal = dataSetValues[i][6];
let sum = +fVal + +gVal;
if (sum > 115) {
let row = dataSetValues[i];
}
}
What do I expect?
I wish set which columns to copy
I edited the code like this
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('ws1');
var startRow = 4;
var lastRow = sheet.getLastRow();
var numRows = lastRow - startRow + 1;
var lastCol = sheet.getLastColumn();
var dataSetValues = sheet.getRange(startRow, 1, numRows, lastCol).getValues();
for (var i = 0; i < numRows; i++){
let aVal = dataSetValues[i][0]; let bVal = dataSetValues[i][1]; // + other columns
let fVal = dataSetValues[i][5];
let gVal = dataSetValues[i][6];
let sum = +fVal + +gVal;
if (sum > 115) {
let row = dataSetValues[i];
var ssDest = spreadsheet.getSheetByName('ws2'); var rngDest = ssDest.getRange(ssDest.getLastRow()+1,1); //start copy rngDest.setValues(row)
}
}
I get this error
The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues
Thanks
1. It is important to note that the setValues() method accepts as parameter a two dimensional array in the form of Object[][].
You are simply passing it a one-dimensional array, hence the The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues error you are receiving.
In order to fix this, you will have to transform row into a 2 dimensional array and making the following changes
From
rngDest.setValues(row)
To
rngDest.setValues([row])
2. You will have to specify exactly the number of rows and the number of columns expected in the destination range.
After making the change above, you will end up running into a The number of columns in the data does not match the number of columns in the range error which is again expected. This is due to the fact that the getRange method will also need the number of rows and the number of columns such that when using setValues it will know exactly the structure of the data to set.
If you take a look at the getRange method:
getRange(row, column, numRows, numColumns)Returns the range with the top left cell at the given coordinates with the given number of rows and columns.
In order to fix this, a simple change has to be made in order to indicate exactly the number of rows and the number of columns:
From
var rngDest = ssDest.getRange(ssDest.getLastRow()+1,1)
To
var rngDest = ssDest.getRange(ssDest.getLastRow() + 1, 1, 1, row.length);
As you can see, the number of rows here is 1 (as you are copying the data one row at a time) and the number of columns is equal to row.length (as the row variable has all the values corresponding to one row at a time).