Copy a range from another spreadsheet to another

Question

I stumbled upon a code here a while ago which works to copy a range from one sheet to another within the same spreadsheet.

var source_sheet = ss.getSheetByName("Sheet1");
var target_sheet = target.getSheetByName("Sheet2");

var url = "#gid=" + newSheet.getSheetId()

var source_range = source_sheet.getActiveRange();
source_range = source_sheet.getRange(source_range.getRow(), 1, 1, 3);
source_sheet.getRange("I" + source_range.getRow()).setFormula('=IFERROR(Sheet2!C4," ")')
source_sheet.getRange("K" + source_range.getRow()).setFormula('=HYPERLINK("' + url + '","Generated Sheet")')
var target_range = target_sheet.getRange("B4");

source_range.copyTo(target_range);

The script works as intended but now I want to copy from one spreadsheet to another. And when I set the a sheet from a different spreadsheet as the target sheet, I get the error of source and target must be within the same spreadsheet.

Therefore, is there any workaround for this? I was think maybe using importrange, but how do I specify the URL and that selected range in the source range to be imported using the insert function script. If you can think of any other solution, please do let me know.

Do you want to just make a copy of the spreadsheet or do you actually want importrange which is for viewing the original source on another workbook? importrange and copying the spreadsheet are two totally different things... — CodeCamper
You can copy the complete sheet from one spreadsheet to another. Then copy the range that you want to the final destination and delete the sheet. copyTo(Spreadsheet) — Cooper
Not the whole spreadsheet. Just a range. I'm aware it's different but I just thought I could use that method because it seemed like I can't just 'copy' the range. — HM Production
@Cooper, That's an interesting idea that I think is workable. Can't believe I didn't think of that. I should give it a try. — HM Production

Tedinoz Tedinoz · Accepted Answer · 2019-11-20T02:54:04

source_range.copyTo(target_range);

You're getting this error message because copyTo() method is based on a range AND assumes that the source and target are in the same spreadsheet.

The more straightforward method to copy a range is to use setValues, even though there are two spreadsheets and two different sheets. The following script demonstrates this.

function so5894550501() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sourcesheetname = "sourcesheet";
  var source = ss.getSheetByName(sourcesheetname);

  var targetid = "<insert targetid>";
  var ts = SpreadsheetApp.openById(targetid);
  var targetsheetname = "targetsheet";
  var target = ts.getSheetByName(targetsheetname);

  var sourceRange = source.getRange(1,1,source.getLastRow(),source.getLastColumn());
  Logger.log("the source range = "+sourceRange.getA1Notation())
  var sourceValues = sourceRange.getValues();
  Logger.log(sourceValues);
  Logger.log(sourceValues.length)


  var targetRange = target.getRange(1,1,source.getLastRow(),source.getLastColumn());
  targetRange.setValues(sourceValues);// this works

}

Copy a range from another spreadsheet to another

1 Answers