CODEWITHSUNDEEP
javascriptgoogle-sheets
Zimox
Zimox

Reputation: 69

Copy rows after loop

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

Upvotes: 1

Views: 73

Answers (1)

ale13
ale13

Reputation: 6072

Your script just needs a few changes made to it:

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).

Reference

Upvotes: 1

Related Questions