Why can't I copy ranges between spreadsheets with copyValuesToRange()?

Question

I am able to copy a range of values from one spreadsheet to another with Range.setValues(values) but not with Range.copyValuesToRange(sheet, column, columnEnd, row, rowEnd):

function test()
{
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var sourceCols = sourceSheet.getRange("A1:B10");

  // Copying the first two columns this way works.
  var sheet1 = SpreadsheetApp.create("New Spreadsheet 1").getActiveSheet();
  sheet1.getRange("A1:B10").setValues(sourceCols.getValues());

  // Copying the first two columns this way does not work.
  var sheet2 = SpreadsheetApp.create("New Spreadsheet 2").getActiveSheet();
  sourceCols.copyValuesToRange(sheet2, 1, 2, 1, 10);
}

Specifically, after running the code, New Spreadsheet 1 contains range A1:B10 of the source spreadsheet, while New Spreadsheet 2 is empty. Both have a single sheet named "Sheet1". The source spreadsheet is unmodified.

What am I doing wrong? I'm wondering if the failing method only works within a single spreadsheet, but I see nothing in the documentation about that.

The reason I want to use Range.copyValuesToRange() is so I can also copy the formatting with Range.copyFormatToRange().

Is this will be helpful to you? https://stackguides.com/questions/16044580/copy-a-complete-list-on-one-spreadsheet-to-append-on-the-bottom-of-another-sprea — Tanaike

Unknown Unknown · Accepted Answer · 2017-01-10T22:41:16

The methods copyValuesToRange and copyFormatToRange, as well as Range.copyTo, are restricted to copying within the same spreadsheets, although documentation is not explicit about that. A workaround which allows you to copy between spreadsheets with formatting is to copy the entire sheet to the destination spreadsheet, using Sheet.copyTo method. Then copy the required range from there, and optionally, delete the sheet. Like this:

function cc() {
  var ss1 = SpreadsheetApp.openByUrl('some url');
  var ss2 = SpreadsheetApp.openByUrl('another url');
  var sheet1 = ss1.getSheetByName('name');
  var sheet2 = ss2.getSheetByName('another name');
     // the above was the setup, main code begins here
  var copySheet = sheet1.copyTo(ss2);
  copySheet.getRange("A1:B2").copyTo(sh2.getRange("C1:D2"));
  ss2.deleteSheet(copySheet);
}

Why can't I copy ranges between spreadsheets with copyValuesToRange()?

1 Answers