I am trying to write a script to copy a range of cells to cell B2. The range to copy from changes each time so want to highlight the top left cell and then the script takes it from there. What I have so far is:

function CopyToB2() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var cellRange = sheet.getActiveCell();
var selectedColumn = cellRange.getColumn();
var selectedRow = cellRange.getRow();
var range = SpreadsheetApp.getActiveSheet().getRange(selectedColumn,selectedRow,2,43);
range.setValues(range);
spreadsheet.getRange('B2').activate();
(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

but get the error

Exception: The parameters (SpreadsheetApp.Range) don't match the method signature for SpreadsheetApp.Range.setValues

CodePudding user response：

Explanation:

• The main issue with your code is that you pass a range object in the setValues function and therefore you are getting this error.

• A second issue is that you passed the parameters of the getRange(row, column, numRows, numColumns) function in the wrong order. It should be:

  getRange(selectedRow,selectedColumn,43,2)
  

instead of getRange(selectedColumn,selectedRow,2,43)

• A third issue is that you need to define the range that the data will be pasted to and that can't be just a single cell B2 in this case. What you need to do is to define B2 as the start of your pasting range and then set the values of your active range.

Solution:

function CopyToB2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var cellRange = sheet.getActiveCell();
  var selectedColumn = cellRange.getColumn();
  var selectedRow = cellRange.getRow();
  var dataRange = sheet.getRange(selectedRow,selectedColumn,43,2).getValues();
  sheet.getRange(2,2,dataRange.length,dataRange[0].length).setValues(dataRange);
};